Freebie: read/grep alert log from sqlplus

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