ODG Physical Standby Gap Resolution

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

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

STANDBY

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

COLUMN current_scn FORMAT 99999999999999

SELECT current_scn FROM v$database;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

dgmgrl /

edit database 'orcl2' set STATE='APPLY-OFF';

If you are not using the Broker, use...ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
Enter the SYS password when prompted

rman target sys

recover database from service ORCL1 noredo using compressed backupset section size 100M;

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

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT; 

Enter the SYS password when prompted

rman target sys

RESTORE STANDBY CONTROLFILE FROM SERVICE ORCL1;

alter database mount;

report schema;

catalog start with '/u02/oradata/ORCL2/';

switch database to copy;

SELECT file#

  FROM V$DATAFILE

  WHERE creation_change# >= &SCN

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 TODO

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;

.... 

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

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

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; 

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 IMMEDIATE;

STARTUP MOUNT;

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

rman target /

CATALOG START WITH '/u01/in/ForStandby'; 

RECOVER DATABASE NOREDO;

rman target /

backup current controlfile for standby format '/u01/out/ForStandbyCTRL.bak';

scp /u01/out/ForStandbyCTRL.bak standby:/u01/in

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

rman target /

SHUTDOWN IMMEDIATE ;

STARTUP NOMOUNT;

RESTORE STANDBY CONTROLFILE FROM '/u01/in/ForStandbyCTRL.bak';

ALTER DATABASE MOUNT;

LIST INCARNATION;

CATALOG START WITH '/u02/oradata/ORCL2/';

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 DATABASE TO COPY;

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

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');  

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;

.... 

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

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

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_config

show 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 null

show parameter LOG_ARCHIVE_DEST_STATE_2

Can remain ENABLE

show parameter DG_BROKER_START

Can remain TRUE

show parameter DG_BROKER_CONFIG_FILE1

Can remain /u02/oradata/dr1ORACL1.dat

show parameter DG_BROKER_CONFIG_FILE2

Can remain /u02/oradata/dr2ORACL1.dat

show parameter STANDBY_ARCHIVE_DEST

?/dbs/arch

show parameter STANDBY_FILE_MANAGEMENT

Can remain AUTO

show parameter FAL_SERVER

Can remain ORCL2

show parameter FAL_CLIENT

Can remain ORCL1

sqlplus / as sysdba

shutdown abort

exit

Make sure ORACLE_SID and ORACLE_HOME are correct

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}

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

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

You will be prompted for the password

sqlplus / as sysdba

startup nomount

exit


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 ORCL2
NOTE: 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;

rman target /

backup current controlfile for standby format '/u01/out/ForStandbyCTRL.bak';

scp /u01/out/ForStandbyCTRL.bak standby:/u01/in

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

rman target /

SHUTDOWN IMMEDIATE ;

STARTUP NOMOUNT;

RESTORE STANDBY CONTROLFILE FROM '/u01/in/ForStandbyCTRL.bak';

ALTER DATABASE MOUNT;

LIST INCARNATION;

CATALOG START WITH '/u02/oradata/ORCL2/';

SWITCH DATABASE TO COPY;

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

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