Archive Log Gap Analysis (varchlog.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script monitors archive log shipping and application lag in Data Guard environments by comparing the highest sequence numbers received versus applied for each thread. Essential for monitoring standby database synchronization and identifying potential lag issues.

The Script

rem  varchlog.sql
rem
ttitle 'Archive Log Gap Analysis'
rem
set lines 160
set pagesize 50
rem
clear col
col thread# format 99999 heading 'THREAD#'
col "Last SEQ# received" format 999999999 heading 'Last SEQ#|received'
col "Last SEQ# Applied" format 999999999 heading 'Last SEQ#|Applied'
col "Nbr of Logs Behind" format 999999999 heading 'Nbr of Logs|Behind'
rem

select a.thread#
     , max(a.sequence#) "Last SEQ# received"
     , max(d.sequence#) "Last SEQ# Applied"
     , max(a.sequence#) - max(d.sequence#) "Nbr of Logs Behind"
  from v$archived_log a
     , (select thread#
             , sequence#
          from v$archived_log
         where resetlogs_id=
               (select max(c.resetlogs_id)
                  from v$archived_log c
               )
           and applied='YES'
       ) d
 where a.resetlogs_id =
       (select max(c.resetlogs_id)
          from v$archived_log c
       )
  and a.thread#=d.thread#
group by a.thread#
/

Parameters

The script prompts for:

• None - this script requires no parameters

Usage

-- Basic usage
@varchlog.sql

Sample Output

   THREAD# Last SEQ# received Last SEQ# Applied Nbr of Logs Behind
---------- ------------------- ------------------ ------------------
         1                 156                155                  1
         2                 158                157                  1