Skip to main content

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 ErrorDescriptionPriority
ORA-00060Deadlock detectedHigh
ORA-01555Snapshot too oldMedium
ORA-01653Unable to extend tablespaceHigh
ORA-27300OS system call errorHigh
ORA-00257Archiver 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
Labels:

Comments

Post a Comment

Popular posts from this blog

Data Safe - Introduction

Oracle Data Safe - Practical Guide Oracle Data Safe learner guide Oracle Data Safe Assess risk, discover sensitive data, audit activity, and mask safely It focuses on what Data Safe helps you do operationally: review security posture, find risky identities, centralize auditing, locate sensitive data, and produce safer non-production copies. Contents 01 Why Data Safe matters 02 Where it fits 03 Capability map 04 Assessments 05 Activity Auditing 06 Discovery and Masking 07 Operating model 08 First 30 days 09 Knowledge check Section 01 Why Data Safe matters Database security work is often fragmented. One process checks configuration drift, another stores audit logs, another team scans for PII, and another team writes masking logic for test refreshes. Data Safe is useful because it turns those separate jobs into one security workflow. Key idea The best way to think about Data Safe is as a control plane for database security posture: assess the target, identify risky accounts, d...
Post a Comment
Read more

Testing Different Access Paths : Concatenated Index

Oracle Concatenated Indexes - Practical Deep Dive Oracle concatenated index deep dive Concatenated Indexes How composite indexes really work, why column order matters, and when skip scan changes the story Concatenated indexes, also called composite indexes, are easy to explain badly and surprisingly rich to explain well. The usual summary is “Oracle can use the index only when the leading column is present,” but that is only the starting point. To design them properly, you need to think about leading portions, equality versus range predicates, ordering requirements, skip scan eligibility, covering behavior, and whether one composite index can replace several single-column indexes in a given workload. Contents 01 What concatenated indexes are 02 Leading edge and leading portion 03 Why column order matters 04 Skip scan and when it helps 05 Access patterns and plan reading 06 Covering and sort elimination 07 Design rules that actually hold 08 Common mistakes 09 End-to-end demo 1...
Post a Comment
Read more

Database Replay - Real Application Testing (RAT)

Oracle Database Replay and RAT - Practical Deep Dive Oracle Database Replay deep dive Database Replay and Real Application Testing How to validate upgrades, patches, migrations, and risky changes with real workload behavior Database Replay is one of the most practical risk-reduction tools in the Oracle DBA toolbox. Instead of trusting synthetic benchmarks, isolated SQL tests, or intuition, you capture a real production workload, restore a test system to the same logical starting point, replay that workload, and analyze whether performance, errors, timing, and transactional behavior still look safe. Contents 01 What RAT actually is 02 Why Database Replay matters 03 End-to-end workflow 04 Capture design and prerequisites 05 Preprocess, calibrate, replay 06 Reading the results well 07 Pitfalls and unreplayable work 08 Database Replay vs SPA 09 Practical playbooks 10 Knowledge check Section 01 What Real Application Testing actually is Real Application Testing, usually shortened...
Post a Comment
Read more