Oracle Alert Log Monitoring with ORA- Error Notifications

ADVERTISEMENT

Oracle alert logs contain critical information, including internal errors, startup/shutdown events, and ORA- errors. Monitoring these logs in real-time ensures issues are detected and addressed before they escalate.

This script uses Oracle’s ADRCI utility to automatically scan alert logs of all registered databases every 15 minutes for any ORA- errors, and sends an email to the DBA team if such errors are found.

This solution is compatible with Oracle 11g and above.

Shell Script: Adrci_alert_log.ksh

#!/bin/bash

################################################################################
# Title   : Oracle Alert Log Monitoring Script
# Script  : adrci_alert_log.sh
# Purpose : Monitor all Oracle alert logs using ADRCI and notify if ORA- errors are found
#
# Features:
#   - Scans alert logs for all ADR homes (RDBMS)
#   - Checks for ORA- errors in the last 15 minutes
#   - Sends detailed alert log output via email
#
# Author  : W3Buddy
# Version : 1.0
################################################################################

export LOG_DIR="/u01/app/oracle/scripts/logs"
mkdir -p "$LOG_DIR"
export LOG_FILE="$LOG_DIR/alert_log_check_$(date +%Y%m%d%H%M).log"

echo "##############################" > "$LOG_FILE"
echo "###### ALERT LOG ORA- ERRORS (Last 15 Minutes) ######" >> "$LOG_FILE"
echo "##############################" >> "$LOG_FILE"

# Get list of RDBMS ADR homes
adrci_homes=( $(adrci exec="show homes" | grep -E '^diag/rdbms') )

for adrci_home in "${adrci_homes[@]}"; do
  echo -e "\n########################################" >> "$LOG_FILE"
  echo "ADR Home: $adrci_home" >> "$LOG_FILE"
  adrci exec="set home ${adrci_home}; show alert -p \"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/96\"" -term >> "$LOG_FILE"
done

# Count number of ORA- errors
num_errors=$(grep -c 'ORA-' "$LOG_FILE")

if [ "$num_errors" -gt 0 ]; then
  mailx -s "⚠️ ORA- Error Detected in Alert Logs on $(hostname)" info.w3buddy@gmail.com < "$LOG_FILE"
fi

⚙️ Setup Instructions

# ----------------------------------------------
# 1. Create the script directory (if not already)
# ----------------------------------------------
mkdir -p /u01/app/oracle/scripts/logs

# ----------------------------------------------
# 2. Save the script
# ----------------------------------------------
vi /u01/app/oracle/scripts/adrci_alert_log.sh

# (Paste the script above and save)

# ----------------------------------------------
# 3. Make it executable
# ----------------------------------------------
chmod +x /u01/app/oracle/scripts/adrci_alert_log.sh

# ----------------------------------------------
# 4. Test manually
# ----------------------------------------------
/u01/app/oracle/scripts/adrci_alert_log.sh

# ----------------------------------------------
# 5. Configure in crontab to run every 15 mins
# ----------------------------------------------
crontab -e

# Add the following line:
0,15,30,45 * * * * /u01/app/oracle/scripts/adrci_alert_log.sh >> /tmp/adrci_monitor.log 2>&1

# ----------------------------------------------
# 6. Confirm the cron job
# ----------------------------------------------
crontab -l

✅ Notes & Best Practices

  • You can modify the time window by changing systimestamp-1/96 (15 minutes) to another interval like 1/48 (30 minutes).
  • This script works on all Oracle databases using ADR, starting from version 11g.
  • Ensure mailx is configured and functional on your server.
  • Compatible with both RAC and Single-instance environments using shared ADR base.

ADVERTISEMENT