Data Guard Cascading Standby Database

11.2.0.2 > 

We can use the cascaded standby database feature to say offload reporting from the primary database to the cascaded standby database.

This is the environment:

A) Primary Database (TESTDB1)
B) Physical Standby Database (TESTDB2)
C) Cascaded Standby Database (TESTDB3)

So this is how the redo log transport will happen:

TESTDB1 >>>> TESTDB2
TESTDB2 >>>> TESTDB3

The assumptions are:

• The Data Guard will be configured in Maximum Availability mode
• The backup location on the Primary server and Standby server is different
• The directory structure on the Standby server is not the same as the Primary server
• The Standby server will host both the Standby database as well as the Cascaded standby database
• The environment used in this example is Oracle database version 11.2.0.3 on OEL Linux 5.7
• The db_unique_name of the Primary database is testdb1 , the Standby database is testdb2 and the Cascaded Standby database is testdb3
• It is assumed that a recent RMAN backup of the Primary database and archivelogs is available on the Primary server. In this example it exists in the FRA.

Steps

Network Configuration

The tnsnames.ora file on both the Primary database server and Standby database serve have the entries :

TESTDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb1)

)

)

TESTDB2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb2)

)

)

TESTDB3 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = testdb3)

)

)

The listener.ora on the Standby database server has a static entry for testdb2 and testdb3

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=testdb2)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=testdb2)

)

(SID_DESC=

(GLOBAL_DBNAME=testdb3)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=testdb3)

)

)

On Primary take a backup of the current control file which will be used by the Standby database

RMAN> backup current controlfile for standby;

…….

…….

channel ORA_DISK_1: finished piece 1 at 06-MAY-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06/o1_mf_ncnnf_TAG20130506T165754_8rgo3n7o_.bkp tag=TAG20130506T165754 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

………

Make a note of the backup piece name as we will be using this backup for the Standby database creation.

Take a backup of the database and archivelogs

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> backup database plus archivelog ;

Starting backup at 06-MAY-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=814288291

input archived log thread=1 sequence=4 RECID=2 STAMP=814288349

input archived log thread=1 sequence=5 RECID=3 STAMP=814289179

input archived log thread=1 sequence=6 RECID=4 STAMP=814289262

input archived log thread=1 sequence=7 RECID=5 STAMP=814295555

input archived log thread=1 sequence=8 RECID=8 STAMP=814296764

……..

……….

input datafile file number=00002 name=/u01/app/oracle/oradata/testdb1/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/testdb1/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/testdb1/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/testdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 06-MAY-13

channel ORA_DISK_1: finished piece 1 at 06-MAY-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp tag=TAG20130506T170129 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Finished backup at 06-MAY-13

…..

Copy the most recent database backup, archivelog backup and controlfile backup to the staging location on the Standby server

cd /u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06

[oracle@pdemvrhl061 2013_05_06]$ ls -l

total 1898088

-rw-r----- 1 oracle dba 684387840 May 6 17:01 o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

-rw-r----- 1 oracle dba 64000 May 6 17:02 o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp

-rw-r----- 1 oracle dba 9994240 May 6 17:09 o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp

-rw-r----- 1 oracle dba 1247256576 May 6 17:02 o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

scp -rp * oracle@host2:/home/oracle/backup

Copy password file from $ORACLE_HOME/dbs on Primary to $ORACLE_HOME/dbs on Standby

scp -rp orapwtestdb1 oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb2

scp -rp orapwtestdb1 oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb3

Copy init.ora file from $ORACLE_HOME/dbs on Primary to $ORACLE_HOME/dbs on Standby

scp –rp inittestdb1.ora oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb2.ora

scp –rp inittestdb1.ora oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb3.ora

Make the required changes to the Standby database (testdb2) init.ora file

These are the changes we have made to the init.ora which we have copied from the Primary database (testdb1) – the remaining parameters like sga_target, db_name, diagnostic_dest etc will be the same regardless if the database is a Primary database or Physical standby database.

Review particularly the entries related to redo transport like the log_archive_dest_* entries

*.audit_file_dest='/u01/app/oracle/admin/testdb2/adump'

*.control_files='/u01/app/oracle/oradata/testdb2/control01.ctl','/u01/app/oracle/oradata/testdb2/control02.ctl'

*.db_file_name_convert='/u01/app/oracle/oradata/testdb1','/u01/app/oracle/oradata/testdb2'

*.db_unique_name='testdb2'

*.fal_client='testdb2'

*.fal_server='testdb1'

*.log_archive_config='DG_CONFIG=(testdb1,testdb2,testdb3)'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb2'

*.log_archive_dest_2='SERVICE=testdb1 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb1'

*.log_archive_dest_3='SERVICE=testdb3 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb3'

*.log_file_name_convert='/u01/app/oracle/oradata/testdb1','/u01/app/oracle/oradata/testdb2'

*.service_names='testdb2'

Create the required directory structure on the Standby site

$ mkdir -p /u01/app/oracle/admin/testdb2/adump
$ mkdir -p /u01/app/oracle/admin/testdb3/adump
$ mkdir -p /u01/app/oracle/oradata/testdb1
$ mkdir -p /u01/app/oracle/oradata/testdb3
$ mkdir  -p /u01/app/oracle/fast_recovery_area

Add entries in /etc/oratab

 testdb2:/u01/app/oracle/product/11.2.0/dbhome_1:N
testdb3:/u01/app/oracle/product/11.2.0/dbhome_1:N

Start the Standby database instance in NOMOUNT state

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size             272633136 bytes

Database Buffers          134217728 bytes

Redo Buffers                8466432 bytes

SQL>

Restore the Standby Controlfile from the backup copied from Primary
 

RMAN> restore standby controlfile from '/home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp';

Starting restore at 06-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/testdb2/control01.ctl

output file name=/u01/app/oracle/oradata/testdb2/control02.ctl

Finished restore at 06-MAY-13

Mount the standby database (note in 11g we can just ‘ALTER DATABASE MOUNT’ command)

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database

released channel: ORA_DISK_1

Now catalog all the backup pieces which we have copied from Primary

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database

=====================================

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp

File Name: /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

File Name: /home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

Do you really want to catalog the above files (enter YES or NO)? y

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp

File Name: /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

File Name: /home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp

File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

Restore the database –on the Standby database testdb2

Note the files are being restored in the new location:/u01/app/oracle/oradata/testdb2

RMAN> restore database;

Starting restore at 06-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/testdb2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/testdb2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/testdb2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/testdb2/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/testdb2/example01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp tag=TAG20130506T170129

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:46

Finished restore at 06-MAY-13

Note the last archived log sequence # which has been backed up

RMAN> list backup of archivelog all;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

……

………

  List of Archived Logs in backup set 23

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    60      1602201    06-MAY-13 1604763    06-MAY-13

  1    61      1604763    06-MAY-13 1604769    06-MAY-13

  1    62      1604769    06-MAY-13 1629416    06-MAY-13

  1    63      1629416    06-MAY-13 1674083    07-MAY-13

  1    64      1674083    07-MAY-13 1694517    07-MAY-13

  1    65      1694517    07-MAY-13 1694574    07-MAY-13

  1    66      1694574    07-MAY-13 1694766    07-MAY-13

Recover the database – the SET UNTIL SEQUENCE will be the last archive log sequence backup available plus 1

RMAN> run

2> { set until sequence 67;

3> recover database;

4> }

executing command: SET until clause

Starting recover at 07-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=64

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=65

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=66

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/o1_mf_annnn_TAG20130507T141625_8rk00td3_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/backup/o1_mf_annnn_TAG20130507T141625_8rk00td3_.bkp tag=TAG20130507T141625

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_64_8rk04zb6_.arc thread=1 sequence=64

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_64_8rk04zb6_.arc RECID=8 STAMP=814803519

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_65_8rk04zbz_.arc thread=1 sequence=65

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_65_8rk04zbz_.arc RECID=7 STAMP=814803519

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_66_8rk04zbp_.arc thread=1 sequence=66

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_66_8rk04zbp_.arc RECID=6 STAMP=814803519

media recovery complete, elapsed time: 00:00:01

Finished recover at 07-MAY-13

Now we need to perform the same steps for the Cascaded Standby database testdb3

We make the following changes in the init.ora file which we have copied from the Primary database:

 *.audit_file_dest=’/u01/app/oracle/admin/testdb3/adump’
*.control_files=’/u01/app/oracle/oradata/testdb3/control01.ctl’,’/u01/app/oracle/oradata/testdb3/control02.ctl’
*.db_file_name_convert=’/u01/app/oracle/oradata/testdb1′,’/u01/app/oracle/oradata/testdb3′
*.log_file_name_convert=’/u01/app/oracle/oradata/testdb1′,’/u01/app/oracle/oradata/testdb3′
*.db_unique_name=’testdb3′
*.fal_client=’testdb3′
*.fal_server=’testdb2′
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb3′
*.service_names=’testdb3′
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(testdb1,testdb2,testdb3)’

•  Set the enevironment for the database testdb3
• Startup NOMOUNT the database
• Restore the backup of the controlfile taken from the Primary database
• Mount the database
• Restore the database
• Recover the database until the same archived log sequence number we used for the earlier standby database testdb2.

Add the Standby Redo Log Files

Create the Standby redo log files on all the three databases. Note that we use the same size as the redo log files and create one additional group in case of the standby redo log files as compared to the online redo log files.

For example:

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo04.log' size 50m;

Database altered.

Open both the Standby Database as well as the Cascaded standby database and put them in managed recovery mode

SQL> alter database open;

Database altered.

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

Configure redo transport for the Primary Database

We add the following entries in the init.ora of the Parimary database

 *.fal_client=’testdb1′
*.fal_server=’testdb2′
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb1′
*.log_archive_dest_2=’SERVICE=testdb2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb2′
*.log_archive_dest_3=’SERVICE=testdb3  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb3′;
*.log_archive_dest_state_3=’DEFER’
*.standby_file_management=’AUTO’

Note that we have set parameter log_archive_dest_state_3 to DEFER because in normal operation, the Primary database testdb2 will only ship redo logs to the standby database testdb2 and NOT the cascaded standby database testdb3.

When the current Primary database testdb1 assumes the role of a standby database at some time in the future when a switchover happens, then only we need to enable the log shipping from testdb1 to testdb3.

Change the protection mode to MAXIMUM AVAILABILITY

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2233000 bytes

Variable Size             494931288 bytes

Database Buffers          335544320 bytes

Redo Buffers                6574080 bytes

Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select  PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Lets Test!

Primary Database TESTDB1
 

SQL> update customers
  2  set cust_first_name=’Smith’
  3   where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

Standby Database/Cascading Standby TESTDB2
 

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME
——————–
Smith

Cascaded Standby Database TESTDB3

At this stage the cascaded standby database is lagging behind the Primary as well as the cascading Standby database because the changes from the TESTDB2 will only be cascaded to TESTDB3 when a log switch is triggered when the archive redo log file fills up

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME

--------------------

Sachin

Primary Database TESTDB1

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

Cascaded Standby Database TESTDB3

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME

--------------------

Smith

Reference

Usage, Benefits and Limitations of Standby Redo Logs (SRL) [ID 219344.1].

Que & Ans.

Question: I have cascade standby in our environment for two node rac. scenario is like that –two node rac primary –> two node rac standby –> standalone standby (cascade). IS cascade standby for two node rac is a standalone is possible?

Answer: if you are using 11.2.0.2 or a higher version, then the restriction has been removed. You can have a cascading standby built for a RAC primary and is supported irrespective of whether your cascaded standby is a standalone or RAC.