Description:
- In this article we are going to see Step by Step Configuration of Oracle Cascading Standby Database 11.2.0.4 in RHEL-6.8.
- To reduce the load on your primary system, you can implement cascaded destinations, where by a standby database receives its redo data from another standby database, instead of directly from the primary database [ Primary->stby1->stby2 ]
- The cascaded standby database does not receive its redo directly from the Primary database and this type of data guard configuration offloads the overhead associated with performing redo transport from a primary database to a cascading standby database.
- A physical standby database to retransmit the incoming redo data it receives from the primary database to other remote destinations in the same manner as the primary database
Environment details of the cascaded standby database.
Let’s Start the Demo:-
Primary Server side Configurations:-
Step1:-Change Archivelog mode:
SQL> archive log list
SQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_prim' scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;
Make sure the archive directory is created in physically in OS level.
Step2:-Change force logging mode:
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;
Step3:-Adding Redologfile for standby database:
SQL> alter database add standby logfile group 4 '/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log' size 50m;
SQL> alter database add standby logfile group 5 '/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log' size 50m;
SQL> alter database add standby logfile group 6 '/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log' size 50m;
Check the newly added standby redo log files with the below qurey: SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
Step4:-Add the network entry in primary and standby side(All the servers):
Listener Entry(Primary):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle
Listener Entry(Standby1):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY1)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby1.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle
Listener Entry(Standby2):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY2)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby2.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle
Tnsnames entry(All three servers):-
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)
ORCL_STBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby1.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY1)
)
)
ORCL_STBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby2.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY2)
)
)
Start the listener in all three servers and try to tnsping to each other. If the tnsping is successful, go ahead for the next step, else fix the tnsping issue and go to the next step.
$ tnsping ORCL_PRIM
$ tnsping ORCL_STBY1
$ tnsping ORCL_STBY2
Step5:-Changing parameters in primary database:
SQL> ALTER SYSTEM SET db_unique_name='ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2='service=ORCL_STBY1 async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_3='service=ORCL_STBY2 async valid_for=(standby_logfiles,standby_role) db_unique_name=ORCL_STBY2' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server='ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client='ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP
Step6:-Check the changed parameters:
SQL> @dg_params.sql
Create a pfile and password file and copy to the standby servers: SQL> create pfile from spfile; $ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=manager entries=5
Copy the remote login password file (orapwORCL) from $ORACLE_HOME/dbs of primary database server to standby1 & standby2 database server to the location $ORACLE_HOME/dbs:
$ scp $ORACLE_HOME/dbs/initORCL.ora oracle@standby1:$ORACLE_HOME/dbs/initORCL.ora
$ scp $ORACLE_HOME/dbs/orapwORCL oracle@standby1:$ORACLE_HOME/dbs/orapwORCL
$ scp $ORACLE_HOME/dbs/initORCL.ora oracle@standby2:$ORACLE_HOME/dbs/initORCL.ora
$ scp $ORACLE_HOME/dbs/orapwORCL oracle@standby2:$ORACLE_HOME/dbs/orapwORCL
Standby1 Server side Configurations:-
Step1:-Change the necessary parameters in the copied pfile as like below
$ cat $ORACLE_HOME/dbs/initORCL.ora *.__oracle_base='/oradb/app/oracle'# ORACLE_BASE set from environment *.audit_file_dest='/oradb/app/oracle/admin/ORCL_STBY1/adump' *.audit_trail='DB' *.compatible='11.2.0.4.0' *.control_files='/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl','/oradb/app/oracle/flash_recovery_area/ORCL_STBY1/control02.ctl' *.db_block_size=8192 *.db_domain='doyensys.com' *.db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' *.db_name='ORCL' *.db_recovery_file_dest='/oradb/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=8G *.db_unique_name='ORCL_STBY1' *.diagnostic_dest='/oradb/app/oracle' *.fal_client='ORCL_STBY1' *.fal_server='ORCL_PRIM' *.job_queue_processes=20 *.log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)' *.log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby1 valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY1' *.log_archive_dest_2='service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM' *.log_archive_dest_3='service=ORCL_STBY2 async valid_for=(standby_logfiles,standby_role) db_unique_name=ORCL_STBY2' *.log_archive_dest_state_3='ENABLE' *.log_archive_format='orcls1_%t_%s_%r.arc' *.log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' *.memory_max_target=692060160 *.memory_target=629145600 *.open_cursors=300 *.optimizer_dynamic_sampling=2 *.optimizer_mode='ALL_ROWS' *.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora *.processes=150 *.query_rewrite_enabled='TRUE' *.remote_login_passwordfile='EXCLUSIVE' *.skip_unusable_indexes=TRUE *.standby_file_management='AUTO' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'
Step2:-Create directory Structure in Standby database
$ mkdir -p /oradb/app/oracle/admin/ORCL_STBY1/adump
$ mkdir -p /oradb/app/oracle/oradata/ORCL_STBY1
$ mkdir -p /oradb/app/oracle/flash_recovery_area/ORCL_STBY1
$ mkdir -p /oradb/app/oracle/archive/orcl_stby1
Step3:-Start the standby1 database using pfile
$ sqlplus ‘/ as sysdba’
SQL> startup nomount
SQL> exit
Step4:-Connect to the rman
$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY1 Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 1 22:53:03 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1508077812) connected to auxiliary database: ORCL (not mounted) RMAN> run { allocate channel p1 type disk; allocate channel p2 type disk; allocate channel p3 type disk; allocate channel p4 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database nofilenamecheck; } using target database control file instead of recovery catalog allocated channel: p1 channel p1: SID=37 device type=DISK allocated channel: p2 channel p2: SID=49 device type=DISK allocated channel: p3 channel p3: SID=50 device type=DISK allocated channel: p4 channel p4: SID=51 device type=DISK allocated channel: s1 channel s1: SID=19 device type=DISK Starting Duplicate Db at 01-JUL-18 22:53:30 contents of Memory Script: { backup as copy reuse targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' ; } executing Memory Script Starting backup at 01-JUL-18 22:53:30 Finished backup at 01-JUL-18 22:53:31 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl'; restore clone controlfile to '/oradb/app/oracle/flash_recovery_area/ORCL_STBY1/control02.ctl' from '/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl'; } executing Memory Script Starting backup at 01-JUL-18 22:53:32 channel p1: starting datafile copy copying standby control file output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180701T225332 RECID=10 STAMP=980376813 channel p1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 01-JUL-18 22:53:35 Starting restore at 01-JUL-18 22:53:35 channel s1: copied control file copy Finished restore at 01-JUL-18 22:53:36 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/oradb/app/oracle/oradata/ORCL_STBY1/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf"; set newname for datafile 2 to "/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf"; set newname for datafile 3 to "/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf"; set newname for datafile 4 to "/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf"; set newname for datafile 5 to "/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF "; set newname for datafile 6 to "/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF"; set newname for datafile 7 to "/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf"; set newname for datafile 8 to "/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf"; set newname for datafile 9 to "/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf"; set newname for datafile 10 to "/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf"; backup as copy reuse datafile 1 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf" datafile 2 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf" datafile 3 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf" datafile 4 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf" datafile 5 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF " datafile 6 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF" datafile 7 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf" datafile 8 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf" datafile 9 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf" datafile 10 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /oradb/app/oracle/oradata/ORCL_STBY1/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 01-JUL-18 22:53:42 channel p1: starting datafile copy input datafile file number=00008 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom02.dbf channel p2: starting datafile copy input datafile file number=00007 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom01.dbf channel p3: starting datafile copy input datafile file number=00009 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom03.dbf channel p4: starting datafile copy input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf tag=TAG20180701T225342 channel p4: datafile copy complete, elapsed time: 00:17:00 channel p4: starting datafile copy input datafile file number=00006 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF output file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF tag=TAG20180701T225342 channel p4: datafile copy complete, elapsed time: 00:10:54 channel p4: starting datafile copy input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF output file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF tag=TAG20180701T225342 channel p4: datafile copy complete, elapsed time: 00:09:43 channel p4: starting datafile copy input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf tag=TAG20180701T225342 channel p4: datafile copy complete, elapsed time: 00:08:01 channel p4: starting datafile copy input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf tag=TAG20180701T225342 channel p4: datafile copy complete, elapsed time: 00:11:19 channel p4: starting datafile copy input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01_1.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf tag=TAG20180701T225342 channel p3: datafile copy complete, elapsed time: 01:01:07 channel p3: starting datafile copy input datafile file number=00010 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom04.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf tag=TAG20180701T225342 channel p3: datafile copy complete, elapsed time: 00:00:17 output file name=/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf tag=TAG20180701T225342 channel p4: datafile copy complete, elapsed time: 00:05:31 output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf tag=TAG20180701T225342 channel p2: datafile copy complete, elapsed time: 01:23:48 output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf tag=TAG20180701T225342 channel p1: datafile copy complete, elapsed time: 01:25:21 Finished backup at 02-JUL-18 00:19:04 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=10 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=11 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=12 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=14 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF datafile 6 switched to datafile copy input datafile copy RECID=15 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF datafile 7 switched to datafile copy input datafile copy RECID=16 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf datafile 8 switched to datafile copy input datafile copy RECID=17 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf datafile 9 switched to datafile copy input datafile copy RECID=18 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf datafile 10 switched to datafile copy input datafile copy RECID=19 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf Finished Duplicate Db at 02-JUL-18 00:19:26 released channel: p1 released channel: p2 released channel: p3 released channel: p4 released channel: s1
Step5:-Connect to the standby1 database and start the recovery process
SQL> alter database recover managed standby database using current logfile disconnect;
Step6:-Physical Standby Database is Performing Correctly
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
Standby2 Server side Configurations:-
Step1:-Change the necessary parameters in the copied pfile as like below
$ cat $ORACLE_HOME/dbs/initORCL.ora *.__oracle_base='/oradb/app/oracle'# ORACLE_BASE set from environment *.audit_file_dest='/oradb/app/oracle/admin/ORCL_STBY2/adump' *.audit_trail='DB' *.compatible='11.2.0.4.0' *.control_files='/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl','/oradb/app/oracle/flash_recovery_area/ORCL_STBY2/control02.ctl' *.db_block_size=8192 *.db_domain='doyensys.com' *.db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY2' *.db_name='ORCL' *.db_recovery_file_dest='/oradb/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=8G *.db_unique_name='ORCL_STBY2' *.diagnostic_dest='/oradb/app/oracle' *.fal_client='ORCL_STBY2' *.fal_server='ORCL_STBY1' *.job_queue_processes=20 *.log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)' *.log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby2' *.log_archive_format='orcls2_%t_%s_%r.arc' *.log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY2' *.memory_max_target=692060160 *.memory_target=629145600 *.open_cursors=300 *.optimizer_dynamic_sampling=2 *.optimizer_mode='ALL_ROWS' *.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora *.processes=150 *.query_rewrite_enabled='TRUE' *.remote_login_passwordfile='EXCLUSIVE' *.skip_unusable_indexes=TRUE *.standby_file_management='AUTO' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'
Step2:-Create directory Structure in Standby database
$ mkdir -p /oradb/app/oracle/admin/ORCL_STBY2/adump
$ mkdir -p /oradb/app/oracle/oradata/ORCL_STBY2
$ mkdir -p /oradb/app/oracle/flash_recovery_area/ORCL_STBY2
$ mkdir -p /oradb/app/oracle/archive/orcl_stby2
Step3:-Start the standby2 database using pfile
$ sqlplus ‘/ as sysdba’
SQL> startup nomount
SQL> exit
Step4:-Connect to the rman
$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY2 Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 2 18:17:44 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1508077812) connected to auxiliary database: ORCL (not mounted) RMAN> run { allocate channel p1 type disk; allocate channel p2 type disk; allocate channel p3 type disk; allocate channel p4 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database nofilenamecheck; } using target database control file instead of recovery catalog allocated channel: p1 channel p1: SID=62 device type=DISK allocated channel: p2 channel p2: SID=29 device type=DISK allocated channel: p3 channel p3: SID=59 device type=DISK allocated channel: p4 channel p4: SID=36 device type=DISK allocated channel: s1 channel s1: SID=19 device type=DISK Starting Duplicate Db at 02-JUL-18 18:17:53 contents of Memory Script: { backup as copy reuse targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' ; } executing Memory Script Starting backup at 02-JUL-18 18:17:53 Finished backup at 02-JUL-18 18:17:54 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl'; restore clone controlfile to '/oradb/app/oracle/flash_recovery_area/ORCL_STBY2/control02.ctl' from '/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl'; } executing Memory Script Starting backup at 02-JUL-18 18:17:54 channel p1: starting datafile copy copying standby control file output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180702T181754 RECID=11 STAMP=980446676 channel p1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 02-JUL-18 18:17:58 Starting restore at 02-JUL-18 18:17:58 channel s1: copied control file copy Finished restore at 02-JUL-18 18:17:59 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/oradb/app/oracle/oradata/ORCL_STBY2/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf"; set newname for datafile 2 to "/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf"; set newname for datafile 3 to "/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf"; set newname for datafile 4 to "/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf"; set newname for datafile 5 to "/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF "; set newname for datafile 6 to "/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF"; set newname for datafile 7 to "/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf"; set newname for datafile 8 to "/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf"; set newname for datafile 9 to "/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf"; set newname for datafile 10 to "/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf"; backup as copy reuse datafile 1 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf" datafile 2 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf" datafile 3 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf" datafile 4 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf" datafile 5 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF " datafile 6 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF" datafile 7 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf" datafile 8 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf" datafile 9 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf" datafile 10 auxiliary format "/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /oradb/app/oracle/oradata/ORCL_STBY2/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 02-JUL-18 18:18:05 channel p1: starting datafile copy input datafile file number=00008 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom02.dbf channel p2: starting datafile copy input datafile file number=00007 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom01.dbf channel p3: starting datafile copy input datafile file number=00009 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom03.dbf channel p4: starting datafile copy input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf tag=TAG20180702T181805 channel p4: datafile copy complete, elapsed time: 00:14:02 channel p4: starting datafile copy input datafile file number=00006 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF output file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF tag=TAG20180702T181805 channel p4: datafile copy complete, elapsed time: 00:09:30 channel p4: starting datafile copy input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF output file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF tag=TAG20180702T181805 channel p4: datafile copy complete, elapsed time: 00:17:02 channel p4: starting datafile copy input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf tag=TAG20180702T181805 channel p4: datafile copy complete, elapsed time: 00:08:15 channel p4: starting datafile copy input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf tag=TAG20180702T181805 channel p4: datafile copy complete, elapsed time: 00:11:03 channel p4: starting datafile copy input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01_1.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf tag=TAG20180702T181805 channel p3: datafile copy complete, elapsed time: 01:06:02 channel p3: starting datafile copy input datafile file number=00010 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom04.dbf output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf tag=TAG20180702T181805 channel p3: datafile copy complete, elapsed time: 00:00:37 output file name=/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf tag=TAG20180702T181805 channel p4: datafile copy complete, elapsed time: 00:07:03 output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf tag=TAG20180702T181805 channel p2: datafile copy complete, elapsed time: 01:31:51 output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf tag=TAG20180702T181805 channel p1: datafile copy complete, elapsed time: 01:33:54 Finished backup at 02-JUL-18 19:51:59 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=11 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=12 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=13 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf datafile 4 switched to datafile copy input datafile copy RECID=14 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=15 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF datafile 6 switched to datafile copy input datafile copy RECID=16 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF datafile 7 switched to datafile copy input datafile copy RECID=17 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf datafile 8 switched to datafile copy input datafile copy RECID=18 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf datafile 9 switched to datafile copy input datafile copy RECID=19 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf datafile 10 switched to datafile copy input datafile copy RECID=20 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf Finished Duplicate Db at 02-JUL-18 19:52:20 released channel: p1 released channel: p2 released channel: p3 released channel: p4 released channel: s1
Step5:-Connect to the standby1 database and start the recovery process
SQL> alter database recover managed standby database using current logfile disconnect;
In Primary switch logfile and the archived log file in both standby servers by executing the below script:
Primary:
Standby1:
Standby2:
Successfully completed the cascading standby database configuration
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
Reference:-
https://docs.oracle.com/cd/B19306_01/server.102/b14239/cascade_appx.htm#g639625
