How to Resolve Gap Sequence on Physical Standby Databases

Answer:
Resolving Archive Gap Sequence on Physical Standby Databases

Oracle9i has introduced many new features designed to detect and resolve gap sequences. One of the main improvements is the new Fetch Archive Log service using fal_server and fal_client parameters. In spite of these automated process, there are certain scenarios when the gap sequence cannot be avoided, and the DBA needs to intervene to resume the managed recovery on the physical standby database.

Resolving Archive Gap Sequence on Physical Standby Databases

Usually, a stalled managed recovery process is the indication of gap sequence. If the recovery process on the physical standby site is stopped, query the v$archive_gap view to find the gap sequence. The query find_gap.sql from code depot will assist in finding out if there is any archive log gap in the database.

Select 
   THREAD#,
   LOW_SEQUENCE#,
   HIGH_SEQUENCE#
From 
   V$ARCHIVE_GAP;

THREAD#      LOW_SEQUENCE#        HIGH_SEQUENCE#
--------     -------------        --------------
      1                 606                  609

Resolving Archive Gap Sequence on Physical Standby Databases

If the LOW_SEQUENCE# is less than the HIGH_SEQUENCE# in the output, the database is having a gap sequence, and the difference in value is the number of archive logs that must be applied to resolve the gap. In the above output, the Oracle instance is three logs behind the primary database.

The next step in gap resolution is to identify the archived logs on the primary database that are missing on the standby database. The v$archive_log view can be used to find the location of logs in the local archive destination. This step can be skipped if the DBA is familiar with the naming convention of archive logs in the database and can identify the SEQUENCE# from the logfile name. 

It is recommended that this procedure be used to find the logs required to resolve gap. Substitute the values for THREAD# LOW_SEQUENCE# and HIGH_SEQUENCE# from previous query in the following query and execute to find the location of the missing archived logs on the primary database:

Select 
   NAME 
From 
   V$ARCHIVED_LOG 
And <High Sequence# from previous query>;

Resolving Archive Gap Sequence on Physical Standby Databases

Once the archived logs required for gap resolution have been identified, the logs should be copied into the directory specified by the standby_archive_dest initialization parameter on the standby site. Also, if the log_archive_format on the standby and the primary database are not same, these files must be renamed to match the format specified by the log_archive_format parameter of the standby database.

The file can be renamed using the operating system utility. Since these logs were not transferred by the log transfer service, the managed recovery process will not have any information about these logs. These logs will need to be manually registered with the managed recovery process before they will be applied by the log apply service. To register the logs with the MRP, use the following statement:

ALTER DATABASE REGISTER LOGFILE 'filespec';

For example:

ALTER DATABASE REGISTER LOGFILE '/oracle/appsdb/arch/stdby_1_607.dbf';

At this point, the managed recovery process will start applying this archive log file.