Assumptions
You cannot use the 11g+ method.
You cannot use the OEM method.
You are on 10.2.0.4 or later.
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}
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
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/shRun 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-Dtail -f ${BACKUPDIR}/backup.log
Copy files across to standby...
scp -pr ${BACKUPDIR}/* standby:${BACKUPDIR}
export PRIMARYSID=ORCL1
export STANDBYSID=ORCL2
export BACKUPDIR=/u02/backup
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'*
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)';
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.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...
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;
CREATE SPFILE FROM MEMORY;
CREATE PFILE FROM SPFILE;
SHUTDOWN IMMEDIATE
STARTUP