Logical Standby Lag Monitor (ng_behind.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 logical standby database synchronization by:

• Showing the last applied time on the standby
• Displaying the latest available time from primary
• Calculating lag in both minutes and hours
• Comparing record counts between primary and standby (example uses claim table)

Script

select
to_char(applied_time,'dd-mon-yyyy hh24:mi:ss') Applied_time,
to_char(latest_time,'dd-mon-yyyy hh24:mi:ss') latest_time,
round((to_date(to_char(latest_time,'dd-mon-yyyy hh24:mi:ss'),'DD-MON-YYYY HH24:MI:SS') -
        to_date(to_char(applied_time,'dd-mon-yyyy hh24:mi:ss'),'DD-MON-YYYY HH24:MI:SS'))*60*24) DELAY_MIN,
(round((to_date(to_char(latest_time,'dd-mon-yyyy hh24:mi:ss'),'DD-MON-YYYY HH24:MI:SS') -
        to_date(to_char(applied_time,'dd-mon-yyyy hh24:mi:ss'),'DD-MON-YYYY HH24:MI:SS'))*60*24)/60) DELAY_HOUR,
(select count(*) from ngprod.claim@ngp1) - (select count(*) from ngprod.claim) claims_behind
from gv$LOGSTDBY_PROGRESS
;

Usage

SQL> @ng_behind.sql

Required Privileges

• SELECT on GV$LOGSTDBY_PROGRESS
• SELECT on target tables (customize based on your schema)
• Database link access to primary database

Sample Output

APPLIED_TIME         LATEST_TIME          DELAY_MIN DELAY_HOUR CLAIMS_BEHIND
-------------------- -------------------- --------- ---------- -------------
15-jan-2024 14:30:45 15-jan-2024 14:35:22         5          0           127

Key Output Columns

• APPLIED_TIME: Last transaction time applied on standby
• LATEST_TIME: Most recent transaction time available from primary
• DELAY_MIN: Lag time in minutes
• DELAY_HOUR: Lag time in hours
• CLAIMS_BEHIND: Difference in record count (customize for your tables)

Understanding the Metrics

Acceptable Lag Times

• Real-time: < 1 minute (critical systems)
• Near real-time: 1-5 minutes
• Standard: 5-30 minutes
• Batch: > 30 minutes (reporting databases)

Common Causes of Lag

1. Network Issues: Slow redo transport
2. Apply Performance: Insufficient resources on standby
3. Large Transactions: Bulk operations causing delays
4. Errors: Apply process errors requiring intervention

Customization

Modify the record count comparison for your environment:

-- Replace this line:
(select count(*) from ngprod.claim@ngp1) - (select count(*) from ngprod.claim) claims_behind

-- With your critical table:
(select count(*) from schema.table@dblink) - (select count(*) from schema.table) records_behind

Common Use Cases

1. Monitoring Dashboard

   • Real-time lag monitoring
   • Alerting on excessive lag
   • Trend analysis over time

2. Troubleshooting

   • Identify apply bottlenecks
   • Validate network performance
   • Verify standby configuration

3. Capacity Planning

   • Assess standby resource needs
   • Plan for peak load periods
   • Validate DR readiness

Advanced Monitoring

For comprehensive monitoring, combine with:

-- Check apply process status
SELECT process, status, thread#, sequence#
FROM v$managed_standby
WHERE process LIKE 'MRP%';

-- Review apply rate
SELECT name, value
FROM v$dataguard_stats
WHERE name LIKE '%lag%';

Troubleshooting Tips

1. Increasing Lag

   • Check alert logs on both primary and standby
   • Verify network connectivity
   • Review apply process errors

2. Stuck Apply

   • Check for blocking locks
   • Review supplemental logging
   • Verify logical standby skip rules

3. Record Count Mismatch

   • Verify skip rules aren’t excluding data
   • Check for apply errors
   • Ensure consistent point-in-time comparison

Related Scripts

• Streams Apply Lag
• Archive Log Status
• Database Status