How to Detect Oracle Data Guard Gap In Your Archivelog Validate SEQUENCE

I will write, what we should do when a log interval “GAP” occurs between Primary / Standby and we accidentally delete some of the archive log files on the Primary database. Suppose we do not have a backup of the deleted archive files. Normally we dba should not allow such a situation, but such a situation can happen to us. In this case, all we need to do is find the missing archive logs of the primary and standby databases.

// Find missing archivelogs

SELECT high.thread #, “LowGap #”, “HighGap #”
 FROM (SELECT thread #, MIN (sequence #) – 1 “HighGap #”
           FROM (SELECT a.thread #, a.sequence #
                   FROM (SELECT * FROM v $ archived_log) a,
                        (SELECT thread #, MAX (next_change #) gap1
                             FROM v $ log_history
                         GROUP BY thread #) b
                  WHERE a.thread # = b.thread # AND a.next_change #> gap1)
       GROUP BY thread #) high,
      (SELECT thread #, MIN (sequence #) “LowGap #”
           FROM (SELECT thread #, sequence #
                   FROM v $ log_history, v $ datafile
                  WHERE checkpoint_change # <= next_change #                         AND checkpoint_change #> = first_change #)
       GROUP BY thread #) low
WHERE low.thread # = high.thread #;

// Archivelogs detected

117763-117787

validate archivelog sequence 117787;

// Check Sequence

select * from V $ ARCHIVED_LOG where SEQUENCE # = 117763;

// We take a backup of the relevant archivelog on the primary side.

BACKUP ARCHIVELOG FROM SEQUENCE 117763 UNTIL SEQUENCE 117787 THREAD 8;

// On the primary side, we copy the corresponding archivelog to the file system.

ASM > cp +fra/dbArch/oradata/devdb12/devdb12_1_283_801988635.arc /dbArch/oradata/devdb12/devdb12_1_283_801988635.arc

// I move Archive log to standby with scp

scp /dbArch/oradata/devdb12/devdb12_1_283_801988635.arc oracle@standby:+fra/dbArch/oradata/devdb12/devdb12_1_283_801988635.arc

// Introduce archive log to standy by

ALTER DATABASE REGISTER LOGFILE ‘+fra/dbArch/oradata/devdb12/devdb12_1_283_801988635.arc’;

Validate

Select sequence #, applied from v $ archived_log where sequence #> = 283;

After these operations are completed, the log apply operation will continue.