Oracle
Data Guard Physical Standby Creation

Assumptions

  • You are using Oracle Database 12c or above

Create Physical Standby

STANDBY

  • Install Oracle binaries. For a Standby it can be a good idea to clone the Oracle Home rather than perform a fresh install.

  • Ensure the Standby TNSNAMES.ORA includes stanzas for both the Standby and Primary database.

  • Ensure the Primary LISTENER.ORA includes a static Listener entry (Note: the Broker specific configuration is optional at this stage)

  • Ensure the Listener is started

  • Create an init.ora containing just DB_NAME...

echo DB_NAME=${ORACLE_SID} >$ORACLE_HOME/dbs/init${ORACLE_SID}.ora

  • Create a Password File - password should match the Primary DB (an alternate approach is to copy the password file from the Primary and rename it). For the command below you will be prompted for the password...

orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID}

  • Startup the database but don't Mount it...

sqlplus / as sysdba

startup nomount

exit

  • Create target directories...

mkdir -p /u01/dba/rman

mkdir -p /u02/oradata/ORCL2

mkdir -p /controlfile1/ORCL2

mkdir -p /controlfile2/ORCL2

mkdir -p /fra/ORCL2

PRIMARY

  • Check the database is running in ARCHIVELOG mode.

  • Enable Forced Logging

  • Ensure the Primary TNSNAMES.ORA includes stanzas for both the Standby and Primary database.

  • Ensure the Primary LISTENER.ORA includes a static Listener entry (Note: the Broker specific configuration is optional at this stage)

  • Ensure the Listener is started

  • LOCAL_LISTENER should be set

  • Create Standby Redo Logs

SELECT log_mode

FROM v$database;

SHOW PARAMETER LOCAL_LISTENER

SELECT force_logging

FROM v$database;

  • Use RMAN to Duplicate the Primary database to create the Physical Standby

In this example the Primary database is ORCL1 and the Standby is ORCL2

rman

connect target sys@ORCL1

connect auxiliary sys@ORCL2


spool log to /u01/dba/rman/ORCL2_create.log

set echo on


run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate channel prmy5 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'ORCL1','ORCL2'

set db_unique_name='ORCL2'

set control_files='/controlfile1/ORCL2/ctlORCL201.ctl','/controlfile2/ORCL2/ctlORCL202.ctl'

set db_create_file_dest='/u02/oradata/'

set db_recovery_file_dest='/fra/ORCL2'

set db_recovery_file_dest_size='100G'

nofilenamecheck;

}


spool log off

STANDBY

*

If the DUPLICATE fails


*

If the Duplicate fails, you need to do the following cleanup steps on the Standby before restarting the RMAN script above...

sqlplus / as sysdba

shutdown abort

exit

rm $ORACLE_HOME/dbs/spfileORCL2.ora

rm -rf /u02/oradata/ORCL2

rm /controlfile*/ORCL2/*.ctl

mkdir -p /u02/oradata/ORCL2

sqlplus / as sysdba

startup nomount

exit

PRIMARY

  • Set Initialisation Parameters

ALTER SYSTEM SET FAL_CLIENT=ORCL1 SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=ORCL2 SCOPE=BOTH;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL1,ORCL2)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;


ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL2 ASYNC NOAFFIRM REOPEN=300 DB_UNIQUE_NAME=ORCL2 VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILE)' SCOPE=BOTH;


ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH;


NOTE: In a Data Guard configuration DB_NAME should be the same on the Primary and the Standby whilst DB_UNIQUE_NAME should be different. In our example we would expect DB_NAME to be ORCL and DB_UNIQUE_NAME to be ORCL1 on the Primary. If DB_UNIQUE_NAME is ORCL then fix it with...ALTER SYSTEM SET db_unique_name='ORCL1' SCOPE=SPFILE;You will need to bounce the database for this change to take effect.INFO: FAL stands for Fetch Archive Log.

LOG_ARCHIVE_DEST_n Parameter Attributes

Data Guard Concepts and Administration

Database Reference

STANDBY

  • Set Initialisation Parameters

ALTER SYSTEM SET FAL_CLIENT=ORCL2 SCOPE=BOTH;

ALTER SYSTEM SET FAL_SERVER=ORCL1 SCOPE=BOTH;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL1,ORCL2)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;


ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 ASYNC NOAFFIRM REOPEN=300 DB_UNIQUE_NAME=ORCL1 VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILE)' SCOPE=BOTH;


ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH;

  • if not using OMF then:

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='ORCL1','ORCL2' SCOPE=SFILE;

  • Start Managed recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

  • Check in alert.log to be sure the databases are communicating. You should see things like this...

RFS[1]: Selected log 4 for T-1.S-33 dbid 1558941581 branch 10311608452020-02-02T05:49:52.100928+00:00Archived Log entry 7 added for T-1.S-32 ID 0x5cec1c8d LAD:12020-02-02T05:49:52.159044+00:00Media Recovery Waiting for thread 1 sequence 33 (in transit)2020-02-02T05:49:52.160623+00:00Recovery of Online Redo Log: Thread 1 Group 4 Seq 33 Reading mem 0 Mem# 0: /u02/oradata/ORCL2/onlinelog/o1_mf_4_h3cymv1f_.log Mem# 1: /u02/fast_recovery_area/ORCL2/ORCL2/onlinelog/o1_mf_4_h3cymv9p_.log

  • Use this query to confirm that the Transport Lag and Apply Lag is 0...

SELECT NAME, VALUE, UNIT

FROM V$DATAGUARD_STATS;


NAME VALUE UNIT-------------------------------- ---------------------------------------------------------------- ------------------------------transport lag +00 00:00:00 day(2) to second(0) intervalapply lag +00 00:00:00 day(2) to second(0) intervalapply finish time +00 00:00:00.000 day(2) to second(3) intervalestimated startup time 8 second

Next Steps

Troubleshooting

If you are on 12.1.0.2 review...

Bug 22153958 - ORA-600 [1868] on RMAN Duplicate or RESTORE from SERVICE (Doc ID 22153958.8)

Bibliography


http://ranjeetwipro.blogspot.com/2011/01/falclient-and-falserver-parameters.htmlhttps://www.red-gate.com/simple-talk/databases/oracle-databases/rolling-forward-a-physical-standby-database-using-the-recover-command/
https://support.oracle.comRolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)How to remove a Data Guard Configuration from Primary Database (Doc ID 733794.1)
Bug 22153958 - ORA-600 [1868] on RMAN Duplicate or RESTORE from SERVICE (Doc ID 22153958.8)RMAN active duplicate hanging on restore control file (Doc ID 2073604.1)SRDC - Required Diagnostic Data Collection for RMAN Issues (Doc ID 1671431.1)