Monitoring Oracle Alert Log

One of the key important place to look at when your database is having issue is the alertlog. All Oracle errors will be logged in the file. Previously, before Oracle 11g, the alertlog was in $ORACLE_BASE/admin/bdump. Oracle introduced the ADR (Automatic Diagnostic Repository) in 11g. The new location of the alertlog is in $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace.

There are many enterprise monitoring tools that will be able to monitor the alert log for any issues with the database. However, some DBAs like me, do not have the luxury to use these tools. Below is a cronjob shell script i used in Solaris 10 to monitor the alertlog.

Do remember to create the required files and folders. ALWAYS test the script first before using it in production!!

#!/usr/bin/ksh
#
# Alert Monitoring Script 1.0
###################################################################################
# Revision History
###################################################################################
# WeiShan V1.0 Creation 02 Oct 2012
###################################################################################

ORACLE_HOME=/u01/app/oracle/product/11.2.0/;export ORACLE_HOME
ORACLE_SID=testdb;export ORACLE_SID
TNS_ADMIN=/u01/app/oracle/product/11.2.0/network/admin;export TNS_ADMIN
HOSTNAME=`/usr/bin/hostname`
DATE=`/usr/bin/date`
BODY=/export/home/$ORACLE_SID/cronjob/monitor_alertlog/body.tmp
#MAILRCV=’emailtome@domain.com’

alert_log_file=/u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log

/usr/bin/date

ALERT_LOGS=`tail -10 $alert_log_file | /usr/xpg4/bin/grep -E ‘ORA-|Error’ | wc -l`

if [ $ALERT_LOGS -gt 0 ]
then
tail -10 $alert_log_file > $BODY
/usr/bin/mailx -s “$HOSTNAME : Oracle Alert Log Error on $ORACLE_SID at $DATE ” $MAILRCV < $BODY
else
echo ” No Error in alert_$ORACLE_SID.log “
fi

Advertisements

2 thoughts on “Monitoring Oracle Alert Log”

  1. Hey,

    I am curious about this line:
    ALERT_LOGS=`tail -10 $alert_log_file | /usr/xpg4/bin/grep -E ‘ORA-|Error’ | wc -l`

    Wont tail -10 prints out the last 10 lines only, meaning if the error was at 11th line your email will return “No error”?

    I simply use one line for monitoring:
    tail -f /path/to/log | grep error

    =D

    1. Hi Nicholas,

      You are right, if the error was on 11th line, there will be “No Error”. Ahh, I guess we are implementing it differently.

      You are using monitoring via a foreground/background process while I am running it every 30 mins via crontab. Am I right on this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s