How to Create a STANDBY database using RMAN Duplicate command in Oracle

We can also use RMAN duplicate command to create a STANDBY database from Production database. We can either use Backups of Production Database or we can create from Active Database also.

PFB steps to create a standby Database using Active Database Duplication Method :-

Primary Database is TEST
Standby Database is STBY

Changes to be done on Primary side :-

1. Database has to be archive log mode

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

2. Change primary database to Force Logging mode :-

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

3. Initialization parameters in Primary database :-

SQL>  show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      TEST
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TEST

4. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of  Standby database has to be different from primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';

System altered.

5. Set suitable remote archivelog destinations.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database

6. Set parameters related to LOG_ARCHIVE and password file parameters :-

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

7. It is recommended to make sure that Primary can switch roles to become a Standby. For that we need to set some -convert parameters if there are path differences between the servers :-

SQL> ALTER SYSTEM SET FAL_SERVER=STBY;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE;   -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=' ',' '  SCOPE=SPFILE;  -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

8. As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             247465824 bytes
Database Buffers          700448768 bytes
Redo Buffers               11358208 bytes
Database mounted.
Database opened.
SQL>

9. In both servers entries of Primary as well as Standby are needed in tnsnames.ora file and we are able to do  tnsping.

###################ORACLE_DATA_GAURD############################

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STBY_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 9001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )
STBY_DGMGRL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STBY_server)(PORT = 9001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY_DGMGRL)
    )
  )

###################ORACLE_DATA_GAURD############################

10. Add static entries to listener.ora file

PRIMARY

        (SID_DESC =
      (GLOBAL_DBNAME = test.db.com)
      (SID_NAME = TEST)
      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
        )
        (SID_DESC =
        (GLOBAL_DBNAME=TEST_DGMGRL)
        (ORACLE_HOME=/opt/oracle/product/database/11.2.0.4)
        (SID_NAME=test)
        )

STANDBY

      (SID_DESC =
      (GLOBAL_DBNAME = STBY.db.com)
      (SID_NAME = STBY)
      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
    )
      (SID_DESC =
      (GLOBAL_DBNAME = STBY_DGMGRL)
      (SID_NAME = STBY)
      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
    )

11. Reload listener after adding these entries

Lsnrctl reload

12. Add Standby redo logs to primary database :-

ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;

13. Copy the passw0rd file from Primary and copy to Standby

14. Copy the init.ora file from primary and change below parameters :-

CREATE PFILE='/initTEST.ora' from spfile;

scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY.ora

Steps to be done in STANDBY Side :-

15. Start the STANDBY database with above pfile

export ORACLE_SID=STBY
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY.ora'
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             264243040 bytes
Database Buffers          683671552 bytes
Redo Buffers               11358208 bytes
               
16. Connect to RMAN and use RMAN duplicate command to create STANDBY

rman target sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='STBY' COMMENT 'Is STANDBY'
    SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
                set control_files='/ora_backup/TEST/duplicate_test/STBY/control01.ctl','/ora_backup/TEST/duplicate_test/STBY/control02.ctl'
                set db_create_file_dest='/ora_backup/TEST/duplicate_test/STBY/DATA/'
                set db_create_online_log_dest_1='/ora_backup/TEST/duplicate_test/STBY/LOG/'
                set diagnostic_dest='/ora_backup/TEST/duplicate_test/STBY/diag'
                set audit_file_dest='/ora_backup/TEST/duplicate_test/STBY/adump'
                set db_recovery_file_dest='/ora_backup/TEST/duplicate_test/STBY/FRA'
                set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','/ora_backup/TEST/duplicate_test/STBY/DATA/'
                set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','/ora_backup/TEST/duplicate_test/STBY/'
                set log_archive_dest_1='location=/ora_backup/TEST1/duplicate_test/STBY/FRA/'
                set FAL_SEVER=TEST
   ;

Above command will create the STANDBY database

17. Create the broker configuration details ( for both Primary and Standby):-

SQL>  alter system set dg_broker_start=FALSE;

System altered.

SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

Create the configuation

Create configuration DR as primary database is TEST connect identifier is TEST;
Add database STBY as connect identifier is STBY maintained as physical;

show configuration
enable configuration

EDIT DATABASE STBY SET STATE = 'APPLY-ON' [WITH APPLY INSTANCE = ];  -- For RAC database

Check if every thing is fine by checking Standby Database details 

show database stby

DGMGRL> show database "STANDBY"

Database - STANDBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      75.00 KByte/s
  Real Time Query: OFF
  Instance(s):
    STANDBY

Database Status:
SUCCESS

Standby Creation using RMAN duplicate command completed.

Standby creation in RAC using RMAN duplicate.

I hope this article helped you

Regards,
Amit Rath