Monitoring Standby Apply Lag with Shell Script

ADVERTISEMENT

This shell script is part of the Shell Script Automations toolkit for Oracle DBAs. It monitors Apply Lag on a standby database using Oracle Data Guard Broker (dgmgrl), and sends alert emails if the lag crosses critical thresholds.

📌 Note: This script should be created and scheduled on the Primary database server, where dgmgrl has access to both primary and standby configuration.

📄 Shell Script: dgmgrl_standby_lag.sh

################################################################################
# Title   : Automated Monitoring of Standby Apply Lag Using dgmgrl
# Script  : dgmgrl_standby_lag.sh
# Purpose : Monitors Apply Lag on standby database via Data Guard Broker
#           and sends alerts to DBAs if lag exceeds defined thresholds.
#
# Features:
#   - Uses dgmgrl for accurate lag reporting
#   - Auto-email alert based on lag severity
#   - Clean separation of logs
#   - Cron-friendly and lightweight
#
# Author  : W3Buddy
# Version : 1.0
################################################################################

#!/bin/bash

# === Environment Setup ===
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=primdb
export PATH=$ORACLE_HOME/bin:$PATH

LOG_DIR="/home/oracle"
DB_LOG="$LOG_DIR/DB_DG_DATABASE.log"
FILTERED_LOG="$LOG_DIR/FILTERED_DB_DG_DATABASE.log"

# === Run DGMGRL to fetch standby lag ===
# TODO: Replace 'sys/orcl1234' with actual credentials OR configure secure access (e.g. SEPS)
echo "show database stydb" | dgmgrl sys/orcl1234 > "$DB_LOG"

# === Filter Apply Lag line ===
grep "Apply Lag" "$DB_LOG" > "$FILTERED_LOG"

# === Exit if Apply Lag not found ===
[[ ! -s "$FILTERED_LOG" ]] && echo "Apply Lag not found." && exit 1

# === Extract Lag Info ===
read time_value time_unit <<< $(awk -F ':' '/Apply Lag/ {
  gsub(/^[ \t]+/, "", $2); split($2,a," "); print a[1], a[2]
}' "$FILTERED_LOG")

# === Alert on Threshold ===
if [[ "$time_unit" == "minutes" && "$time_value" -ge 1 ]]; then
  mailx -s "STYDB Apply Lag: $time_value minutes" info.w3buddy@gmail.com < "$DB_LOG"
elif [[ "$time_unit" == "seconds" && "$time_value" -ge 30 ]]; then
  mailx -s "STYDB Apply Lag: $time_value seconds" info.w3buddy@gmail.com < "$DB_LOG"
elif [[ "$time_unit" =~ hour && "$time_value" -ge 1 ]]; then
  mailx -s "STYDB Apply Lag: $time_value hours" info.w3buddy@gmail.com < "$DB_LOG"
fi

⚙️ Setup Instructions

# ----------------------------------------------
# 1. Create the script file on PRIMARY DB server
# ----------------------------------------------
cd /home/oracle

# Create the script file
vi dgmgrl_standby_lag.sh

# (Paste the full script content inside and save)

# ----------------------------------------------
# 2. Make the script executable
# ----------------------------------------------
chmod +x dgmgrl_standby_lag.sh

# ----------------------------------------------
# 3. Test the script manually
# ----------------------------------------------
./dgmgrl_standby_lag.sh

# Ensure it outputs no error and optionally sends test mail if lag threshold is met.

# ----------------------------------------------
# 4. Schedule it via cron (every 10 minutes)
# ----------------------------------------------
crontab -e

# Add the below entry
*/10 * * * * /home/oracle/dgmgrl_standby_lag.sh > /tmp/dg_lag.log 2>&1

# ----------------------------------------------
# 5. Validate cron is scheduled
# ----------------------------------------------
crontab -l

⚠️ Security Note

❗ The script currently uses hardcoded credentials (sys/orcl1234) for demo purposes.
In production, avoid embedding passwords. Consider using:

  • Oracle Secure External Password Store (SEPS)
  • Environment variables or credential files with restricted access
  • Oracle wallets or OS authentication if possible

ADVERTISEMENT