Recovering a Standby database from a missing archivelog
A Physical Standby database needs a contiguous supply of of archivelogs from a Primary Database to be kept in sync with the primary.
Starting in Oracle version 10gR2 you can use an incremental backup from the primary to recover the standby using the primary incremental to re-synchronize when there is archivelog missing on the standby.
Step 0: make sure you are out of sync – by comparing SCN on both primary and standby:
# run this select on both standby and primary - noting both SCNs SQL> select current_scn from v$database; CURRENT_SCN ----------- 6041670 Then - on the primary only convert both SCNs to time as follows. It will give you an idea of how far apart in clock time the Primary is from the standby, expect a short delay unless you are using "real time apply"... SQL> select scn_to_timestamp(6041670)from dual; SCN_TO_TIMESTAMP(6041670) --------------------------------------------------------------------------- 23-OCT-17 03.34.14.000000000 PM SQL> select scn_to_timestamp(6041455)from dual; SCN_TO_TIMESTAMP(6041455) --------------------------------------------------------------------------- 23-OCT-17 03.32.32.000000000 PM Note this primary and standby SCNs are NOT really out of sync... the time difference would likely be much larger. But the approach / queries would be the same. 1: On the standby database check the current scn. STDBY>>select to_char(current_scn) from v$database; CURRENT_SCN ----------- 28998484 2: On the primary database create the needed incremental backup from the above SCN $ >rman target / Recovery Manager: Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: USYD (DBID=768471617) RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 28998484 DATABASE FORMAT '/home/oracle/backup/stdbybkup_%U'; 3: Cancel managed recovery at the standby database STDBY>>recover managed standby database cancel; Media recovery complete. 4: Catalog the Incremental Backup Files at the Standby Database RMAN> CATALOG START WITH '/home/oracle/backup/'; 5: Apply the Incremental Backup to the Standby Database RMAN> RECOVER DATABASE NOREDO; 6: On the primary create standby controlfile and replace controlfiles at standby site with these newly created file. PRIM>>alter database create standby controlfile as '/home/oracle/backup/'standby01.ctl'; System altered. Copy the standby control file to the standby site and restart the standby database in managed recovery mode... Verifying the sync of standby with production. Use the following query to find the difference in sync process of standby and production database. SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- You can also look at the most current scn's and their time stamps as follows: # run this select on both standby and primary - noting both SCNs SQL> select current_scn from v$database; CURRENT_SCN ----------- 6041670 Then - on the primary only convert both SCNs to time as follows. It will give you an idea of how far apart in clock time the Primary is from the standby, expect a short delay unless you are using "real time apply"... SQL> select scn_to_timestamp(6041670)from dual; SCN_TO_TIMESTAMP(6041670) --------------------------------------------------------------------------- 23-OCT-17 03.34.14.000000000 PM SQL> select scn_to_timestamp(6041455)from dual; SCN_TO_TIMESTAMP(6041455) --------------------------------------------------------------------------- 23-OCT-17 03.32.32.000000000 PM SQL> !date Mon Oct 23 15:37:56 EDT 2017