Dev's Weblog

Reading time: 2 - 4 minutes

You can use following Perl script to monitor alert.log

=======================================

#!/usr/bin/perl -w
#
#A /usr/local/bin/alertlog.pl [log days] [mail address]
#
use strict;
use Time::Local;
local (*ALERT, *MAIL);
my %errs = ();
my $errors = 0;
my $errtxt;
my $ast;
my $astx = 0;
my @exclude_list = (1109, 1142, 1145, 1511);
my $stanzatime;
my @stanza = ();
my %months = (Jan => 0, Feb => 1,A Mar => 2,
Apr => 3, May => 4,A Jun => 5,
Jul => 6, Aug => 7,A Sep => 8,
Oct => 9, Nov => 10, Dec => 11);
my ($mon,$day,$time,$year);
my ($hr,$min,$sec);

die "No ORACLE_HOME available" unless $ENV{ORACLE_HOME};
die "No ORACLE_SID available"A unless $ENV{ORACLE_SID};
my $input_dir = "$ENV{ORACLE_BASE}/admin/$ENV{ORACLE_SID}/bdump";
my $input_file = "alert_$ENV{ORACLE_SID}.log";

chdir $input_dir or die "Unable to chdir to ". $input_dir .": $!";
die "Alert log not readable"A unless -r $input_file;

# set the number of log days to scan.A Default=1.
my $logdays = (shift @ARGV || 1);

# get the mail address if there is one.
my $hostname = "from ". (`hostname 2>/dev/null` ||"who");
if (my $mailaddr = shift @ARGV) {
open MAIL, "|mail -s \"Database Alert $hostname\" $mailaddr";
}
else {
open MAIL, ">&STDOUT";
}

# Set ltime to be today less the logdays parm, at this time of day.
# Used to capture only current errors
my $ltime = time() - - (60*60*24*$logdays);
print MAIL "\nLogging from " . localtime($ltime) ."\n\n";

foreach my $file (reverse sort <$input_file*>) {
open (ALERT, "$file") || die "Can't open $file";
while (<ALERT>) {
# Extract the most recent date-time stamp from the logfile.
if (/^(?:Mon|Tue|Wed|Thu|Fri|Sat|Sun)/) {
print_and_flush_stanza($stanzatime, $ltime, @stanza)
if $stanzatime;
@stanza = $_;
($mon,$day,$time,$year) = (split / +/, $_) [1,2,3,4];
($hr,$min,$sec) = (split /:/, $time);
$stanzatime = timelocal($sec,$min,$hr,$day,$months{$mon},$year);
}
else {
push @stanza, $_;
}
}
close (ALERT);
}

stat("$ENV{ORACLE_HOME}/bin/oerr"); # load the filestat info to _

print MAIL "\nOracle ErrorAA Count Standard Message Text\n";
print MAILAA "------------AA ----- ---------------------\n";
foreach my $key (sort keys %errs) {
if (-x _ ) {
($errtxt) = ((`$ENV{ORACLE_HOME}/bin/oerr ora $key`)[0] ||".");
$errtxt =~ s/(^\w+, \w+,) "(.+)"/$2/;
chomp($errtxt);
}
$ast = "";
foreach my $excluded (@exclude_list) {
if ($key == $excluded) {
$ast = "*";
$astx++;
}
}
printf MAIL "%-12sA %5dA %s\n", "ORA-$key$ast", $errs{$key}, $errtxt;
$errors += $errs{$key};
}
printA MAIL "------------AA -----\n";
printf MAIL "Grand TotalAA %5d\n\n", $errors;
printA MAIL "* denotes errors on the exclude list.\n\n" if $astx > 0;
close MAIL;

sub print_and_flush_stanza {
my ($dt, $lt, @r) = @_;
my $ora_errno = 0;
my $ora_err_flag = 0;

return
if ($dt < $lt);A # only look at errors from the last 24 hours

foreach my $line (@r) {
if (($ora_errno) = $line =~ m/^ORA-(\d+)/) {
$ora_errno = sprintf "%05d", $ora_errno; # count errors, but only
$errs{$ora_errno}++;AAAAAAAAAAAAAAAAAAAA # display those not excluded.
$ora_err_flag++;AAAAAAAAAAAAAAAAAAAAAAAA # flag it now.
foreach my $excluded (@exclude_list) {
if ($ora_errno == $excluded) {
$ora_err_flag--;AAAAAAAAAAAAAAAAAAAA # unflag it. >1 means some
last;AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # error is included.
}
}
}
}
# now print the stanza if there was an error there.
if ($ora_err_flag) {
foreach my $line (@r) {
print MAIL $line;
}
print MAIL "\n";
}
}

============================================