OracleData Guard
Archive gaps and last applied sequence
Missing log sequences the standby is waiting for, then the last applied sequence per thread. Compare with the current sequence on the primary.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN thread# HEADING "THREAD" FORMAT 9994COLUMN low_sequence# HEADING "GAP FROM" FORMAT 999999995COLUMN high_sequence# HEADING "GAP TO" FORMAT 999999996COLUMN last_applied HEADING "LAST APPLIED" FORMAT 999999997 8SELECT thread#, low_sequence#, high_sequence#9FROM v$archive_gap;10 11SELECT thread#, MAX(sequence#) AS last_applied12FROM v$archived_log13WHERE applied = 'YES'14GROUP BY thread#15ORDER BY thread#;Save it as ora-dg-gap.sql and run it with SQL> @ora-dg-gap.
Part of these runbooks
More Oracle scripts: Data Guard
- Transport and apply lagHow far behind the standby is in receiving and applying redo. A growing apply lag with no transport lag points at the apply process, not the network.
- Redo apply and transport processesWhich Data Guard processes are running and what they're doing. No MRP0 row means redo is arriving but not being applied. Before 12.2, query…
- Redo destinations and transport errorsEvery active archive destination with its status, gap state and last error. Any text in the ERROR column means redo isn't reaching that standby.
- Data Guard errors and warnings, last 24 hoursMessages Data Guard wrote to V$DATAGUARD_STATUS, filtered to warnings and errors. Run on both sides.
- Standby redo log configurationStandby redo logs by thread. You want one more group per thread than you have online log groups, all the same size as the online logs.
- Broker health checks (DGMGRL)The broker's own view of the configuration. VALIDATE DATABASE is the best single readiness check before a switchover.