Manually Recover Standby Archived Logs

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> ALTER DATABASE REGISTER LOGFILE '/ebiz/backup/flash_recovery_area/PRODSTB/archivelog/2017_05_03/o1_mf_1_11129_djmd9bg4_.arc';

Database altered.

4. Restart the database (Not Mandatory)

SQL> shutdown immediate

ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup mount

ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1895826312 bytes
Database Buffers 2365587456 bytes
Redo Buffers 12107776 bytes
Database mounted.

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

SQL> ALTER DATABASE RECOVER MANAGED STANDBY USING CURRENT LOGFILE DISCONNECT;

6. 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;