Oracle
Data Guard Physical Standby Creation (10g)

Assumptions

  • You cannot use the 11g+ method.

  • You cannot use the OEM method.

  • You are on 10.2.0.4 or later.

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 ORACLE_SID and ORACLE_HOME are set.

  • 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

DB_CREATE_FILE_DEST=/u02/oradata/$ORACLE_SID

DB_RECOVERY_FILE_DEST=/fra/$ORACLE_SID

CONTROL_FILE_DEST1=/u01/oradata/$ORACLE_SID/controlfile

CONTROL_FILE_DEST2=/u02/oradata/$ORACLE_SID/controlfile


These values are specific to your installation.

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

sqlplus / as sysdba

startup nomount

exit

  • Create target directories...

mkdir -p /u01/dba/rman

mkdir -p ${DB_CREATE_FILE_DEST}

mkdir -p ${CONTROL_FILE_DEST1}

mkdir -p ${CONTROL_FILE_DEST2}

mkdir -p ${DB_RECOVERY_FILE_DEST}

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

SELECT log_mode

FROM v$database;

SHOW PARAMETER LOCAL_LISTENER

SELECT force_logging

FROM v$database;

export PRIMARYSID=ORCL1

export STANDBYSID=ORCL2

export BACKUPDIR=/u02/backup

These values are specific to your installation.

Collect files to transfer to new standby...

mkdir -p ${BACKUPDIR}

sqlplus / as sysdba

CREATE PFILE='/u02/backup/initORCL2.ora' FROM SPFILE;

exit

cp ${ORACLE_HOME}/dbs/orapw${PRIMARYSID} ${BACKUPDIR}/orapw${STANDBYSID}

Create a suitable rman script called ${BACKUPDIR/backup.txt...

echo "BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK FORMAT '${BACKUPDIR}/Database%U' DATABASE PLUS ARCHIVELOG;" >${BACKUPDIR}/backup.txt

echo "BACKUP FORMAT '${BACKUPDIR}/Control%U' CURRENT CONTROLFILE FOR STANDBY;" >>${BACKUPDIR}/backup.txt

Add the following lines to a file called ${BACKUPDIR}backup.sh...

#!/bin/sh
function oradbenv{ cd /home/${USER}
 . /etc/profile
 if [ -a .profile ] then . ./.profile fi}
oradbenv
rman target / @${BACKUPDIR}/backup.txt

Run the backup...

at now

${BACKUPDIR}/backup.sh >${BACKUPDIR}/backup.log 2>&1

EOT

NOTE: In the example above, EOT refers to the "End Of Transmission" character, generally CTRL-D

tail -f ${BACKUPDIR}/backup.log

Copy files across to standby...

scp -pr ${BACKUPDIR}/* standby:${BACKUPDIR}

NOTE: The location of BACKUPDIR should be the same on Primary and Standby. If you need to use a different location then create a symbolic link so that the original path still works.

STANDBY

export PRIMARYSID=ORCL1

export STANDBYSID=ORCL2

export BACKUPDIR=/u02/backup

These values are specific to your installation, but MUST match the values you previously set on the Primary node.

cp /u01/out/forstandby/initORCL2.ora $ORACLE_HOME/dbs

cp /u01/out/forstandby/orapwORCL2 $ORACLE_HOME/dbs

vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

Update database name...

1,$s/ORCL1/ORCL2/g

But DB_NAME needs to remain the same...

*.DB_NAME='ORCL1'

Add...

*.DB_UNIQUE_NAME='ORCL2'

*.DB_FILE_NAME_CONVERT='/orcl1/','/orcl2/','/ORCL1/','/ORCL2/'

*.LOG_FILE_NAME_CONVERT='/orcl1/','/orcl2/','/ORCL1/','/ORCL2/'

Restore the backup...

sqlplus / as sysdba

startup nomount;

exit;

rman

connect target sys@ORCL1

connect auxiliary /

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

exit;

This error is ok...

Oracle Error:ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/u02/oradata/PALLAS/data_D-ATHENA_I-1180906920_TS-SYSTEM_FNO-1_ufo9ac86'
RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 10/12/2022 14:56:09RMAN-05501: aborting duplication of target databaseRMAN-03015: error occurred in stored script Memory ScriptRMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of archived log for thread 1 with sequence 89362 and starting SCN of 11077939790156 found to restore

*

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/spfile${ORACLE_SID}.ora

rm -rf ${DB_CREATE_FILE_DEST}

rm ${CONTROL_FILE_DEST1}/*.ctl

rm ${CONTROL_FILE_DEST2}/*.ctl

mkdir -p ${DB_CREATE_FILE_DEST}

sqlplus / as sysdba

startup nomount

exit

Create Standby Redo Logs on the Standby...

Standby configuration...

ALTER SYSTEM SET FAL_SERVER=ORCL1;

ALTER SYSTEM SET FAL_CLIENT=ORCL2;

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

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=ORCL1 ASYNC NOAFFIRM REOPEN=300 DB_UNIQUE_NAME=ORCL1 VALID_FOR=(primary_role,online_logfile)';

PRIMARY

Create Standby Redo Logs on the Primary...

Primary configuration...

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

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=ORCL2 ASYNC NOAFFIRM REOPEN=300 DB_UNIQUE_NAME=ORCL2 VALID_FOR=(primary_role,online_logfile)';

ALTER SYSTEM SET FAL_SERVER=ORCL2;

ALTER SYSTEM SET FAL_CLIENT=ORCL1;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4

ALTER SYSTEM SWITCH LOGFILE;

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.

STANDBY

Start the Apply Process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

  • 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

Check Lag

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

SET LINESIZE 200

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

Create SPFILE and PFILE

CREATE SPFILE FROM MEMORY;

CREATE PFILE FROM SPFILE;

SHUTDOWN IMMEDIATE

STARTUP

Next Steps

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://minimalistic-oracle.blogspot.com/2014/06/how-to-set-dbfilenameconvert-and.html
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)

LOG_ARCHIVE_DEST_n Parameter Attributes

Data Guard Concepts and Administration

Database Reference