Managing Archive Gaps Manually on Standby Database Setup

To determine if there is an archive gap on your physical standby database

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 12499 12511


In this case, Standby database is waiting for archive log 12499

SQL> select process,sequence#,status from v$managed_standby;

PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 12509 CLOSING
ARCH 12510 CLOSING
ARCH 12504 CLOSING
ARCH 12508 CLOSING
MRP0 12499 WAIT_FOR_GAP
RFS 12511 IDLE

6 rows selected.

But if you check, archives have been either shipped already or even if you copy these on to Standby server (MRP is still waiting for 12499)

[oracle@xxxdb2 ~]$ cd /oracle/backups/xxx2/stby_archive
[oracle@xxxdb2 stby_archive]$ ll -l 1_12499*
-rw-r----- 1 oracle oinstall 85923328 Apr 23 19:01 1_12499_704597469.arc
[oracle@xxxdb2 stby_archive]$ ll -l 1_1250* head -5
-rw-r----- 1 oracle oinstall 85897728 Apr 23 19:38 1_12500_704597469.arc
-rw-r----- 1 oracle oinstall 83760640 Apr 23 19:39 1_12501_704597469.arc
-rw-r----- 1 oracle oinstall 1121792 Apr 23 19:39 1_12502_704597469.arc
-rw-r----- 1 oracle oinstall 21140480 Apr 23 19:39 1_12503_704597469.arc
-rw-r----- 1 oracle oinstall 246272 Apr 23 19:31 1_12504_704597469.arc
[oracle@xxxdb2 stby_archive]$


Below SQL is also confirmed for same:


SQL> column dest format a20
SQL> select substr(DEST_NAME,1,20) dest ,archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;


DEST ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
----------------------- ---------------- ------------- --------------- ------------
LOG_ARCHIVE_DEST_1 0 0 0 0
LOG_ARCHIVE_DEST_2 1 12509 0 0
….
LOG_ARCHIVE_DEST_10 0 0 0 0
STANDBY_ARCHIVE_DEST 1 12508 1 12498

11 rows selected.



You may find similar entry or error in standby database's alert log file:

Media Recovery Waiting for thread 1 sequence 12499
Fetching gap sequence in thread 1, gap sequence 12499-12503
Fri Apr 23 21:56:44 2010
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 12499-12503
DBID 2467369570 branch 704597469
FAL[client]: All defined FAL servers have been attempted.


Some time, due to the cancelation of Recovery Process at standby causes a partial apply of archive log. To fix this problem


To find out archive log at Primary database Server:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# between 12499 and 12503;

Copy the missing archives to Standby Server (optional).

Register them using the ALTER DATABASE REGISTER LOGFILE sQL on your physical standby database (to re-appy them):

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12499_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12500_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12501_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12502_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12503_704597469.arc';
Database altered.

MRP automatically start and apply the archive one by one.