Tuesday, March 13, 2018

12c Data Guard : Resolve Log gap Error: ORA-16724: "cannot resolve gap for one or more standby databases"

This error happened while my Lab standby server was unreachable for few days. Although my setup was built on top of a Windows environment, the resolution process stays the same here for fixing this error (using an RMAN incremental backup) .
For more details about the Data Guard setup used in this example please refer to my previous post >> Part I :  Implementation

• Note : mtldb = Primary        mtlstd = Standby

- Display of the DGMGRL “show configuration” output

DGMGRL> show configuration verbose
Configuration - dg12
  Protection Mode: MaxPerformance
  Members:
  mtldb  - Primary database
    Error: ORA-16724: cannot resolve gap for one or more standby databases
    mtlstd - Physical standby database

  - Check Primary DB SCN Number

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  135966683

  - Check Standby DB SCN Number

@mtlsd> select current_scn from gv$database;

CURRENT_SCN
-----------
  114264654 

- Check  Standby checkpoint DB SCN Number

@mtlstd> select min (checkpoint_change#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
-----------------------
  114264655



STEP 1 STOP THE RECOVERY PROCESS (APPLY OFF)

DGMGRL> edit  DATABASE MTLSTD set STATE=APPLY-OFF;



STEP 2  PERFORM A INCREMENTAL BACKUP FROM PRIMARY DB to an SCN that is a bit behind that of the STANDBY

  RMAN> run
  {allocate channel ch1 type disk;
   backup incremental from scn 114264000 database format       
   'E:\MSSQL_backup\standby_%d_%t_%c_%p';
  }



STEP 3 RECOVER STANDBY DB

  A) Move the rman backup piece to the standby server => F:\Ora_Backup\standby

  B) Catalog backup piece on the standby server
      --- ON STANDBY SERVER 

$ rman target /   
RMAN> catalog start with 'F:\Ora_Backup\standby';
    List of Cataloged Files
    =======================
    File Name: F:\ORA_BACKUP\STANDBY\STANDBY_MTLDB_955116673_1_1
    File Name: F:\ORA_BACKUP\STANDBY\STANDBY_MTLDB_955116718_1_1



C) Recover the standby database using the backup piece which has taken from the PRIMARY DB

-- RUN FROM STANDBY SERVER 

RMAN> run
{allocate channel ch1 type disk;
  recover database noredo;}


STEP 4 Create standby control from primary DB

@mtldb> alter database create standby controlfile as 'E:\backup\standby.ctl';




STEP 5 Restore the Control file in the STANDBY DB

-- Below steps are in the standby server
A) STOP THE STANDBY DATABASE (if it’s a  RAC database run it on all instances)

$ srvctl stop database -d mtlstd

B) OPEN THE STANDBY INSTANCE in NoMount mode  (if it’s a  RAC database only one instance should be open)

   $ sqlplus / as sysdba
   SQL> startup nomount


C) IDENTIFY THE CONTROL FILE LOCATION

SQL> show parameter control_files


D) Past the copied standby control file from primary DB in STEP4

E) RESTORING CONTROL FILE TO STANDBY DATABASE

RMAN> restore controlfile from 'F:\Ora_Backup\standby\standby.ctl';
RMAN> alter database mount;


STEP 6 CHANGING CONTROLFILE CONFIGURATION (on standby DB)

$rman target /   
RMAN> CATALOG START WITH '+DATA/MTLSTD/DATAFILE/';

  
/*** STEP 7 (special case)  - If no Datafile has been added during the redo apply lag please skip to STEP9

Check for New Datafile added in primary DB

-- IN STANBDY DB

   SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# >      114264655;

   No rows ..

  NOTE: If any files available, we have to copy it from primary to the standby database using RMAN

  •    copy the datafile from the primary database (example here file 10 exist only in primary DB).
RMAN>
run
  {allocate channel a1 type disk; backup datafile 10 format 'E:\backup\datafile_%d_%U_%p_%c';}


  • Transfer the rman backup datafile to standby database location then catalog the backup piece in the standby database.
RMAN>
run
{allocate channel a1 type disk; Restore datafile 10;}



STEP 8 Switch the datafile USING RMAN
  This process will change the datafile details of the standby database in the controlfile

RMAN> SWITCH DATABASE TO COPY;

*****/

STEP 9 Clear the standby redo log files

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;

  


STEP 10 START THE RECOVEY PROCESS ON STANDBY

$dgmgrl     ----- On standby server
DGMGRL> edit database 'mtlstd' set state='APPLY-ON';
Succeeded.

** FINAL RESULT

DGMGRL> show configuration
Configuration - dg12
  Protection Mode: MaxPerformance
  Members:
  mtldb  - Primary database
    mtlstd - Physical standby database

Fast-Start Failover: DISABLED


Configuration Status:

SUCCESS   (status updated 2 seconds ago)

2 comments: