📋Steps in this guide1/18

Automating ORA- Error Alerts from Alert Log in Oracle

Tired of discovering critical ORA- errors too late? Learn how to automate Oracle alert log monitoring with a shell script and email alerts

oracle configurationintermediate

by OracleDba

36 views

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: 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.

Code/Command (click line numbers to comment):

1
2

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

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 1: Identify Your Alert Log Location

Use the following to locate your alert log: If you’re unsure of the ORACLE_BASE, use:

Code/Command (click line numbers to comment):

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

bash
ps -ef | grep pmon echo $ORACLE_BASE

Step 2: Write the Monitoring Script

Here’s a basic yet solid shell script: This script checks for any new ORA- errors that appeared since the last run and emails them. Simple and effective.

Code/Command (click line numbers to comment):

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"

Step 3: Set Up Cron Job

Run crontab -e and add this line: This checks the log every 15 minutes. Adjust the frequency based on your environment’s criticality.

Code/Command (click line numbers to comment):

1
2

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

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:

Code/Command (click line numbers to comment):

1
2

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: Combine that with Windows Task Scheduler and you’re good to go.

Code/Command (click line numbers to comment):

1
2

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

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😎

Comments (0)

Please to add comments

No comments yet. Be the first to comment!