| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Freebie: read/grep alert log from sqlplus
I wrote something similar but in Perl. It runs as a crontab-job and scans
all alert-log files on a machine every hour and mails any error found.
I wrote this tool when we had problem with the intelligent agent and had
to check alert-logs on different machines with different OS (unix, openvms).
The scanning of the alert-logs is sequential but it is still fast (on hour
machines
at least). The script expects the alert-log files in OFA complaint directory:
/u01/app/oracle/admin/<INSTANCENAME>/bdump/alert_<INSTANCENAME>.log
To scheduled the script I use :
0 0-23 * * * (cd Alert; ./alert_log.pl) > /dev/null
#! By I. Saez: Looks for ora- errors in alert-log
#! Oracle instances. Range is between current date and hour and previous hour
(and date)
#! date format Oracle in alert-log : Thu Aug 2 16:34:27 MET DST 2001
#!
$mailprog = "/usr/lib/sendmail -t -oi"; # email-program
$mailto = "i.a.saez.scheihing\@tue.nl"; # change to your email
$subject = "Errors found in";
$from = "i.a.saez.scheihing\@tue.nl";
$cc = "";
$date = `date`;
$adebug = 0; # set to 1 for debug info
chop($date);
$date = substr($date,0,index($date,":")-2); # example: Tue May 28
$hour = `date +'%H'`; # example: 07
chop($hour);
$now = $date.$hour; # Tue May 28 07
# hour = 0
if ($hour == 0 ) {
# 12 o'clock in the evening; go one day back
$hour = 23;
# previous day
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time-86400);
$y+=1900;
$mon = (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)[$mon];
$dag = (Sun,Mon,Tue,Wed,Thu,Fri,Sat)[$wday];
$date = ( $mday < 10 ) ? $dag.' '.$mon.' '.$mday : $dag.' '.$mon.'
'.$mday;
}
else {
$hour = $hour - 1
}
# previous hour
if ( $hour < 10 ) {
$prev = $date.'0'.$hour;
} else {
$prev = $date.$hour;
}
$nodenaam = `hostname`;
#
open ORA,"/var/opt/oracle/oratab" || die "Can't open /var/opt/oracle/oratab";
while (<ORA>) {
# read all instance names
chop;
# print $_;
if ( /#/ || /^$/ ) {
# skip comment and empty lines
} else {
# get instance name
( $inst,$home,$start) = split(/:/);
# Alert log-file name plus path; works only if OFA is used!!!
$alert_log = "/u01/app/oracle/admin/$inst/bdump/alert_$inst.log";
#
if ($adebug) {
print qq|
Alert log : $alert_log Hour : $hour now : $now Prev : $prev
&CHECK_LOG( $alert_log,$inst);
#
} # if (index($_,"#") ) {
} # while (<ORA>) {
close ORA;
exit;
sub CHECK_LOG ()
{
# scan alert_log between now ($now) and one hour back ($prev)
print qq|
Scanning alert file $alert_log of instance $inst
between "$prev" en "$now"
|;
$i = 0; $mailbool = 0; $bool = 0;
chop;
if ( /$prev/ || $bool ) {
# here scan for ora- errors
if ( /ORA-/ || /Errors in/ ) {
$mailbool = 1;
$mailarr{$j++} = $_;
} # if ( /ORA-/ ) {
$bool = 1; # keep scanning
} #if ( /$prev/ || $bool ) {
if ( /$now/ ) {
$bool = 0; # no more scanning
$last;
$i++;
} # while (<IN> ) {
&Mail_errors() if $mailbool;
print qq|
$i lines scanned
|;
close (IN);
}
sub Mail_errors () {
print "in Mail_errors\n";
open ( MAIL,"|${mailprog} " ) || die "can't open $mailprog!";;
print MAIL "To: ${mailto}\r\n";
print MAIL "Cc: ${cc}\r\n";
print MAIL "From: ${from}\r\n";
print MAIL "Subject: $subject $alert_log!\r\n";
print MAIL "\r\n";
print MAIL "Between $prev and $now \r\n";
foreach $key ( sort keys %mailarr ) {
} # end sub Mail_errors
Steve Perry wrote:
> I thought this might be useful to somebody. I wrote it (with help from
> metalink and akadia) because I hated having to telnet to different servers
> to look at the alert log. save it off in directory, start sqlplus and type
> @alertlog. It will show you filtered errors in it. If you don't manage the
> alertlogs and archive them weekly or monthly, this can take a bit to run. On
> my systems, it takes a few seconds. I'm almost done converting it to a
> package with more features (i.e. date range, you don't have keep creating
> the directory). It does require v8.0+ and the utl_raw package. Other than
> that, it should work just fine.
>
> If you see areas for improvement, let me know.
>
> Steve
>
> rem clear screen
> set echo off
> set lines 150
> set arraysize 120
>
> --
> * ------------------------------------------------------------------------ *
> -- * @alertlog
> *
> -- * FILE: alertlog.sql
> *
> -- * SOURCE: "glued" together code from metalink, akadia and my own
> *
> -- * AUTHOR: Steve Perry and above mentioned caveats.
> *
> -- * EMAIL : sperry_at_sprynet.com
> *
> -- * DATE : 3/15/02
> *
> -- * INPUT : none
> *
> -- * OUTPUT: report
> *
> -- * ORACLE: 8.0.x and above.
> *
> -- * Restrictions: assumes the path is "hardcoded" and no env vars.
> *
> -- * DESCRIPTION: displays info about errors and things of interest in the
> *
> -- * database alert log.
> *
> -- *
> *
> -- * Maintenance
> *
> -- * SMP 3/15/02 Created, *
> -- * smp 3/18/02 modified for Unix support and alertlog
> default*
> -- * names: Unix : alert_<sid>.log *
> -- * Windows: <sid>ALRT.LOG *
> -- *
> *
> --
> * ------------------------------------------------------------------------ *
>
> set verify off
> set feedback off
>
> --Determine the OS (WIN or UNIX)
> col OS_PLATFORM new_value OS_PLATFORM noprint
> select decode(instr(upper(product), 'WINDOWS',1), 0 , 'UNIX', 'WINDOWS')
> OS_PLATFORM
> from PRODUCT_COMPONENT_VERSION
> WHERE upper(PRODUCT) LIKE 'TNS%'
> /
>
> col dirname new_value dirname noprint
> select decode('&OS_PLATFORM', 'WINDOWS', value || '\', value || '/') dirname
> from v$parameter
> where name = 'background_dump_dest'
> /
>
> -- debug stuff
> -- prompt dir = &&dirname
>
> create or replace directory "ALERTDIR" as '&&dirname';
>
> --allows leading whitespace to be printed by dbms_output
> SET SERVEROUTPUT ON FORMAT WRAPPED
>
> rem clear screen
>
> -- ****************************************************************
> -- BEGIN CODE PART
> -- ****************************************************************
>
> DECLARE
> -- Input Directory as specified in create directory
> l_dir CONSTANT VARCHAR2(30) := 'ALERTDIR';
>
> -- Input File which is read word by word
> l_fil VARCHAR2(30) ;
> l_fil_size number;
>
> -- Separator Character between words is a BLANK (ascii = 32)
> l_sep CONSTANT RAW(500) := UTL_RAW.CAST_TO_RAW(CHR(32));
> l_eol CONSTANT RAW(500) := UTL_RAW.CAST_TO_RAW(CHR(10));
>
> -- Character at the end of the file is NEWLINE (ascii = 10)
> l_sen CONSTANT RAW(500) := UTL_RAW.CAST_TO_RAW(CHR(10));
>
> -- Pointer to the BFILE
> l_loc BFILE;
> --l_mode_RO BINARY_INTEGER := file_readonly;
>
> -- Current position in the file (file begins at position 1)
> l_pos NUMBER := 1;
>
> -- Amount of characters have been read
> l_read_amt BINARY_INTEGER := 0;
>
> -- Read BufferS
> l_rawbuf raw(500);
> l_buf VARCHAR2(500);
>
> -- End of the current line which will be read
> l_end NUMBER;
>
> -- Return value
> l_ret BOOLEAN := FALSE;
>
> -- DB Name to resolve alert log
> l_ora_sid varchar2(30);
>
> -- work date var
> l_date date;
>
> --alert string
> l_alrt varchar2(10);
>
> --debug setting
> bDebug boolean := FALSE;
>
> --OS PLATFORM
> l_OS_PLATFORM varchar2(15) := 'WINDOWS';
>
> BEGIN
> -- set the max buffer size
> dbms_output.enable(1000000);
>
> --get database name
> select value into l_ora_sid from sys.v_$parameter where name =
> 'db_name';
>
> --Determine the OS (WIN or UNIX)
> select decode(instr(upper(product), 'WINDOWS',1), 0 , 'UNIX', 'WINDOWS')
> into l_OS_PLATFORM
> from sys.PRODUCT_COMPONENT_VERSION
> WHERE upper(PRODUCT) LIKE 'TNS%';
>
> --set the alert file name now
> if l_OS_PLATFORM = 'WINDOWS' then
> l_fil := l_ora_sid || 'ALRT.LOG';
> else
> l_fil := 'alert_' || l_ora_sid || '.log';
> end if;
>
> -- Mapping the physical file with the pointer to the BFILE
> l_loc := BFILENAME(l_dir,l_fil);
>
> dbms_output.put_line('File: ALERTLOG.SQL - Compliments of SQL*Buddy
> (Steve Perry)...' );
> dbms_output.put_line('Date: ' || to_char(sysdate, 'DY, MON DD HH24:MI:SS
> YYYY') );
> dbms_output.put_line('Processing alert.log for sid: ' || l_ora_sid );
> dbms_output.put_line('Opening File ' || l_fil || ' in Directory ' ||
> l_dir );
>
> -- Check if the file exists
> l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1;
> IF (l_ret) THEN
> -- Open the file in READ_ONLY mode
> -- DBMS_LOB.OPEN(l_loc, DBMS_LOB.LOB_READONLY);
> DBMS_LOB.FILEOPEN(l_loc, DBMS_LOB.file_readonly );
>
> l_fil_size := DBMS_LOB.GETLENGTH( l_loc );
> dbms_output.put_line('Alert log size is: ' ||
> ltrim(to_char(l_fil_size, '999,999,999,999,999')) || ' bytes.' || chr(10));
>
> --print heading
> dbms_output.put_line(chr(10) || 'Date
> Description');
>
> bms_output.put_line('------------------------- --------------------------
> --------------------------------------');
>
> /**************/
> LOOP
> /*************/
> -- find the end of the current line
> l_end := DBMS_LOB.INSTR(l_loc,l_eol,l_pos,1);
> -- Process end-of-file
> --only goes into here if: reached end of file l_end = 0
> IF (l_end = 0) THEN
> l_end := DBMS_LOB.INSTR(l_loc,l_eol,l_pos,1);
> l_read_amt := l_end - l_pos - 1;
> --debug stuff
> IF bDebug then
> dbms_output.put_line(' --------------- ' );
> dbms_output.put_line('l_fil_size: ' || to_char(l_fil_size,
> '999,999,999,999') );
> dbms_output.put_line('l_end: ' || to_char(l_end ,
> '999,999,999,999'));
> dbms_output.put_line('l_read_amt: ' || to_char(l_read_amt ,
> '999,999,999,999'));
> dbms_output.put_line('l_pos: ' || to_char(l_pos ,
> '999,999,999,999'));
> dbms_output.put_line('l_rawbuf: ' ||
> UTL_RAW.CAST_TO_VARCHAR2(l_rawbuf) );
> dbms_output.put_line(' --------------- ' );
> --debug stuff
> -- DBMS_LOB.READ(l_loc,l_read_amt,l_pos,l_rawbuf);
> -- l_buf := UTL_RAW.CAST_TO_VARCHAR2(l_rawbuf);
> -- dbms_output.put_line(l_buf);
> EXIT;
> END IF;
> END IF;
>
> --set chunk of data to read
> l_read_amt := l_end - l_pos;
> -- Read until end-of-file
> DBMS_LOB.READ(l_loc,l_read_amt,l_pos,l_rawbuf);
>
> --debug stuff
> -- if bDebug then
> -- dbms_output.put_line(' --------------- ' );
> -- dbms_output.put_line('l_end: ' || to_char(l_end ,
> '999,999,999,999'));
> -- dbms_output.put_line('l_read_amt: ' || to_char(l_read_amt ,
> '999,999,999,999'));
> -- dbms_output.put_line('l_pos: ' || to_char(l_pos ,
> '999,999,999,999'));
> -- dbms_output.put_line(' --------------- ' );
> -- --debug stuff
> -- end if;
>
> --convert the line to character data
> l_buf := UTL_RAW.CAST_TO_VARCHAR2(l_rawbuf);
> --if win os, strip off the chr(12), otherwise leave it alone.
> if l_OS_PLATFORM = 'WINDOWS' then
> l_buf := substr(l_buf, 1, length(l_buf) - 1);
> end if;
>
> --hack to find the date (line prior to error)
> if substr(l_buf, 4,1) = ' ' then
> begin
> -------select to_date('Tue Mar 12 03:24:18 2002', 'DY, MON
> DD HH24:MI:SS YYYY') from dual
> select to_date(l_buf, 'DY, MON DD HH24:MI:SS YYYY') into
> l_date from dual;
> exception
> when others then
> l_date := null;
> end;
> end if;
> IF instr(l_buf,'ORA-' , 1 ) > 0
> or instr(l_buf,'Starting up ORACLE' , 1 ) > 0
> or instr(l_buf,'Shutting down instance', 1 ) > 0
> or instr(l_buf,'Errors in' , 1 ) > 0
> or instr(l_buf,'alter database ' , 1 ) > 0
> or instr(l_buf,'Completed: alter data' , 1 ) > 0 then
> --hilight errors...
> if instr(l_buf, 'ORA-', 1) > 0 then
> l_buf := ' !! ' || ltrim(l_buf);
> l_alrt := chr(10);
> else
> l_buf := ' ' || ltrim(l_buf);
> l_alrt := '';
> end if;
> dbms_output.put_line(l_alrt || to_char(l_date, 'DY, MON DD
> HH24:MI:SS YYYY') || l_buf);
> -- dbms_output.put_line(' - ' || l_buf);
> END IF;
> --move pointer to next part of data
> l_pos := l_pos + l_read_amt + 1;
> IF l_pos > l_fil_size THEN
> exit;
> END IF;
> /***********/
> END LOOP;
> /***********/
> --close the file normally
> DBMS_LOB.FILECLOSE(l_loc);
> dbms_output.put_line( chr(10) || chr(10) );
> ELSE
> dbms_output.put_line(chr(10) || 'File ' || l_fil || ' in Directory '
> || l_dir || ' was NOT FOUND' || chr(10));
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line('Error: ' || SQLERRM);
> DBMS_LOB.FILECLOSE(l_loc);
> END;
> /
>
> drop directory "ALERTDIR"
> /
>
> set verify off
> set feedback on
> set echo on
Received on Tue May 28 2002 - 00:47:28 CDT
 |
 |