| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Freebie: read/grep alert log from sqlplus
"Steve Perry" <sperry_at_sprynet.com> wrote in message
news:adjure$r6k$1_at_slb6.atl.mindspring.net...
> thanks Howard,
>
> I considered that, but my databases are 8.1.7 and below. Plus, I run a lot
> scripts from my NT client to instances on NT and Unix. As long as they are
> v8.0+, I don't have to telnet to check the logs, copy this script to
> numerous systems or setup shell scripts. I know it's lazy, but it works
for
> me :)
>
> when I get time to finish the package, I'll post it.
>
> Steve
>
> P.S. I'm curious how much faster the external tables are than this method.
> If the file is more than a couple of meg, this can be slow. I'll have to
> test that one out.
Don't forget that you can parallelize the read of the data file (and the degree of parallelism is independent of the number of datafiles, unlike with standard SQL Loader). But yes, I would be interested in hearing your experiences.
Regards
HJR
> "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');
> > >
> > >
> > >
> >
tput.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,
 |
 |