How to Use Oracle Alert Log Monitoring with Shell Script and Email Notification

How to Use Oracle Alert Log Monitoring with Shell Script and Email Notification

Introduction

Monitoring the Oracle alert log is one of the simplest yet most effective ways to catch early warnings of database issues—like ORA errors, corrupt blocks, or memory issues. Many DBAs rely on OEM or third-party tools, but what if you need a lightweight, script-based solution?

This blog will walk you through creating a shell script that monitors the Oracle alert log, detects new errors, and sends an email notification—all in under 15 minutes.

Why Monitor the Alert Log Manually?

• Lightweight and works even without monitoring tools.

• Can be customized for any Oracle version or error type.

• Works in secure environments with no internet access.

Step-by-Step: Build Your Alert Log Monitor

Pre-requisites

• Linux/Unix-based Oracle Server

• Mailx configured (for email notifications)

• Access to $ORACLE_BASE/diag/rdbms/.../trace/alert_<SID>.log

1. Create the Alert Log Monitor Script

#!/bin/bash

# Set Environment
ORACLE_SID=PROD
ORACLE_BASE=/u01/app/oracle
ALERT_LOG="$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_${ORACLE_SID}.log"
LAST_CHECK="/tmp/last_check_${ORACLE_SID}.txt"
EMAIL="dba_team@example.com"

# Initialize last check timestamp
if [ ! -f "$LAST_CHECK" ]; then
  date +"%b %d %H" > "$LAST_CHECK"
fi

# Read last timestamp
LAST_TIME=$(cat "$LAST_CHECK")

# Check for new ORA- errors
grep "$LAST_TIME" -A 500 "$ALERT_LOG" | grep "ORA-" > /tmp/errors_found.txt

if [ -s /tmp/errors_found.txt ]; then
  mailx -s "ALERT: Oracle DB $ORACLE_SID - Errors Detected" "$EMAIL" < /tmp/errors_found.txt
fi

# Update timestamp for next run
date +"%b %d %H" > "$LAST_CHECK"
 

2. Schedule with Cron

crontab -e

# Run every 15 minutes
*/15 * * * * /home/oracle/scripts/alert_log_monitor.sh

3. Example Email Notification

Subject: ALERT: Oracle DB PROD - Errors Detected

ORA-04031: unable to allocate bytes of shared memory
ORA-00600: internal error code, arguments: [13011]
 

Benefits of This Script

• Works even if Oracle Enterprise Manager is down.

• Easily extensible for other log files (listener, ASM, etc.)

• Great for non-production or DR environments.

  Conclusion

  In environments where simplicity, reliability, and customization matter, shell-based alert log monitoring can be a lifesaver. With just a few lines of code, you gain real-time insight into database issues—no OEM or third-party tool required.

  Start small. Tune it. And make it part of your DBA toolkit.