Check alert log, monit alerts in alertlog?

One duty of DBA is to monit the databases often, one place with lot of value information is the alert log file,  errors and any other problems are writed to this file by the datbase.

What happend when u have lot of databases? this process can be a high spend of time. We have the solution.

Other day i configure scripts to atomatic check alert log files and send to dbas email box if any ORA appears.

For this duty ORACLE give us in datbase a sys table to read the alert log files (xmls alert files) so with a simple query we can check for any
occurence in alert log at any time.

Feel free to change the script(ckalert.sh), this is a simple script to do the job.
I program in crontab a frecuency of 10 mins to check last 10 minutes if some ORA founded will send email.

First server must be ready to send emails, and better create a user to check and send emails, sys not good, and cause not work give privileges to sys.x$dbgalertext need create a view and give access to user to this view:

create view alertfile as select * from sys.x$dbgalertext;

grant select on sys.alertfile to [USER];

— ckalert.sh
#!/bin/sh

for i in $db
do

$ORACLE_HOME/bin/sqlplus -s [USER]/[PASSWORD]@$i << ! > $ERR_MSG_FILE

SET PAGESIZE 1000
set lines 120
set echo off
set heading off

select to_char(ORIGINATING_TIMESTAMP, ‘dd-mon-yyyy hh24:mi:ss’),
substr(MESSAGE_TEXT, 1, 300) message_text
from sys.alertfile
where (MESSAGE_TEXT like ‘%ORA-%’
or upper(MESSAGE_TEXT) like ‘%ERROR%’)
and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate – 10/1440
and MESSAGE_TEXT not like ‘%ORA-29400%’
and MESSAGE_TEXT not like ‘%GATHER_STATS_JOB%’
and MESSAGE_TEXT not like ‘%error 19502%’
and MESSAGE_TEXT not like ‘%error 12545%’
and MESSAGE_TEXT not like ‘%Fatal NI%’
and MESSAGE_TEXT not like ‘%Tns error struct%’;

!

result=$(cat $ERR_MSG_FILE | egrep -e “No row selected”)

if [ -s “$ERR_MSG_FILE” ]
then
if [[ -z “${result}” ]]
then
SUBJECT=”Alerta Oracle $i – $ENVIR – `hostname`”
cat -v $ERR_MSG_FILE > $ERR_MSG_FILE.new.txt
cat $ERR_MSG_FILE.new.txt | mailx -s “${SUBJECT}” “$MAIL”
echo ” cat $ERR_MSG_FILE.new.txt | mailx -s “${SUBJECT}” “$MAIL”” > /home/oracle/scripts/send_mail
rm $ERR_MSG_FILE.new.txt
fi
fi
done
— crontab:
# alert log monit
00,10,20,30,40,50 * * * * /home/oracle/scripts/ckalert.sh > /home/oracle/scripts/ckalert.cronlog 2>&1

 

 

Advertisements

Comment

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