crontab for log monitor

[gatetec]

// AIX 5.2

I have a log file ( /u02/oracle/admin/prod/bdump/prod1/alert_prod1.log ).

The log has the following pattern.
Each segment of the log ALWAYS starts with the time stamp with the format of i.e. Tue Oct 23 23:20:20 2007

If a segment has the word 'Error' or 'error' and/or 'ORA-' in it, I want to get the error/alert messages via e-mail.
So, what I want in e-mail is the specific segment i.e. from the the below example.

Tue Oct 23 23:22:05 2007
Errors in file /u02/oracle/admin/prod/udump/prod1/prod1_ora_1360080.trc:
ORA-01555: snapshot too old: rollback segment number 7 with name "RB05" too small

The log is very big and lengthy, so I like to run the crontab every 10 minutes.
Thus, the alert e-mail should contain segments with the word 'Error' or 'error' and/or 'ORA-' within 10 minutes from 'now'.

thx much

********** log example ***********

bla ....
Tue Oct 23 23:20:20 2007
Thread 1 advanced to log sequence 40125
Current log# 1 seq# 40125 mem# 0: /dev/rprod_0256_900
Current log# 1 seq# 40125 mem# 1: /dev/rprod_0256_901
Tue Oct 23 23:20:20 2007
ARC1: Evaluating archive log 4 thread 1 sequence 40124
ARC1: Beginning to archive log 4 thread 1 sequence 40124
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/prod/arch/pr
od1/redo1_40124.arc'
ARC1: Completed archiving log 4 thread 1 sequence 40124
Tue Oct 23 23:22:05 2007
Errors in file /u02/oracle/admin/prod/udump/prod1/prod1_ora_1360080.trc:
ORA-01555: snapshot too old: rollback segment number 7 with name "RB05" too small
Tue Oct 23 23:22:42 2007
Thread 1 advanced to log sequence 40126
Current log# 2 seq# 40126 mem# 0: /dev/rprod_0256_902
Current log# 2 seq# 40126 mem# 1: /dev/rprod_0256_903

 

[bdw238]

Hello,

What I would do is setup a script as UNIX Daemon that loops for ever and searchs for the timestamp pattern. Something like this psudeocode:

Open alert file
Goto EOF mark of alert file
Reset EOF marker
while (1) {
Read Alert File
if NOT (End of Alert file)
if line == Timestamp pattern
while (Read Next line until EOF) {
if line == "error/ORA,etc"
Do Something
ifend
}
ifend

endif
reset EOF marker
}
close Alert file

Brian

Note,

By no means this code is perfect.

 

[p5wizard]

a trace file is generated also IMHO:

Tue Oct 23 23:22:05 2007
Errors in file /u02/oracle/admin/prod/udump/prod1/prod1_ora_1360080.trc:
ORA-01555: snapshot too old: rollback segment number 7 with name "RB05" too small

So why don't you look for recent files (younger than 10 minutes if cron job every 10 mins) in that dir?

find /u02/oracle/admin/prod/udump/prod1 -mmin -10 -type f -name '*.trc'

and base your actions on the contents of the thus found files?


HTH,

p5wizard

 

[asch337]

I wrote a perl script a few months ago to do almost exactly what you are describing. I run it from crontab every 10 minutes. Here's the code for the perl script

<---begin script---->
#!/usr/bin/perl

#==============================================================================
#
# Program Name: ora_alert
# Date: 06/11/2007#
# Description: Scan the alert log for all databases in the array
# @Oracle_SID. If there are entries after the date in the
# SID.time file, then mail the entry to a specified user
#
# Options:
# -e user@host : E-Mail address to send output to
# -h : Show help screen
# -i : File containing regex. If line matches a
# regex, ignore it. Default is
# $ORACLE_BASE/admin/alert.ignore
# -s ORACLE_SID : Check alert log for ORACLE_SID. Default is
# all SID's in /etc/oratab
# -t DIRECTORY : File containing the time the alert log was
# last scanned. Default is
# $ORACLE_BASE/admin.alert.time
#
#===============================================================================

use Getopt::Std;
%opt=();
getopts("e:hi:s:t:",\%opt);

sub DisplayHelp{
print "\n";
print "Usage: Script to check alert log for latest entries\n";
print "Syntax: check_alert [-e user@host] [-h] [-i file] [-s (SID|ALL)] [-t file]\n";
print " -e : Email the output to user\@host\n";
print " -h : Print this screen and exit\n";
print " -i : File containing regexs. Lines matching the regexs are ignored.\n";
print " Default is /ora001/app/oracle/admin/alert.ignore\n";
print " -s : Check alert log of either just the SID specified or all SIDs in /etc/oratab.\n";
print " Default is ALL\n";
print " -t : File containing the times the alert log was last scanned.\n";
print " Default is /ora001/app/oracle/admin/alert.time\n";
print "\n";
exit;
}

sub AfterDate{
my($Left, $Right) = @_;

($Day, $Month, $Date, $Hour, $Min, $Sec, $Year) = split(/[ :]+/, $Left);
$DateStr = ($Date < 10) ? "0" . $Date : $Date;
my $LeftNbr = $Year . $Mon_to_Nbr{$Month} . $DateStr . $Hour . $Min . $Sec;

($Day, $Month, $Date, $Hour, $Min, $Sec, $Year) = split(/[ :]+/, $Right);
$DateStr = ($Date < 10) ? "0" . $Date : $Date;
my $RightNbr = $Year . $Mon_to_Nbr{$Month} . $DateStr . $Hour . $Min . $Sec;

return ($LeftNbr >= $RightNbr) ? 0 : 1;
}

sub GetLastScanDate{
my ($sid) = @_;

open(TIME, $opt{t}) || die "Couldn't open $opt{t}\n";
my @time = grep(/^$sid:/, <TIME>);
$time[0] =~ s/^.*?://;
chomp $time[0];
close(TIME);

return $time[0];
}

sub GetRegEx{
my ($sid) = @_;

open(IGNORE, $opt{i}) || die "Couldn't open $opt{i}\n";
@regex = <IGNORE>;
close(IGNORE);

return @regex;
}

sub GetAlertLogLoc{
my ($oracle_home, $sid) = @_;

open(INITORA, "$oracle_home/dbs/init$sid.ora") || die "Couldn't open $oracle_home/dbs/init$sid.ora\n";
@bg_dump_dest = grep(/^ *background_dump_dest/i, <INITORA>);
$bg_dump_dest[0] =~ s/^.*= *//;
$bg_dump_dest[0] =~ s/ .*$//;

chomp $bg_dump_dest[0];
return "$bg_dump_dest[0]/alert_$sid.log";
}

sub UpdateTime {
my ($file, $sid, $newdate) = @_;

open(TIME, $file) || die "Couldn't open $file\n";
@time = <TIME>;
close(TIME);

open(TIME, ">$file") || die "Couldn't open $file\n";
foreach (@time) {
if (/^$sid:/) { print TIME "$sid:$newdate\n"; }
else { print TIME "$_" if not (/^$sid:/); }
}
close(TIME);
}
#=============================================
# Setup default values
#=============================================
$opt{i} = "/ora001/app/oracle/admin/alert.ignore" if not defined $opt{i};
$opt{s} = "ALL" if not defined $opt{s};
$opt{t} = "/ora001/app/oracle/admin/alert.time" if not defined $opt{t};
$tmpfile = "/tmp/checkalert.$$";
%Mon_to_Nbr = ("Jan", "01",
"Feb", "02",
"Mar", "03",
"Apr", "04",
"May", "05",
"Jun", "06",
"Jul", "07",
"Aug", "08",
"Sep", "09",
"Oct", "10",
"Nov", "11",
"Dec", "12");

#=============================================
# Check if -h is set
#=============================================
DisplayHelp if defined $opt{h};

#=============================================
# Loop through Oracle SIDs.
#=============================================
open(ORATAB, "/etc/oratab") || die "Couldn't open /etc/oratab.";
@ORATAB = <ORATAB>;
close(ORATAB);

@RegEx = GetRegEx;

$Error = 1;
foreach (($opt{s} eq "ALL") ? grep(/^[A-Za-z0-9]/, @ORATAB) : grep(/^$opt{s}:/, @ORATAB)) {
($SID, $OracleHome, $Junk) = split(/:/);

$Last_Scanned = GetLastScanDate($SID);
$New_Date = $Last_Scanned;
$Alert_Log = GetAlertLogLoc($OracleHome, $SID);

open(TMPFILE, ">$tmpfile") || die "Couldn't open $tmpfile\n";
open(ALERT_LOG, $Alert_Log) || die "Couldn't open $Alert_Log\n";
while (<ALERT_LOG>) {
chomp;
if (/^[A-z][a-z]{2} [A-Z][a-z]{2} [ 0-9][0-9] [0-9:]{8} [0-9]{4}$/) {
$DateCheck = AfterDate($Last_Scanned, $_) if (not $DateCheck);

if ($DateCheck) {
$State = "NEW";
$New_Date = $_;
}
}
else {
if ($State eq "NEW") {
$State = "KEEP";

for my $regex (@RegEx) {
chomp $regex;
$State = "IGNORE" if (/($regex)/);
}

print TMPFILE "\n=========== $New_Date ===========\n" if ($State eq "KEEP");
}
print TMPFILE "$_\n" if ($State eq "KEEP");
}
}
close(ALERT_LOG);
close(TMPFILE);

if (defined $opt{e}) {
system("mailx -s'" . $SID . ": Error in alert log' $opt{e} < $tmpfile") if (-s $tmpfile);
}
else {
system("cat $tmpfile") if (-s $tmpfile);
}

system("rm $tmpfile");
UpdateTime($opt{t}, $SID, $New_Date);
}
<---end script--->

The script makes use of 2 additional files: alert.time and alert.ignore. The alert.time is used to limit output to just the entries added since the last scan. The alert.ignore is used to filter out new entries you don't want to get emailed on.