That Darn Alert Log

Until Oracle decides to give DBAs
built-in direct access to the alert log, it will always be our responsibility
to continually check and maintain it. Here is a simple solution that will help
with this most basic of DBA task.

The Oracle alert log file is a chronological log of
important messages and errors that are encountered by the simple use of an
Oracle database. It is the first line-of-defense, or area of notification for
problems encountered in the database. If you haven’t been introduced to this
log file in the past, I have provided a short list of the types of messaging
that you will see in this file. As you can see there is tons of information
just waiting for discovery.

Types of messages and errors

  • ORA-600 internal errors that need immediate support from Oracle’s customer support
  • ORA-1578 block corruption errors that require recovery
  • ORA-12012 job queue errors
  • STARTUP & SHUTDOWN, and RECOVER statement execution messages
  • Certain CREATE, ALTER, & DROP statements
  • When a resumable statement is suspended
  • When log writer (LGWR) cannot write to a member of a group
  • When new Archiver Process (ARCn) is started
  • Dispatcher information
  • The occurrence of someone changing a dynamic parameter

As a DBA, you are responsible for the proper running of your
Oracle database. You have a task list one mile long, that must be performed
every day. In the case of alert log monitoring, you must check this log file a
few times a day, if not more, to ensure that users have not experienced any
problems and that the database is running without any internal notifications.
What makes this worse, most database shops have more than one instance of
Oracle running. In order to check the alert log, you must log onto each
database box, open the alert log file with an editor, and search for errors.
The joy of the approach I would suggest gives you access to the alert log
without ever having to log onto the database box again. Through the use of a
simple package and procedures, which you should modify to suit your tastes, you
can have a robust alert log monitoring device and sleep well at nights.

Reference for the procedures, packages, and objects used

Directory Object
This object allows you to access and use external Large
Objects (LOBS) or Binary Files (BFILEs) in an Oracle Server. The DIRECTORY
object is an alias for a physical directory under which there may be files you
wish to access.

Note: In Oracle documentation there is a statement that

Do Not Map DIRECTORY to Directories of Data Files, And
So On. A DIRECTORY should not be mapped to physical directories that contain
Oracle data files, control files, log files, and other system files. Tampering
with these files (accidental or otherwise) could corrupt the database or the
server operating system.

I have contacted Oracle support (TAR 266057.996) and received
the following response / blessing from them on this method of accessing the alert.log:

You can access the alert.log
file through the creation of a directory object.
This should not be any problem with respect to performance etc.

Utl_raw package
This package supplied by Oracle allows you to manipulate raw
data and should be available with a typical install. If it is not there, you
can find it in the $ORACLE_HOMErdbmsadmin directory. If you have to install
this package yourself just do so as user ‘sys’.

DBMS_LOB package
Used to access, search, and manipulate LOB/BFILE

This procedure is an initial setup for use of the package.
It determines where your alert log is, and creates a directory object to that
log file.

When supplied with possible SID names from the database,
this procedure builds a string of possible alert log file names. Typically, for
Windows’ environments, the alert log name is SIDALRT.LOG and for UNIX environments,
it is alert_SID.log.

This procedure just loops through the possible alert log
file names and checks for its’ existence.

The meat of the code. Read_alertlog sets up calls to the build_possible_alert_log_name
procedure, calls to check_alertlog_name for the existence of a good alert log
file, opens the alert log file, and begins reading the alert log file for the
lines encountered after first finding the current date in the log. It then
prints out the lines to the terminal.

To get you going, let’s first go through the ‘How to Use’
procedures to put the simple shell on a database box and see the results.
After you are comfortable with the shell, I would suggest you modify it by
removing the DBMS_OUTPUT.PUT_LINE with a call to code that will search the
strings returned, insert them into a table, and have email or pager
notification wrapped around those important error messages. After you get that
done, just put the procedures on every database you want to monitor, schedule a
task or dbms_job around it and then sleep peacefully.

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles