Embedded Files
In the event that you see something like this...
DGMGRL> show configuration;Configuration - orcl_dg_config
Protection Mode: MaxPerformance Members: orcl1 - Primary database Error: ORA-16724: cannot resolve gap for one or more standby databases
orcl2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:ERROR (status updated 10 seconds ago)
DGMGRL> show database orcl2
Database - orcl2
Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: (unknown) Apply Lag: (unknown) Average Apply Rate: (unknown) Real Time Query: OFF Instance(s): ORCL2
Database Warning(s): ORA-16857: standby disconnected from redo source for longer than specified threshold
Database Status:WARNING
Method #1
Method #1
NOTE: This is the preferred method for Oracle database version 12c and later. However, I was unable to use this method successfully for version 12.1.0.2 during testing.NOTE: Use of the SYS password is recommended by Oracle in 1987763.1 and this is what I have tested. However, other sites imply you may be able to connect with: rman target /ASSUMPTIONS: 12c+, no RAC, Only 1 Data Guard Standby instance, ORCL1 is the Primary, ORCL2 is the Physical Standby, the instances are running on separate servers and the Standby does not have direct access to the logs on the Primary (e.g. this could be the case if the databases are on the same server and/or are using the same ASM disk group. Refer to 1987763.1.)
PRIMARY
PRIMARY
STANDBY
STANDBY
Check the SCNs to confirm the which datafiles are out of sync...
Retrieve the SCN of the datafiles on the Primary...
sqlplus / as sysdba @/u01/dba/orachkscn.sql
Identify the datafiles which are out of sync...
sqlplus / as sysdba @/u01/dba/orachkscn.sql
Note the current_scn of the Standby...
COLUMN current_scn FORMAT 99999999999999
SELECT current_scn FROM v$database;
Shutdown the Physical Standby and MOUNT it...
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Stop Managed Recovery on Standby...
dgmgrl /
edit database 'orcl2' set STATE='APPLY-OFF';
If you are not using the Broker, use...ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Recover Standby database...
Enter the SYS password when prompted
rman target sys
recover database from service ORCL1 noredo using compressed backupset section size 100M;
Recheck the SCNs to confirm the gap is gone or significantly reduced...
Retrieve the SCN of the datafiles on the Primary...
sqlplus / as sysdba @/u01/dba/orachkscn.sql
Retrieve the SCN of the datafiles on the Standby...
sqlplus / as sysdba @/u01/dba/orachkscn.sql
Refresh Standby Controlfile...
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
Enter the SYS password when prompted
rman target sys
RESTORE STANDBY CONTROLFILE FROM SERVICE ORCL1;
alter database mount;
report schema;
If you are using OMF it is likely that the paths will incorrectly contain the Primary instance name instead of the Standby instance name. This can be easily fixed...
catalog start with '/u02/oradata/ORCL2/';
switch database to copy;
Check whether any datafiles were added to the Primary after the SCN (from the standby) that you noted earlier...
SELECT file#
FROM V$DATAFILE
WHERE creation_change# >= &SCN
Use any datafile numbers returned to restore the missing datafiles to the standby....
run
{
set newname for database to '/';
restore datafile 21 from service ORCL1;
switch datafile 21 to copy;
}
NOTE: This may not work if you are using a recovery catalog.. further investigation is TODOUpdate the names of the Online Redo Logs and Standby Redo Logs in the Standby Controlfile....
SELECT GROUP#
FROM v$logfile
WHERE TYPE='STANDBY'
GROUP BY GROUP#;
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
Start Managed Recovery on Standby...
dgmgrl /
edit database 'orcl2' set STATE='APPLY-ON';
If you are not using the Broker, use...ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Method #2
Method #2
ASSUMPTIONS: 12c+, no RAC, Only 1 Data Guard Standby instance, ORCL1 is the Primary, ORCL2 is the Physical Standby, the instances are running on separate servers and the Standby does not have direct access to the logs on the Primary (e.g. this could be the case if the databases are on the same server and/or are using the same ASM disk group. Refer to 1987763.1.)
PRIMARY
PRIMARY
STANDBY
STANDBY
Stop Managed Recovery on Standby...
dgmgrl /
edit database 'orcl2' set STATE='APPLY-OFF';
exit
If you are not using the Broker, use...ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Note the current_scn of the Standby...
sqlplus / as sysdba
COLUMN current_scn FORMAT 99999999999999
SELECT current_scn
FROM v$database;
COLUMN checkpoint_change# FORMAT 99999999999999
SELECT MIN(checkpoint_change#) AS checkpoint_change#
FROM v$datafile_header
WHERE file# NOT IN (SELECT file#
FROM v$datafile
WHERE enabled = 'READ ONLY');
Shutdown the Physical Standby and MOUNT it...
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Take an incremental Backup of the PRIMARY
Transfer Backup to Standby
rman target /
backup incremental from scn 999999 database format '/u01/out/ForStandby_%U' tag 'FORSTANDBY';
exit
where 999999 is the SCN you noted from the Standby (above)scp /u01/out/ForStandby_* standby:/u01/in
Catalog the backups in the Standby controlfile
rman target /
CATALOG START WITH '/u01/in/ForStandby';
Recover the Standby database
RECOVER DATABASE NOREDO;
Create a standby controlfile backup on Primary...
Transfer Backup to Standby
rman target /
backup current controlfile for standby format '/u01/out/ForStandbyCTRL.bak';
scp /u01/out/ForStandbyCTRL.bak standby:/u01/in
Capture Standby datafile information...
sqlplus / as sysdba
SPOOL /tmp/standby_datafile_names.txt
SET PAGESIZE 1000;
SET LINESIZE 200
COLUMN name FORMAT a60
SELECT file#, name
FROM v$datafile
ORDER BY file# ;
SPOOL OFF
Restore the standby controlfile...
Mount the Standby...
Check the incarnation
rman target /
SHUTDOWN IMMEDIATE ;
STARTUP NOMOUNT;
RESTORE STANDBY CONTROLFILE FROM '/u01/in/ForStandbyCTRL.bak';
ALTER DATABASE MOUNT;
LIST INCARNATION;
Assuming the Standby datafile locations is different to the Primary location... use the information you captured above to catalog the files in the correct location...
CATALOG START WITH '/u02/oradata/ORCL2/';
Check whether any datafiles were added to the Primary after the SCN (from the standby) that you noted earlier...
SELECT file#
FROM V$DATAFILE
WHERE creation_change# >= &SCN
If new datafiles exist, you will need to back them up on Primary and restore them on the Standby before continuing.
Switch datafiles to update the controlfile...
SWITCH DATABASE TO COPY;
Compare the datafile names now with those captured before we started...
sqlplus / as sysdba
SPOOL /tmp/standby_datafile_names1.txt
SET PAGESIZE 1000;
SET LINESIZE 200
COLUMN name FORMAT a60
SELECT file#, name
FROM v$datafile
ORDER BY file# ;
SPOOL OFF
exit
diff /tmp/standby_datafile_names.txt /tmp/standby_datafile_names1.txt
Check the SCN of the Standby has incremented...
sqlplus / as sysdba
COLUMN current_scn FORMAT 999999999999999
SELECT current_scn
FROM v$database;
COLUMN checkpoint_change# FORMAT 99999999999999
SELECT MIN(checkpoint_change#) AS checkpoint_change#
FROM v$datafile_header
WHERE file# NOT IN (SELECT file#
FROM v$datafile
WHERE enabled = 'READ ONLY');
Update the names of the Online Redo Logs and Standby Redo Logs in the Standby Controlfile....
SELECT GROUP#
FROM v$logfile
WHERE TYPE='STANDBY'
GROUP BY GROUP#;
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
....
Start Managed Recovery on Standby...
dgmgrl /
edit database 'orcl2' set STATE='APPLY-ON';
If you are not using the Broker, use...ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Method #3
Method #3
ASSUMPTIONS: 12c+, Broker, no RAC, Only 1 Data Guard Standby instance, ORCL1 is the Primary, ORCL2 is the Physical Standby, the instances are running on separate servers and the Standby does not have direct access to the logs on the Primary (e.g. this could be the case if the databases are on the same server and/or are using the same ASM disk group. Refer to 1987763.1.). Assumes my standard for mount points for controlfiles (/controlfile1/2), (/redo1/2) redo, fra (/fra) and datafiles (/u02).
This method recreates the Standby (it is a subset of the commands used to create the Standby at initial Data Guard configuration). It is the most time-consuming of the three methods, particularly for large databases. Use it as a last resort.
PRIMARY
PRIMARY
STANDBY
STANDBY
dgmgrl /
EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
REMOVE CONFIGURATION;
sqlplus / as sysdba
show parameter LOG_ARCHIVE_CONFIG
If you are using Broker, this will have automatically reverted to nodg_configshow parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter LOG_ARCHIVE_DEST_2
If you are using Broker, these will have automatically reverted to nullshow parameter LOG_ARCHIVE_DEST_STATE_2
Can remain ENABLEshow parameter DG_BROKER_START
Can remain TRUEshow parameter DG_BROKER_CONFIG_FILE1
Can remain /u02/oradata/dr1ORACL1.datshow parameter DG_BROKER_CONFIG_FILE2
Can remain /u02/oradata/dr2ORACL1.datshow parameter STANDBY_ARCHIVE_DEST
?/dbs/archshow parameter STANDBY_FILE_MANAGEMENT
Can remain AUTOshow parameter FAL_SERVER
Can remain ORCL2show parameter FAL_CLIENT
Can remain ORCL1Shutdown Standby
sqlplus / as sysdba
shutdown abort
exit
Remove the old database files from the Standby...
rm /controlfile1/${ORACLE_SID}/*
rm /controlfile2/${ORACLE_SID}/*
rm /redo1/${ORACLE_SID}/onlinelog/*
rm /redo2/${ORACLE_SID}/onlinelog/*
rm /u02/oradata/${ORACLE_SID}/datafile/*
rm /u01/oradata/dr1${ORACLE_SID}.dat
rm /u02/oradata/dr2${ORACLE_SID}.dat
rm $ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora
rm $ORACLE_HOME/dbs/hc_${ORACLE_SID}.dat
rm $ORACLE_HOME/dbs/snapcf_${ORACLE_SID}.f
rm $ORACLE_HOME/dbs/lk${ORACLE_SID}
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 ...
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}
You will be prompted for the passwordStartup the database but don't Mount it...
sqlplus / as sysdba
startup nomount
exit
Use RMAN to Duplicate the Primary database to create the Physical Standby
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
In this example the Primary database is ORCL1 and the Standby is ORCL2NOTE: If you get unexpected RMAN-04006 with ORA-01017 errors connecting to the Auxiliary, then see:
connect target sys@ORCL1
backup database format '/u02/out/ORCL1_%U' tag 'FORSTANDBY';
Transfer backup file(s) to Standby
rman target /
CATALOG START WITH '/u02/in/ORCL1';
REPORT SCHEMA;
Restore tablespaces in turn (ignore TEMP and UNDO)...
RESTORE TABLESPACE system;
RESTORE TABLESPACE sysaux;
RESTORE TABLESPACE users;
RESTORE TABLESPACE mytablespace;
Create a standby controlfile backup on Primary...
Transfer Backup to Standby
rman target /
backup current controlfile for standby format '/u01/out/ForStandbyCTRL.bak';
scp /u01/out/ForStandbyCTRL.bak standby:/u01/in
Capture Standby datafile information...
sqlplus / as sysdba
SPOOL /tmp/standby_datafile_names.txt
SET PAGESIZE 1000;
SET LINESIZE 200
COLUMN name FORMAT a60
SELECT file#, name
FROM v$datafile
ORDER BY file# ;
SPOOL OFF
Restore the standby controlfile...
Mount the Standby...
Check the incarnation
rman target /
SHUTDOWN IMMEDIATE ;
STARTUP NOMOUNT;
RESTORE STANDBY CONTROLFILE FROM '/u01/in/ForStandbyCTRL.bak';
ALTER DATABASE MOUNT;
LIST INCARNATION;
Assuming the Standby datafile locations is different to the Primary location... use the information you captured above to catalog the files in the correct location...
CATALOG START WITH '/u02/oradata/ORCL2/';
Switch datafiles to update the controlfile...
SWITCH DATABASE TO COPY;
Compare the datafile names now with those captured before we started...
sqlplus / as sysdba
SPOOL /tmp/standby_datafile_names1.txt
SET PAGESIZE 1000;
SET LINESIZE 200
COLUMN name FORMAT a60
SELECT file#, name
FROM v$datafile
ORDER BY file# ;
SPOOL OFF
exit
diff /tmp/standby_datafile_names.txt /tmp/standby_datafile_names1.txt
Check the SCN of the Standby has incremented...
sqlplus / as sysdba
COLUMN current_scn FORMAT 999999999999
SELECT current_scn
FROM v$database;
COLUMN checkpoint_change# FORMAT 99999999999999
SELECT MIN(checkpoint_change#) AS checkpoint_change#
FROM v$datafile_header
WHERE file# NOT IN (SELECT file#
FROM v$datafile
WHERE enabled = 'READ ONLY');
Bibliography
Bibliography
Page updated
Google Sites
Report abuse