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

• 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
• Statements that effect TABLESPACES, DATAFILES, and ROLLBACK SEGMENTS
• 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 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.
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_HOME\rdbms\admin 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 information.

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

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

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

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