{"id":4268,"date":"2025-06-16T23:32:56","date_gmt":"2025-06-16T23:32:56","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4268"},"modified":"2025-06-16T23:53:33","modified_gmt":"2025-06-16T23:53:33","slug":"monitoring-standby-apply-lag-with-shell-script","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/shell-scripts\/monitoring-standby-apply-lag-with-shell-script\/","title":{"rendered":"Monitoring Standby Apply Lag with Shell Script"},"content":{"rendered":"\n

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

\n

\ud83d\udccc Note:<\/strong> This script should be created and scheduled on the Primary database server<\/strong>, where dgmgrl<\/code> has access to both primary and standby configuration.<\/p>\n<\/blockquote>\n\n\n\n

\ud83d\udcc4 Shell Script: dgmgrl_standby_lag.sh<\/code><\/h3>\n\n\n\n
################################################################################\n# Title   : Automated Monitoring of Standby Apply Lag Using dgmgrl\n# Script  : dgmgrl_standby_lag.sh\n# Purpose : Monitors Apply Lag on standby database via Data Guard Broker\n#           and sends alerts to DBAs if lag exceeds defined thresholds.\n#\n# Features:\n#   - Uses dgmgrl for accurate lag reporting\n#   - Auto-email alert based on lag severity\n#   - Clean separation of logs\n#   - Cron-friendly and lightweight\n#\n# Author  : W3Buddy\n# Version : 1.0\n################################################################################\n\n#!\/bin\/bash\n\n# === Environment Setup ===\nexport ORACLE_HOME=\/oracle\/app\/oracle\/product\/12.1.0\/dbhome_1\nexport ORACLE_SID=primdb\nexport PATH=$ORACLE_HOME\/bin:$PATH\n\nLOG_DIR=\"\/home\/oracle\"\nDB_LOG=\"$LOG_DIR\/DB_DG_DATABASE.log\"\nFILTERED_LOG=\"$LOG_DIR\/FILTERED_DB_DG_DATABASE.log\"\n\n# === Run DGMGRL to fetch standby lag ===\n# TODO: Replace 'sys\/orcl1234' with actual credentials OR configure secure access (e.g. SEPS)\necho \"show database stydb\" | dgmgrl sys\/orcl1234 > \"$DB_LOG\"\n\n# === Filter Apply Lag line ===\ngrep \"Apply Lag\" \"$DB_LOG\" > \"$FILTERED_LOG\"\n\n# === Exit if Apply Lag not found ===\n[[ ! -s \"$FILTERED_LOG\" ]] && echo \"Apply Lag not found.\" && exit 1\n\n# === Extract Lag Info ===\nread time_value time_unit <<< $(awk -F ':' '\/Apply Lag\/ {\n  gsub(\/^[ \\t]+\/, \"\", $2); split($2,a,\" \"); print a[1], a[2]\n}' \"$FILTERED_LOG\")\n\n# === Alert on Threshold ===\nif [[ \"$time_unit\" == \"minutes\" && \"$time_value\" -ge 1 ]]; then\n  mailx -s \"STYDB Apply Lag: $time_value minutes\" info.w3buddy@gmail.com < \"$DB_LOG\"\nelif [[ \"$time_unit\" == \"seconds\" && \"$time_value\" -ge 30 ]]; then\n  mailx -s \"STYDB Apply Lag: $time_value seconds\" info.w3buddy@gmail.com < \"$DB_LOG\"\nelif [[ \"$time_unit\" =~ hour && \"$time_value\" -ge 1 ]]; then\n  mailx -s \"STYDB Apply Lag: $time_value hours\" info.w3buddy@gmail.com < \"$DB_LOG\"\nfi<\/code><\/code><\/pre>\n\n\n\n
\u2699\ufe0f Setup Instructions<\/h3>\n\n\n\n
# ----------------------------------------------\n# 1. Create the script file on PRIMARY DB server\n# ----------------------------------------------\ncd \/home\/oracle\n\n# Create the script file\nvi dgmgrl_standby_lag.sh\n\n# (Paste the full script content inside and save)\n\n# ----------------------------------------------\n# 2. Make the script executable\n# ----------------------------------------------\nchmod +x dgmgrl_standby_lag.sh\n\n# ----------------------------------------------\n# 3. Test the script manually\n# ----------------------------------------------\n.\/dgmgrl_standby_lag.sh\n\n# Ensure it outputs no error and optionally sends test mail if lag threshold is met.\n\n# ----------------------------------------------\n# 4. Schedule it via cron (every 10 minutes)\n# ----------------------------------------------\ncrontab -e\n\n# Add the below entry\n*\/10 * * * * \/home\/oracle\/dgmgrl_standby_lag.sh > \/tmp\/dg_lag.log 2>&1\n\n# ----------------------------------------------\n# 5. Validate cron is scheduled\n# ----------------------------------------------\ncrontab -l<\/code><\/code><\/pre>\n\n\n\n
\u26a0\ufe0f Security Note<\/h3>\n\n\n\n
\n
\u2757 The script currently uses hardcoded credentials (sys\/orcl1234<\/code>) for demo purposes.
In production<\/strong>, avoid embedding passwords. Consider using:<\/p>\n\n\n\n
    \n
  • Oracle Secure External Password Store (SEPS)<\/strong><\/li>\n\n\n\n
  • Environment variables or credential files with restricted access<\/li>\n\n\n\n
  • Oracle wallets or OS authentication if possible<\/li>\n<\/ul>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
    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. \ud83d\udccc Note: This script should be created and scheduled on the Primary database server, where dgmgrl has […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,990],"class_list":["post-4268","cposts","type-cposts","status-publish","hentry","category-notes","category-shell-scripts"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}