Runbook6 steps

Data Guard check

Transport on the primary, then lag, apply processes and gaps on the standby. Each script is labeled with the side it belongs on.

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.
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 V$MANAGED_STANDBY…
Archive gaps and last applied sequenceMissing log sequences the standby is waiting for, then the last applied sequence per thread. Compare with the current sequence on the primary.
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.

Open in denrepo

The whole runbook as one file: SQL> @dataguard

dataguard.sql

1-- denrepo runbook: Data Guard check2-- Transport on the primary, then lag, apply processes and gaps on the standby. Each script is labeled with the side it belongs on.3-- Source: https://denrepo.com/runbooks/dataguard/4 5SET ECHO OFF6 7-- Not yet verified8PROMPT9PROMPT ==== 1 of 6: Redo destinations and transport errors ====10PROMPT (run on primary)11CLEAR COLUMNS12SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF13COLUMN dest_id       HEADING "ID"       FORMAT 9914COLUMN dest_name     HEADING "DEST"     FORMAT A2015COLUMN status        FORMAT A916COLUMN type          FORMAT A817COLUMN database_mode HEADING "DB MODE"  FORMAT A1518COLUMN recovery_mode HEADING "RECOVERY" FORMAT A25 TRUNCATE19COLUMN gap_status    HEADING "GAP"      FORMAT A1820COLUMN error         FORMAT A50 WORD_WRAPPED21 22SELECT dest_id, dest_name, status, type, database_mode,23       recovery_mode, gap_status, error24FROM v$archive_dest_status25WHERE status <> 'INACTIVE'26ORDER BY dest_id;27 28-- Not yet verified29PROMPT30PROMPT ==== 2 of 6: Data Guard errors and warnings, last 24 hours ====31CLEAR COLUMNS32SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF33COLUMN at_time    HEADING "TIME"   FORMAT A1934COLUMN facility   FORMAT A2435COLUMN severity   FORMAT A836COLUMN error_code HEADING "ERROR"  FORMAT 9999937COLUMN message    FORMAT A90 WORD_WRAPPED38 39SELECT TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,40       facility, severity, error_code, message41FROM v$dataguard_status42WHERE timestamp > SYSDATE - 143  AND severity IN ('Warning', 'Error', 'Fatal')44ORDER BY timestamp DESC;45 46-- Not yet verified47PROMPT48PROMPT ==== 3 of 6: Transport and apply lag ====49PROMPT (run on standby)50CLEAR COLUMNS51SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF52COLUMN name          FORMAT A2053COLUMN value         FORMAT A1854COLUMN time_computed HEADING "COMPUTED" FORMAT A2055COLUMN datum_time    HEADING "DATUM"    FORMAT A2056 57SELECT name, value, time_computed, datum_time58FROM v$dataguard_stats59WHERE name IN ('transport lag', 'apply lag', 'apply finish time');60 61-- Not yet verified62PROMPT63PROMPT ==== 4 of 6: Redo apply and transport processes ====64PROMPT (run on standby)65CLEAR COLUMNS66SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF67COLUMN name        FORMAT A668COLUMN role        FORMAT A2469COLUMN action      FORMAT A1470COLUMN client_role HEADING "CLIENT ROLE" FORMAT A1871COLUMN thread#     HEADING "THREAD"      FORMAT 99972COLUMN sequence#   HEADING "SEQUENCE"    FORMAT 9999999973COLUMN block#      HEADING "BLOCK"       FORMAT 999999999974 75SELECT name, role, action, client_role, thread#, sequence#, block#76FROM v$dataguard_process77ORDER BY name;78 79-- Not yet verified80PROMPT81PROMPT ==== 5 of 6: Archive gaps and last applied sequence ====82PROMPT (run on standby)83CLEAR COLUMNS84SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF85COLUMN thread#        HEADING "THREAD"        FORMAT 99986COLUMN low_sequence#  HEADING "GAP FROM"      FORMAT 9999999987COLUMN high_sequence# HEADING "GAP TO"        FORMAT 9999999988COLUMN last_applied   HEADING "LAST APPLIED"  FORMAT 9999999989 90SELECT thread#, low_sequence#, high_sequence#91FROM v$archive_gap;92 93SELECT thread#, MAX(sequence#) AS last_applied94FROM v$archived_log95WHERE applied = 'YES'96GROUP BY thread#97ORDER BY thread#;98 99-- Not yet verified100PROMPT101PROMPT ==== 6 of 6: Standby redo log configuration ====102CLEAR COLUMNS103SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF104COLUMN group#   HEADING "GROUP"   FORMAT 999105COLUMN thread#  HEADING "THREAD"  FORMAT 999106COLUMN size_mb  HEADING "SIZE MB" FORMAT 99,990107COLUMN status   FORMAT A12108 109SELECT group#, thread#, ROUND(bytes / 1048576) AS size_mb, status110FROM v$standby_log111ORDER BY thread#, group#;112

-- denrepo runbook: Data Guard check
-- Transport on the primary, then lag, apply processes and gaps on the standby. Each script is labeled with the side it belongs on.
-- Source: https://denrepo.com/runbooks/dataguard/

SET ECHO OFF

-- Not yet verified
PROMPT
PROMPT ==== 1 of 6: Redo destinations and transport errors ====
PROMPT (run on primary)
CLEAR COLUMNS
SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN dest_id       HEADING "ID"       FORMAT 99
COLUMN dest_name     HEADING "DEST"     FORMAT A20
COLUMN status        FORMAT A9
COLUMN type          FORMAT A8
COLUMN database_mode HEADING "DB MODE"  FORMAT A15
COLUMN recovery_mode HEADING "RECOVERY" FORMAT A25 TRUNCATE
COLUMN gap_status    HEADING "GAP"      FORMAT A18
COLUMN error         FORMAT A50 WORD_WRAPPED

SELECT dest_id, dest_name, status, type, database_mode,
       recovery_mode, gap_status, error
FROM v$archive_dest_status
WHERE status <> 'INACTIVE'
ORDER BY dest_id;

-- Not yet verified
PROMPT
PROMPT ==== 2 of 6: Data Guard errors and warnings, last 24 hours ====
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN at_time    HEADING "TIME"   FORMAT A19
COLUMN facility   FORMAT A24
COLUMN severity   FORMAT A8
COLUMN error_code HEADING "ERROR"  FORMAT 99999
COLUMN message    FORMAT A90 WORD_WRAPPED

SELECT TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,
       facility, severity, error_code, message
FROM v$dataguard_status
WHERE timestamp > SYSDATE - 1
  AND severity IN ('Warning', 'Error', 'Fatal')
ORDER BY timestamp DESC;

-- Not yet verified
PROMPT
PROMPT ==== 3 of 6: Transport and apply lag ====
PROMPT (run on standby)
CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN name          FORMAT A20
COLUMN value         FORMAT A18
COLUMN time_computed HEADING "COMPUTED" FORMAT A20
COLUMN datum_time    HEADING "DATUM"    FORMAT A20

SELECT name, value, time_computed, datum_time
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag', 'apply finish time');

-- Not yet verified
PROMPT
PROMPT ==== 4 of 6: Redo apply and transport processes ====
PROMPT (run on standby)
CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN name        FORMAT A6
COLUMN role        FORMAT A24
COLUMN action      FORMAT A14
COLUMN client_role HEADING "CLIENT ROLE" FORMAT A18
COLUMN thread#     HEADING "THREAD"      FORMAT 999
COLUMN sequence#   HEADING "SEQUENCE"    FORMAT 99999999
COLUMN block#      HEADING "BLOCK"       FORMAT 9999999999

SELECT name, role, action, client_role, thread#, sequence#, block#
FROM v$dataguard_process
ORDER BY name;

-- Not yet verified
PROMPT
PROMPT ==== 5 of 6: Archive gaps and last applied sequence ====
PROMPT (run on standby)
CLEAR COLUMNS
SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN thread#        HEADING "THREAD"        FORMAT 999
COLUMN low_sequence#  HEADING "GAP FROM"      FORMAT 99999999
COLUMN high_sequence# HEADING "GAP TO"        FORMAT 99999999
COLUMN last_applied   HEADING "LAST APPLIED"  FORMAT 99999999

SELECT thread#, low_sequence#, high_sequence#
FROM v$archive_gap;

SELECT thread#, MAX(sequence#) AS last_applied
FROM v$archived_log
WHERE applied = 'YES'
GROUP BY thread#
ORDER BY thread#;

-- Not yet verified
PROMPT
PROMPT ==== 6 of 6: Standby redo log configuration ====
CLEAR COLUMNS
SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN group#   HEADING "GROUP"   FORMAT 999
COLUMN thread#  HEADING "THREAD"  FORMAT 999
COLUMN size_mb  HEADING "SIZE MB" FORMAT 99,990
COLUMN status   FORMAT A12

SELECT group#, thread#, ROUND(bytes / 1048576) AS size_mb, status
FROM v$standby_log
ORDER BY thread#, group#;