#!/usr/bin/perl
##########################################
# This script checks alert logs for all 
# Oracle instances on the server where 
# it's scehduled to run and pages dba oncall
# in case of any new ORA- errors 
#########################################
#

$ORATAB_FILE="/etc/oratab";
#$ORATAB_FILE="/tmp/oratab";
$host=`hostname`;
chomp($host);
# Getting list of Oracle instances on the server
open (OF,$ORATAB_FILE);
@oratablines=<OF>;
close (OF);
# Getting oncall DBAs pagers
$pagerlines='henry.shao@xxx.com';
$pagerlines1=$pagerlines;
$pagerlines1=~s/\@/\\\@/g;
  foreach $oratabline (@oratablines) {
     undef (@newalerts);
     undef (@oramessarray);
     undef (@orarray);
     if ($oratabline=~/^o/ || $oratabline=~/^\+/ ) {
       chomp ($oratabline);
       ($sid,$sid_home,$flag)=split(/:/,$oratabline);
       # Location of alert log based on ORACLE_SID
    $ENV{'ORACLE_HOME'}=$sid_home;
    if (! -e "$sid_home/bin/orabase") {
    $ORABASE="/u01/app/oracle"; }
    else {
    $ORABASE=`$sid_home/bin/orabase|grep "\/"`;
    chomp($ORABASE);
    }
###print  "$ENV{'ORACLE_HOME'} $ORABASE";
$DB_NAME=substr($sid,0,length($sid)-1);
#10g
$ORALOG_DIR="$ORABASE/admin/$sid/bdump";
if (! -d "$ORALOG_DIR") {
#11g single admin/
$ORALOG_DIR="$ORABASE/admin/diag/rdbms/$sid/$sid/trace";}
if (! -d "$ORALOG_DIR") {
#11g RAC admin/
$ORALOG_DIR="$ORABASE/admin/diag/rdbms/$DB_NAME/$sid/trace";}
       #If ASM instance 11g standard, admin/
       if (! -d "$ORALOG_DIR") {
          $ORALOG_DIR="$ORABASE/admin/diag/asm/+asm/$sid/trace";
       }
       if (! -d "$ORALOG_DIR") {
          $ORALOG_DIR="$ORABASE/diag/asm/+asm/$sid/trace";
       }

       if (! -d "$ORALOG_DIR") {
# 11g single standard
$ORALOG_DIR="$ORABASE/diag/rdbms/$sid/$sid/trace";
       }
       if (! -d "$ORALOG_DIR") {
# 11g RAC standard
$ORALOG_DIR="$ORABASE/diag/rdbms/$DB_NAME/$sid/trace";
       }

print "!!!$ORABASE$sid.$ORALOG_DIR\n";
       $ALERTLOG="$ORALOG_DIR/alert_"."$sid.log";
       open (AL,$ALERTLOG);
       # LOcation of file that keeps track of number of lines of alert log
       $TRACK_KEEPER="$ORALOG_DIR/check_alert_$sid.tk";
       open (TK,$TRACK_KEEPER);
       @tracklines=<TK>;
       close (TK);
       $lastcnt=pop (@tracklines);
       chomp ($lastcnt);
       # If alert log tracker doesn't exist - create it
       if (! -e $TRACK_KEEPER) {
         open (TK,">$TRACK_KEEPER");
         close (TK);
       }
       # If alert log tracker is empty - set line counter to 0
       if (! $lastcnt) {
          $lastcnt=0;
       }
       $cnt=0;
       # Read alert log and find newly added lines 
       while (<AL>) {
         $line=$_;
         $cnt++;
         if ($cnt > $lastcnt) {
           push (@newalerts,$line);
         }
       }
       close (AL);
       # If alert log was cleaned up, the counter will start from 0, recheck alert log next time
       if ($cnt < $lastcnt) {
         $lastcnt=0;
         $cnt=0;
         open (TK, ">>$TRACK_KEEPER"); 
         print TK "$cnt\n";   
         close (TK);
       }
       if ($cnt > $lastcnt) { 
         open (TK, ">$TRACK_KEEPER");
         print TK "$cnt\n";
         close (TK);
         # Go through new lines and check for ORA- errors
     $cnt=0;
         foreach $newline (@newalerts) {
        $cnt++;
            chomp ($newline);
            if ($newline=~/ORA-/) {
               if (($newline!~/signalled during/) & ($newline!~/caused by/) & ($newline!~/ORA-3136/) & ($newline!~/ORA-01013/) & ($newline!~/ORA-00060/) & ($newline!~/temp segment/) & ($newline!~/memcpy()+252/) & ($newline!~/12235/) & ($newline!~/ORA-00313/) & ($newline!~/ORA-00314/) & ($newline!~/ORA-03113/) & ($newline!~/ORA-02063/) & ($newline!~/ORA-00321/) & ($newline!~/ORA-27037/) & ($newline!~/ORA-00312/) & ($newline!~/ORA-07445/)& ($newline!~/ORA-28/)) {
 #                 ($error,$errordesc)=split(/:/,$newline);

        if (! $cnt_keep) {
        $cnt_keep=$cnt + $lastcnt;
        }
 #print $cnt_keep;
        if($newline=~/^(ORA-[0-9]+):/) {
    $error=$1;
    }
    if ($newline=~/\((ORA-[0-9]+)\)/) {
    $error=$1;
    }
                  chomp ($error);
                  push (@orarray, $error);
               }
            }
         }
       } 
### print "$TRACK_KEEPER";
       # Format the page message in case of ORA- errors 
       if (@orarray) {
          $errorcnt=$#orarray+1; 
          $oraerror=@orarray[0];
          ($ora,$errnum)=split(/-/,$oraerror);
          $ENV{'ORACLE_HOME'}      = $sid_home;   
          $oramessages=`$sid_home/bin/oerr ora $errnum`;
#   print $errnum."111".$oramessages;
          $position=index($oramessages, "\/\/");
          $oramessage=substr($oramessages,0,$position);
          chomp ($oramessage);
          @oramesarray=split(/,/,$oramessage);
          shift(@oramesarray);
          shift(@oramesarray);
          $oraline=join ",", @oramesarray;
          $oraline=~s/"//g;
          ($sec, $min, $hour, $d, $m, $y, $dw, $f)=localtime(time);
          $year=1900+$y;
          $month=1+$m;
          $date="$hour:$min $month\/$d\/$year";
          $mailmessage="$date $oraerror $sid   $oraline at $cnt_keep\n\n";

      shift(@orarray);
      $oraerror=join("\n",@orarray);
      $mailmessage=$mailmessage.$oraerror;

          open (FL,">/tmp/oramess.$$");
      print FL "Subject:ORA- errors in alert_$sid.log on ${host}\n";
      print FL "from:${host}\@xxx.com\n";
      print FL "To:$pagerlines\n";
          print FL "$mailmessage\n";
          close (FL);
          #$mailcommand=`sendmail -f "${host}\@xxx.com" -t $pagerlines < /tmp/oramess.$$`;
$mailcommand=`sendmail -f "${host}\@xxx.com" $pagerlines1 < /tmp/oramess.$$`;
#$mailcommand=`mailx -s "ORA- errors in alert_$sid.log on $host" -r "${host}\@xxx.com  $pagerlines < /tmp/oramess.$$`;   
       #   unlink "/tmp/oramess.$$";
       }
     }
  }