Resolving the ORA-16724: cannot resolve gap : A Step-by-Step Adventure

Ever experienced the frustration of dealing with the ORA-16724 error in Oracle Data Guard? You’re not alone! This pesky error popped up in our enviroment when standby server was off the grid for a few days due to hardware issues.

Let’s dive into resolving this log gap error using RMAN incremental backup, with a touch of fun and simplicity.

Environment :

ashbrn à Primary

stmfrd à Standby

The Dreaded Error

When you encounter the ORA-16724 error, it looks something like this:

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

Let's Get to Work!

Step 1: Check the SCNs

First, let's check the SCN (System Change Number) of the primary and standby databases to understand the gap.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
135966683

Standby DB SCN:

STMFRD@ SQL> select current_scn from gv$database;
CURRENT_SCN
-----------
117654839

Standby Checkpoint SCN:

STMFRD@ SQL> select min (checkpoint_change#) from v$datafile_header;
MIN(CHECKPOINT_CHANGE#)
-----------------------
117654840

Step 2: Stop the Recovery Process

Pause the recovery process on the standby database.

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

Step 3: Perform an Incremental Backup from the Primary DB

Take an incremental backup from the primary database, just behind the standby's SCN.

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

Step 4: Recover the Standby DB

    A)Transfer the backup piece to the standby server

    B) Catalog the Backup Piece on the Standby Server:

$ rman target /
RMAN> catalog start with 'F:\Oracle_backup_from_primary\standby';

List of Cataloged Files
    =======================
    File Name: F:\Oracle_backup_from_primary\STANDBY\STANDBY_ASHBRN_458392651_1_1
    File Name: F:\Oracle_backup_from_primary\STANDBY\STANDBY_ASHBRN_458392659_1_1

C) Recover the Standby Database:

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

Step 5: Create and Restore the Standby Control File

Create Standby Control File from Primary:

@ashbrn> alter database create standby controlfile as 'D:\backup\for_standby.ctl';

Restore Control File on Standby and mount the database :

$ srvctl stop database -d stmfrd
$ sqlplus / as sysdba
SQL> startup nomount
SQL> show parameter control_files
RMAN> restore controlfile from 'F:\Ora_Backup_from_standby\standby\for_standby.ctl';
RMAN> alter database mount;

Step 6: Catalog the Datafiles

$rman target /

RMAN> CATALOG START WITH '+DATA/STMFRD/DATAFILE/';

Step 7: Handle New Datafiles (If Any)

Check for any new datafiles added during the gap, using the scn we gathered before :

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

If there are new datafiles, back them up from the primary and restore them on the standby.

Step 8: Switch the Datafile

Step 9: Clear Standby Redo Logs

RMAN> SWITCH DATABASE TO COPY; 

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: Restart the Recovery Process

$dgmgrl
DGMGRL> edit database 'stmfrd' set state='APPLY-ON';
Succeeded.

Final Result 

DGMGRL> show configuration
Configuration - dg_config
  Protection Mode: MaxPerformance
  Members:
  ashbrn - Primary database
  stmfrd - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status: 

SUCCESS (status updated 1 seconds ago)

Conclusion

And there you have it! A simple, step-by-step guide to resolving the ORA-16724 log gap error in Oracle Data Guard using RMAN incremental backup. With this method, you can keep your Data Guard configuration running smoothly without pulling your hair out. Happy database managing!

Thank you for reading! If you have any questions or run into any issues, drop a comment below. Let's keep the conversation going!