Configure Cascading Standby Database on 11G
Table of Contents
___________________________________________________________________________________________________
0. Overview
1. Environment
2. Configure Standby Database (DELL_DG)
3. Configure init parameter on Primary (DELL)
4. Configure init parameter on STANDBY (DELL_DG)
5. Configure init parameter on Cascaded STANDBY (DELL_CDG)
6. Create required directories for cascaded standby
7. Add oratab entry for cascaded standby
8. Startup nomount Cascaded STANDBY database
9. Configure LISTENER Configuration
10. Configure TNS Configuration
11. Backup from Primary (DELL)
12. Transfer Backup from primary (DELL) to CASCADED standby (DELL_CDG)
13. Transfer Password file from primary (DELL) to CASCADED standby (DELL_CDG)
On CASCADED STANDBY
14. Restore the Standby Controlfile
15. Mount the cascaded standby database
16. Catalog all the backup pieces
17. Restore and Recover Database
18. Verify Standby redo logs
19. Enable MRP
___________________________________________________________________________________________________
What is cascaded standby ? Cascaded standby database receives redo data from another standby database instead of directly from primary database. PRIMARY ---> STANDBY ----> CASCADED STANDBY In a Data Guard configuration using a cascaded destination, a physical standby database can forward the redo data it receives from the primary database to another standby database. Why cascaded standby ? To reduce the load on your primary system, or to reduce the bandwidth requirements imposed when your standbys are separated from the primary database through a Wide Area Network (WAN), you can implement cascaded destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. Limitations: Cascading logical standby databases from a logical standby database is not supported. Cascading standby databases (logical or physical) from a primary database that is part of an Oracle Real Application Cluster (RAC) is not supported (This restriction has been lifted in 11.2.0.2) Using Cascaded standby databases in a Data Guard Broker environment is not supported.
PRIMARY: (DELL)
Platform : Linuxx86_64
Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version : Oracle 11.2.0.4
File system : Normal
DB Name : DELL
DB_UNIQUE_NAME : DELL
Flashback : Disabled
Oracle Home Path : /u01/app/oracle/product/11.2.0.4
STANDBY: (DELL_DG)
Platform : Linuxx86_64
Server Name : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version : Oracle 11.2.0.4
File system : Normal
DB Name : DELL
DB_UNIQUE_NAME : DELL_DG
Flashback : Disabled
Oracle Home Path : /u01/app/oracle/product/11.2.0.4
CASCADED STANDBY: (DELL_CDG)
Platform : Linuxx86_64
Server Name : RAC3.RAJASEKHAR.COM, IP: 192.168.2.103
DB Version : Oracle 11.2.0.4
File system : Normal
DB Name : DELL
DB_UNIQUE_NAME : DELL_CDG
Flashback : Disabled
Oracle Home Path : /u01/app/oracle/product/11.2.0.4
2. Configure Standby Database (DELL_DG)
This is sample document, database names will be different http://www.br8dba.com/active-dataguard/
3. Configure init parameter on Primary (DELL)
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE --------- -------------------- ------------------------------ ---------------- DELL READ WRITE DELL PRIMARY SQL> SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(DELL,DELL_DG) SQL> SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' SCOPE=BOTH; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG' SCOPE=BOTH; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='DEFER' SCOPE=BOTH; System altered. SQL> [oracle@rac1 ~]$ cat initDELL.ora DELL.__db_cache_size=503316480 DELL.__java_pool_size=16777216 DELL.__large_pool_size=33554432 DELL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DELL.__pga_aggregate_target=520093696 DELL.__sga_target=754974720 DELL.__shared_io_pool_size=0 DELL.__shared_pool_size=184549376 DELL.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/DELL/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u02/oracle/oradata/DELL/control01.ctl','/u02/oracle/oradata/fast_recovery_area/DELL/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='/u02/oracle/oradata/DELL_DG','/u02/oracle/oradata/DELL' *.db_name='DELL' *.db_recovery_file_dest='/u02/oracle/oradata/fast_recovery_area' *.db_recovery_file_dest_size=3221225472 *.db_unique_name='DELL' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DELLXDB)' *.fal_client='DELL' *.fal_server='DELL_DG' *.local_listener='LISTENER_DELL' *.log_archive_config='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' *.log_archive_dest_1='LOCATION=/u02/oracle/archive/DELL VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL' *.log_archive_dest_2='SERVICE=DELL_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL_DG' *.log_archive_dest_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_dest_state_3='DEFER' *.log_archive_format='%t_%s_%r.dbf' *.log_file_name_convert='/u02/oracle/oradata/DELL_DG','/u02/oracle/oradata/DELL' *.memory_target=1259339776 *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac1 ~]$
4. Configure init parameter on STANDBY (DELL_DG)
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE --------- -------------------- ------------------------------ ---------------- DELL MOUNTED DELL_DG PHYSICAL STANDBY SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(DELL,DELL_DG) SQL> SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string SQL> SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' SCOPE=BOTH; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG' SCOPE=BOTH; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='ENABLE' SCOPE=BOTH; System altered. SQL> [oracle@rac2 ~]$ cat initDELL_DG.ora DELL_DG.__db_cache_size=503316480 DELL_DG.__java_pool_size=16777216 DELL_DG.__large_pool_size=33554432 DELL_DG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DELL_DG.__pga_aggregate_target=520093696 DELL_DG.__sga_target=754974720 DELL_DG.__shared_io_pool_size=0 DELL_DG.__shared_pool_size=184549376 DELL_DG.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/DELL_DG/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u02/oracle/oradata/DELL_DG/control01.ctl','/u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_DG' *.db_name='DELL' *.db_recovery_file_dest='/u02/oracle/oradata/fast_recovery_area' *.db_recovery_file_dest_size=3221225472 *.db_unique_name='DELL_DG' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DELL_DGXDB)' *.fal_client='DELL_DG' *.fal_server='DELL' *.log_archive_config='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' *.log_archive_dest_1='LOCATION=/u02/oracle/archive/DELL_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_DG' *.log_archive_dest_2='SERVICE=DELL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL' *.log_archive_dest_3='SERVICE=DELL_CDG ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=DELL_CDG' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_dest_state_3='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_DG' *.memory_target=1259339776 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac2 ~]$
5. Configure init parameter on Cascaded STANDBY (DELL_CDG)
[oracle@rac3 ~]$ cat /home/oracle/initDELLCDG.ora DELL_CDG.__db_cache_size=469762048 DELL_CDG.__java_pool_size=16777216 DELL_CDG.__large_pool_size=33554432 DELL_CDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment DELL_CDG.__pga_aggregate_target=520093696 DELL_CDG.__sga_target=754974720 DELL_CDG.__shared_io_pool_size=0 DELL_CDG.__shared_pool_size=201326592 DELL_CDG.__streams_pool_size=16777216 *.audit_file_dest='/u01/app/oracle/admin/DELL_CDG/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u02/oracle/oradata/DELL_CDG/control01.ctl','/u02/oracle/oradata/fast_recovery_area/DELL_CDG/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='DELL' *.db_recovery_file_dest='/u02/oracle/oradata/fast_recovery_area' *.db_recovery_file_dest_size=3221225472 *.db_unique_name='DELL_CDG' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DELL_CDGXDB)' *.fal_client='DELL_CDG' *.fal_server='DELL_DG' *.log_archive_config='DG_CONFIG=(DELL,DELL_DG,DELL_CDG)' *.log_archive_dest_1='LOCATION=/u02/oracle/archive/DELL_CDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_CDG' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.db_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_CDG' *.log_file_name_convert='/u02/oracle/oradata/DELL','/u02/oracle/oradata/DELL_CDG' *.memory_target=1259339776 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac3 ~]$
6. Create required directories (On Node 3 – DELL_CDG)
[oracle@rac3 ~]$ mkdir -p /u01/app/oracle/admin/DELL_CDG/adump [oracle@rac3 ~]$ mkdir -p /u02/oracle/oradata/DELL_CDG/ [oracle@rac3 ~]$ mkdir -p /u02/oracle/oradata/fast_recovery_area/DELL_CDG [oracle@rac3 ~]$ mkdir -p /u02/oracle/archive/DELL_CDG
7. Modify /etc/oratab (On Node 3 – DELL_CDG)
[oracle@rac3 ~]$ cat /etc/oratab | grep -i DELL_CDG
DELL_CDG:/u01/app/oracle/product/11.2.0.4:N
[oracle@rac3 ~]$
8. Startup nomount (On Node 3 – DELL_CDG)
[oracle@rac3 ~]$ . oraenv ORACLE_SID = [DELL_CDG] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac3 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 23 13:20:39 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/initDELLCDG.ora'; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes SQL> create spfile from pfile='/home/oracle/initDELLCDG.ora'; File created. SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes SQL>
9. Configure LISTENER Configuration
Node 1: PRIMARY (DELL)
[oracle@rac1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER_11G = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DELL) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4) (SID_NAME = DELL) ) ) LISTENER_11G = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621)) ) ) ADR_BASE_LISTENER_11G = /u01/app/oracle [oracle@rac1 admin]$ [oracle@rac1 admin]$ ps -ef | grep tns root 15 2 0 11:46 ? 00:00:00 [netns] oracle 7558 1 0 13:11 ? 00:00:00 /u01/app/oracle/product/11.2.0.4/bin/tnslsnr LISTENER_11G -inherit oracle 11686 7404 0 14:28 pts/0 00:00:00 grep tns [oracle@rac1 admin]$ [oracle@rac1 admin]$ lsnrctl status LISTENER_11G LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2018 14:26:45 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621))) STATUS of the LISTENER ------------------------ Alias LISTENER_11G Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 23-DEC-2018 13:11:38 Uptime 0 days 1 hr. 15 min. 7 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_11g/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621))) Services Summary... Service "DELL" has 2 instance(s). Instance "DELL", status UNKNOWN, has 1 handler(s) for this service... Instance "DELL", status READY, has 1 handler(s) for this service... Service "DELLXDB" has 1 instance(s). Instance "DELL", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 admin]$
Node 2: STANDBY (DELL_DG)
[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0.4/network/admin/
[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DELL_DG)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
(SID_NAME = DELL_DG)
)
)
LISTENER_11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
)
)
ADR_BASE_LISTENER_11G = /u01/app/oracle
[oracle@rac2 admin]$
[oracle@rac2 admin]$ ps -ef | grep tns
root 15 2 0 11:46 ? 00:00:00 [netns]
oracle 6971 1 0 13:13 ? 00:00:00 /u01/app/oracle/product/11.2.0.4/bin/tnslsnr LISTENER_11G -inherit
oracle 10712 6909 0 14:29 pts/0 00:00:00 grep tns
[oracle@rac2 admin]$
[oracle@rac2 admin]$ lsnrctl status LISTENER_11G
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2018 14:29:11
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias LISTENER_11G
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-DEC-2018 13:13:37
Uptime 0 days 1 hr. 15 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener_11g/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "DELL_DG" has 1 instance(s).
Instance "DELL_DG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$
Node 3: CASCADED STANDBY (DELL_CDG)
[oracle@rac3 ~]$ cd /u01/app/oracle/product/11.2.0.4/network/admin/
[oracle@rac3 admin]$ cp listener.ora listener.ora_bkp_new
[oracle@rac3 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DELL_CDG)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)
(SID_NAME = DELL_CDG)
)
)
LISTENER_11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac3.rajasekhar.com)(PORT = 1621))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
)
)
ADR_BASE_LISTENER_11G = /u01/app/oracle
[oracle@rac3 admin]$
[oracle@rac3 admin]$ lsnrctl start LISTENER_11G
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2018 14:20:47
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.4/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac3/listener_11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3.rajasekhar.com)(PORT=1621)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac3.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias LISTENER_11G
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-DEC-2018 14:20:49
Uptime 0 days 0 hr. 0 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac3/listener_11g/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac3.rajasekhar.com)(PORT=1621)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "DELL_CDG" has 1 instance(s).
Instance "DELL_CDG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac3 admin]$
10. Configure TNS Configuration
Node 1: Primary (DELL)
[oracle@rac1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_11G = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) ) (CONNECT_DATA = (SERVICE_NAME = DELL_DG) ) ) DELL_CDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_CDG) ) ) [oracle@rac1 admin]$
Node 2: STANDBY (DELL_DG)
[oracle@rac2 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_11G = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) ) (CONNECT_DATA = (SERVICE_NAME = DELL_DG) ) ) DELL_CDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_CDG) ) ) [oracle@rac2 admin]$
Node 3: CASCADED STANDBY (DELL_CDG)
[oracle@rac3 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_11G = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621)) DELL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL) ) ) DELL_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) ) (CONNECT_DATA = (SERVICE_NAME = DELL_DG) ) ) DELL_CDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.103)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DELL_CDG) ) ) [oracle@rac3 admin]$
11. Backup from Primary (DELL)
[oracle@rac1 ~]$ cat rmanbackup.sh ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4 BACKUP_LOG_PATH=/u02/oracle/backup export ORACLE_BASE export ORACLE_HOME export ORACLE_SID=DELL export BACKUP_LOG_PATH LOG_FILE=${BACKUP_LOG_PATH}/backup_db.log $ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF connect target / run { allocate channel t1 type disk; allocate channel t2 type disk; allocate channel t3 type disk; backup database format '/u02/oracle/backup/database_%d_%u_%s'; release channel t1; release channel t2; release channel t3; } sql 'alter system archive log current'; run { allocate channel a1 type disk; allocate channel a2 type disk; allocate channel a3 type disk; backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s'; release channel a1; release channel a2; release channel a3; } run { allocate channel c1 type disk; backup current controlfile for standby format '/u02/oracle/backup/Control_%d_%u_%s'; release channel c1; } exit; EOF [oracle@rac1 DELL]$ chmod 775 rmanbackup.sh [oracle@rac1 DELL]$ ls -ltr total 4 -rwxrwxr-x. 1 oracle dba 951 Dec 23 13:58 rmanbackup.sh [oracle@rac1 DELL]$ [oracle@rac1 DELL]$ nohup ./rmanbackup.sh & [1] 10351 [oracle@rac1 DELL]$ nohup: ignoring input and appending output to `nohup.out' [oracle@rac1 DELL]$ jobs -l [1]+ 10351 Running nohup ./rmanbackup.sh & [oracle@rac1 DELL]$ [oracle@rac1 DELL]$ cat backup_db.log Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 23 14:14:59 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: DELL (DBID=3965088591) RMAN> 2> 3> 4> 5> 6> 7> 8> 9> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=50 device type=DISK allocated channel: t2 channel t2: SID=42 device type=DISK allocated channel: t3 channel t3: SID=31 device type=DISK Starting backup at 23-DEC-18 channel t1: starting full datafile backup set channel t1: specifying datafile(s) in backup set input datafile file number=00001 name=/u02/oracle/oradata/DELL/system01.dbf input datafile file number=00004 name=/u02/oracle/oradata/DELL/users01.dbf channel t1: starting piece 1 at 23-DEC-18 channel t2: starting full datafile backup set channel t2: specifying datafile(s) in backup set input datafile file number=00002 name=/u02/oracle/oradata/DELL/sysaux01.dbf input datafile file number=00003 name=/u02/oracle/oradata/DELL/undotbs01.dbf channel t2: starting piece 1 at 23-DEC-18 channel t3: starting full datafile backup set channel t3: specifying datafile(s) in backup set including current control file in backup set channel t3: starting piece 1 at 23-DEC-18 channel t3: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/database_DELL_0dtlgf74_13 tag=TAG20181223T141500 comment=NONE channel t3: backup set complete, elapsed time: 00:00:01 channel t3: starting full datafile backup set channel t3: specifying datafile(s) in backup set including current SPFILE in backup set channel t3: starting piece 1 at 23-DEC-18 channel t3: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/database_DELL_0etlgf76_14 tag=TAG20181223T141500 comment=NONE channel t3: backup set complete, elapsed time: 00:00:03 channel t1: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/database_DELL_0btlgf74_11 tag=TAG20181223T141500 comment=NONE channel t1: backup set complete, elapsed time: 00:00:27 channel t2: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/database_DELL_0ctlgf74_12 tag=TAG20181223T141500 comment=NONE channel t2: backup set complete, elapsed time: 00:00:27 Finished backup at 23-DEC-18 released channel: t1 released channel: t2 released channel: t3 RMAN> sql statement: alter system archive log current RMAN> 2> 3> 4> 5> 6> 7> 8> 9> allocated channel: a1 channel a1: SID=50 device type=DISK allocated channel: a2 channel a2: SID=42 device type=DISK allocated channel: a3 channel a3: SID=31 device type=DISK Starting backup at 23-DEC-18 current log archived channel a1: starting archived log backup set channel a1: specifying archived log(s) in backup set input archived log thread=1 sequence=14 RECID=19 STAMP=994364876 input archived log thread=1 sequence=15 RECID=20 STAMP=994365204 input archived log thread=1 sequence=16 RECID=22 STAMP=994365247 input archived log thread=1 sequence=17 RECID=24 STAMP=994365249 input archived log thread=1 sequence=18 RECID=26 STAMP=994365252 input archived log thread=1 sequence=19 RECID=28 STAMP=994365253 input archived log thread=1 sequence=20 RECID=30 STAMP=994365253 input archived log thread=1 sequence=21 RECID=32 STAMP=994366193 input archived log thread=1 sequence=22 RECID=34 STAMP=994366194 input archived log thread=1 sequence=23 RECID=37 STAMP=994366197 input archived log thread=1 sequence=24 RECID=39 STAMP=994366557 input archived log thread=1 sequence=25 RECID=41 STAMP=994367307 input archived log thread=1 sequence=26 RECID=43 STAMP=994367413 input archived log thread=1 sequence=27 RECID=44 STAMP=994367418 input archived log thread=1 sequence=28 RECID=45 STAMP=994367503 input archived log thread=1 sequence=29 RECID=46 STAMP=994367652 input archived log thread=1 sequence=30 RECID=47 STAMP=994367750 input archived log thread=1 sequence=31 RECID=49 STAMP=994367754 input archived log thread=1 sequence=32 RECID=51 STAMP=994368247 input archived log thread=1 sequence=33 RECID=53 STAMP=994396624 input archived log thread=1 sequence=34 RECID=54 STAMP=994396628 input archived log thread=1 sequence=35 RECID=55 STAMP=994433377 input archived log thread=1 sequence=36 RECID=56 STAMP=994433381 channel a1: starting piece 1 at 23-DEC-18 channel a2: starting archived log backup set channel a2: specifying archived log(s) in backup set input archived log thread=1 sequence=37 RECID=57 STAMP=994433417 input archived log thread=1 sequence=38 RECID=58 STAMP=994752599 input archived log thread=1 sequence=39 RECID=59 STAMP=994752603 input archived log thread=1 sequence=40 RECID=62 STAMP=994753201 input archived log thread=1 sequence=41 RECID=69 STAMP=994754147 input archived log thread=1 sequence=42 RECID=71 STAMP=994758897 input archived log thread=1 sequence=43 RECID=73 STAMP=994761301 channel a2: starting piece 1 at 23-DEC-18 channel a3: starting archived log backup set channel a3: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=74 STAMP=994761304 input archived log thread=1 sequence=2 RECID=76 STAMP=994761308 input archived log thread=1 sequence=3 RECID=79 STAMP=994761665 input archived log thread=1 sequence=4 RECID=81 STAMP=994761687 input archived log thread=1 sequence=5 RECID=83 STAMP=994761689 input archived log thread=1 sequence=6 RECID=85 STAMP=994761692 input archived log thread=1 sequence=7 RECID=88 STAMP=994766042 input archived log thread=1 sequence=8 RECID=89 STAMP=994766335 input archived log thread=1 sequence=9 RECID=91 STAMP=994766336 input archived log thread=1 sequence=10 RECID=93 STAMP=994766340 input archived log thread=1 sequence=11 RECID=95 STAMP=994766736 input archived log thread=1 sequence=12 RECID=97 STAMP=994783917 input archived log thread=1 sequence=13 RECID=99 STAMP=994784010 input archived log thread=1 sequence=14 RECID=100 STAMP=994784015 input archived log thread=1 sequence=15 RECID=101 STAMP=994784051 input archived log thread=1 sequence=16 RECID=103 STAMP=994826449 input archived log thread=1 sequence=17 RECID=102 STAMP=994826448 input archived log thread=1 sequence=18 RECID=104 STAMP=994826449 input archived log thread=1 sequence=19 RECID=105 STAMP=994826923 input archived log thread=1 sequence=20 RECID=106 STAMP=994827013 input archived log thread=1 sequence=21 RECID=108 STAMP=994827017 input archived log thread=1 sequence=22 RECID=110 STAMP=994834119 input archived log thread=1 sequence=23 RECID=112 STAMP=995352031 channel a3: starting piece 1 at 23-DEC-18 channel a1: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/arch_DELL_0ftlgf81_15 tag=TAG20181223T141528 comment=NONE channel a1: backup set complete, elapsed time: 00:00:04 channel a1: starting archived log backup set channel a1: specifying archived log(s) in backup set input archived log thread=1 sequence=24 RECID=113 STAMP=995352034 input archived log thread=1 sequence=25 RECID=116 STAMP=995352079 input archived log thread=1 sequence=26 RECID=118 STAMP=995634672 input archived log thread=1 sequence=27 RECID=119 STAMP=995634676 input archived log thread=1 sequence=28 RECID=120 STAMP=995634913 input archived log thread=1 sequence=29 RECID=124 STAMP=995635432 input archived log thread=1 sequence=30 RECID=126 STAMP=995635435 input archived log thread=1 sequence=31 RECID=129 STAMP=995638037 input archived log thread=1 sequence=32 RECID=130 STAMP=995638037 input archived log thread=1 sequence=33 RECID=132 STAMP=995638172 input archived log thread=1 sequence=34 RECID=134 STAMP=995638173 input archived log thread=1 sequence=35 RECID=136 STAMP=995638177 input archived log thread=1 sequence=36 RECID=138 STAMP=995638528 input archived log thread=1 sequence=37 RECID=140 STAMP=995638528 channel a1: starting piece 1 at 23-DEC-18 channel a2: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/arch_DELL_0gtlgf81_16 tag=TAG20181223T141528 comment=NONE channel a2: backup set complete, elapsed time: 00:00:04 channel a3: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/arch_DELL_0htlgf82_17 tag=TAG20181223T141528 comment=NONE channel a3: backup set complete, elapsed time: 00:00:03 channel a1: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/arch_DELL_0itlgf85_18 tag=TAG20181223T141528 comment=NONE channel a1: backup set complete, elapsed time: 00:00:03 Finished backup at 23-DEC-18 released channel: a1 released channel: a2 released channel: a3 RMAN> 2> 3> 4> 5> allocated channel: c1 channel c1: SID=50 device type=DISK Starting backup at 23-DEC-18 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including standby control file in backup set channel c1: starting piece 1 at 23-DEC-18 channel c1: finished piece 1 at 23-DEC-18 piece handle=/u02/oracle/backup/Control_DELL_0jtlgf89_19 tag=TAG20181223T141537 comment=NONE channel c1: backup set complete, elapsed time: 00:00:03 Finished backup at 23-DEC-18 released channel: c1 RMAN> Recovery Manager complete. [oracle@rac1 DELL]$
12. Transfer Backup from primary to CASCADED standby
[oracle@rac1 backup]$ scp database_DELL_0* oracle@rac3:/u02/oracle/backup oracle@rac3's password: database_DELL_0btlgf74_11 100% 645MB 58.6MB/s 00:11 database_DELL_0ctlgf74_12 100% 414MB 46.0MB/s 00:09 database_DELL_0dtlgf74_13 100% 9856KB 9.6MB/s 00:00 database_DELL_0etlgf76_14 100% 96KB 96.0KB/s 00:00 [oracle@rac1 backup]$ scp arch_DELL_0* oracle@rac3:/u02/oracle/backup oracle@rac3's password: arch_DELL_0ftlgf81_15 100% 21MB 20.7MB/s 00:00 arch_DELL_0gtlgf81_16 100% 66MB 65.6MB/s 00:01 arch_DELL_0htlgf82_17 100% 66MB 66.2MB/s 00:01 arch_DELL_0itlgf85_18 100% 52MB 52.4MB/s 00:01 [oracle@rac1 backup]$ [oracle@rac1 backup]$ scp Control_DELL_0jtlgf89_19 oracle@rac3:/u02/oracle/backup oracle@rac3's password: Control_DELL_0jtlgf89_19 100% 9856KB 9.6MB/s 00:00 [oracle@rac1 backup]$
13. Transfer Password file from primary to CASCADED standby
[oracle@rac1 dbs]$ scp orapwDELL oracle@rac3:/u01/app/oracle/product/11.2.0.4/dbs/orapwDELL_CDG
oracle@rac3's password:
orapwDELL 100% 1536 1.5KB/s 00:00
[oracle@rac1 dbs]$
On CASCADED STANDBY
14. Restore the Standby Controlfile
[oracle@rac3 backup]$ ls -ltr total 1313304 -rw-r-----. 1 oracle dba 675995648 Dec 23 15:11 database_DELL_0btlgf74_11 -rw-r-----. 1 oracle dba 433709056 Dec 23 15:11 database_DELL_0ctlgf74_12 -rw-r-----. 1 oracle dba 10092544 Dec 23 15:11 database_DELL_0dtlgf74_13 -rw-r-----. 1 oracle dba 98304 Dec 23 15:11 database_DELL_0etlgf76_14 -rw-r-----. 1 oracle dba 21750784 Dec 23 15:13 arch_DELL_0ftlgf81_15 -rw-r-----. 1 oracle dba 68794880 Dec 23 15:13 arch_DELL_0gtlgf81_16 -rw-r-----. 1 oracle dba 69376000 Dec 23 15:13 arch_DELL_0htlgf82_17 -rw-r-----. 1 oracle dba 54898688 Dec 23 15:13 arch_DELL_0itlgf85_18 -rw-r-----. 1 oracle dba 10092544 Dec 23 15:13 Control_DELL_0jtlgf89_19 [oracle@rac3 backup]$ . oraenv ORACLE_SID = [DELL_CDG] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac3 backup]$ [oracle@rac3 backup]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 23 15:51:13 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: DELL (not mounted) RMAN> restore standby controlfile from '/u02/oracle/backup/Control_DELL_0jtlgf89_19'; Starting restore at 23-DEC-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oracle/oradata/DELL_CDG/control01.ctl output file name=/u02/oracle/oradata/fast_recovery_area/DELL_CDG/control02.ctl Finished restore at 23-DEC-18 RMAN>
15. Mount the cascaded standby database
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1
RMAN>
16. Catalog all the backup pieces
RMAN> catalog start with '/u02/oracle/backup';
Starting implicit crosscheck backup at 23-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 23-DEC-18
Starting implicit crosscheck copy at 23-DEC-18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 23-DEC-18
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u02/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /u02/oracle/backup/Control_DELL_0jtlgf89_19
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/oracle/backup/Control_DELL_0jtlgf89_19
RMAN>
17. Restore and Recover Database
RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 10 20.74M DISK 00:00:02 23-DEC-18 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20181223T141528 Piece Name: /u02/oracle/backup/arch_DELL_0ftlgf81_15 List of Archived Logs in backup set 10 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 14 954005 08-DEC-18 954381 08-DEC-18 1 15 954381 08-DEC-18 955102 08-DEC-18 1 16 955102 08-DEC-18 955160 08-DEC-18 1 17 955160 08-DEC-18 955164 08-DEC-18 1 18 955164 08-DEC-18 955170 08-DEC-18 1 19 955170 08-DEC-18 955174 08-DEC-18 1 20 955174 08-DEC-18 955178 08-DEC-18 1 21 955178 08-DEC-18 956913 08-DEC-18 1 22 956913 08-DEC-18 956917 08-DEC-18 1 23 956917 08-DEC-18 956922 08-DEC-18 1 24 956922 08-DEC-18 957349 08-DEC-18 1 25 957349 08-DEC-18 978443 08-DEC-18 1 26 978443 08-DEC-18 978446 08-DEC-18 1 27 978446 08-DEC-18 978739 08-DEC-18 1 28 978739 08-DEC-18 978869 08-DEC-18 1 29 978869 08-DEC-18 999033 08-DEC-18 1 30 999033 08-DEC-18 999036 08-DEC-18 1 31 999036 08-DEC-18 999299 08-DEC-18 1 32 999299 08-DEC-18 999926 08-DEC-18 1 33 999926 08-DEC-18 1001120 09-DEC-18 1 34 1001120 09-DEC-18 1001401 09-DEC-18 1 35 1001401 09-DEC-18 1003961 09-DEC-18 1 36 1003961 09-DEC-18 1004255 09-DEC-18 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 11 65.61M DISK 00:00:03 23-DEC-18 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20181223T141528 Piece Name: /u02/oracle/backup/arch_DELL_0gtlgf81_16 List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 37 1004255 09-DEC-18 1010611 09-DEC-18 1 38 1010611 09-DEC-18 1011007 13-DEC-18 1 39 1011007 13-DEC-18 1011331 13-DEC-18 1 40 1011331 13-DEC-18 1012228 13-DEC-18 1 41 1012228 13-DEC-18 1013929 13-DEC-18 1 42 1013929 13-DEC-18 1020556 13-DEC-18 1 43 1020556 13-DEC-18 1023975 13-DEC-18 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 12 66.16M DISK 00:00:03 23-DEC-18 BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20181223T141528 Piece Name: /u02/oracle/backup/arch_DELL_0htlgf82_17 List of Archived Logs in backup set 12 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 1023975 13-DEC-18 1023978 13-DEC-18 1 2 1023978 13-DEC-18 1024272 13-DEC-18 1 3 1024272 13-DEC-18 1024698 13-DEC-18 1 4 1024698 13-DEC-18 1024733 13-DEC-18 1 5 1024733 13-DEC-18 1024738 13-DEC-18 1 6 1024738 13-DEC-18 1024744 13-DEC-18 1 7 1024744 13-DEC-18 1030847 13-DEC-18 1 8 1030847 13-DEC-18 1031174 13-DEC-18 1 9 1031174 13-DEC-18 1031178 13-DEC-18 1 10 1031178 13-DEC-18 1031185 13-DEC-18 1 11 1031185 13-DEC-18 1031853 13-DEC-18 1 12 1031853 13-DEC-18 1078584 13-DEC-18 1 13 1078584 13-DEC-18 1078587 13-DEC-18 1 14 1078587 13-DEC-18 1078869 13-DEC-18 1 15 1078869 13-DEC-18 1078925 13-DEC-18 1 16 1078925 13-DEC-18 1079499 14-DEC-18 1 17 1079499 14-DEC-18 1079798 14-DEC-18 1 18 1079798 14-DEC-18 1079977 14-DEC-18 1 19 1079977 14-DEC-18 1100515 14-DEC-18 1 20 1100515 14-DEC-18 1100518 14-DEC-18 1 21 1100518 14-DEC-18 1100817 14-DEC-18 1 22 1100817 14-DEC-18 1110716 14-DEC-18 1 23 1110716 14-DEC-18 1132055 20-DEC-18 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 13 52.35M DISK 00:00:02 23-DEC-18 BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20181223T141528 Piece Name: /u02/oracle/backup/arch_DELL_0itlgf85_18 List of Archived Logs in backup set 13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 24 1132055 20-DEC-18 1132357 20-DEC-18 1 25 1132357 20-DEC-18 1132668 20-DEC-18 1 26 1132668 20-DEC-18 1148516 23-DEC-18 1 27 1148516 23-DEC-18 1148845 23-DEC-18 1 28 1148845 23-DEC-18 1152235 23-DEC-18 1 29 1152235 23-DEC-18 1152930 23-DEC-18 1 30 1152930 23-DEC-18 1152936 23-DEC-18 1 31 1152936 23-DEC-18 1157442 23-DEC-18 1 32 1157442 23-DEC-18 1157469 23-DEC-18 1 33 1157469 23-DEC-18 1157673 23-DEC-18 1 34 1157673 23-DEC-18 1157677 23-DEC-18 1 35 1157677 23-DEC-18 1157684 23-DEC-18 1 36 1157684 23-DEC-18 1158527 23-DEC-18 1 37 1158527 23-DEC-18 1158553 23-DEC-18 <------ RMAN> [oracle@rac3 backup]$ cat restore_db.sh ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4 BACKUP_LOG_PATH=/u02/oracle/backup export ORACLE_BASE export ORACLE_HOME export ORACLE_SID=DELL_CDG export BACKUP_LOG_PATH LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log /u01/app/oracle/product/11.2.0.4/bin/rman msglog=${LOG_FILE} <<EOF connect target / run { allocate channel t1 type disk; allocate channel t2 type disk; allocate channel t3 type disk; set until sequence 38; <--- 37 + 1 restore database; recover database; } exit EOF [oracle@rac3 backup]$ chmod 775 restore_db.sh [oracle@rac3 backup]$ nohup ./restore_db.sh & [1] 15411 [oracle@rac3 backup]$ nohup: ignoring input and appending output to `nohup.out' [oracle@rac3 backup]$ jobs -l [1]+ 15411 Running nohup ./restore_db.sh & [oracle@rac3 backup]$ [oracle@rac3 backup]$ cat restore_db.log Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 23 16:07:42 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: DELL (DBID=3965088591, not open) RMAN> 2> 3> 4> 5> 6> 7> 8> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=18 device type=DISK allocated channel: t2 channel t2: SID=30 device type=DISK allocated channel: t3 channel t3: SID=31 device type=DISK executing command: SET until clause Starting restore at 23-DEC-18 channel t1: starting datafile backup set restore channel t1: specifying datafile(s) to restore from backup set channel t1: restoring datafile 00002 to /u02/oracle/oradata/DELL_CDG/sysaux01.dbf channel t1: restoring datafile 00003 to /u02/oracle/oradata/DELL_CDG/undotbs01.dbf channel t1: reading from backup piece /u02/oracle/backup/database_DELL_0ctlgf74_12 channel t2: starting datafile backup set restore channel t2: specifying datafile(s) to restore from backup set channel t2: restoring datafile 00001 to /u02/oracle/oradata/DELL_CDG/system01.dbf channel t2: restoring datafile 00004 to /u02/oracle/oradata/DELL_CDG/users01.dbf channel t2: reading from backup piece /u02/oracle/backup/database_DELL_0btlgf74_11 channel t1: piece handle=/u02/oracle/backup/database_DELL_0ctlgf74_12 tag=TAG20181223T141500 channel t1: restored backup piece 1 channel t1: restore complete, elapsed time: 00:00:15 channel t2: piece handle=/u02/oracle/backup/database_DELL_0btlgf74_11 tag=TAG20181223T141500 channel t2: restored backup piece 1 channel t2: restore complete, elapsed time: 00:00:15 Finished restore at 23-DEC-18 Starting recover at 23-DEC-18 starting media recovery channel t1: starting archived log restore to default destination channel t1: restoring archived log archived log thread=1 sequence=36 channel t1: restoring archived log archived log thread=1 sequence=37 channel t1: reading from backup piece /u02/oracle/backup/arch_DELL_0itlgf85_18 channel t1: piece handle=/u02/oracle/backup/arch_DELL_0itlgf85_18 tag=TAG20181223T141528 channel t1: restored backup piece 1 channel t1: restore complete, elapsed time: 00:00:01 archived log file name=/u02/oracle/archive/DELL_CDG/1_36_994761301.dbf thread=1 sequence=36 archived log file name=/u02/oracle/archive/DELL_CDG/1_37_994761301.dbf thread=1 sequence=37 Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u02/oracle/oradata/DELL_CDG/system01.dbf' media recovery complete, elapsed time: 00:00:00 Finished recover at 23-DEC-18 released channel: t1 released channel: t2 released channel: t3 RMAN> Recovery Manager complete. [oracle@rac3 backup]$
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL READ ONLY DELL_CDG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> SQL> set lines 180 SQL> col member for a50 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 3 ONLINE /u02/oracle/oradata/DELL_CDG/redo03.log NO 2 ONLINE /u02/oracle/oradata/DELL_CDG/redo02.log NO 1 ONLINE /u02/oracle/oradata/DELL_CDG/redo01.log NO 4 STANDBY /u02/oracle/oradata/DELL_CDG/redo04.log NO 5 STANDBY /u02/oracle/oradata/DELL_CDG/redo05.log NO 6 STANDBY /u02/oracle/oradata/DELL_CDG/redo06.log NO 7 STANDBY /u02/oracle/oradata/DELL_CDG/redo07.log NO 7 rows selected. SQL> SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#; GROUP# MEMBER BYTES ---------- -------------------------------------------------- ---------- 4 /u02/oracle/oradata/DELL_CDG/redo04.log 52428800 5 /u02/oracle/oradata/DELL_CDG/redo05.log 52428800 6 /u02/oracle/oradata/DELL_CDG/redo06.log 52428800 7 /u02/oracle/oradata/DELL_CDG/redo07.log 52428800 SQL>
19. Enable MRP on CASCADED STANDBY
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL READ ONLY DELL_CDG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> / ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION * ERROR at line 1: ORA-01153: an incompatible media recovery is active <-- This is expected SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL READ ONLY WITH APPLY DELL_CDG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL>
On Primary : DELL
SQL> set lines 180 SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL READ WRITE DELL PRIMARY MAXIMUM PERFORMANCE SQL> CREATE TABLE TEST (A NUMBER); Table created. SQL> INSERT INTO TEST VALUES (1); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM TEST; COUNT(*) ---------- 1 <----- SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL>
On STANDBY : DELL_DG
SQL> SET LINES 180 SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL MOUNTED DELL_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * ERROR at line 1: ORA-10456: cannot open standby database; media recovery session may be in progress SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL READ ONLY WITH APPLY DELL_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> SQL> SELECT COUNT(*) FROM TEST; COUNT(*) ---------- 1 <----- SQL>
On CASCADED STANDBY: DELL_CDG
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- DELL READ ONLY WITH APPLY DELL_CDG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> SELECT COUNT(*) FROM TEST; COUNT(*) ---------- 1 <---- SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Reference:
Cascaded Standby Databases in Oracle 10g/11g (Doc ID 409013.1)
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com