Script to monitor ORA errors in alert log

By Gowthami | apps-dba.com | Oracle Database Administration Series

Monitoring the Oracle alert log for ORA- errors is a critical DBA responsibility. Catching errors early prevents minor issues from escalating into major outages. This post provides a shell script to automate alert log monitoring and send notifications when ORA- errors are detected.

What You Will Learn: How to write a shell script that scans the Oracle alert log for ORA- errors, logs findings, and sends email notifications to the DBA team.

Why Monitor the Alert Log?

The Oracle alert log records critical database events including:

• ORA- errors (deadlocks, tablespace issues, corruption warnings)
• Instance startup and shutdown events
• Log switch and checkpoint information
• Redo log and archive log events

Alert Log Location

Find the alert log location using SQL*Plus:

SQL> SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

VALUE
------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

The alert log file is named alert_<SID>.log in that directory.

The Monitoring Script

Save the following script as /home/oracle/scripts/monitor_alert_log.sh:

#!/bin/bash
# ============================================================
# Script  : monitor_alert_log.sh
# Purpose : Monitor Oracle alert log for ORA- errors
# Author  : apps-dba.com
# ============================================================

# Configuration
ORACLE_SID=ORCL
ALERT_LOG=/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_ORCL.log
LOG_DIR=/home/oracle/scripts/logs
EMAIL=dba-team@yourcompany.com
HOSTNAME=$(hostname)

# Create log directory if not exists
mkdir -p $LOG_DIR

# Timestamp
TS=$(date '+%Y%m%d_%H%M%S')
OUTPUT_FILE=$LOG_DIR/ora_errors_$TS.log

# Check if alert log exists
if [ ! -f "$ALERT_LOG" ]; then
  echo "Alert log not found: $ALERT_LOG"
  exit 1
fi

# Get errors from last 60 minutes
CUTOFF=$(date -d '60 minutes ago' '+%Y-%m-%d %H:%M:%S')

# Search for ORA- errors
grep -E "ORA-[0-9]+" "$ALERT_LOG" > "$OUTPUT_FILE"

# Check if any errors were found
ERROR_COUNT=$(wc -l < "$OUTPUT_FILE")

if [ "$ERROR_COUNT" -gt 0 ]; then
  # Send email notification
  mail -s "[ALERT] ORA- Errors on $HOSTNAME ($ORACLE_SID)" "$EMAIL" << EOF
ORA- errors detected in alert log on $HOSTNAME.
Database SID: $ORACLE_SID
Time: $(date)
Error Count: $ERROR_COUNT

Errors Found:
$(cat $OUTPUT_FILE)

Please investigate immediately.
-- apps-dba.com monitoring script
EOF
  echo "Alert sent: $ERROR_COUNT ORA- errors found."
else
  echo "No ORA- errors found. Log check complete."
  rm -f "$OUTPUT_FILE"
fi

Make the Script Executable

$ chmod +x /home/oracle/scripts/monitor_alert_log.sh

Schedule with Cron

Run the script every 30 minutes using cron:

$ crontab -e

# Monitor Oracle alert log every 30 minutes
*/30 * * * * /home/oracle/scripts/monitor_alert_log.sh >> /home/oracle/scripts/logs/cron_monitor.log 2>&1

Common ORA- Errors to Watch

ORA Error	Description	Priority
ORA-00060	Deadlock detected	High
ORA-01555	Snapshot too old	Medium
ORA-01653	Unable to extend tablespace	High
ORA-27300	OS system call error	High
ORA-00257	Archiver error (archive full)	Critical

Enhancements

• Filter specific ORA errors you want to ignore (e.g., ORA-12541 in test environments)
• Parse the log incrementally using a marker file to avoid re-reading old entries
• Integrate with monitoring tools like Nagios, OEM, or PagerDuty

Master Oracle Exadata

This post is part of our Oracle Database Administration series. Get our comprehensive Exadata guide with architecture diagrams, performance tuning, and practical DBA scripts.

Get the Exadata PDF Guide