Oracle DB RAC 3 Node DR Configuration Steps
Summary :
This document provides a comprehensive step-by-step guide to configure a 3-node Oracle RAC Data Guard disaster recovery (DR) environment. It covers prerequisites, configuration details, and validation procedures for setting up a physical standby database in a RAC environment. Key steps include enabling force logging and archivelog mode, creating and modifying parameter files and standby redo logs, and performing an RMAN backup and duplication for standby creation.· Detailed instructions for ASM directory setup, password file copying, and TNS configuration ensure proper connectivity and environment consistency.
· The guide concludes with commands to start the standby database, create SPFILE, add instances to OCR, and start managed recovery, ensuring synchronization and replication validation between primary and standby databases.
Assumed you have already Installed Grid Infrastructure and Oracle Database Binary/Software on STANDBY Servers.
High Level Steps:-
Step 1:-Check FORCE LOGGING is enabled
Step 2:- Make sure primary is in archivelog mode
Step 3:- PFILE creation and copy to standby database
Step 4:-Edit the parameters and directories in created pfile
Step 5:- Parameters for primary database
Step 6:- Copy password file from primary to standby server
Step 7:- Make the respective directories for standby database
Step 8:- Keep the database in NOMOUNT stage to create standby database
Step 9:- Create spfile for standby database
Step 10:- Listener and TNS Configuration
Step 11:-Start the listener in Standby database and check the status
Step 12:-In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)
Step 13:-Active Duplication for creating standby database
Step 14:- Post check status for Standby database
Step 15:- Enable the recovery(MRP Process)
Step 16:- Check the Standby database sync status with primary
Perform Prerequisites on Primary Database:
· Enable Force Logging.
· Modify Data Guard related Parameters on spfile or pfile.
· Enable Archivelog Mode.
· Create the Standby Redo Logs.
· Backup the Database for Standby.
· Create pfile for standby database.
· Update the tnsnames.ora file.
Step1:-Enable Force Logging:
SQL> SELECT NAME, FORCE_LOGGING FROM V$DATABASE;
NAME FORCE_LOGGING
--------- ---------------------------------------
ORCL NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT NAME, FORCE_LOGGING FROM V$DATABASE;
NAME FORCE_LOGGING
--------- ---------------------------------------
ORCL YES
Step 2:- Modify Data Guard related Parameters on spfile or pfile:
Primary DB UNIQUE NAME is ORCL and Standby DB UNIQUE NAME is ORCLDR. So Change the value accordingly for your environment.
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL
SQL> SHOW PARAMETER DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL
DB_NAME=ORAL
DB_UNIQUE_NAME=ORCL
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLDR)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
LOG_ARCHIVE_DEST_2='SERVICE=INDIA ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=ORCLDR
DB_FILE_NAME_CONVERT='ORCLDR','ORCL'
LOG_FILE_NAME_CONVERT= 'ORCLDR','ORCL'
STANDBY_FILE_MANAGEMENT=AUTO
Step 3:- Before changing values, create a PFile from SPfile for backup.
Step 3.1:- Below parameters can be changed without rebooting database.
SQL> CREATE PFILE='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_orcl.ora' from SPFILE;
File created.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,DRORCL)' SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRORCL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET FAL_SERVER=DRORCL SCOPE=BOTH SID='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH SID='*';
Step 3.2:-Database reboot is required to be impacted of below parameters changes.
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DRORCL','ORCL' SCOPE=SPFILE SID='*';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DRORCL','ORCL' SCOPE=SPFILE SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE SID='*';
Step 4:- Make sure primary is in archivelog mode
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 87280
Next log sequence to archive 87283
Current log sequence 87283
Step 5:- Create the Standby Redo Logs on Primary DB
Make sure standby logfile size is same with REDO Logfile size. Otherwise it will generate error.
SQL> SELECT MAX(BYTES/1024/1024) SIZE_MB, COUNT(*) FROM V$LOG;
SIZE_MB COUNT(*)
---------- ----------
50 6
alter database add standby logfile thread 1 group 7 '+DATA/ORCL/STANDBYLOG/standby_group_07.log' size 50M;
alter database add standby logfile thread 1 group 8 '+DATA/ORCL/STANDBYLOG/standby_group_08.log' size 50M;
alter database add standby logfile thread 1 group 9 '+DATA/ORCL/STANDBYLOG/standby_group_09.log' size 50M;
alter database add standby logfile thread 1 group 10 '+DATA/ORCL/STANDBYLOG/standby_group_10.log' size 50M;
alter database add standby logfile thread 2 group 11 '+DATA/ORCL/STANDBYLOG/standby_group_11.log' size 50M;
alter database add standby logfile thread 2 group 12 '+DATA/ORCL/STANDBYLOG/standby_group_12.log' size 50M;
Step 6:- Backup the Database for Standby:
Take full backup of PROD database using RMAN:
[oracle@ocmnode1 backup]$ rman target /
connected to target database: ORCL (DBID=1575875723)
RMAN>run
{
allocate channel ch1 type disk format '/u01/software/backup/Primary_bkp_for_stndby_%U';
configure controlfile autobackup format for device type disk to '/u01/software/backup/controlfile_stndby_%F';
backup tag='LVL0_FULLDB_BACKUP' as backupset database include current controlfile plus archivelog;
backup current controlfile for standby;
}
Step 7:- Create pfile for standby database:
[oracle@ocmnode1 admin]$ sqlplus / as sysdba
SQL> create pfile='/u01/software/backup/pfile_standby.ora' from spfile;
File created.
Step 8:- To Find Password File:
[oracle@ocmnode1 admin]$ asmcmd pwget --dbuniquename ORCL
+DATA/ORCL/PASSWORD/pwdorcl.256.1048094823
OR,
[oracle@ocmnode1 admin]$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/ORCL/PARAMETERFILE/spfile.269.1048095383
Password file: +DATA/ORCL/PASSWORD/pwdorcl.256.1048094823
Domain:
Start options: open
Step 8.1:- Copy password file from ASM to Filesystem:
[oracle@ocmnode1 admin]$ asmcmd
ASMCMD> cd +DATA/ORCL/PASSWORD/
ASMCMD> ls
pwdorcl.256.1048094823
ASMCMD> pwcopy pwdorcl.256.1048094823 /tmp/pwdorcl.256.1048094823
copying +DATA/ORCL/PASSWORD/pwdorcl.256.1048094823 -> /tmp/pwdorcl.256.1048094823
[oracle@ocmnode1 admin]$ ls -l /tmp/pwdorcl.256.1048094823
-rw-r----- 1 grid oinstall 7680 Aug 16 18:25 /tmp/pwdorcl.256.1048094823
[oracle@ocmnode1 admin]$ cp /tmp/pwdorcl.256.1048094823 /u01/software/backup/
Step 9:-Update the tnsnames.ora file:
[oracle@ocmnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/network/admin
[oracle@ocmnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
Step 10:- Perform Prerequisites on Standby Database:
· Create required directories for Standby Instance
· Create directories on ASM Diskgroup.
· Copy pfile (created from PROD DB on previous stage) to Standby Database Server
· Copy the password file.
· Copy RMAN backup.
· Copy the tnsnames.ora.
· Modify the pfile.
Step 11:- Create required directories on Standby Servers (all nodes):
# On Node1:
$mkdir –p /u01/app/oracle/admin/DRORCL/adump
$mkdir –p /u01/app/oracle/diag/rdbms/DRORCL/ORCL1
$cd /u01/app/oracle/diag/rdbms/DRORCL/ORCL1
$mkdir trace cdump
# On Node2:
$mkdir –p /u01/app/oracle/admin/DRORCL/adump
$mkdir –p /u01/app/oracle/diag/rdbms/DRORCL/ORCL2
$cd /u01/app/oracle/diag/rdbms/DRORCL/ORCL2
# On Node3:
$mkdir –p /u01/app/oracle/admin/DRORCL/adump
$mkdir –p /u01/app/oracle/diag/rdbms/DRORCL/ORCL3
$cd /u01/app/oracle/diag/rdbms/DRORCL/ORCL3
$mkdir trace cdump
Step 12:- Create directories on ASM Diskgroup:
[oracle@ocmdrnode1 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 8188 8089 0 8089 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 8188 8089 0 8089 0 N FRA/
MOUNTED EXTERN N 512 4096 1048576 8189 3717 0 3717 0 Y OCR/
ASMCMD> cd DATA
ASMCMD> mkdir DRORCL
ASMCMD> mkdir DATAFILE CONTROLFILE TEMPFILE ONLINELOG STANDBYLOG PASSWORD PARAMETERFILE
ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> cd FRA
ASMCMD> mkdir DRORCL
ASMCMD> cd DRORCL
ASMCMD> mkdir ARCHIVELOG CONTROLFILE ONLINELOG STANDBYLOG PARAMETERFILE
Step 13:- Copy pfile (created from PROD DB on previous stage) to Standby Database Server:
[oracle@ocmnode1 backup]$ scp pfile_standby.ora oracle@ocmdrnode1:/u01/software/backup/
oracle@ocmdrnode1's password:
pfile_standby.ora 100% 1910 1.9KB/s 00:00
Step14 :- Copy Password File:
[oracle@ocmnode1 backup]$ scp pwdorcl.256.1048094823 oracle@ocmdrnode1:/u01/software/backup/
oracle@ocmdrnode1's password:
pwdorcl.256.1048094823 100% 7680 7.5KB/s 00:00
Step 15:- Copy RMAN Backup:
[oracle@ocmnode1 backup]$ scp Primary_bkp* oracle@ocmdrnode1:/u01/software/backup/
oracle@ocmdrnode1's password:
Primary_bkp_for_stndby_01v8149b_1_1 100% 830MB 59.3MB/s 00:14
Primary_bkp_for_stndby_02v8149s_1_1 100% 1467MB 61.1MB/s 00:24
Primary_bkp_for_stndby_03v814al_1_1 100% 18MB 18.3MB/s 00:00
Primary_bkp_for_stndby_04v814at_1_1 100% 56KB 56.0KB/s 00:00
Primary_bkp_for_stndby_05v814au_1_1 100% 18MB 18.2MB/s 00:00
Step 16:- Modify pfile for Standby Database:
Step 16.1:- Parameter values on PROD database:
[oracle@ocmnode1 backup]$ cat pfile_standby.ora
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.control_files='+DATA/ORCL/CONTROLFILE/current.261.1048094987','+FRA/ORCL/CONTROLFILE/current.256.1048094987'
*.db_file_name_convert='DRORCL','ORCL'
*.db_unique_name='ORCL'
*.fal_server='DRORCL'
*.log_archive_config='DG_CONFIG=(ORCL,DRORCL)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
*.log_archive_dest_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRORCL'
*.log_file_name_convert='DRORCL','ORCL'
Above mentioned parameters need to change for Standby database. After Changed:
[oracle@ocmdrnode1 backup]$ cat pfile_standby.ora
*.audit_file_dest='/u01/app/oracle/admin/DRORCL/adump'
*.control_files='+DATA/DRORCL/CONTROLFILE/control01.ctl','+FRA/DRORCL/CONTROLFILE/control02.ctl'
*.db_file_name_convert='ORCL','DRORCL'
*.db_unique_name='DRORCL'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL,DRORCL)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DRORCL'
*.log_archive_dest_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='ORCL','DRORCL'
*.remote_listener='ocmdrnode-scan:1521'
Standby database parameter file will be like below: (Final Version)
[oracle@ocmdrnode1 backup]$ cat pfile_standby.ora
ORCL1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/DRORCL/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/DRORCL/CONTROLFILE/control01.ctl','+FRA/DRORCL/CONTROLFILE/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='ORCL','DRORCL'
*.db_name='ORCL'
*.db_unique_name='DRORCL'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fal_server='ORCL'
ORCL1.instance_number=1
ORCL2.instance_number=2
*.log_archive_config='DG_CONFIG=(ORCL,DRORCL)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DRORCL'
*.log_archive_dest_2='SERVICE=DRORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='ORCL','DRORCL'
*.memory_target=800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
ORCL2.thread=2
ORCL1.thread=1
ORCL2.undo_tablespace='UNDOTBS2'
ORCL1.undo_tablespace='UNDOTBS1'
3.7. Copy tnsnames.ora File:
Cope primary database’s tnsnames.ora file to Standby database server:
From Primary node to Standby node:
[oracle@ocmnode1 backup]$ scp tnsnames.ora oracle@ocmdrnode1:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/
oracle@ocmdrnode1's password:
tnsnames.ora 100% 691 0.7KB/s 00:00
Add LOCAL_LISTENER parameters in the tnsnames.ora on standby nodes to reflect the standby specific vip host names:
LISTENER_ORCL1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode3-vip)(PORT = 1521)))
LISTENER_DRORCL1_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.56.111)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL2_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.56.112)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL3_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode3-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.56.113)(PORT=1521)(IP=FIRST))))
Step 17:- TNSNAMES.ora files on Standby:
[oracle@ocmdrnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin
[oracle@ocmdrnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode3-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
LISTENER_ORCL1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521)))
LISTENER_ORCL3=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode3-vip)(PORT = 1521)))
LISTENER_DRORCL1_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.111)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL2_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL3_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode3-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521)(IP=FIRST))))
Step 18:- Create Physical Standby Data Guard with RAC:
· Start the Instance in NOMOUNT using Created pfile for standby.
· Restore the Standby Database using RMAN DUPLICATE command.
· Create the Online Redo logs and Standby redo logs.
· Add Standby Database’s TNS on TNSNAMES.ORA on all Standby and Primary Nodes.
· Create SPFILE from PFile and add to OCR.
· Add Standby database to OCR.
· Start Managed Recovery Process on Standby.
· Validate Replication between Primary and Standby.
· Start the Data Guard.
Step 18.1:- Start the Instance in NOMOUNT using Created pfile for standby: (Standby Node1)
[oracle@ocmdrnode1 dbs]$ export ORACLE_SID=ORCL1
[oracle@ocmdrnode1 dbs]$ export ORACLE_BASE=/u01/app/oracle
[oracle@ocmdrnode1 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
[oracle@ocmdrnode1 dbs]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@ocmdrnode1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 17 12:44:06 2025
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_standby.ora';
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 654314224 bytes
Database Buffers 176160768 bytes
Redo Buffers 5455872 bytes
SQL> create spfile='+DATA/DRORCL/PARAMETERFILE/spfileorcl.ora' from pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_standby.ora';
File created.
Step 18.2:- Restore Standby Database using RMAN:
[oracle@ocmdrnode1 dbs]$ rman AUXILIARY /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 17 14:03:06 2025
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: ORCL (not mounted)
RMAN> run{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
DUPLICATE DATABASE FOR STANDBY BACKUP LOCATION '/u01/software/backup/' nofilenamecheck;
release channel c1;
release channel c2;
}
Restoration script log:
Step 18.3:- Add Standby Database’s TNS on TNSNAMES.ORA on all Standby and Primary Nodes:
DRORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
)
)
DRORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL1)
)
)
DRORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL2)
)
)
DRORCL3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode3-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL3)
)
)
Make sure that primary and standby databases TNS are added in tnsnames.ora file on primary and standby database.
Step 18.4:- TNS file on Primary and Standby:
TNSNAMES.ORA File will look like below on Primary Nodes:
TNSNAMES.ORA file on Primary Database: (All Nodes)
[oracle@ocmnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/network/admin
[oracle@ocmnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode3-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
DRORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
)
)
DRORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL1)
)
)
DRORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL2)
)
)
DRORCL3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode3-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL3)
)
)
TNSNAMES.ORA File will look like below on Standby Nodes:
TNSNAMES.ORA file on Standby Database: (All Nodes)
[oracle@ocmdrnode1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/network/admin
[oracle@ocmdrnode1 admin]$ cat tnsnames.ora
# tnsnames.ora.ocmnode1 Network Configuration File: /u01/app/12.1.0/grid/network/admin/tnsnames.ora.ocmnode1
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
ORCL3=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmnode3-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
LISTENER_ORCL1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521)))
LISTENER_ORCL2=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521)))
LISTENER_ORCL3=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode3-vip)(PORT = 1521)))
LISTENER_DRORCL1_OCMDRNODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.111)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL2_OCMDRNODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.112)(PORT=1521)(IP=FIRST))))
LISTENER_DRORCL3_OCMDRNODE3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ocmdrnode3-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521)(IP=FIRST))))
DRORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
)
)
DRORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL1)
)
)
DRORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL2)
)
)
DRORCL3=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocmdrnode3-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRORCL)
(SID = ORCL3)
)
)
From Primary Database Node, check tnsping of standby database.
PRIMARY NODE $ tnsping drorcl
STANDBY NODE $ tnsping orcl
Create SPFILE from PFile:
SQL> create spfile='+DATA/DRORCL/PARAMETERFILE/spfileorcl.ora' from pfile='/u01/app/oracle/product/12.1.0/dbhome_2/dbs/pfile_standby.ora';
$ srvctl modify database –d ORCL –p +DATA/DRORCL/PARAMETERFILE/spfileorcl.ora
Step 18.6:- Add Standby database to OCR:
[oracle@ocmdrnode1 dbs]$ srvctl add database -d ORCL -o /u01/app/oracle/product/12.1.0/dbhome_2 -p +DATA/DRORCL/PARAMETERFILE/spfileorcl.ora -r physical_standby -s MOUNT -t immediate -c RAC -a OCR,DATA,FRA
[oracle@ocmdrnode1 dbs]$ srvctl add instance -d ORCL -i ORCL1 -n ocmdrnode1
[oracle@ocmdrnode1 dbs]$ srvctl add instance -d ORCL -i ORCL2 -n ocmdrnode2
[oracle@ocmdrnode1 dbs]$ srvctl add instance -d ORCL -i ORCL3 -n ocmdrnode3
[oracle@ocmdrnode1 dbs]$ srvctl config database -d orcl
Database unique name: ORCL
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_2
Oracle user: oracle
Spfile: +DATA/DRORCL/PARAMETERFILE/spfileorcl.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: OCR,DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORCL1,ORCL2,ORCL3
Configured nodes: ocmdrnode1,ocmdrnode2,cmdrnode3
Database is administrator managed
[oracle@ocmdrnode1 admin]$ srvctl start database -d orcl
[oracle@ocmdrnode2 ~]$ srvctl status database -d orcl
Instance ORCL1 is running on node ocmdrnode1
Instance ORCL2 is running on node ocmdrnode2
Instance ORCL3 is running on node ocmdrnode3
SQL> SELECT DB_UNIQUE_NAME, NAME, OPEN_MODE, PROTECTION_MODE, DATABASE_ROLE FROM V$DATABASE;
DB_UNIQUE_NAME NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
------------------------------ --------- -------------------- -------------------- ----------------
DRORCL ORCL MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
Step 18.7:- Start Managed Recovery Process on Standby:
Start MRP process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
# Cancel / Stop MRP Process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Validate Replication between Primary and Standby:
SQL> SELECT sequence#, first_time, next_time, APPLIED from V$ARCHIVED_LOG order by sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
25 16-AUG-2025 17:25:58 16-AUG-2025 17:26:49 YES
2
18 rows selected.
SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;
THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
---------- ---------- ----------- -------------------- ----------
1 61 61 17-AUG-2025 20:53:16 0
2 30 29 17-AUG-2025 20:10:54 1
Comments
Post a Comment