Alert Log   «Prev  Next»
Lesson 6Monitor the alert log
ObjectiveMonitor the alert log for serious conditions.

Monitor Oracle alert log

Finding troublesome messages

There are many ways to scan an Oracle alert log to find abnormal messages. Serious messages that result in an instance crash will be readily apparent, so you do not need to monitor for these. However, errors that may cause a session to abort without crashing the instance can be monitored.

grep utility and tail command

The most common technique is to use the UNIX grep utility to display any lines at the end of the alert log that contain the bad string. Since the alert log is a continuously growing flat file, you need to monitor the most recent lines in the alert log using the Unix tail command. It is important to determine the correct amount of data to inspect so that you do not receive duplicate messages each time that the monitor is executed. The number of recent lines can range from 100 lines for a small database to 800 lines for a large active database. Of course, the frequency with which you monitor also determines how many lines in the alert log that you must inspect. Here is the basic command to display all ORA-600 messages (unknown serious errors):
grep ORA-00600 $ORACLE_HOME/rdbms/log/
# Check alert log for ORA-600
tail -400 $ALERT_DIR/alert_$ORACLE_SID.log|grep ORA-00600 > /tmp/ora600_${ORACLE_SID}.txt
check_stat=`cat /tmp/ora600_${ORACLE_SID}.txt|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
 then echo "telephone the work number of the DBA for this system and  
 tell them that ORA-600 messages were found." | mail oracle

ORA-600 messages

Now, if you want to check ongoing messages, such as ORA-600 messages, in your alert log you need to only examine the most recent 400 lines of the alert log. To do this, you can use the UNIX tail command. The script you will see by clicking on the View Code button can be scheduled. This type of monitoring script can also be extended to look for other important conditions, including database problems that cause individual programs to fail. The idea is to periodically (using a Unix cron task) run the script to check the alert log, and mail the important messages to the Oracle DBA.

monitoring script
monitoring script

Now that we see how to automate the inspection of the alert log, let's conclude this module with a wrap-up of the important concepts.

Monitor alert Log - Exercise

Click the Exercise link below to try writing your own script to check the alert log for other conditions.
Monitor alert Log - Exercise