ORACLE DATA GUARD 19c Daily Monitoring Guide

ORACLE DATA GUARD 19c Daily Monitoring Guide

 

 

1.  Check standby database role and status (run on STANDBY)

select INST_ID,INSTANCE_NAME, name DB_NAME, db_unique_name,HOST_NAME,DATABASE_ROLE,OPEN_MODE,status, to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

 

2.  Check if redo apply (MRP) is running on standby

 

3.  Check apply lag (run on STANDBY)

4.  Check lag using v$managed_standby (MRP process)

 

 

 

5.  Archive log GAP

 

6.  Check all Data Guard processes (run on STANDBY)

 

7.  Verify Data Guard Configuration Status

 

Dgmgrl / as sysdba

Show configuration;

8.  DGMGRL>Check Database Status (Primary & Standby)

dgmgrl -silent / <<EOF >> $LOGFILE

SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> SHOW DATABASE VERBOSE <STANDBY_DB>;

 

9.  DGMGRL>Validate Database Health

dgmgrl -silent / <<EOF >> $LOGFILE

SHOW CONFIGURATION;

DGMGRL> VALIDATE DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> VALIDATE DATABASE VERBOSE <STANDBY_DB>;

 

 

10.              Daily Data Guard Monitoring Checklist

 

 

SQL Script (dg_check.sql)

SET LINES 200

SET PAGES 200

PROMPT === DATABASE STATUS ===

SELECT NAME, DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

 

PROMPT === DATAGUARD STATS ===

SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

 

PROMPT === ARCHIVE DEST ===

SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;

 

PROMPT === MRP STATUS ===

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

 

PROMPT === ARCHIVE GAP ===

SELECT * FROM V$ARCHIVE_GAP;

 

PROMPT === FRA USAGE ===

SELECT SPACE_LIMIT, SPACE_USED FROM V$RECOVERY_FILE_DEST;

 

Data Guard Daily Monitoring Automation Script

 

Shell Script (dg_health_check.sh)

 

#!/bin/bash

 

# Set Oracle Environment

export ORACLE_SID=YOUR_DB_SID

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

 

# Date

DATE=$(date +"%Y-%m-%d_%H-%M")

LOGFILE=/tmp/dg_health_$DATE.log

 

echo "=========================================" >> $LOGFILE

echo " Data Guard Daily Health Check - $DATE " >> $LOGFILE

echo "=========================================" >> $LOGFILE

 

# Run SQL Checks

sqlplus -s / as sysdba<<EOF >> $LOGFILE

 

SET LINESIZE 200

SET PAGESIZE 200

 

PROMPT === DATABASE ROLE & STATUS ===

SELECT NAME, DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS FROM V\\$DATABASE;

 

PROMPT === DATAGUARD STATS ===

SELECT NAME, VALUE, UNIT FROM V\\$DATAGUARD_STATS;

 

PROMPT === ARCHIVE DEST STATUS ===

SELECT DEST_ID, STATUS, ERROR FROM V\\$ARCHIVE_DEST WHERE STATUS <> 'INACTIVE';

 

PROMPT === ARCHIVE GAP ===

SELECT * FROM V\\$ARCHIVE_GAP;

 

PROMPT === MANAGED STANDBY ===

SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V\\$MANAGED_STANDBY;

 

PROMPT === STANDBY REDO LOGS ===

SELECT GROUP#, THREAD#, SEQUENCE#, STATUS FROM V\\$STANDBY_LOG;

 

PROMPT === FLASHBACK STATUS ===

SELECT FLASHBACK_ON FROM V\\$DATABASE;

 

PROMPT === FRA USAGE ===

SELECT NAME, SPACE_LIMIT, SPACE_USED FROM V\\$RECOVERY_FILE_DEST;

 

EXIT;

EOF

 

# Run DGMGRL Checks

echo "=== DGMGRL CONFIGURATION ===" >> $LOGFILE

 

dgmgrl -silent / <<EOF >> $LOGFILE

SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> SHOW DATABASE VERBOSE <STANDBY_DB>;

DGMGRL> VALIDATE DATABASE VERBOSE <PRIMARY_DB>;

DGMGRL> VALIDATE DATABASE VERBOSE <STANDBY_DB>;

EXIT;

EOF

 

# Basic Alert Check

echo "=== ALERT LOG ERRORS (last 50 lines) ===" >> $LOGFILE

tail -50 $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log >> $LOGFILE

 

# Output location

echo "Health check completed. Log: $LOGFILE"