Good idea !
Except, I'm sure about adding the index on the column. This is not supported
with external tables (as it's impossible for Oracle to create and maintain).
Regards
Richard
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:ad0sgt$ak3$1_at_lust.ihug.co.nz...
> For what it's worth: in 9i it's possible to attach external text files as
> pseudo-tables ('create table blah.... organization external'). Therefore,
> just as a thought, you create an external table comprising a single,
> un-differentiated, column that references each database's alert log. You
> slap a text index on the column. Voilą! You can now search for ORA-600's
and
> any other nasties using standard select statements. Create a database link
> or two, and you can monitor other databases' alert logs from the one
> location.
>
> Advantages of this solution: it works whether you run Unix or something
> else. And it requires no shell scripting skills, just the nouse to type in
a
> complex create table statement.
>
> Regards
> HJR
>
>
> "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 Tue May 28 2002 - 18:43:17 CDT
