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.

Advertisements

About deepakguptadba

10 years of experience in Database Administrator for Oracle Database 10g//9i/8i/7.x with total 15 years’ comprehensive experience in IT , Pharma / Biotech , Manufacturing, Construction , Auto & Electronics.
This entry was posted in Data Guard and tagged . Bookmark the permalink.

4 Responses to Managing Archive Gaps Manually on Standby Database Setup

  1. kuldeep says:

    Good one, thanks for explaining with example….

  2. Manu says:

    Do you think is it possible to automate this process?
    Maybe with a batch file who finds the gap sequence and regists it using the ALTER DATABASE REGISTER LOGFILE?

    It often happens to my server and i’d like to avoid to check and repare manually.

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s