Pradip
14 Jun, 2025
0 Comments
5 Mins Read

Automating ORA- Error Alerts from Alert Log in Oracle

Hey there,

If you’re a DBA like me, you’ve probably faced this moment: it’s 2:30 AM, your phone buzzes, and someone from the app team says, “The database is stuck, we can’t proceed. Please check ASAP!”

You drag yourself out of bed, log in, check the alert log… and there it is — an ORA-00257 or ORA-1653 quietly sitting there since midnight. Had it been caught early, you could’ve avoided the panic.

Sound familiar?

That’s exactly why I’m writing this.

In the real world, things break when you’re not looking. And checking the alert log manually? Let’s be honest, no one has time for that every hour. So, why not automate it?

In this article, I’ll walk you through a practical, production-ready way to automate email alerts when any ORA- error appears in your Oracle database’s alert log. I’ll keep the language simple but the content highly technical, so you can apply it directly in your setup.

Let’s get into it.

What Is the Oracle Alert Log?

The Oracle alert log is your database’s black box recorder. It stores essential messages generated by Oracle’s background processes. These include:

ORA- errors
Instance startup and shutdown events
Archiving operations
Tablespace/datafile issues
Internal errors like ORA-00600 and ORA-07445

Starting from Oracle 11g onwards, the alert log is stored in a standard ADR (Automatic Diagnostic Repository) path:

bash
$ORACLE_BASE/diag/rdbms/<DB_UNIQUE_NAME>/<SID>/trace/alert_<SID>.log

Real-world tip: On one 19c RAC system I handled, we missed an ORA-00257 (archiver stuck) error for four hours because nobody checked the logs during the night. That delay caused our standby to fall behind significantly, which impacted morning reporting. After that, I made automated monitoring mandatory.

Why Automate ORA-Error Alerts?

Let’s break it down logically. Why should you care about this?

Real consequences of ignoring the alert log:

Error Code Impact

ORA-00257 Archive log stuck, backups fail, standby stops

ORA-1653 Tablespace full, DML fails

ORA-00600 Internal corruption, possible crash

ORA-19809 FRA full, backup and archiving fail

According to Oracle Support, over 60 percent of critical severity tickets are preventable if the alert log is monitored properly. That’s not a random number — it’s from real Oracle SR data.

In today’s world where uptime is money and database stability is non-negotiable, automation is no longer a luxury. It’s a necessity.

What Are We Automating?

Our goal is to automatically monitor the alert log, and as soon as it detects any new ORA- error, it should send an email to the DBA team. This needs to happen without manual intervention and without repeatedly sending the same alert.

Here’s how we’ll do it:

Write a shell script to scan the alert log
Track only the new entries since the last scan
Filter for ORA- messages
Email the filtered results
Schedule it using cron

Step-by-Step Guide

Step 1: Identify Your Alert Log Location

Use the following to locate your alert log:

bash
export ORACLE_SID=ORCL cd $ORACLE_BASE/diag/rdbms/orcl/ORCL/trace ls alert_ORCL.log

If you’re unsure of the ORACLE_BASE, use:

bash
ps -ef | grep pmon echo $ORACLE_BASE

Step 2: Write the Monitoring Script

Here’s a basic yet solid shell script:

bash
#!/bin/bash
ORACLE_SID=ORCL
ALERT_LOG="/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log"
TEMP_LOG="/tmp/ora_errors.tmp"
LAST_LOG="/tmp/ora_last_check.log"
EMAIL="[email protected]"
HOST=$(hostname)
# If no last log exists, initialize it
if [ ! -f "$LAST_LOG" ]; then
    cp "$ALERT_LOG" "$LAST_LOG"
fi
# Capture new lines
NEW_LINES=$(diff "$LAST_LOG" "$ALERT_LOG" | grep ORA-)
if [ ! -z "$NEW_LINES" ]; then
    echo "$NEW_LINES" | mail -s "ORA- Error Detected on $HOST - $ORACLE_SID" "$EMAIL"
fi
cp "$ALERT_LOG" "$LAST_LOG"

This script checks for any new ORA- errors that appeared since the last run and emails them. Simple and effective.

Step 3: Set Up Cron Job

Run crontab -e and add this line:

bash
*/15 * * * * /home/oracle/scripts/check_alert_log.sh

This checks the log every 15 minutes. Adjust the frequency based on your environment’s criticality.

A Real Use Case from Production

In one enterprise setup with 15+ Oracle 19c databases, including RAC and standby environments, we implemented this script with the following customizations:

Logtail used for more efficient reading
Grouped error types for criticality
Slack and email integration for faster visibility

Within the first month, this alerting caught 6 different ORA- errors within minutes — all before they impacted applications. This resulted in zero business outages for that quarter. Zero.

Enhancements You Should Consider

If you’re handling more than one database, or if your alert logs are large, here are some pro tips:

Use logtail or sed markers

Instead of diff, use logtail for efficient scanning:

bash
logtail -f "$ALERT_LOG" -o /tmp/logtail.state | grep ORA-

Categorize errors

Not all ORA- errors are equal. You can filter based on severity:

Critical: ORA-00600, ORA-00257, ORA-07445
Moderate: ORA-01555, ORA-1653
Informational: ORA-00001, ORA-01403

Push alerts to collaboration tools

Email is fine, but in modern environments, integrate with Slack or Microsoft Teams using webhooks.

Integrate with monitoring dashboards

Tools like:

Zabbix with custom UserParameters
OEM Cloud Control
Prometheus + Grafana via shell wrappers

can all collect and visualize alert log errors in near real-time.

What If You’re on Windows?

If your Oracle instance runs on Windows, don’t worry. You can still do this using PowerShell:

powershell
Get-Content -Path "alert_ORCL.log" -Tail 200 | Select-String "ORA-" | Send-MailMessage ...

Combine that with Windows Task Scheduler and you’re good to go.

Frequently Asked Questions

What if my alert log rotates?

Oracle rotates alert logs once they grow large. Use checksums or file size comparison to reset your last-read marker if rotation is detected.

Will this script send duplicate alerts?

Not if implemented correctly. By maintaining a copy of the last scan and comparing it with the current log, duplicates can be avoided.

Final Thoughts

Monitoring the alert log is one of those old-school DBA practices that will never go out of style. But doing it manually in a 24×7 environment? That’s just not sustainable.

Automating ORA- error alerts is one of the simplest and most valuable steps you can take to keep your systems stable, your team responsive, and your weekends peaceful.

I’ve implemented this for clients in banking, healthcare, and manufacturing — and the feedback is always the same: “Why didn’t we do this earlier?”

Coming Up Next

In the next article, I’ll show you how to set up archive log lag monitoring between primary and standby databases in Oracle Data Guard and trigger alerts when thresholds are crossed.

Let me know if you want that sent directly, or if you’d prefer a packaged script download.

And if you’ve already implemented this alerting, I’d love to hear what challenges or improvements you made. Let’s keep learning from each other.

Happy Reading

ANKUSH😎