11.1 Monitoring the Alert Log

When an Oracle database is created, a file commonly referred to as the alert log is created. As errors and other conditions occur during processing, messages are logged to this file. Basically, every important event that occurs causes a record to be written to the alert log: when the database starts up, when it shuts down, and everything in between (e.g,. creating tablespaces and datafiles, performing privileged operations). Every important error message ends up in the alert log; in some cases, a message directs the DBA to a trace file that contains more detailed alert information.

It is the responsibility of the DBA to monitor the alert log on a regular basis so as to deal with any problems or potential security issues before they affect the database or its users. The sooner you find out about error conditions, the better; unlike wine and cheese, database problems don't improve with age. Monitoring the alert log can be a challenge, however: there is an alert log for every database, and busy DBAs have many other things to do that are more pressing than manually scanning alert log files. As somewhat dyed-in-the-wool geeks,^[1] your authors find that the thought of spending valuable daily minutes manually poring over database alert logs sends shivers up and down our workstations. Frankly, trawling through alert logs makes for a great insomnia cure, but it's hard to fit into the day.

^[1] If you think this is synonymous with "aging geeks," you're right. Your authors readily admit to being over 18 years old.

To automate this tedious process and help DBAs keep close watch over their databases, we've written a collection of scripts designed to monitor the Oracle alert logs in real time and to report directly to the DBA the instant a database problem is detected. These scripts let you configure what to look for in the alert log and the email addresses to which to send messages. In addition, they allow messages to be mailed individually or batched up (depending on platform) and to be sent either immediately or at particular time intervals.

11.1.1 Where is the Alert Log?

The location and name of the file containing the alert log are operating system and version-dependent; they may also be subject to local DBA standards or the caprice of a third-party application's enforcing its own standards for the location of the alert log.

Given a database of the name orcl, the alert log may normally be found at either of these locations on Unix systems:

$ORACLE_HOME/admin/orcl/bdump/alert_orcl.log
$ORACLE_BASE/admin/orcl/bdump/alert_orcl.log

On Win32 systems it would likely be found at:

%ORACLE_HOME%\admin\orcl\bdump\orclALRT.log

11.1.2 Monitoring with chkalert.pl

The chkalert.pl script is at the center of the toolkit alert-monitoring application. This Perl script provides the following capabilities:

Constant monitoring

Monitors the alert log for errors and collects these into an array that is emailed automatically to the addresses you've specified.

Multiple email address

Emails error messages to multiple email addresses if you configure them.

Message throttling

Controls the rate at which that email is sent; this is very helpful in cases where a large number of error messages occur simultaneously. (This feature is only available on Unix systems.)

Flexible configuration of errors to check

Allows you to specify the errors the script should check for in the alert log. You specify errors via a list of Perl regular expressions (giving you the ultimate in flexibility).

This script runs in the background as a daemon on Unix. A version of it, ckalert_NT.pl, runs as a service on Win32.

One particularly important feature of chkalert.pl is the set of configurable controls it provides in an effort to prevent error messages from overwhelming your mail server. You may wonder what terrible circumstances would generate so many error messages that it could bring a mail server to its knees! Actually, this happens more than you would imagine. A relatively common example is an imperfectly tested program that ignores error messages and continues to attempt the same operation over and over. For example, consider a PL/SQL routine that collects records from an OLTP system and inserts them into a data warehouse. After a month of record-breaking sales, the warehouse table chews through all the available space allocated for that month — suddenly, you'll run into a brick wall in the form of an Oracle error such as the following:

ORA-1653 unable to extend table MY_TABLE by 16 in tablespace OLTP_DATA.

A reasonable and well-behaved PL/SQL routine would catch this error and abort the process, notifying someone of the problem. However, if a miscreant piece of code fails to catch an exception of this kind, it can easily generate thousands of error messages in a very short time. This can keep a mail server very busy!

The problem is compounded when the receivers of such emails are running a mail filter, such as procmail, which spawns a new process for each email received. This can easily cause your company's mail server to suddenly display poorer performance than the old Commodore 64 you still have in your closet (don't try to deny it).

As you can well imagine, system administrators are somewhat less than amused by such denials of service, especially when they occur in the wee hours of the morning. And let's face it, things like this always occur in the wee hours of the morning. We like our own sysadmins, and try to avoid giving them reasons to feel otherwise about us, so we use chkalert.pl to keep mail server disasters from occurring.

Although in almost every other case we provide a single script that operates on both Unix and Win32 platforms, that isn't the case here. We originally designed the chkalert.pl script to run on Unix and subsequently created a modified version that works on Win32. When dealing with background daemons for Unix or services for Win32, the code base becomes quite unwieldy if it tries to do both jobs. So in this case we achieved much better performance out of maintaining separate Unix and Win32 versions. In the installation procedures described later in this chapter, we cover Unix installation followed by Win32 differences. Because most aspects of installation and configuration are the same for the two platforms, we recommend that if you are running on Win32, you nevertheless read the Unix section first.

11.1.3 Installing and Configuring chkalert on Unix

If you followed the installation instructions in Chapter 9, the Unix version of the alert-monitoring script, chkalert.pl, will already be installed on your system. You'll find it in the same location as the other executable scripts, most likely in /usr/local/bin. (As long as PATH includes the script installation directory, your system will find it.) Once underway, the Perl daemon carries out the following tasks (all of which can be configured)

11.1.3.1 chkalert.conf

The first installation step is to locate and update the chkalert.conf configuration file used by the chkalert.pl script. The configuration file contains settings for the parameters used to control alert monitoring at your site. Default settings are provided in the configuration file that is included in the toolkit when you first install it. You can then edit this file as desired. We perform this editing process as follows:

There are just a few parameters you'll need to modify. We've reproduced the configuration file from the PDBA distribution in Example 11-1. Following the example we describe the main parameters you can specify in the file.

Example 11-1. Configuration for chkalert.pl — chkalert.conf

package chkalert;
use vars qw{ %ckConf };
  
%ckConf = (
   # recipients of email for alert log errors
   dbaAddresses => [qw{ [email protected] [email protected] }],
   # mail addresses for debugging
   debugDBA => [qw{[email protected]}],
   oratabFile => '/etc/oratab',
   # or whatever the location of your oratab file is
   # it consists of the instance name, Oracle_Home and
   # a flag field of Y or N.  
  
   alarmTime => 300,
   # this is the number of seconds to wait before sending
   # a batch of error messages as email
   # this is batched to prevent large numbers of email
  
   maxLogLines => 100,
   # this is the override for alarmTime.  If maxLogLines
   # of messages are received, mail them now
  
   watchdogLength => 5,
   # this is the max size of the array used to 
   # determine if too much mail is being sent too fast
  
   watchdogTime => 10,
   # this is the elapsed number of seconds between email
   # batches that is used to determine if mail is being
   # sent too quickly
  
   # if the time between the first and last times in the 
   # watchdog buffer is < 
   #  ( watchdog[watchdogLength] - watchdog[0] ) 
   #  * ( watchdogLength * watchdogTime )
   # then the mail delivery is throttled back until things slow down
  
   throttleDelaySeconds => 10,
   # how many seconds to delay between email batches
   # when many errors are being generated
   # this is to protect the system from being choked
   # with procmail processes if you are using it
  
   # what is the name of this server?
   serverName => 'sherlock.jks.com', 
  
   # list of errors to check for
   # must be a comma separated list of regular expressions
   # e.g.  errorList = ^ORA-, ^TNS-, crash
   # the qw operator may also be used
   errorList => qw{^ORA- ^TNS- crash},
);
1;

Here are the configuration file parameters:

dbaAddresses

Address list to which email is sent when errors are detected. Notice that the list is included inside paired square brackets, [], indicating an anonymous array reference (see Appendix A, for a description of anonymous arrays). The qw (quote word) Perl operator also avoids the need to use quote punctuation, which simplifies editing.

debugDBA

Has the same form as dbaAddresses, but consists of a list of addresses for use when debugging the application. You will only need one address for this entry.

oratabFile

An all-important directive that locates the crucial oratab file. We won't try to guess the location of this file, as it can differ widely on various Unix flavors. Simply insert the location of your own oratab file so chkalert.pl can find the proper ORACLE_HOME for each target database.

alarmTime

Number of seconds you want chkalert.pl to hold onto error messages before mailing them. Sometimes errors come in large grape-like bunches, so you may wish to avoid separate emails being sent out for each one (especially if the email destination happens to be your pager and it's 2:00 AM on Sunday morning). One piercing scream from a pager is enough to wake most of us. If you really do want to receive a separate email for each error, possibly because you own shares in the phone company, read on.

maxLogLines

Maximum number of lines chkalert.pl should buffer before mailing them out. This overrides alarmTime. At its default setting of 100, if 100 error messages appear in the alert log before alarmTime times out, this overrides the timer and causes the messages to be mailed immediately. The timer simply resets to 0 when this happens. To see error messages immediately, just set maxLogLines to a value of 1. An email is then sent the moment the monitor detects an error.

watchdogLength , watchdogTime, throttleDelaySeconds

These closely related parameters prevent chkalert.pl from overwhelming a mail system in the event of a runaway error's flooding the alert log:

Whenever error messages are mailed out, the time is recorded in an array called @watchdog. The number of entries in @watchdog is controlled by the parameter watchdogLength. The parameter watchdogTime is the number of seconds and is used as a control to determine whether chkalert.pl should slow down mail deliveries.

Whenever error messages get mailed, chkalert.pl compares the current time with the oldest timestamp in @watchdog. If the difference in seconds between these is less than (watchdogLength * watchdogTime), chkalert.pl inserts a sleep of throttleDelaySeconds between each mailing until the incoming error message rate slows down.

For example, if your database starts throwing errors into the alert log faster than a Tribble population can munch its way through a star cruiser shipment of Quadrotriticale,^[2] this could send your mail server into a reproductive frenzy trying to keep up. The script tries to prevent that from happening. Let's assume these values are set as in Example 11-1, and that chkalert.pl finds that the first batch of error messages got sent out less than 50 seconds ago and this is the fifth batch since then. A 10-second wait will be inserted between mailings, giving the mail server time to breathe.

^[2] Star Trek, season 2, episode 15. See http://www.scifi.com/startrek/episodes/42.html.

serverName

Name of the database server. This name is used purely for informational purposes whenever error messages are emailed.

errorList

Allows us to specify exactly what we want chkalert.pl to consider as an error. This is a list of regular expressions contained within either a comma-separated list or a list specified by Perl's qw operator. (See Appendix C, for more information.)

11.1.3.2 Running chkalert.pl

We're now ready to run chkalert.pl to see how it works. Let's test it first, on a copy of an alert log in which we've generated our own error messages:

11.1.3.3 Testing with a real alert log

This time we'll execute chkalert.pl against a real alert log without the previous -debug option. This forces chkalert.pl to run as a daemon process:

$ chkalert.pl -sendmail -database ts01 \
     -alertlog $ORACLE_HOME/admin/ts01/bdump/alert_ts01.log
DATABASE: ts01
ALERT LOG: /u02/app/oracle/product/8.1.7/admin/ts01/bdump/alert_ts01.log
DBA's    : [email protected] [email protected]

We're set. If you've included your cell phone or pager number as one of the email addresses and if the database has a sudden problem at 2:00 AM, you'll get the call first so you can deal with it. You'll then be able to stroll casually into your office at 8:45 AM without anyone ever knowing there was a problem. No more angry mobs ambushing you in the car park, no more smirkers loafing round the coffee machine waiting for the DBA to fix the database, no more sudden surprises.^[3]

^[3] OK, so we're the first to agree that restful slumber's being broken is a long way from being good, but performance reviews where share options may go up, rather than down, should provide adequate compensation.

Just to maintain this blissful nirvana moment and help ensure that chkalert.pl is always running, you might want to schedule it to start periodically via your system scheduler (often cron on Unix systems).

When chkalert.pl starts, it creates a lock on a temporary baton file of the form /tmp/chkalert.$ORACLE_SID. Subsequent attempts to run chkalert.pl on the same database will fail when the file lock operation is attempted. This is harmless, because chkalert.pl merely exits when it's unable to lock the temporary baton file.

If you need to terminate the chkalert.pl daemon, it's easily done via chkalert.pl's own -kill option. When chkalert.pl starts, it creates a temporary file containing its own process ID — sort of like an Apache .pid file. The -kill option tells chkalert.pl to open that file, in read-only mode, and use its artificial intelligence, glowing red eyes, and liquid metal, to locate the PID of the running process. Once tracked down, the process is terminated.

The file containing the process ID is always named chkalert.<database>. On our Unix server for instance, the database is named ts01. When chkalert.pl is started, a file named chkalert.ts01 is created. On Unix systems this file is created in the /tmp directory, and on Win32 servers it's created in C:\TEMP. The contents of this file are fairly simple:

$ cat /tmp/chkalert.ts01
15575

The following is an example of stopping a currently running chkalert.pl daemon. All of the command-line options are summarized in Table 11-1:

oramon > chkalert.pl -database ts01 -kill
DATABASE: ts01
ALERT LOG: /u02/app/oracle/product/8.1.7/admin/ts01/bdump/alert_ts01.log
DBA's    : [email protected] [email protected]
chkalert process 3790 killed

Table 11-1. Command-line options — chkalert.pl

Option	Description
-alertlog	Full path to the database alert log file
-database	ORACLE_SID of database to check
-debug	Runs in console mode and prints debugging messages
-kill	Used with -database option to stop the chkalert.pl daemon
-sendmail	Error messages mailed to DBA addresses in configuration file

11.1.4 Installing and Configuring chkalert_NT.pl for Win32

So far, we've been focusing on how to do alert monitoring on Unix systems. The methods for handling background processes differ significantly for Win32, and as we mentioned, there is a separate script for Win32. That script, chkalert_NT.pl, was installed along with the rest of the toolkit. This script is dependent on the Win32::Daemon Perl module, also installed in Chapter 9, which allows chkalert_NT.pl to run as a service. These are the main configuration steps. Note that in many cases, the installation is the same for Unix and Win32, so we'll refer to the earlier Unix discussion where appropriate:

Table 11-2. Command-line options — chkalert_service.pl

Option	Description
-install	Installs the alert log monitor service
-remove	Removes the alert log monitor service
-database	Supplies the SID of the database
-alertlog	Provides the full path to the alert log

11.1.4.1 Starting the service

Now that chkalert_NT.pl has been installed, we fire up the Win32 Service Manager to start the service. You can navigate to this tool with one of the following sets of keystrokes. (Note that Service Manager works much the same on the two platforms.)

Windows NT

Start Settings Control Panel, click Services

Windows 2000

Start Settings Control Panel, click Administrative Tools, click Services

Figure 11-1 is an example of what you should see in Service Manager after successfully installing chkalert_NT.pl. The new service appears as Oracle_ts20_AlertLogMon. Simply click on the Start button, and your alert log monitor should be off and running. (If this install fails to go smoothly the first time around, check Chapter 9 for all the modules required by chkalert_NT.pl.)

Figure 11-1. The new Oracle_ts20_AlertLogMon service

If the install should have the temerity to run imperfectly first time out, you may see a screen like that shown in Figure 11-2. You will need to go to the command line to try to sort it out. Check to see if Perl can compile the script:

C:> perl -cw c:\perl\bin\chkalert_NT.pl

You can see the results of that in the following:

Can't locate Win32/Daemon.pm in @INC (@INC contains: C:/Perl/lib
C:/Perl/site/lib .) at c:\perl\bin\chkalert_NT.pl line 10.
BEGIN failed--compilation aborted at c:\perl\bin\chkalert_NT.pl line 10.

It appears that we missed installing the Win32::Daemon module. Should something like this happen, go back and review the installation instructions in Chapter 9 to make sure that all the required elements were installed.

Figure 11-2. Oracle_ts20_AlertLogMon service failure

After re-installing Win32::Daemon to its former glory (we kept removing it in our test runs), the compile was successful, producing this output:

c:\perl\bin\chkalert_NT.pl syntax OK

If you continue to experience difficulties in running the service, make sure that the fully qualified file name, specified for the alert log during chkalert_service.pl installation, is correct. If it is incorrect, it won't cause an error until you attempt to restart the service.

11.1.4.2 Testing Oracle_SID_AlertLogMon

Now that we have the Oracle_SID_AlertLogMon service running, we can verify that it's working as expected. Carry out this test only while a test database is down, as we need to directly edit its alert log.

The command-line options for chkalert_NT.pl are listed in Table 11-3; note that they differ somewhat from the earlier Unix list. If you want to change any of these, you will need to remove the alert checking service, modify chkalert_service.pl and then reinstall the service.

Table 11-3. Command-line options — chkalert_NT.pl

Option	Description
-alertlog	Full path to the database alert log file
-database	ORACLE_SID of the database to check
-debug	Indicates debugging messages to be included in log file
-logging	Turns on the logging feature
-sendmail	Error messages mailed to the configured DBA addresses