Pages

Advertisement

Thursday, May 2, 2019

Oracle Database - 19c DB Creation

 
In this blog I will discuss on how to create a database after your installation. 
My previous blog discussed on database binaries installation for Oracle Database. 

 In this blog I will continue from the last point and create a database. 
$  cd /u01/app/oracle/product/193/dbhome_1/bin
$ ./dbca
















This completes the database creation.

Oracle Database - 19c: On Premise Installation - Oracle Linux (7.5) - Part 2

 
The second part of the blog discusses on how to install 19c once all the pre-req are complete. 
Download the required software from 
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

 Make sure to accept the license agreement before downloading. 

 Once you have the software then Create the directories

 [As root]
mkdir /u01

 chown oracle:oinstall /u01

 [Connect as user - oracle]
mkdir -p /u01/app/oracle/product/193/dbhome_1

 Copy the Downloaded file to /u01/app/oracle/product/193/dbhome_1

 unzip -qq LINUX.X64_193000_db_home.zip

 Working Directory = /u01/app/oracle/product/193/dbhome_1
Run - runInstaller and follow the steps as in the Screenshots
(./runInstaller)











At this point you will be prompted for root script execution.
Run the root Scripts as follows.



Script 1
[root@oraclebox19c /]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

 Changing groupname of /u01/app/oraInventory to oinstall.

The execution of the script is complete.
Script 2
[root@oraclebox19c /]# /u01/app/oracle/product/193/dbhome_1/root.sh
Performing root user operation.

 The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/193/dbhome_1

 Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Oracle Trace File Analyzer (TFA - Standalone Mode) is available at :
    /u01/app/oracle/product/193/dbhome_1/bin/tfactl

 Note :
1. tfactl will use TFA Service if that service is running and user has been granted access
2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed


Press okay after complete and Close to complete the installation. 

Oracle Database - 19c: On Premise Installation - Oracle Linux (7.5) - Part 1

 
In this blog I discuss installation of Oracle 19c on premise. 

 The part 1 covers the pre-requisites installation and part 2 covers the installation. 

 Pre-requistites

 The easy and fast way of covering the preq-reqs on Oracle Linux is to use Oracle validated rpm for 19c Linux. 

 On a fresh installation of Oracle Linux (Linux oraclebox19c.novalocal 4.1.12-112.16.4.el7uek.x86_64 )
This is version 7.5 Oracle Linux
[root@orclbox19c ~]# yum install oracle-database-preinstall-19c.x86_64 -y

 You can find the changes it did from /var/log/oracle-database-preinstall-19c/results
and backup of files from /var/log/oracle-database-preinstall-19c/backup

 Swap Creation for both Manual and automated pre-req completion - http://o-dba.blogspot.com/2018/05/oracle-database-12c19c-swap-swapon.html
Manual Pre-reqs Install
1. Install required RPMs as per your Operating System and Kernel version & support. https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/database-installation-guide-linux.pdf
2. User Creation


  • groupadd -g 54321 oinstall
  • groupadd -g 54322 dba
  • useradd -g 54321 -G dba -u 54321 oracle

3. Create file - /etc/sysctl.d/99-oracle-database-preinstall-19c-sysctl.conf

  • For shmmax - 90% of memory in KB
  • For shmmall - shmmax/4096
  • For vm.nr_hugepages = 80% of memory in MB / 2
  • fs.file-max = 6815744
  • kernel.sem = 250 32000 100 128
  • kernel.shmmni = 4096
  • kernel.shmall = 1073741824
  • kernel.shmmax = 4398046511104
  • kernel.panic_on_oops = 1
  • net.core.rmem_default = 262144
  • net.core.rmem_max = 4194304
  • net.core.wmem_default = 262144
  • net.core.wmem_max = 1048576
  • net.ipv4.conf.all.rp_filter = 2
  • net.ipv4.conf.default.rp_filter = 2
  • fs.aio-max-nr = 1048576
  • net.ipv4.ip_local_port_range = 9000 65500
  • vm.nr_hugepages = 13100

4. Create file - /etc/security/limits.d/oracle-database-preinstall-19c.conf

  • oracle   soft   nofile    1024
  • oracle   hard   nofile    65536
  • oracle   soft   nproc    16384
  • oracle   hard   nproc    16384
  • oracle   soft   stack    10240
  • oracle   hard   stack    32768
  • ## oracle-database-preinstall-19c setting for memlock soft/hard limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90% of RAM
  • oracle   hard   memlock    134217728
  • oracle   soft   memlock    134217728
  • Disable Transparent Huge Pages

5. Add NOZEROCONF parameter in network configuration in file /etc/sysconfig/network
6. Disabling Transparent Hugepages - Refer Oracle Note:1557478.1
7. Disabling defrag - Refer Oracle Note:1557478.1

Tuesday, April 16, 2019

Oracle Database: RAC 2 Nodes - Standby and DG Broker Build - Part 2

 
This blog is in continuation with my previous blog to build Standby and DG broker. 
In this blog I do the setup of Dataguard and DG Broker.
Step 1 - Add Standby Redo log files (Optional and on both primary and Standby)
Standby redo logfiles' count is n+1 where n = total redo logfiles per thread
Syntax is 
alter database add standby logfile thread <num> group <num> ('+DATA','+FRA' ) size 1024M;

 Step 2 - Create Directories for DG Broker Configuration
[Node 1 - grid user - Primary Cluster]
asmcmd mkdir +FRA/MYDBPROD/DATAGUARDCONFIG
asmcmd mkdir +DATA/MYDBPROD/DATAGUARDCONFIG

 [Node 1 - grid user - StandbyCluster ]
asmcmd mkdir +FRA/MYDBSTDBY/DATAGUARDCONFIG
asmcmd mkdir +DATA/MYDBSTDBY/DATAGUARDCONFIG

Step 3 - Setup Dataguard configuration
[Node 1 - oracle user - Standby Cluster]
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MYDBPROD,MYDBSTDBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MYDBPROD ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYDBPROD' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set fal_server=MYDBPROD scope=both sid='*';

 [Node 1 - oracle user - Primary Cluster]
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MYDBPROD,MYDBSTDBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MYDBSTDBY ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYDBSTDBY' scope=both sid='*';
alter system set fal_server=MYDBSTDBY scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';

Step 4 - Setup DG Broker configuration
[Node 1 - oracle user - Primary Cluster]
dgmgrl /
CREATE CONFIGURATION 'DG_Config' AS PRIMARY DATABASE IS 'MYDBPROD' CONNECT IDENTIFIER IS MYDBPROD;
show configuration;
ADD DATABASE 'MYDBSTDBY' AS CONNECT IDENTIFIER IS 'MYDBSTDBY';
show configuration;

Enable configuration;

Step 5 - Verify Configuration and Status
[Node 1 - oracle user - Primary Cluster]
dgmgrl /
show configuration;
show database verbose 'MYDBPROD';
show database verbose 'MYDBSTDBY';
show instance verbose 'MYDBPR11' on database 'MYDBPROD';
show instance verbose 'MYDBPR12' on database 'MYDBPROD';
show instance verbose 'MYDBPR11' on database 'MYDBSTDBY';

show instance verbose 'MYDBPR12' on database 'MYDBSTDBY';

 This completes the setup of Dataguard and DG Broker

Oracle Database: RAC 2 Nodes - Standby and DG Broker Build - Part 1

 
In this blog I show how to configure Dataguard Broker configuration for a 2-node RAC cluster to a 2-Node Standby.
This is a 2 blog series - you can find the second blog here - which only talks about configuration of Dataguard and Broker.

 Here is my configuration
Production DB
Unique Name - MYDBPROD
 - Instance 1 - MYDBPR11
 - Insance 2 - MYDBPR12
Unqiue Name - MYDBSTDBY
 - Instance 1 - MYDBPR11
 - Insance 2 - MYDBPR12
Step 1 - Create tnsnames.ora entries in all nodes
MYDBPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYPRODDB-PRD-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBPROD)
        (UR = A)
    )
  )

 MYDBSTDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYSTDBYDB-PRD-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBSTDBY)
        (UR = A)
    )
  )
Step 2 - Static Listener Entries for DG broker operations
[Node 1 - Grid user Standby Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
 )
lsnrctl stop
lsnrctl start

 [Node 1 - Grid user Standby Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR12)
  )
 )

lsnrctl stop 
lsnrcl start

 [Node 1- Grid user - Primary Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBPROD_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
 )
lsnrctl stop
lsnrctl start

 [Node 2 - Grid user - Primary Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBPROD_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR12)
  )
 )

 lsnrctl stop
lsnrctl start


Step 3 - Reset sys and system password and copy password file from Node 1 of Primary to other 3 nodes (in $ORACLE_HOME/dbs), also change the name as per the name of the instance

 Step 4 -  Add Database and Instance in OCR registry 
[Node 1 - oracle user - Standby Cluster]
srvctl add database -d MYDBSTDBY -o /opt/oracle/product/112/db -c RAC \
-p +DATA/MYDBSTDBY/spfileMYDBSTDBY.ora -r PHYSICAL_STANDBY -s MOUNT -t IMMEDIATE -n <DB_NAME> -a DATA,REDO1,FRA

 srvctl add instance -d MYDBSTDBY -i MYDBPR11 -n Node1
srvctl add instance -d MYDBSTDBY -i MYDBPR12 -n Node2

Step 5 - Create init files
[Node 1 - oracle user - Standby Cluster]
cd /opt/oracle/product/112/db/dbs
cat initMYDBPR11.ora
SPFILE='+DATA/MYDBSTDBY/spfileMYDBSTDBY.ora'

 [Node 2 - oracle user - Standby Cluster]
cd /opt/oracle/product/112/db/dbs
cat initMYDBPR12.ora
SPFILE='+DATA/MYDBSTDBY/spfileMYDBSTDBY.ora'
Step 6 - Create Audit Directories
[Node 1 - oracle user - Standby Cluster]
mkdir -p /opt/oracle/base/admin/MYDBSTDBY/adump

 [Node 2 - oracle user - Standby Cluster]
mkdir -p /opt/oracle/base/admin/MYDBSTDBY/adump
Step 7 - Start the instance, mount it and recover standby database. 
You can use any method to recover/restore.
One of the methods which I recommend is to use active database duplication. It's generally faster. 

 rman << EOF
connect TARGET sys/<pwd>@MYDBPROD ;
connect AUXILIARY sys/<pwd>@MYDBSTDBY;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;


In the next blog I discuss on DG Broker Build

Oracle Database: How to Know if Real Time Apply is Happening or Not (11g,12c,18c,19c)

 
In this blog I tell how to know if your Managed Standby is using Real time apply or not. 
Note  - for Real time apply - you must have standby redo logs configured (n + 1 standby logs per thread)
Case 1 - Idle  When no Recovery is Happening 
On Production 

 select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2

    DEST_ID DEST_NAME                           STATUS    TYPE           SRL RECOVERY_MODE
---------- ----------------------------------- --------- -------------- --- -----------------------

         2 LOG_ARCHIVE_DEST_2                  VALID     PHYSICAL       YES IDLE

Case 2 - Recovery (no Real time apply) 
On Production 
 select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2 ;

   DEST_ID DEST_NAME                           STATUS    TYPE           SRL RECOVERY_MODE
---------- ----------------------------------- --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2                  VALID     PHYSICAL       YES MANAGED

3. Case 3 - Real Time Apply 
On Production 
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2

   DEST_ID DEST_NAME                           STATUS    TYPE           SRL RECOVERY_MODE
---------- ----------------------------------- --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2                  VALID     PHYSICAL       YES MANAGED REAL TIME APPLY

I have highlighted the changes. Make note of your dest_id, i have shown here for 2, as my standby is configured using dest id 2. 

You can start a real time apply using. 
alter database recover managed standby database using current logfile disconnect;

Wednesday, April 10, 2019

Oracle Database: kggpnpInit: failed to init gpnp (Only one Instance running) 11g,12c,18c,19c

 
Symptom - 
1.Only one instance of Oracle DB can run on a RAC node (1 or 2)
2. Public interface used as interconnect interface as seen in gv$cluster_interconnects

 In Alert log you can see - 
[USER(153710)]CRS-2316:Fatal error: cannot initialize GPnP, CLSGPNP_ERR (Generic GPnP error).
kggpnpInit: failed to init gpnp
  WARNING: No cluster interconnect has been specified. Depending on
           the communication driver configured Oracle cluster traffic
           may be directed to the public interface of this machine.
           Oracle recommends that RAC clustered databases be configured
           with a private interconnect for enhanced security and

           performance.


Reason - From October 2018 PSU of Oracle RAC you must apply OCW component of the GI patch as well to the Oracle Home. 

 Example - 
Current  Patch Level 
28790660;OJVM PATCH SET UPDATE 11.2.0.4.190115
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262)

Solution - Apply OCW Patch
OCW Patch is available as part of GI PSU Bundle, you cannot download it individually (in general)
But you can apply it individually to DB Home
This can be really an issue if you have done newer versions of Oracle install on higher version of Cluster

Patch Apply Process
Step 1 - Stop all Database instances running from the home

Step 2 - Create the ocm file 
$ cd $ORACLE_HOME/ccr/bin
$ ./emocmrsp
(respond <Enter> and then 'Y'>
$ cp ocm.rsp /home/oracle/

Step 3 - Patch Apply 
PrePatch
<Patch Unzipped Location>/<patch_number>/custom/server/<patch_number>/custom/scripts/prepatch.sh -dbhome <oh>
Patch
<oh>/OPatch/opatch apply -oh <oh> -local <Patch Unzipped Location>/<patch_number>/custom/server/<patch_number>
Post Patch
<Patch Unzipped Location>/<patch_number>/custom/server/<patch_number>/custom/scripts/postpatch.sh -dbhome <oh>

Example for Jan 2019 Patch
$ /images/oracle/11.2.0.4/p28729234/28729234/custom/server/28729234/custom/scripts/prepatch.sh -dbhome /opt/oracle/product/112/db
$ /opt/oracle/product/112/db/OPatch/opatch napply -oh /opt/oracle/product/112/db -local /images/oracle/11.2.0.4/p28729234/28729234/custom/server/28729234 -ocmrf /home/oracle/ocm.rsp
$ /images/oracle/11.2.0.4/p28729234/28729234/custom/server/28729234/custom/scripts/postpatch.sh -dbhome /opt/oracle/product/112/db


Final Patch Level
28729234;OCW Patch Set Update : 11.2.0.4.190115 (28729234)
28790660;OJVM PATCH SET UPDATE 11.2.0.4.190115
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262

Reference Doc ID - 
Customer Recommended11.2.0.4 RAC Database Instance Fails to Start with "No connectivity to other instances in the cluster during startup" After Applying 11.2.0.4 OCT 2018 DB PSU (11.2.0.4.181016 DB PSU) (Patch# 28204707) (Doc ID 2471441.1)

Popular Posts

© Copyright 2019 DBA to DA. . Powered by Blogger.