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/traceThe 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"
fiMake the Script Executable
$ chmod +x /home/oracle/scripts/monitor_alert_log.shSchedule 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>&1Common 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