Archive log gap in standby database - ORACLE

Resolving archive log gap at standby database - ORACLE

Archive log gap between primary database and standby database is one of the common issue in ORacle, there are multiple reasons for this like Network issue, oracle process issue, host might be down. 

This will be helpful in checking archive log gap and resolve the same. This is applicable once standby and primary DB is up and running. 

 - SQL script to check last archive log generated; Execute this in primary Database

SQL> select sequence#, archived, completion_Time from v$archived_log where completion_Time = (select max(Completion_time) from v$archived_log);

SAMPLE OUTPUT: 

 - SQL to check archive logs applied in standby database; 

SQL> select sequence#, applied from v$archived_log;

 - STOP archive log auto recovery; 

SQL>alter database recover managed standby database cancel; 

-  Check for if any archive log sequence# is missing, once you find it out. Copy the archive files from primary system, based on your infrastructure design and architecture you can copy the files. 

 - Once you copy the archive logs, register the missing archive log files, to register; 

SQL> alter database register logfile '<archive directory path>/<Archive log name>';

NOTE: we need to register all the archive logs which is not in v$archived_log table in standby. 

 - We should see missing archive logs sequence numbers in v$archived_log table, start automatic recovery; 

SQL> alter database recover managed standby database disconnect from session; 

This will apply all logs automatically.