Creating Data Guard with 2 node RAC Primary and 2 node RAC standby

In this article, we will see the creation of DataGuard with 2 node RAC Primary Database and 2 node RAC Standby database.

Primary Database Standby Database Primary Instance Standby Instance
RACDB DGRACDB RAC1 DGRAC
RAC2 DGRAC1

What I have already done

1. My production database is up and running with 2 node RAC

Oracle Version : 11.2.0.4.0

Grid Version : 11.2.0.4.0

2. At standby Grid Infrastructure is already installed.

3. ASM is up and running.

4. Oracle Binaries are installed.

Step 1: Check standby database cluster services are running :

[oracle@dgrac ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Step 2: Prepare the Primary Database :

Check force logging at primary database and enable it if not enabled.

SQL> select force_logging from v$database;

FOR
---
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR
---
YES

Step 3: Check the archive log if not enabled , enable it.

Step 4: Check details no of redo log files and size of each redo log file.

SQL> SELECT GROUP#,BYTES/1024/1024 AS MB FROM V$LOG ;        

    GROUP#	   MB
---------- ----------
	 1	   50
	 2	   50
	 3	   50
         1         50
         2         50
         3         50
  
SQL> SELECT THREAD#,INSTANCE FROM GV$THREAD;

   THREAD# INSTANCE
---------- --------------------
	 1 racdb1
         2 racdb2

We can see here , 6 redo log files and with each file having size 50 MB.

So we will create 8 standby redo log files .

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+DATA' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+DATA' SIZE 50M;

Database altered.

Step 5: Set initialization parameters necessary for data guard environment.

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME=RACDB SCOPE=SPFILE SID='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DGRACDB LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGRACDB' SID='*';

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=DGRACDB SID='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='dg_config=(RACDB,DGRACDB) SID='*';

 System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT=RACDB SID='*';

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

System altered.

Step 6: Set tnsnames.ora file and listener file.

Tnsnames .ora

RACDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
  )

DGRACDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dgrac)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = racdb)
    )
  )

We need to do a static entry of listener at standby database :

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (SID_NAME=dgracdb1)
          (ORACLE_HOME=/u02/oracle/product/11.2.0/dbhome_1)
        )
      )

Step 7: Restart listener and restart database so parameters can take effect of new value.

Step 8: Prepare Standby database

Create a necessary directory at standby :

[oracle@dgrac ~]$ mkdir -p /u02/oracle/admin/racdb/adump
[oracle@dgrac ~]$ asmcmd
ASMCMD> cd DGDATA   
ASMCMD> mkdir RACDB
ASMCMD> mkdir DGRACDB
ASMCMD> ls
DGRACDB/
RACDB/
dgrac-scan/
ASMCMD> mkdir arc
ASMCMD> cd arc
ASMCMD> mkdir racdb
ASMCMD>

Step 9: Create pfile  $ORACLE_HOME/dbs/initdgracdb1.ora with one parameter.

DB_NAME=’racdb’

Step 10: Start the database in the nomount state using pfile.

[oracle@dgrac dbs]$ sqlplus sys/oracle@dgracdb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 18 15:50:33 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile ='initdgracdb1.ora'
ORACLE instance started.

Total System Global Area  238034944 bytes
Fixed Size		    2252016 bytes
Variable Size		  180355856 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5095424 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Step 11: Copy pfile to both standby servers.

scp orapwracdb dgracdb:$ORACLE_HOME/dbs/orapwdgracdb
scp orapwracdb dgracdb:$ORACLE_HOME/dbs/orapwdgracdb1
scp orapwracdb dgracdb1:$ORACLE_HOME/dbs/orapwdgracdb2

Step 12: Create a RMAN script to create a standby database using duplicate database command.

run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate auxiliary channel stdby1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
set 'db_unique_name'='dgracdb'
set instance_name='dgracdb1'
set instance_number='1'
set control_files='+DGDATA'
set remote_listener='dgrac-scan:1521'
set db_create_online_log_dest_1='+DGDATA'
set db_create_online_log_dest_2='+DGDATA'
set db_recovery_file_dest='+DGDATA'
set db_file_name_convert='+DATA','+DGDATA'
set log_file_name_convert='+DATA','+DGDATA'
set audit_file_dest='/u02/oracle/admin/racdb/adump'
set core_dump_dest='/u02/oracle/admin/racdb/cdump'
nofilenamecheck;
}

Diskgroup name in my standby database is DGDATA so I have used db_file_name_Convert and log_file_name_convert accordingly.

Step 13: Connect RMAN and run above script to create standby

[oracle@dgrac dbs]$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 18 15:51:09 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/oracle@racdb

connected to target database: RACDB (DBID=979421601)

RMAN> connect auxiliary sys/oracle@dgracdb

connected to auxiliary database: RACDB (not mounted)

RMAN> run {
2> allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate auxiliary channel stdby1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
set 'db_unique_name'='dgracdb'
set instance_name='dgracdb1'
set instance_number='1'
set control_files='+DGDATA'
set remote_listener='dgrac-scan:1521'
set db_create_online_log_dest_1='+DGDATA'
set db_create_online_log_dest_2='+DGDATA'
set db_recovery_file_dest='+DGDATA'
set db_file_name_convert='+DATA','+DGDATA'
set log_file_name_convert='+DATA','+DGDATA'
set audit_file_dest='/u02/oracle/admin/racdb/adump'
set core_dump_dest='/u02/oracle/admin/racdb/cdump'
nofilenamecheck;
}3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 

using target database control file instead of recovery catalog
allocated channel: prim1
channel prim1: SID=67 instance=racdb1 device type=DISK

allocated channel: prim2
channel prim2: SID=68 instance=racdb1 device type=DISK

allocated channel: stdby1
channel stdby1: SID=25 device type=DISK

Starting Duplicate Db at 18-JUL-18

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u02/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb1' auxiliary format 
 '/u02/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb'   targetfile 
 '+DATA/racdb/spfileracdb.ora' auxiliary format 
 '/u02/oracle/product/11.2.0/dbhome_1/dbs/spfileracdb.ora'   ;
   sql clone "alter system set spfile= ''/u02/oracle/product/11.2.0/dbhome_1/dbs/spfileracdb.ora''";
}
executing Memory Script

Starting backup at 18-JUL-18
Finished backup at 18-JUL-18

sql statement: alter system set spfile= ''/u02/oracle/product/11.2.0/dbhome_1/dbs/spfileracdb.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name = 
 ''dgracdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  instance_name = 
 ''dgracdb1'' comment=
 '''' scope=spfile";
   sql clone "alter system set  instance_number = 
 1 comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''+DGDATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  remote_listener = 
 ''dgrac-scan:1521'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_1 = 
 ''+DGDATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_2 = 
 ''+DGDATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest = 
 ''+DGDATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert = 
 ''+DATA'', ''+DGDATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert = 
 ''+DATA'', ''+DGDATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest = 
 ''/u02/oracle/admin/racdb/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  core_dump_dest = 
 ''/u02/oracle/admin/racdb/cdump'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''dgracdb'' comment= '''' scope=spfile

sql statement: alter system set  instance_name =  ''dgracdb1'' comment= '''' scope=spfile

sql statement: alter system set  instance_number =  1 comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DGDATA'' comment= '''' scope=spfile

sql statement: alter system set  remote_listener =  ''dgrac-scan:1521'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_1 =  ''+DGDATA'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_2 =  ''+DGDATA'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''+DGDATA'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA'', ''+DGDATA'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA'', ''+DGDATA'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u02/oracle/admin/racdb/adump'' comment= '''' scope=spfile

sql statement: alter system set  core_dump_dest =  ''/u02/oracle/admin/racdb/cdump'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1135747072 bytes

Fixed Size                     2252544 bytes
Variable Size                805306624 bytes
Database Buffers             318767104 bytes
Redo Buffers                   9420800 bytes
allocated channel: stdby1
channel stdby1: SID=32 instance=racdb device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''+DGDATA/dgracdb/controlfile/current.256.981820331'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DGDATA/dgracdb/controlfile/current.257.981820331';
   sql clone "alter system set  control_files = 
  ''+DGDATA/dgracdb/controlfile/current.257.981820331'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DGDATA/dgracdb/controlfile/current.256.981820331'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 18-JUL-18
channel prim1: starting datafile copy
copying standby control file
output file name=/u02/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb1.f tag=TAG20180718T155211 RECID=1 STAMP=981820337
channel prim1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JUL-18

sql statement: alter system set  control_files =   ''+DGDATA/dgracdb/controlfile/current.257.981820331'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1135747072 bytes

Fixed Size                     2252544 bytes
Variable Size                805306624 bytes
Database Buffers             318767104 bytes
Redo Buffers                   9420800 bytes
allocated channel: stdby1
channel stdby1: SID=34 instance=racdb device type=DISK

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+dgdata";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+dgdata";
   set newname for datafile  2 to 
 "+dgdata";
   set newname for datafile  3 to 
 "+dgdata";
   set newname for datafile  4 to 
 "+dgdata";
   backup as copy reuse
   datafile  1 auxiliary format 
 "+dgdata"   datafile 
 2 auxiliary format 
 "+dgdata"   datafile 
 3 auxiliary format 
 "+dgdata"   datafile 
 4 auxiliary format 
 "+dgdata"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +dgdata in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 18-JUL-18
channel prim1: starting datafile copy
input datafile file number=00001 name=+DATA/racdb/datafile/system.256.981804637
channel prim2: starting datafile copy
input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.257.981804637
output file name=+DGDATA/dgracdb/datafile/sysaux.259.981820377 tag=TAG20180718T155254
channel prim2: datafile copy complete, elapsed time: 00:01:05
channel prim2: starting datafile copy
input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.258.981804639
output file name=+DGDATA/dgracdb/datafile/system.258.981820375 tag=TAG20180718T155254
channel prim1: datafile copy complete, elapsed time: 00:01:16
channel prim1: starting datafile copy
input datafile file number=00004 name=+DATA/racdb/datafile/users.259.981804639
output file name=+DGDATA/dgracdb/datafile/undotbs1.260.981820445 tag=TAG20180718T155254
channel prim2: datafile copy complete, elapsed time: 00:00:08
output file name=+DGDATA/dgracdb/datafile/users.261.981820453 tag=TAG20180718T155254
channel prim1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JUL-18

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=981820459 file name=+DGDATA/dgracdb/datafile/system.258.981820375
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=981820459 file name=+DGDATA/dgracdb/datafile/sysaux.259.981820377
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=981820459 file name=+DGDATA/dgracdb/datafile/undotbs1.260.981820445
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=981820459 file name=+DGDATA/dgracdb/datafile/users.261.981820453
Finished Duplicate Db at 18-JUL-18
released channel: prim1
released channel: prim2
released channel: stdby1

RMAN>

Step 14: Verify instance is running fine.

SQL>  SELECT NAME,DB_UNIQUE_NAME,INSTANCE_NAME,DATABASE_ROLE,OPEN_MODE FROM GV$DATABASE,GV$INSTANCE;

NAME	  DB_UNIQUE_NAME		 INSTANCE_NAME	  DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- ---------------- --------------------
RACDB	  dgracdb			 dgracdb1	  PHYSICAL STANDBY MOUNTED

Step 15: Add the database using srvctl

[oracle@dgrac ~]$ srvctl add database -d dgracdb -o $ORACLE_HOME -r physical_standby -s mount -a dgdata

Step 16: Add instance using srvctl and start the first instance

[oracle@dgrac dbs]$ srvctl add instance -d dgracdb -i dgracdb1 -n dgrac
[oracle@dgrac dbs]$ srvctl add instance -d dgracdb -i dgracdb2 -n dgrac1
[oracle@dgrac dbs]$ srvctl start instance -d dgracdb -i dgracdb1

Step 17: Set initialization parameters for standby needed for data guard environment.

SQL> ALTER SYSTEM SET FAL_SERVER=RACDB SID='*';

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT=DGRACDB SID='*';

System altered.

Step 18: Move spfile in ASM storage so we can use shared spfile for both instances.

SQL> create pfile from spfile; 

File created.

SQL> create spfile ='+DGDATA/DGRACDB/PASSWORDFILE/spfiledgracdb.ora' from pfile;

File created.

SQL>

[oracle@dgrac dbs]$ srvctl modify database -d dgracdb -p +DGDATA/DGRACDB/PASSWORDFILE/spfiledgracdb.ora

Step 19: Restart database

Step 20: Change instance parameters

ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='dgracdb1';
ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='dgracdb2';
ALTER SYSTEM SET INSTANCE_NAME='dgracdb1' SCOPE=SPFILE SID='dgracdb1';
ALTER SYSTEM SET INSTANCE_NAME='dgracdb2' SCOPE=SPFILE SID='dgracdb2';

Step 21: Start the second instance

[oracle@dgrac dbs]$ srvctl start instance -d dgracdb -i dgracdb2

Step 22: Check details of the second instance

[oracle@dgrac dbs]$ srvctl status instance -d dgracdb -i dgracdb2
Instance dgracdb2 is running on node dgrac1

Step 23: Check the active instance of data guard :

SQL> select * from v$active_instances;

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
	  1 dgrac.localdomain:dgracdb1
	  2 dgrac1.localdomain:dgracdb2

Step 24: Start media recovery :

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.