Step by Step Process to Resolve gap on the Standby database
1. check RECOVERY_FILE_DEST
2. Check the status of database
3. Check redo received and applied on standby
4. Identify missing archive log files
5. Copy missing archive log files
6. Register archive log files on standby
7. Restart the managed recovery operations
step 1: check RECOVERY_FILE_DEST
col name for a15
SELECT name, SPACE_LIMIT/1024/1024/1024 AS SPACE, SPACE_USEED/1024/1024/1024 AS USED, SPACE_RECLAIMABLE/1024/1024/1024 AS RECLAIMABLE FROM V$RECOVERY_FILE_DEST;
delete archivelog if needed
RMAN> delete archivelog all completed before 'sysdate-1';
RMAN> delete noprompt archivelog until sequence=3790;
step 2: Check the status of database on both server
SQL> set sqlprompt “PRIMARY’@’_connect_identifier>”
SQL> select name, open_mode, database_role from v$database;
SQL> set sqlprompt “STANDBY’@’_connect_identifier>”
SQL> select name, open_mode, database_role from v$database;
step3: Check redo received and applied on Standby
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#
ORDER BY 1;
step4: Identify missing archive log files
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM $ARCHIVE_GAP;
step5: Copy missing archive log files
Query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:
SELECT name FROM v$archived_log
WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN 20931 and 76922;
step6: Register archive logfile on standby
Copy the above redo log files to the physical standby database and register them.
For example:
ALTER DATABASE REGISTER LOGFILE 'DB_00374_0673561489_00000073498.arc';
step7: Restart the managed recovery operations
alter database recover managed standby database disconnect from session;