Monday, September 16, 2013

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

SMS Alert logs ORA- errors



This is something new in database administration part.Suppose DBA was out of office and got call from company manager to come to office.There is some problem in database.Generally DBA connect database and check connection possible or not then check any alert log entry.It is better if DBA and their company people got messages whenever any ORA- errors came in certain period of time.

For this,I found some solution to read alert log and send sms to respective people.
It is mandatory if we put database in archive log and where frequent archives generated.This is require because of the timestamp of archives used to get the start and end time of alertlogs entry.

This is all done through SHELL script and on linux machine.

Here it is the magical script can help to get the info of SMS by mail ans SMS.

This shell designed to read alerts of last 30 minutes.

#!/bin/bash
#Go to bdump location
cd /oracle1/oradata/pindb/bdump/

#SMS duration
sms_first=`perl -e "use POSIX qw/strftime/; print strftime('%I:%M %p',localtime(time()-1800))"`
sms_last=`perl -e "use POSIX qw/strftime/; print strftime('%I:%M %p',localtime(time()))"`

#Current and previos time of alert
current_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()-1800))"`
future_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()))"`
#search the current time
first=`cat -n alert_pindb.log | grep -i "$current_time" | awk '{print $1}' | head -1`
#last=`cat -n test_kavya.log | grep -i "$future_time" | awk '{print $1}' | tail -1`
#1800(30 min)-60(1 min)
i=-1740
count_min=0
#j=1860
#Log the all activities of this shell
echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
echo "Current Time Is $future_time" >> /oracle1/oradata/pindb/ora_alert_activity.log
echo "Time To Check Is $current_time" >> /oracle1/oradata/pindb/ora_alert_activity.log

while [ -z "$first" ]
do
   
        current_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()+"$i"))"`
        first=`cat -n alert_pindb.log | grep -i "$current_time" | awk '{print $1}' | head -1`
        i=`expr $i + 60`;
        count_min=`expr $count_min + 1`;
        if [ "$count_min" -ge "31" ] ; then
            echo "Min is $count_min" >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo "We have reached an Impasse $count_min" >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo "Goodbye" >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo "-------------------------------------------------------------------------" >> /oracle1/oradata/pindb/ora_alert_activity.log
            exit
        fi
       
        echo "In Loop Time Is $current_time" >> /oracle1/oradata/pindb/ora_alert_activity.log
done

#while [ -z "$last" ]
#do
#        future_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()+"$j"))"`
#        last=`cat -n test_kavya.log | grep -i "$future_time" | awk '{print $1}' | tail -1`
#        j=`expr $j+60`
#        #echo "Future In Loop $future_time"
#done

#echo "Current $current_time"
#echo "Future $future_time"

echo "Found Timestamp On Line Number -->  $first" >> /oracle1/oradata/pindb/ora_alert_activity.log
#echo "Last $last"

list1=`cat alert_pindb.log | awk '{if(NR>='$first'){print $0}}' | grep -i ORA- | grep -v ORA-27091 | grep -v ORA-27069 | grep -v ORA-19883 | grep -v ORA-01115 | grep -v 17281 | grep -v ORA-01001`
list2=`cat alert_pindb.log | awk '{if(NR>='$first'){print $0}}' | grep -i ALTER | grep -v BACKUP | grep -v "ARCHIVE LOG"`

cd /oracle1/oradata/pindb/

echo $list1 > /oracle1/oradata/pindb/ora_alert.log
echo $list2 >> /oracle1/oradata/pindb/ora_alert.log

#Remove duplicate errors from list1,list2

cat /oracle1/oradata/pindb/ora_alert.log | sort | uniq > /oracle1/oradata/pindb/ora_alert1.log
cat /oracle1/oradata/pindb/ora_alert1.log > /oracle1/oradata/pindb/ora_alert.log
rm /oracle1/oradata/pindb/ora_alert1.log
#Re-assign values to list1,list2
list1=`cat /oracle1/oradata/pindb/ora_alert.log | grep -i ORA- `
list2=`cat alert_pindb.log | grep -i ALTER `


sms_list1=`echo $list1 | cut -c-160`
sms_list2=`echo $list2 | cut -c-160`
#Mail by mailx and SMS by perl script that developed individually or you can use other way to send sms
if [[ -n $list1 || -n $list2 ]] ; then
    mailx -s "DATABASE Alert" to@mail.com,cc@mail.com < /oracle1/oradata/pindb/ora_alert.log
    ssh XXX.XXX.XXX.XX -l itadmin "cd /itapps/apps/NodeMonitor/;perl sms_ora_alert.pl \"$sms_first to $sms_last $sms_list1 $sms_list2\""
    echo "Mail & Message Sent for -- $sms_list1 -- $sms_list2" >> /oracle1/oradata/pindb/ora_alert_activity.log
else
    echo "I Can't Believe ORA- Error --> $list1 Is Blank" >> /oracle1/oradata/pindb/ora_alert_activity.log
    echo "I Can't Believe ALTER Error --> $list2 Is Blank" >> /oracle1/oradata/pindb/ora_alert_activity.log
fi

echo "Goodbye" >> /oracle1/oradata/pindb/ora_alert_activity.log
echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
echo "-------------------------------------------------------------------------" >> /oracle1/oradata/pindb/ora_alert_activity.log

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response