Shell Script to Monitor the 11g Oracle Database Alert Log File

From Oracle database version 11g, the default format of alert.log file is XML. The text version of the alert log file is still available in ADR (Automatic Diagnostic Repository) directory, but it exists for backward compatibility only. Database Administrators do have a few options to view XML formatted alert log file:

1. Use Oracle Enterprise Manager;
2. Query X$DBGALERTEXT table; and
3. Use ADRCI command line utility.

Using ADRCI utility is the only option to access to alert log file while database is closed. You can even view a XML formatted alert log file for another database or from another server, as long as the file can be read from the server where ADRCI is run. To open the file, use following command after ADRCI utility is started. It will open the alert log file in your text editor with the XML tags stripped.

show alert –file /directory-where-you-have-the-logfile/log.xml

Following script provides a way to check 11g alert log file periodically using cronjob. It will send out an email whenever a new Oracle error message is added in alert log file by the Oracle server. The email will include the original error message from the alert log file, and brief description of the error. The format of email is something similar to this:
ORA-19815: WARNING: db_recovery_file_dest_size of 4294967296 bytes is 97.38% used, and has 112542208 remaining bytes available.
19815, 00000, "WARNING: %s of %s bytes is %s%% used, and has %s remaining bytes available."
// *Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
// *Action: One of the following:
// 1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
// 2. Backup files to tertiary device using RMAN.
// 3. Consider changing RMAN retention policy.
// 4. Consider changing RMAN archived log deletion policy.
// 5. Delete files from recovery area using RMAN.

-----------------------------------------------------
Here is the script:

#!/bin/sh
#Author: Dan Zheng, danthedba.blogspot.com
# June 2009
 
# This script checks alert_sid.log and email ORA error.
# It can be used on a Oracle database version 11g and higher.
#
# For RAC database, please change the homepath setting accordingly when calling
# the ADRCI utility in the script.
#
# Before running this script for the first time, please create a directory:
# $ORACLE_BASE/admin/$SID/dbmonitor for files related to this script.
# Also make sure $ORACLE_BASE AND $ORACLE_HOME is set correctly in your environment.

# Set up environment.
# Make necessary change on following line based on your Unix flavor:

. ~oracle/.bash_profile

export SID=$ORACLE_SID
export TIMESTAMP=`date +%M%H%d%m%y`;
export mon_dir=$ORACLE_BASE/admin/$SID/dbmonitor
export fn=$mon_dir/email_body.tmp
export alertck=$mon_dir/alertck.log

touch $alertck
touch $fn
touch $ORACLE_BASE/admin/$SID/dbmonitor/donot_delete.log
EMAIL='dan_zheng@danthedba.blogspot.com'

cd $ORACLE_BASE
sid=$( echo "$ORACLE_SID" tr '[:upper:]' '[:lower:]')

ADRCI << EOF
spool $mon_dir/alert_$TIMESTAMP.log
set termout off
set homepath diag/rdbms/$sid/$SID
SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'" -term
spool off
exit
EOF

export c_log=`wc -l $mon_dir/alert_$TIMESTAMP.log awk '{ print $1 }'`
export c_tmp=`wc -l $mon_dir/donot_delete.log awk '{ print $1 }'`
echo $c_log
echo $c_tmp
if (($(($c_log)) > $(($c_tmp)))); then
comm -23 $mon_dir/alert_$TIMESTAMP.log $mon_dir/donot_delete.log grep ORA- sort -u > $alertck

exec 3< $alertck
# read until the end of the file
until [ $done ]
do
read <&3 ERR_LINE
if [ $? != 0 ] then
done=1
continue
fi
echo $ERR_LINE >> $fn
export ERR_NO=`echo $ERR_LINE awk '{ print $1 }'awk -F- '{ print $2 }' awk -F: '{ print $1 }'`
echo " Oracle error is : ORA-$ERR_NO"
oerr ora $ERR_NO >> $fn
echo " " >> $fn
echo "-----------------------------------------------------" >> $fn
echo " " >> $fn
done

echo "ERRORS: sending mail!"
SUBJECT="$SID - ORA Error Message found in alert.log on `date`"
echo $SUBJECT

# for a HPUX server, use following line to send email:
#cat $fn /usr/bin/mailx -s "$SUBJECT" $EMAIL

# for a Linux server, use following line to send email:
cat $fn mail -s "$SUBJECT" $EMAIL

else
echo "No Oracle error is found in alert.log since last time this script was run."
fi

mv $mon_dir/alert_$TIMESTAMP.log $mon_dir/donot_delete.log
rm $fn

echo "The script was executed successfully."


Reference:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/adrci.htm#BABBHGFC