Script to monitor the Oracle Database “alert log”

The Oracle Database leaves important information on the alert log, it is important to review the messages periodically in all our databases.

This information can be viewed in various ways, using operating system commands (tail, vi, more, notepad ….), from within the database using the X$DBGALERTEXT view (release 11g and up) or using the ADRCI tool from the operating system.

I use this last approach because this tool allows me to filter the lines using “SQL like” commands.

However, it is important to not retrieve the same error multiple times, we can accomplish this by checking the alert in a timed fashion and only retrieving lines from last period of time.

This script searches in the alert file the string “ORA-” and sends the lines if found by mail (using operating systems built in mail system). The ARDCI tool exists also in Windows platforms and because of that it can be adapted.

Let’s begin:

#!/bin/bash

#Load environment variables, do not define any particular SID o DBNAME
. /home/oracle/scripts/setenv_nobd.env

declare -a INSTANCE_LIST=("instance1" "instance2" "instance3" )

for INSTANCE in "${INSTANCE_LIST[@]}"
do

#Program on cron every hour, check for errors on the past hour.
#Select the OH for the instance and show alert log lines generated last hour containing "ORA-" string
OUTPUT=$(adrci exec="set home $INSTANCE; show alert -term -p \\\"MESSAGE_TEXT like '%ORA-%' and ORIGINATING_TIMESTAMP >= systimestamp-1/24\\\"")

#If some of the lines contains "ORA-" string mail them
case $OUTPUT in
( *ORA-* ) mailx -r databases@mydomain.com -s "Errors in alert $INSTANCE" "myself@mydomain.com" «< "$OUTPUT"
esac

done
Advertisements

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