写了一个 Oracle DG 监控脚本，这下可以安心睡觉了

1ADG error：SCN not change，please check。

1SELECT process,
2       thread#,
3       sequence#,
4       status
5FROM v$managed_standby;
6
7PROCESS   THREAD#   SEQUENCE#   STATUS
8-------   -------   ---------   ------------
9MRP0            1      169889   APPLYING_LOG
10RFS             1      169889   IDLE

1SELECT name,
2       value
3FROM v$dataguard_stats
4WHERE name IN ('transport lag','apply lag');
5
6transport lag   +00 00:00:00
7apply lag       +00 00:00:00

1查询 CURRENT_SCN
2      ↓
3等待 5 秒
4      ↓
5再次查询 CURRENT_SCN
6      ↓
7SCN 没变化
8      ↓
9发送 DG 异常邮件

1SELECT current_scn
2FROM v$database;

186803034214
286803034214

1Database Role
2Open Mode
3MRP 状态
4RFS 状态
5Transport Lag
6Apply Lag
7Received / Applied Sequence
8最近的 DG Error/Fatal

1MRP 是否正常运行
2Transport Lag 是否超过阈值
3Apply Lag 是否超过阈值
4最近是否存在 DG Error/Fatal

1./check_dg_status.sh

1======================================================================
2 Oracle Data Guard Monitor V4.3
3======================================================================
4Database          : ORCL
5Oracle Version    : 19.0.0.0.0
6Host / Check Time : ssthlmesdbdg01 / 2026-09-26 20:24:40
7Role / Open Mode  : PHYSICAL STANDBY / READ ONLY WITH APPLY
8Protection Mode   : MAXIMUM PERFORMANCE
9Switchover Status : NOT ALLOWED
10
11[Instances]
12  Inst=1 Name=orcldg Host=ssthlmesdbdg01 Status=OPEN Thread=1
13
14[Recovery]
15  MRP Count=1 Inst=1 Status=APPLYING_LOG Thread=2 Seq=9415
16  RFS Count=10
17  RFS Inst=1 Status=IDLE Thread=1 Seq=9389
18  RFS Inst=1 Status=IDLE Thread=2 Seq=9415
19
20[Lag]
21  Transport Lag : +00 00:00:00
22  Apply Lag     : +00 00:00:00
23
24[Archive by Thread]
25  Thread 1: Received=9388 Applied=9388 Diff=0
26            LastReceive=2026-09-26 19:55:52 LastApply=2026-09-26 19:55:52
27  Thread 2: Received=9414 Applied=9414 Diff=0
28            LastReceive=2026-09-26 20:06:27 LastApply=2026-09-26 20:06:27
29
30[Recent DG Errors]
31  NONE
32
33Status            : HEALTHY
34Health Basis      : MRP running; Transport Lag=+00 00:00:00; Apply Lag=+00 00:00:00; no recent DG Error/Fatal
35======================================================================

1*/5 * * * * $HOME/jobs/check_dg_status.sh >/dev/null 2>&1

1#!/bin/bash
2# Oracle Data Guard Monitor V4.3
3# Tested scenarios:
4#   Oracle 11.2.0.4 Physical Standby / MOUNTED
5#   Oracle 19c Active Data Guard / READ ONLY WITH APPLY
6# Supports multi redo threads. RAC standby code path is retained but not field-tested.
7#
8# Alert policy:
9#   Healthy: no mail
10#   First abnormal check: record only
11#   Second consecutive abnormal check: send alert
12#   Persistent abnormal: reminder every 6 hours
13#   Recovery: clear state silently, no recovery mail
14
15source ~/.bash_profile
16export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
17
18MAIL_TO="pc1107750981@163.com"
19TRANSPORT_LAG_THRESHOLD=300
20APPLY_LAG_THRESHOLD=300
21FAIL_THRESHOLD=2
22REMINDER_INTERVAL=21600
23DG_ERROR_LOOKBACK_MIN=10
24
25HOST=$(hostname)
26CHECK_TIME=$(date '+%Y-%m-%d %H:%M:%S')
27NOW_EPOCH=$(date +%s)
28
29TMP_FILE="/tmp/dg_monitor_$$.out"
30MAIL_FILE="/tmp/dg_monitor_mail_$$.out"
31STATE_DIR="$HOME/jobs/.dg_monitor"
32FAIL_FILE="$STATE_DIR/fail_count"
33ALERT_FILE="$STATE_DIR/alert_sent"
34LAST_MAIL_FILE="$STATE_DIR/last_mail_epoch"
35
36mkdir -p "$STATE_DIR"
37trap 'rm -f "$TMP_FILE" "$MAIL_FILE"' EXIT
38
39read_int() {
40    local file="$1"
41    local default_value="$2"
42    local value
43    if [ ! -f "$file" ]; then
44        echo "$default_value"
45        return
46    fi
47    value=$(cat "$file" 2>/dev/null)
48    case "$value" in
49        ''|*[!0-9]*) echo "$default_value" ;;
50        *) echo "$value" ;;
51    esac
52}
53
54lag_to_seconds() {
55    local lag="$1"
56    local clean days tm hh mm ss
57    if [ -z "$lag" ]; then
58        echo -1
59        return
60    fi
61    clean=$(echo "$lag" | sed 's/^+//' | xargs)
62    days=$(echo "$clean" | awk '{print $1}')
63    tm=$(echo "$clean" | awk '{print $2}')
64    hh=$(echo "$tm" | cut -d: -f1)
65    mm=$(echo "$tm" | cut -d: -f2)
66    ss=$(echo "$tm" | cut -d: -f3 | cut -d. -f1)
67    if [ -z "$days" ] || [ -z "$hh" ] || [ -z "$mm" ] || [ -z "$ss" ]; then
68        echo -1
69        return
70    fi
71    echo $((10#$days * 86400 + 10#$hh * 3600 + 10#$mm * 60 + 10#$ss))
72}
73
74sqlplus -s / as sysdba > "$TMP_FILE" 2>&1 <<EOF
75whenever sqlerror exit 10
76whenever oserror exit 11
77set heading off feedback off pagesize 0 linesize 1000 trimspool on verify off echo off tab off
78
79SELECT 'DB|'||name||'|'||database_role||'|'||open_mode||'|'||
80       protection_mode||'|'||switchover_status
81FROM v\$database;
82
83SELECT 'VER|'||version
84FROM v\$instance;
85
86SELECT 'INST|'||inst_id||'|'||instance_name||'|'||host_name||'|'||
87       status||'|'||thread#
88FROM gv\$instance
89ORDER BY inst_id;
90
91SELECT 'MRP|'||inst_id||'|'||status||'|'||thread#||'|'||sequence#
92FROM gv\$managed_standby
93WHERE process='MRP0'
94ORDER BY inst_id;
95
96SELECT 'RFS|'||inst_id||'|'||status||'|'||thread#||'|'||sequence#
97FROM gv\$managed_standby
98WHERE process='RFS'
99ORDER BY inst_id,thread#,sequence#;
100
101SELECT 'TLAG|'||value
102FROM v\$dataguard_stats
103WHERE name='transport lag';
104
105SELECT 'ALAG|'||value
106FROM v\$dataguard_stats
107WHERE name='apply lag';
108
109SELECT 'ERR|'||TO_CHAR(timestamp,'YYYY-MM-DD HH24:MI:SS')||'|'||
110       severity||'|'||NVL(TO_CHAR(error_code),'0')||'|'||
111       REPLACE(REPLACE(REPLACE(message,'|','/'),CHR(10),' '),CHR(13),' ')
112FROM v\$dataguard_status
113WHERE severity IN ('Error','Fatal')
114AND timestamp > SYSDATE - (${DG_ERROR_LOOKBACK_MIN}/1440)
115ORDER BY timestamp;
116
117SELECT 'REC|'||thread#||'|'||MAX(sequence#)||'|'||
118       TO_CHAR(MAX(completion_time),'YYYY-MM-DD HH24:MI:SS')
119FROM v\$archived_log
120WHERE registrar='RFS'
121AND resetlogs_change#=(SELECT resetlogs_change# FROM v\$database)
122GROUP BY thread#
123ORDER BY thread#;
124
125SELECT 'APP|'||thread#||'|'||MAX(sequence#)||'|'||
126       TO_CHAR(MAX(completion_time),'YYYY-MM-DD HH24:MI:SS')
127FROM v\$archived_log
128WHERE applied='YES'
129AND resetlogs_change#=(SELECT resetlogs_change# FROM v\$database)
130GROUP BY thread#
131ORDER BY thread#;
132
133exit
134EOF
135
136SQL_RC=$?
137
138if [ "$SQL_RC" -ne 0 ]; then
139    FAIL_COUNT=$(read_int "$FAIL_FILE" 0)
140    FAIL_COUNT=$((FAIL_COUNT + 1))
141    echo "$FAIL_COUNT" > "$FAIL_FILE"
142
143    {
144        echo "======================================================================"
145        echo " Oracle Data Guard Monitor - CHECK FAILED"
146        echo "======================================================================"
147        echo "Host          : $HOST"
148        echo "Check Time    : $CHECK_TIME"
149        echo "SQLPlus RC    : $SQL_RC"
150        echo "Failure Count : $FAIL_COUNT/$FAIL_THRESHOLD"
151        echo
152        cat "$TMP_FILE"
153        echo "======================================================================"
154    } > "$MAIL_FILE"
155
156    cat "$MAIL_FILE"
157
158    if [ "$FAIL_COUNT" -ge "$FAIL_THRESHOLD" ]; then
159        LAST_MAIL=$(read_int "$LAST_MAIL_FILE" 0)
160        if [ ! -f "$ALERT_FILE" ] || [ $((NOW_EPOCH - LAST_MAIL)) -ge "$REMINDER_INTERVAL" ]; then
161            mailx -s "[DG CHECK FAILED] $HOST" "$MAIL_TO" < "$MAIL_FILE"
162            if [ $? -eq 0 ]; then
163                touch "$ALERT_FILE"
164                echo "$NOW_EPOCH" > "$LAST_MAIL_FILE"
165            fi
166        fi
167    fi
168    exit 2
169fi
170
171DB_LINE=$(grep '^DB|' "$TMP_FILE" | head -1)
172if [ -z "$DB_LINE" ]; then
173    echo "[ERROR] Cannot obtain V\$DATABASE information."
174    exit 2
175fi
176
177DB_NAME=$(echo "$DB_LINE" | cut -d'|' -f2)
178DB_ROLE=$(echo "$DB_LINE" | cut -d'|' -f3)
179OPEN_MODE=$(echo "$DB_LINE" | cut -d'|' -f4)
180PROTECTION_MODE=$(echo "$DB_LINE" | cut -d'|' -f5)
181SWITCHOVER_STATUS=$(echo "$DB_LINE" | cut -d'|' -f6)
182ORACLE_VERSION=$(grep '^VER|' "$TMP_FILE" | head -1 | cut -d'|' -f2)
183
184MRP_COUNT=$(grep -c '^MRP|' "$TMP_FILE")
185if [ "$MRP_COUNT" -gt 0 ]; then
186    MRP_LINE=$(grep '^MRP|' "$TMP_FILE" | head -1)
187    MRP_INST=$(echo "$MRP_LINE" | cut -d'|' -f2)
188    MRP_STATUS=$(echo "$MRP_LINE" | cut -d'|' -f3)
189    MRP_THREAD=$(echo "$MRP_LINE" | cut -d'|' -f4)
190    MRP_SEQ=$(echo "$MRP_LINE" | cut -d'|' -f5)
191else
192    MRP_INST="-"
193    MRP_STATUS="NOT_RUNNING"
194    MRP_THREAD="-"
195    MRP_SEQ="-"
196fi
197
198RFS_COUNT=$(grep -c '^RFS|' "$TMP_FILE")
199TRANSPORT_LAG=$(grep '^TLAG|' "$TMP_FILE" | head -1 | cut -d'|' -f2)
200APPLY_LAG=$(grep '^ALAG|' "$TMP_FILE" | head -1 | cut -d'|' -f2)
201TRANSPORT_SEC=$(lag_to_seconds "$TRANSPORT_LAG")
202APPLY_SEC=$(lag_to_seconds "$APPLY_LAG")
203DG_ERROR_COUNT=$(grep -c '^ERR|' "$TMP_FILE")
204
205ABNORMAL=0
206PROBLEM=""
207
208add_problem() {
209    ABNORMAL=1
210    PROBLEM="${PROBLEM}
211$1"
212}
213
214if [ "$DB_ROLE" != "PHYSICAL STANDBY" ]; then
215    add_problem "[CRITICAL] Database role is $DB_ROLE; expected PHYSICAL STANDBY."
216fi
217
218case "$OPEN_MODE" in
219    "MOUNTED"|"READ ONLY WITH APPLY") ;;
220    *) add_problem "[CRITICAL] Unexpected standby open mode: $OPEN_MODE." ;;
221esac
222
223if [ "$MRP_COUNT" -eq 0 ]; then
224    add_problem "[CRITICAL] MRP0 is NOT running."
225fi
226
227if [ "$TRANSPORT_SEC" -lt 0 ]; then
228    add_problem "[WARNING] Cannot obtain Transport Lag."
229elif [ "$TRANSPORT_SEC" -gt "$TRANSPORT_LAG_THRESHOLD" ]; then
230    add_problem "[WARNING] Transport Lag $TRANSPORT_LAG exceeds ${TRANSPORT_LAG_THRESHOLD}s."
231fi
232
233if [ "$APPLY_SEC" -lt 0 ]; then
234    add_problem "[WARNING] Cannot obtain Apply Lag."
235elif [ "$APPLY_SEC" -gt "$APPLY_LAG_THRESHOLD" ]; then
236    add_problem "[WARNING] Apply Lag $APPLY_LAG exceeds ${APPLY_LAG_THRESHOLD}s."
237fi
238
239if [ "$DG_ERROR_COUNT" -gt 0 ]; then
240    add_problem "[CRITICAL] $DG_ERROR_COUNT Data Guard Error/Fatal message(s) detected in last ${DG_ERROR_LOOKBACK_MIN} minutes."
241fi
242
243print_report() {
244    echo "======================================================================"
245    echo " Oracle Data Guard Monitor V4.3"
246    echo "======================================================================"
247    echo "Database          : $DB_NAME"
248    echo "Oracle Version    : $ORACLE_VERSION"
249    echo "Host / Check Time : $HOST / $CHECK_TIME"
250    echo "Role / Open Mode  : $DB_ROLE / $OPEN_MODE"
251    echo "Protection Mode   : $PROTECTION_MODE"
252    echo "Switchover Status : $SWITCHOVER_STATUS"
253
254    echo
255    echo "[Instances]"
256    grep '^INST|' "$TMP_FILE" |
257    while IFS='|' read -r TAG INST_ID INST_NAME INST_HOST INST_STATUS INST_THREAD
258    do
259        echo "  Inst=$INST_ID Name=$INST_NAME Host=$INST_HOST Status=$INST_STATUS Thread=$INST_THREAD"
260    done
261
262    echo
263    echo "[Recovery]"
264    echo "  MRP Count=$MRP_COUNT Inst=$MRP_INST Status=$MRP_STATUS Thread=$MRP_THREAD Seq=$MRP_SEQ"
265    echo "  RFS Count=$RFS_COUNT"
266    grep '^RFS|' "$TMP_FILE" |
267    while IFS='|' read -r TAG R_INST R_STATUS R_THREAD R_SEQ
268    do
269        if [ "$R_THREAD" -gt 0 ] 2>/dev/null && [ "$R_SEQ" -gt 0 ] 2>/dev/null; then
270            echo "  RFS Inst=$R_INST Status=$R_STATUS Thread=$R_THREAD Seq=$R_SEQ"
271        fi
272    done
273
274    echo
275    echo "[Lag]"
276    echo "  Transport Lag : ${TRANSPORT_LAG:--}"
277    echo "  Apply Lag     : ${APPLY_LAG:--}"
278
279    echo
280    echo "[Archive by Thread]"
281    THREADS=$(
282        {
283            grep '^REC|' "$TMP_FILE" | cut -d'|' -f2
284            grep '^APP|' "$TMP_FILE" | cut -d'|' -f2
285        } | grep '^[0-9][0-9]*$' | sort -n -u
286    )
287
288    if [ -z "$THREADS" ]; then
289        echo "  No archive thread information."
290    else
291        for THREAD in $THREADS
292        do
293            REC_LINE=$(grep "^REC|${THREAD}|" "$TMP_FILE" | tail -1)
294            APP_LINE=$(grep "^APP|${THREAD}|" "$TMP_FILE" | tail -1)
295
296            REC_SEQ=$(echo "$REC_LINE" | cut -d'|' -f3)
297            REC_TIME=$(echo "$REC_LINE" | cut -d'|' -f4)
298            APP_SEQ=$(echo "$APP_LINE" | cut -d'|' -f3)
299            APP_TIME=$(echo "$APP_LINE" | cut -d'|' -f4)
300
301            [ -n "$REC_SEQ" ] || REC_SEQ=0
302            [ -n "$APP_SEQ" ] || APP_SEQ=0
303            [ -n "$REC_TIME" ] || REC_TIME="-"
304            [ -n "$APP_TIME" ] || APP_TIME="-"
305
306            if echo "$REC_SEQ" | grep -q '^[0-9][0-9]*$' &&
307               echo "$APP_SEQ" | grep -q '^[0-9][0-9]*$'; then
308                ARCHIVE_DIFF=$((REC_SEQ - APP_SEQ))
309            else
310                ARCHIVE_DIFF="-"
311            fi
312
313            echo "  Thread $THREAD: Received=$REC_SEQ Applied=$APP_SEQ Diff=$ARCHIVE_DIFF"
314            echo "            LastReceive=$REC_TIME LastApply=$APP_TIME"
315        done
316    fi
317
318    echo
319    echo "[Recent DG Errors]"
320    if [ "$DG_ERROR_COUNT" -eq 0 ]; then
321        echo "  NONE"
322    else
323        grep '^ERR|' "$TMP_FILE" | sed 's/^ERR|/  /'
324    fi
325}
326
327if [ "$ABNORMAL" -eq 1 ]; then
328    FAIL_COUNT=$(read_int "$FAIL_FILE" 0)
329    FAIL_COUNT=$((FAIL_COUNT + 1))
330    echo "$FAIL_COUNT" > "$FAIL_FILE"
331
332    print_report
333    echo
334    echo "Status            : ABNORMAL"
335    echo "Failure Count     : $FAIL_COUNT/$FAIL_THRESHOLD"
336    echo "Problems:"
337    printf "%b\n" "$PROBLEM"
338    echo "======================================================================"
339
340    if [ "$FAIL_COUNT" -ge "$FAIL_THRESHOLD" ]; then
341        LAST_MAIL=$(read_int "$LAST_MAIL_FILE" 0)
342        SEND_MAIL=0
343        SUBJECT="[DG ALERT] $DB_NAME@$HOST"
344
345        if [ ! -f "$ALERT_FILE" ]; then
346            SEND_MAIL=1
347        elif [ $((NOW_EPOCH - LAST_MAIL)) -ge "$REMINDER_INTERVAL" ]; then
348            SEND_MAIL=1
349            SUBJECT="[DG REMINDER] $DB_NAME@$HOST"
350        fi
351
352        if [ "$SEND_MAIL" -eq 1 ]; then
353            {
354                print_report
355                echo
356                echo "Status            : ABNORMAL"
357                echo "Failure Count     : $FAIL_COUNT/$FAIL_THRESHOLD"
358                echo "Problems:"
359                printf "%b\n" "$PROBLEM"
360                echo "======================================================================"
361            } > "$MAIL_FILE"
362
363            mailx -s "$SUBJECT" "$MAIL_TO" < "$MAIL_FILE"
364            if [ $? -eq 0 ]; then
365                touch "$ALERT_FILE"
366                echo "$NOW_EPOCH" > "$LAST_MAIL_FILE"
367            fi
368        fi
369    fi
370    exit 1
371fi
372
373echo 0 > "$FAIL_FILE"
374rm -f "$ALERT_FILE" "$LAST_MAIL_FILE"
375
376print_report
377echo
378echo "Status            : HEALTHY"
379echo "Health Basis      : MRP running; Transport Lag=${TRANSPORT_LAG:--}; Apply Lag=${APPLY_LAG:--}; no recent DG Error/Fatal"
380echo "======================================================================"
381exit 0