I want to automate the monitoring of my Oracle alert log,
and I want to write a program to monitor the alert log for messages. What is
the best way to monitor the alert log, externally with a shell script or by
defining my alert log as an external table and using SQL? Also, what alert
log messages should I look for?
It's important to monitor the Oracle alert log and
send alerts when serious errors are logged into the alert log. There are many
approaches to detecting alert log messages and sending e-mail alerts:
SQL against the alert log - You can define the alert log file as an
external table and detect messages with SQL.
Scripts - Write your own OS shell scripts to detect alert log messages.
PL/SQL - You can write a SQL*Plus procedure using utl_file to read the
A comprehensive alert log monitor is not trivial, and it can can take
hundreds of hours to write and de-bug and maintain. Many companies find it
more cost effective and reliable to choose to use a
remote DBA service that provides
sophisticated alert log monitor software.
The key to monitoring the alert log for critical messages is to use regular
expressions which are available either in UNIX/Linux shell scripts or with SQL
regular expressions. The script that I use on my remote DBA clients is very
sophisticated and took hundreds of hours to develop, but simple alert log
monitor scripts can be developed using the standard UNIX and Linux utilities.
An alert log monitor script may have these features:
1 - Records of all startups and shutdowns - Startup is in
v$instance.startup_time, but shutdown data is problematic as it is only
written to the alert log. During a normal shutdown, Oracle writes message
to the alert log, but under an emergency abort (i.e. un-plug the server),
nothing appears in the logs, nor within Oracle. Oracle stops abruptly, and
there is no way to see it, except periodic polling for a PMON or SMON
2 - Finds important unexpected events - Most people write an alert
log monitor with ?exceptions?, designing a ?clean list? of alert log
messages to ignore, and sending e-mail alerts for new, unexpected messages.
Oracle monitoring package may alert for hundreds of metrics.
3 - Seeks important error messages - Using a utility like grep,
you can easily find ORA-00600 errors, but it's a bit trickier to find
"unexpected" messages. While OEM has primitive
alert log monitoring, real shops need scripts that are more