Monitor ora XXX error messages in alert files.

The script code of alertlog.pl is as follows:

!/ usr/bin/perl

Name: alertlog.pl

sub countSet{

open(OUT, “>$idFile”);

Printf (out “% d”, $currcount); # write the current line number to the file

close(OUT);

}

————————–

Define Variables

————————–

$obase=”/u01/app/oracle”;

$SH_ TOP=”/home/oracle/general/sh”;

$SID=”$ARGV[0]”;

$idFile=”$SH_TOP/log/alert_${SID}.id”; # Only the start line number and end line number of the current file have been checked

If (! – f $idfile) {# if the file does not exist

Open (out, “& gt; $idfile”); # then create and open the file in writing mode

Printf (out “% s \ n”, 1); # write 1

close(OUT);

}

$alertFile=”${obase}/admin/${SID}/bdump/alert_${SID}.log”;

$errFile=”$SH _TOP/err${SID}.txt”;

$cmd=”head -1 $idFile”;

$preCount=qx/$cmd/;

chomp($preCount); # Remove line breaks at the end of string variable values

$cmd=”wc -l $alertFile | awk ‘{print $1}'”;

$currCount=qx/$cmd/;

printf (“$preCount:$currCount\n”);

–Modified tail +preCount with tail -minusCount for Linux Compliance

$minusCount=$currCount-$preCount;

$cmd=”tail -$minusCount $alertFile | grep “ORA-” | /bin/egrep -v -f $SH_TOP/errLogAlert.dat | sort -u> $errFile”;

printf (“$cmd \n”) ;

system($cmd);

Ssize=-s $errFile;

if ($size >1){

printf (“Sending Alert for $SID \n”);

$cmd=”$SH_TOP/alert_notification_db.ksh -s “Alert Log Error” -d $SID -h ‘hostname’ -f $errFile”;

printf(“$cmd \n”);

system($cmd);

}

& countSet; # Execute counterset to write $currcount to the file $idfile

——————————

In order to run the above monitoring script, we need to start with sorace_ Bdump directory under base/admin/$Oracle Sid directory, such as/u01/APP/Oracle/admin/+ ASM/bdump

In addition, we also need to create an alert in the bdump directory that points to the tracking directory_+ ASM [i]. Log symbolic link. Examples are as follows:

exaddb01:/u01/app/oracle/admin/+ASM/bdump

+ASM1 -oracle:1s -1tr

total 0

Irwxrwxrwx 1 oracle dba 57 Nov 13 2011 alert_+ ASM.1og-→

/001/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ ASM1.1og

In the above example, the symbolic link created is alert ＋ asm.log (no ASM instance name is used), which points to alert_+ Asm1.log file

This is intentional, because by doing so, we can configure the same cron task on each database server, regardless of whether the server is in independent mode or a node in RAC

The cron task looks like this:

–Monitor Alert Logs

0,10,20,30,40,50 * * * * /home/oracle/general/sh/alertlog.p1 +ASM >/ tmp/alertlog_+ ASM.log 2>& amp; one

SH top in the above script alertlog.pl defines where to execute the script. Under the SH top directory, the line number of the last line of the alarm log is recorded in the log subdirectory

This log subdirectory should be created as part of the alarm log file monitoring configuration:

exaddb01:/home/oracle/general/sh/log

TOOLSDEV1 – oracle:cat alert_+ ASM.id

191126

If, for some reason, you want to mine from the beginning of the alarm log, you can simply add alert_+ Modify the content of asm.id file to 0

The alert notify ati on db.ksh line in the above Perl script can also be replaced by mail or mailx

In our example, each detected ora message in the alarm log file will be uploaded to an Oracle table for further analysis in the future.

Like database alarm logs, ASM alarm logs need to be cleaned and archived:

exaddb01:/home/oracle/general/sh

+ASM1 – oracle: cat rotate_ asm.ksh

export CONF=/tmp/asml.conf

cat <& lt;!!& gt;$ CONF

/u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ ASM1.log

weekly

copytruncate

rotate 4

compress

}

!!

1ogrotate -s $SH/1og_ rotate_ asml -f $CONF-s SSH/log_ rotate_ listener_ scanl -f $CONF

The – s option in the above command enables the logrotate utility (log management tool under Linux) to specify other status files

In order for Oracle or grid users to execute the logrotate command, you must specify the – s option. Because the default status file is/var/lib/logrotate.status

Generally speaking, only the root user has permission to write to the file- The f option forces the rotation of log files, even if the logrotate tool considers this rotation unnecessary

The last option specifies the profile for log rotation. In this configuration file, we can set the following contents:

·Location of log files to be rotated and rotation options

·Rotation frequency

·Number of files to keep

·Whether to use compression

After we execute the log rotation script for the first time, we can see that the old alarm log has been compressed, and the size of the newly generated log file is 0 bytes:

exaddb01:/u01/app/oracle/diag/asm/+asm/+ASM1/trace

+ASM1 – oracle: 1s -1 alert_+ ASM*

-rw-r—–1 oracle dba 0 Dec 11 14:55 alert +ASM1.log

-rw-r—–1 oracle dba 643647 Dec 11 14:55 alert_+ ASM1.log.1.gz

Attachment:

####################################################################################

Every 15 seconds

* * * * * sleep 15;/ home/oracle/general/sh/alertlog.p1 +ASM >/ tmp/alertlog_+ ASM.log 2>& amp; one

####################################################################################