Configure Cascading Standby Database

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

20. Verify ..Lets Test

___________________________________________________________________________________________________


0. Overview

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.


1. Environment

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]$


18. Verify Standby redo logs

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>


20. Verify ..Lets Test

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

2 thoughts on “Configure Cascading Standby Database

  1. Ty for this blog had read about it in the Oracle documents, along with examples where it can be used. But you actually demonstrated with a setup… Much appreciated…

    Well I have a question here… Would like to use this strategy for migration and eventually get the primary DB out of the setup…
    So setup up would be
    Primary DB > Standby1 > Standby 2
    During the cutover date
    – Switch roles and make Standby1 as Primary DB
    – Get the old Primary DB out of the setup
    – and let the DG continue between
    New Primary (Previous Standby1) > Standby2

  2. Thanks for this helpful article. A bit confused/lost when it comes to cascading standby. I am actually looking for setting up cascading standby databases using dgmgrl/DataGuard, is there no such thing or is this one and the same. After doing all the steps here, I just need to create configuration and add database accordingly?

Leave a Reply

Your email address will not be published. Required fields are marked *