Manually resolve archive log gap/ Recover Standby / Manual Standby recovery

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:

SQL> select thread#, low_sequence#, high_sequence#  from v$archive_gap;

Identify the filenames of the missing logs by running this SQL on the PRIMARY database:

SQL>SELECT   NAME
FROM   V$ARCHIVED_LOG
WHERE   DEST_ID = 1 AND SEQUENCE# BETWEEN &Low_Sequence AND &High_Sequence;

1. Cancel managed recovery in the STANDBY database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Copy the missing archive redo logs from primary to standby server.

This can be done using any available O/S utility. If you use ftp make sure the transfer is done in binary mode. We assume here that the redo files are going to the same location on the standby server.

3. Recover the STANDBY database

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

When all logs have been applied Oracle will ask for the next, as yet unarchived, redo log. At this point cancel the recovery, e.g.

ORA-00279: change 2668131127884 generated at 12/16/2004 07:45:58 needed forthread 2
ORA-00289: suggestion : /oraarchive/standby/arc2_00000001992.log
ORA-00280: change 2668131127884 for thread 2 is in sequence #1992
ORA-00278: log file ‘/oraarchive/standby/arc2_00000001992.log’no longer needed for this recovery
ORA-00308: cannot open archived log ‘/oraarch01/smp/arch/arc2_00000001992.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL

4. Put the STANDBY database back into managed recovery mode:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

5. Check that the archive redo gaps have been resolved byt running thie SQL on the STANDBY database:

SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;