Monitor Oracle alert log

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.

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/
alert_$ORACLE_SID.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
fi

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.

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