Fix the archive GAP issues in DATAGUARD

Fix the archive GAP issues in DATAGUARD

Gap is a range of archived redo logs that were created at a time when the standby database was not available to receive them.in many pratices it happen because the network problems.

We have 2 method to resolving this problem.

a. Manually resolving
b. Automatic resolving : Using log switched and FAL configuration

1. start with detecting gaps in the redo logs by querying the v$archive_gap.

SQL> select * from v$archive_gap;

thread#   low_sequence#   high_sequence#
-------- ----------------  ------------------
         1                     30                      32
   The output indicates our standby database is currently missing log files from sequence 30 to 34.

2. issue following statement on primary database to locate the archived redo log files. assuming
    the local archive destination on primary is LOG_ARCHIVE_DEST_1

SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 30 and 32;
name
----------------------------------
/usr/oraarchive/hai/arch_t1_s30.dbf
/usr/oraarchive/hai/arch_t1_s31.dbf
/usr/oraarchive/hai/arch_t1_s32.dbf
/usr/oraarchive/hai/arch_t1_s33.dbf
/usr/oraarchive/hai/arch_t1_s34.dbf

3. stop the automatic recovery (MRP) of the data guard

SQL> alter database recover managed standby database cancel;

4. transfer manually the archived log files shown on the step 2 to standby database

5. register that archived log files on standby database

SQL> alter database register logfile '/usr/oraarchive/hai/arch_t1_s30.dbf';
SQL> alter database register logfile '/usr/oraarchive/hai/arch_t1_s31.dbf';
SQL> alter database register logfile '/usr/oraarchive/hai/arch_t1_s32.dbf';


6. put the standby database into automatic recovery managed mode

 SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database using current logfile disconnect from session;

7. verify that the gap gets resolved on standby database

SQL> select sequence#, applied from v$archived_log order by sequence#;


SQL> select message from v$dataguard_status where severity like 'Warning';

8. Check the alrt log