The primary and standby database have the same filesystem layout, i.e. Archive redo is at same place on both servers. References: Metalink note 150214.1 – Synchronization of primary and standby database due to log transfer gap. Identify the missing log sequence numbers by running this SQL on the STANDBY database.

Search BC Oracle Sites! Fix Archive Gap Sequence on Physical Standby Databases Oracle Database Tips by Donald BurlesonDecember 9, 2015 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; A sample output from find_gap.sql is: 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; 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. The above text is an excerpt from the book: �� Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our. Verify experience!

Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their. Oracle technology is changing and we strive to update our BC Oracle support information.

If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.

Burleson Consulting The Oracle of Database Support Copyright © 1996 - 2017 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation. Remote Emergency Support provided. Christina Aguilera My Kind Of Christmas Zip Code on this page.

Step by Step Process to Resolved gap on Standby database. Check the name and status of database. Check for GAP on standby 3. Check redo received on standby 4. Check redo applied on standby 5.

Identify missing archive log files 6. Copy archive log files 7. Register archive log files with standby 8. Restart the managed recovery operations ****************************************************************************************************************** Step 1: Check the status of database on both server. On Primary Server. SQL>select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ——— ———- —————- MYDB READ WRITE PRIMARY SQL>set sqlprompt “>” >On Standby Server.