How to create a Standby Database in RAC using RMAN Duplicate command

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

PFB steps to create a Standby Database in RAC using Active Database Duplication Method :-

Primary Database is TEST
Standby Database is STBY(RAC)

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 = SCAN-IP)(PORT = 9010))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY.wellsfargo.com)
    )
  )

STBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VIP-Firstnode)(PORT = 9001))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY.wellsfargo.com)
       (INSTANCE_NAME = STBY1)
    )
  )
STBY2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VIP-SECONDnode)(PORT = 9001))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY.wellsfargo.com)
       (INSTANCE_NAME = STBY2)
    )
  )

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 = SCAN-IP)(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

  First node :-

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

Second Node :-

  (SID_DESC =
    (GLOBAL_DBNAME = STBY.wellsfargo.com)
    (SID_NAME = STBY2)
    (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
    (SID_NAME = STBY2)
    (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/orapwSTBY1
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY1.ora

Steps to be done in STANDBY Side :-

15 . Update Pfile for below parameters for Standby server

    *.audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
    *.diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'

16. Start the STANDBY database with above pfile

export ORACLE_SID=STBY1
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY1.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
               
17. Connect to RMAN and use RMAN duplicate command to create STANDBY

rman target sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL

run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
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='+DATA_0220/STBY/controlfile/control01.ctl','+FRA_0220/STBY/controlfile/control02.ctl'
                set db_create_file_dest='+DATA_0220/'
                set db_create_online_log_dest_1='+DATA_0220/'
                set diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
                set audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
                set db_recovery_file_dest='+FRA_0220/'
                set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','+DATA_0220/STBY/'
                set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','+DATA_0220/STBY/'
                set FAL_SERVER='TEST'
set cluster_database='false'
set instance_number='1'
set LOG_ARCHIVE_DEST_1='location=+FRA_0220/STBY/';

}  

Above command will create the STANDBY database

18. Create pfile from spfile

        create pfile='/ora_backup/duplicate_test/STBY/initTEST.ora' from spfile;

19. Add the below parametErs for RAC database

STBY1.log_archive_format='%t_%s_%r.dbf'
STBY2.log_archive_format='%t_%s_%r.dbf'
STBY2.thread=2
STBY1.thread=1
STBY1.undo_tablespace='UNDOTBS1'
STBY2.undo_tablespace='UNDOTBS2'
STBY2.instance_number=2
STBY1.instance_number=1
STBY1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = first_node)(PORT = 9001))'
STBY2. local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = second_node)(PORT = 9001))'
*.cluster_database=TRUE

20. Start the instance with above pfile

==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 12:51:57 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options

SQL> startup force pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             679478240 bytes
Database Buffers          369098752 bytes
Redo Buffers                9748480 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST     READ ONLY

SQL> create spfile='+DATA_0220/STBY/spfileSTBY.ora' from pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             679478240 bytes
Database Buffers          369098752 bytes
Redo Buffers                9748480 bytes
Database mounted.
Database opened.

SQL> show parameter cluster

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

cluster_interconnects                string

SQL>

SQL> show parameter listenner

SQL> show parameter listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

listener_networks                    string

local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO

                                                 ST = first_node)(PORT = 9001))

remote_listener                      string      scan_ip:9010

21. Login to second instance 

Export ORACLE_SID=STBY2
==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 13:01:32 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             679478240 bytes
Database Buffers          369098752 bytes
Redo Buffers                9748480 bytes
Database mounted.
Database opened.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST        READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,LOGINS from gv$instance;

   INST_ID INSTANCE_NAME    HOST_NAME                 VERSION           LOGINS
---------- ---------------- ------------------------- ----------------- ----------
         2 STBY2         wuprd02a0220              11.2.0.4.0        ALLOWED
         1 STBY1         wuprd01a0220              11.2.0.4.0        ALLOWED

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO
                                                 ST = second_node)(PORT = 9001))
remote_listener                      string      scan_ip:9010
SQL>

Check all the parameters in both instances are same or not

22. Start the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

23. Add the details of Standby database in OCR

Srvctl add database –d STBY –o $ORACLE_HOME -p 

Srvctl add instance –d STBY –i STBY1-n firstnode

Srvctl add instance –d STBY –I STBY2 –n secondnode

Srvctl status database –d STBY

24. 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';

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

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

Create the configuation

DGMGRL> connect /
Connected.
DGMGRL> create configuration DR as primary database is TEST connect identifier is TEST;
Configuration "dr" created with primary database "TEST"

DGMGRL> add database STBY as connect identifier is STBY  maintained as physical;
Database "STBY" added
DGMGRL> show configuration

Configuration - dr

  Protection Mode: MaxPerformance
  Databases:
    TEST   - Primary database
    STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.

Check if every thing is fine by checking Standby Database details 

DGMGRL> show database STBY

Database - STBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    STBY1 (apply instance)
    STBY2

Database Status:
SUCCESS

DGMGRL> exit

Standby Creation in RAC using RMAN duplicate command completed.

Standby creation for standalone using RMAN duplicate command

I hope this article helped you

Regards,
Amit Rath