Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Freebie: read/grep alert log from sqlplus

Re: Freebie: read/grep alert log from sqlplus

From: Alex Ivascu <alex_at_ivascu.com>
Date: Mon, 27 May 2002 16:55:01 GMT
Message-ID: <FxtI8.6863$861.2032912@typhoon1.we.ipsvc.net>


Thanks for sharing, Steve!

Alex Ivascu

"Steve Perry" <sperry_at_sprynet.com> wrote in message news:actk54$87r$1_at_slb5.atl.mindspring.net...
> 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');
>
>
>

_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 Mon May 27 2002 - 11:55:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US