Resolving Archive Log Gap on an Oracle Standby Database

If you are running an Oracle Standby Database sooner or later you will find yourself in the situation where your logs stop applying to the standby database due to a gap in the logs.

To check the logs on the standby database run the following SQL.

SELECT sequence#, applied, first_change# "Start SCN",
next_change# "End SCN", to_char(completion_time, 'hh24:mi:ss  DD_MON_YYYY')
FROM v$archived_log
WHERE completion_time > sysdate -1
ORDER BY sequence#

Running this SQL produces theoutput below:

SEQUENCE# APP Start SCN End SCN TO_CHAR(COMPLETION_T
——— — ———- ———- ——————–
12740 YES 1001281482 1003384098 08:32:12 05-NOV-2012
12742 NO 1003385234 1003388620 08:32:52 05-NOV-2012
12743 NO 1003388620 1003399424 08:42:07 05-NOV-2012
12744 NO 1003399424 1003402559 08:42:36 05-NOV-2012
12745 NO 1003402559 1003405620 08:43:03 05-NOV-2012
12746 NO 1003405620 1003408725 08:43:29 05-NOV-2012
12747 NO 1003408725 1003411778 08:43:54 05-NOV-2012
12748 NO 1003411778 1003414873 08:44:19 05-NOV-2012
12749 NO 1003414873 1003417952 08:44:44 05-NOV-2012
12750 NO 1003417952 1003421013 08:45:13 05-NOV-2012
12751 NO 1003421013 1003424066 08:45:43 05-NOV-2012
12752 NO 1003424066 1003427138 08:46:11 05-NOV-2012

You can see here that log 12740 has been applied by the database, log 12742 has been recieced but not applied.  The reason that log 12742 has not been applied is because log 12741 is missing.

To rectify this situation manualy copy the archive log from the primary database to the archive log location on the standby database.  Once the copy is complete register the  missing log with the standby database with the following SQL:

alter database register logfile ‘e:\oracle\oradata\mydb\archive\ARCH12741′

Once the logfile is registerd the recovery should resume automaticaly.