That Darn Alert Log
January 23, 2003
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
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
Note: In Oracle documentation there is a statement that states:
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.
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.