Query To Monitor Archive Log Shipping and Gap Status on a Standby Database

Please run below query on a Standby Oracle Database

Download(archshipgapmon.sql)

REM +======================================================================+
REM                    
REM File Name: archshipgapmon.sql
REM 
REM Description:
REM   Query To Monitor Archive Log Shipping and Gap Status
REM   on a standby Database
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @archlogapplymon.sql 
REM
REM   
REM +======================================================================+

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') time,
        a.thread#,
        (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived,
        max(a.sequence#) applied,
        (select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max(a.sequence#) gap
    from v$archived_log a where a.applied='YES'  group by a.thread#;

Sample Output: