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