Dataguard

Create a Standby from another Standby – Cascading Standby

If you have a standby database and you want to create another standby database then you can use the existing standby database to create and transport logs. It is extremely useful when you do not want to make any configurations changes on the prod system or you can not access the prod system due to firewall restrictions. Also this way guarantees not performing any operational load on the prod system.

I will use the following configuration.  For the new standby database instance name will be TESTDR,  the logs will be coming from the ISTANDR standby database.

Database Type Instace Name Host Logs Trasported to
PRIMARY ISTAN node1 ISTANDR
STANDBY existing ISTANDR node2 TESTDR
STANDBY new TESTDR testhost

A ) Create New standby Using RMAN duplicate

1 ) Startup the auxilary instance in mount mode using the following  pfile.

[oracle@testhost~]$ cat /oracle/product/11.2.0/dbs/initTESTDR.ora
db_name=ISTAN
db_unique_name=TESTDR
control_files='+DATA'
db_create_file_dest='+DATA'
db_create_online_log_dest_1='+DATA'
db_recovery_file_dest='+DATA'
db_recovery_file_dest_size=15000G
sga_target=50G
compatible='11.2.0.3'
db_files=900

2) Set the listener so that the auxiliary instance is statically registered

[oracle@testhost~]$ cat /oracle/product/11.2.0_grid/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
	(DESCRIPTION =
	  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))
	  (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
	)
  )

SID_LIST_LISTENER =
  (SID_LIST =
	(SID_DESC =
	  (GLOBAL_DBNAME = TESTDR)
	  (ORACLE_HOME = /oracle/product/11.2.0)
	  (SID_NAME = TESTDR)
	 )
   )

3 ) Create the password file for the axuilary instance in this case the new Standby. Password is oracleoracle.

   orapwd file=$ORACLE_HOME/dbs/orapwTESTDR password=oracleoracle

4 ) On both hosts node2 and testhost add the existing standby database and new auxilary instance tns entries.  auxilary instance will be the new standby database.

[oracle@testhost~]$ cat /oracle/product/11.2.0/network/admin/tnsnames.ora 
ISTANDR=
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.11 )(PORT = 1522))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = ISTANDR)
	)
  )

TESTDR =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = TESTDR)
	)
  )

5 ) Stop the apply process on the existing standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

6 ) Connect the existing standby database and the new auxiliary database using RMAN and run the duplicate command

rman  auxiliary  sys/oracleoracle@TESTDR  target sys/oracleoracle@ISTANDR
RMAN > run {
ALLOCATE CHANNEL tgt10 TYPE DISK;
ALLOCATE CHANNEL tgt20 TYPE DISK;
ALLOCATE CHANNEL tgt30 TYPE DISK;
ALLOCATE CHANNEL tgt40 TYPE DISK;
ALLOCATE CHANNEL tgt50 TYPE DISK;
ALLOCATE CHANNEL tgt60 TYPE DISK;
ALLOCATE CHANNEL tgt70 TYPE DISK;
ALLOCATE CHANNEL tgt80 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup3 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup4 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup5 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup6 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup7 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup8 TYPE DISK;
duplicate target database for standby from active database nofilenamecheck;
}

After successfully running the rman duplicate command, we will have a new mounted database ready for log apply.  The next part we will see how to set up this. By the way, Canceled apply process on the existing standby database can be resumed.

7 ) Set up the standby parameters for the new standby database. Set up the log transport to the new standby. Start the log apply process on the new standby. Finally check if the log transport to the new standby and log apply on the new standby are working without any problems.

*********************************************************
**************ON the New Standby Database****************
*********************************************************

[oracle@testhost~]$ . profileORACLE 
[oracle@testhost~]$ tnsping ISTANDR
	TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 28-AUG-2013 09:30:30
	Copyright (c) 1997, 2011, Oracle.  All rights reserved.
	Used parameter files:
	Used TNSNAMES adapter to resolve the alias
	Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.11)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ISTANDR)))
	OK (0 msec)	
SQL> show parameter spfile 
NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
spfile                     string      /oracle/product/11.2.0/dbs/spfileTESTDR.ora
SQL> show parameter fal_server
NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
fal_server                 string		
SQL> ALTER SYSTEM SET FAL_SERVER=ISTANDR;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.

************************************************************
**************ON the Existing Standby Database**************
************************************************************

[oracle@node2 ~]$ tnsping TESTDR
	TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 28-AUG-2013 12:43:42
	Copyright (c) 1997, 2011, Oracle.  All rights reserved.
	Used parameter files:
	/u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/sqlnet.ora
	Used TNSNAMES adapter to resolve the alias
	Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDR)))
	OK (0 msec)
SQL> set lines 200
SQL> col DEST_NAME for a20
SQL> col DESTINATION for a15
SQL> SELECT DEST_ID, DEST_NAME,DESTINATION  FROM   V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL ORDER BY DESTINATION;
DEST_ID 	DEST_NAME            DESTINATION
---------- -------------------- ---------------
		32 STANDBY_ARCHIVE_DEST +RECO
		 1 LOG_ARCHIVE_DEST_1   +RECO

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=TESTDR ASYNC DB_UNIQUE_NAME=TESTDR VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) ';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.

SQL> set lines 200
SQL> col DEST_NAME for a20
SQL> col DESTINATION for a15
SQL> SELECT DEST_ID, DEST_NAME,DESTINATION, STATUS, PROCESS, REGISTER, APPLIED_SCN, ERROR   FROM   
		V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL ORDER BY DESTINATION;
   DEST_ID DEST_NAME            DESTINATION     STATUS    PROCESS    REG APPLIED_SCN ERROR
---------- -------------------- --------------- --------- ---------- --- ----------- -----------
		 1 LOG_ARCHIVE_DEST_1   +RECO      VALID     ARCH       YES           0
		32 STANDBY_ARCHIVE_DEST +RECO      VALID     RFS        YES           0
		 4 LOG_ARCHIVE_DEST_2   TESTDR     VALID     LGWR       YES           0

*********************************************************
**************ON the New Standby Database****************
*********************************************************
Check the alert log of the new standby database if the new logs are transported and registered.

[oracle@testhost~]$ tail -f  /oracle/product/11.2.0/log/diag/rdbms/testdr/TESTDR/trace/alert_TESTDR.log 
RFS[6]: Opened log for thread 4 sequence 202202 dbid 1571437681 branch 670968434
Archived Log entry 337 added for thread 3 sequence 217780 rlc 670968434 ID 0x6e967317 dest 2:
Archived Log entry 338 added for thread 2 sequence 202696 rlc 670968434 ID 0x6e967317 dest 2:
Archived Log entry 339 added for thread 4 sequence 202202 rlc 670968434 ID 0x6e967317 dest 2:
RFS[4]: Opened log for thread 3 sequence 217781 dbid 1571437681 branch 670968434
RFS[5]: Opened log for thread 3 sequence 217782 dbid 1571437681 branch 670968434
RFS[6]: Opened log for thread 1 sequence 240503 dbid 1571437681 branch 670968434
Archived Log entry 340 added for thread 3 sequence 217782 rlc 670968434 ID 0x6e967317 dest 2:
Archived Log entry 341 added for thread 1 sequence 240503 rlc 670968434 ID 0x6e967317 dest 2:
Archived Log entry 342 added for thread 3 sequence 217781 rlc 670968434 ID 0x6e967317 dest 2:

Start the log apply process and check the logs are applied on the new standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT PARALLEL 16; 
Database altered
[oracle@testhost ~]$ tail -f  /oracle/product/11.2.0/log/diag/rdbms/testdr/TESTDR/trace/alert_TESTDR.log		
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT PARALLEL 16
Attempt to start background Managed Standby Recovery process (TESTDR)
Wed Aug 28 10:00:29 2013
MRP0 started with pid=29, OS id=1713 
MRP0: Background Managed Standby Recovery process started (TESTDR)
 started logmerger process
Wed Aug 28 10:00:34 2013
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 16 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT PARALLEL 16
Media Recovery Log +DATA/testdr/archivelog/2013_08_28/thread_1_seq_240424.792.824636873
Media Recovery Log +DATA/testdr/archivelog/2013_08_28/thread_2_seq_202623.795.824636875
Media Recovery Log +DATA/testdr/archivelog/2013_08_28/thread_3_seq_217662.796.824636877
Media Recovery Log +DATA/testdr/archivelog/2013_08_28/thread_4_seq_202136.799.824636877
Media Recovery Log +DATA/testdr/archivelog/2013_08_28/thread_2_seq_202624.797.824636877

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby where process like 'MRP%';
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
MRP0      APPLYING_LOG          3     217668      83802    1721238
Advertisements

4 thoughts on “Create a Standby from another Standby – Cascading Standby

  1. But what happened to primary, you haven’t modify any thing on primary. What about want to switch over primary to new standby.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s