This Perl script is for parsing Oracle database alert log on Windows server. It will send email notification to DBA if error (ORA-) message is found.

#!/usr/bin/perl -w
# alert_log_parser.pl - Oracle alert log monitor
#
# Synopsis: Monitors the instance alertlog for ORA- errors (or anything else
# you would like to look for). When these errors are encountered,
# email is sent to the DBA.
#
# usage: alert_log_parser.pl [-sleep <seconds>]
#
# Paul Shaw, Ph.D. InnoVita Inc., All Right Reserved (c) 2002-2007
#
=head1 NAME
alert_log_parser.pl - Oracle alert log parser & monitor
=head1 SYNOPSIS
alert_log_parser.pl [-sleep <seconds>]
=head1 DESCRIPTION
Monitors the Oracle instance alert log for problems (i.e. ORA-00600, block
corruption errors, etc).
=head1 AUTHOR
Paul Shaw, Ph.D. InnoVita Inc., All Right Reserved (c) 2002-2007
=cut
use strict;
#use Carp;
#use DBI;
#use File::Basename;
#use FileHandle;
use Getopt::Long;
#use Net::SMTP;
use POSIX qw/uname/;
#use Mail::Webmail::Gmail;
use MIME::Lite;
use vars qw/$dbh $scr $sleep_time/;
if ($#ARGV != 5)
{
print $#ARGV ."\n";
print "usage: perl alert_log_parser.pl alert_log_path alert_log_name java working_dir oracle_DB_name recipients.\n";
exit (0);
}
my $alert_log_path = $ARGV[0];
my $alert_log_name = $ARGV[1];
my $java= $ARGV[2];
my $working_dir= $ARGV[3];
my $oracle_DB_name = $ARGV[4];
my $recipients2=$ARGV[5];

my $mail_server='gmail';
my $account ='oracle';
my $password = 'xxx';
#my $recipients= ['Paul.Shaw@xxx.com', 'Christine.Davis@xxx.com', ];
my $recipients= 'Paul.Shaw@xxx.com';

#our $TZ="EST5EDT";

#my $recipients2="Paul.Shaw;Christine.Davis";
my $from= 'paulshawxxx@gmail.com';
my @recipient_array=();
my $db_username='pauls';
my $db_password='pauls';
my $message='';
my $text_message='';
my $db_status_subject='';
# specify the following if not using gmail
my $smt_phost='md01ex02';
my $smtp_username='Paul.Shaw';
my $smtp_password='';
my $exchange_server="VA03EX01";
my ($mailServer1) = 'us01ex01';
my ($mailServer2) = 'us01ex02';
my ($mailFrom) = 'Costpoint_DBA';
my ($mailTo) = 'paul.shaw@xxx.com,christine.davis@xxx.com';
my ($realName) = "DBA";
my ($subject_for_trace) = "DB Trace File";
my ($body_trace) = "";

#$scr = basename($0, '');
GetOptions('sleep:i', \$sleep_time);
$sleep_time = 300 if (!defined $sleep_time);
my $parsing_interval = $sleep_time; # 5 minutes
#my $computer_name="";
# $computer_name = (uname())[1];
#$dbh = DBI->connect('dbi:Oracle:', '$db_username', '$db_password',
# {RaiseError => 0, PrintError => 0, AutoCommit => 0}) ||
# warn "$scr: connect error on $computer_name [$DBI::errstr]";
#my $alert_log = get_alert();
#$dbh->disconnect;
my $alert_log=$alert_log_path."\\". $alert_log_name;
my $output=$working_dir."\\Body\\error.txt";
my @error_msg=();
my $newtime = 0;
my $has_error=0;
my $last_log_timestamp=0;
my $last_log_timestamp_str='';
my $cur_log_timestamp=0;
my $cur_log_timestamp_str='';
my $error_started_time='';
my $timestamp='';
my $last_timestamp="";
my $old_error_text="";
my $cur_error_text="";
my $elapsed=0;
my $now=0;
my $counter =0;
my $error_found = 0;
my $db_shutdown_status=0;
my $db_start_status=0;
my $db_status=0;
my $mesg1 = "Starting ORACLE instance";
my $mesg2 ="OPEN";
my $mesg3 = "Shutting down instance:";
my $mesg4 ="DISMOUNT";
my $mesg5 ="Completed";
my $mesg6 ="Shutting down instance";
my $mesg7 ="immediate";

open (IN, "$output") or warn "can not open file: $output $!";
while (<IN>)
{
#Mon Jan 08 03:35:20 2007
chomp;
if (m/^(\S+\s+)(\S+)(\s+\d+) # word (like Wed) followed by Mon and Day
\s+(\d+):(\d+):(\d+)\s+ # the time
(\d+) # the year
/x)
{
$last_timestamp = $_;
$last_log_timestamp = ($4 *60*60 + $5 * 60 + $6) if (defined $4 && defined $5 && defined $6);
$last_log_timestamp_str= $2. $3. " ". $7. " ". $last_log_timestamp ;
}
$old_error_text .=$_ ."\n";
}
close (IN);
#print $last_timestamp ."\n";
#print $last_log_timestamp ."\n";
#print $last_log_timestamp_str ."\n";
sub trim($)
{
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}
# Left trim function to remove leading whitespace
sub ltrim($)
{
my $string = shift;
$string =~ s/^\s+//;
return $string;
}
# Right trim function to remove trailing whitespace
sub rtrim($)
{
my $string = shift;
$string =~ s/\s+$//;
return $string;
}
my $date= "";
my $temp ="";
my $trc_file="";
my @trc_files=();
my $today = localtime;
$today =~ m/^(\S+\s+\S+\s+)(\d+) # word (like Wed) followed by Mon and Day
\s+\d+:\d+:\d+\s+ # the time - ignored
(\d+) # the year
/x;
$date =trim($2) if (defined $2) ;
$temp= trim($1) if (defined $1) ;
if (length ($date) ==1)
{
$date= "0".$date;
}
my $today_date_tag = $temp ." ". $date. ' ' .$3 if (defined $3);

open (IN, "$alert_log") or die "can not open file $alert_log. $!";
while (<IN>)
{
# get a date tag for today
chop;
my $log_date_tag;
$today =~ m/^\S+\s+\S+\s+\d+ # word (like Wed) followed by Mon and Day

Pages: 1 2 3