René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Script to read Oracle's alert log

The following script creates two tables: read_alert and read_alert_disk.
read_alert_disk is an external table and contains the content of the alert log.
read_alert will be empty after this script has been executed. It is used by the update_alert_log script, shown further below.
define alert_length="2000"

drop table alert_log;

create table alert_log (
  alert_date date,
  alert_text varchar2(&&alert_length)
)
storage (initial 512k next 512K pctincrease 0);

create index alert_log_idx on alert_log(alert_date)
storage (initial 512k next 512K pctincrease 0);

column db    new_value _DB    noprint;
column bdump new_value _bdump noprint;

select instance_name db from v$instance;

select value bdump from v$parameter 
 where name ='background_dump_dest';


drop   directory BDUMP;
create directory BDUMP as '&&_bdump';

drop table alert_log_disk;

create table alert_log_disk ( text varchar2(&&alert_length) )
organization external (
  type oracle_loader
  default directory BDUMP
      access parameters (
          records delimited by newline nologfile nobadfile
          fields terminated by "&" ltrim
      )
  location('alert_&&_DB..log')
)
reject limit unlimited;

update_alert_log.sql

Now, after the two tables are created, the alert_log table can be filled with the following script. It only loads those records that are greater than the last time it loaded. And it loads the date/time on every line for convienance. It also helps when the alertlogs get rotated. You still keep the history within an Oracle table. Finally, it also strips out all the «crap» that is really not needed to see if you are looking for errors.
update_alert_log.sql
set serveroutput on 

declare
  
  isdate         number := 0;
  start_updating number := 0;
  rows_inserted  number := 0;
  
  alert_date     date;
  max_date       date;
  
  alert_text     alert_log_disk.text%type;

begin
  
  /* find a starting date */
  select max(alert_date) into max_date from alert_log;
  
  if (max_date is null) then
    max_date := to_date('01-jan-1980', 'dd-mon-yyyy');
  end if;
  
  for r in (
    select substr(text,1,180) text from alert_log_disk
     where text not like '%offlining%' 
       and text not like 'ARC_:%' 
       and text not like '%LOG_ARCHIVE_DEST_1%'
       and text not like '%Thread 1 advanced to log sequence%'
       and text not like '%Current log#%seq#%mem#%'
       and text not like '%Undo Segment%lined%'
       and text not like '%alter tablespace%back%'
       and text not like '%Log actively being archived by another process%'
       and text not like '%alter database backup controlfile to trace%'
       and text not like '%Created Undo Segment%'
       and text not like '%started with pid%'
       and text not like '%ORA-12012%'
       and text not like '%ORA-06512%'
       and text not like '%ORA-000060:%'
       and text not like '%coalesce%'
       and text not like '%Beginning log switch checkpoint up to RBA%'
       and text not like '%Completed checkpoint up to RBA%'
       and text not like '%specifies an obsolete parameter%'
       and text not like '%BEGIN BACKUP%'
       and text not like '%END BACKUP%'
  )
  loop
  
    isdate     := 0;
    alert_text := null;
  
    select count(*) into isdate 
      from dual 
     where substr(r.text, 21) in ('2003','2004','2005','2006','2007')
       and r.text not like '%cycle_run_year%';
  
    if (isdate = 1) then  
  
      select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr') 
        into alert_date 
        from dual;
  
      if (alert_date > max_date) then
        start_updating := 1;
      end if;
  
    else
      alert_text := r.text;
    end if;
  
    if (alert_text is not null) and (start_updating = 1) then
     
      insert into alert_log values (alert_date, substr(alert_text, 1, 180));
      rows_inserted := rows_inserted + 1;
      commit;
  
    end if;
  
  end loop;
  
  sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'MM/DD/RR HH24:MI:SS'));
  sys.dbms_output.put_line('Rows Inserted: '||rows_inserted);
  
  commit;

end;
/
Let's execute the script:
SQL> @update_alert_log
Inserting after date 01/01/80 00:00:00
Rows Inserted: 17361

PL/SQL procedure successfully completed.
The alert_log table now contains the errors as recorded in the alert.log file:
select alert_date, substr(alert_text,1, 69) 
  from alert_log;
12-JUL-06 Error stack returned to user:
12-JUL-06 ORA-02049: timeout: distributed transaction waiting for lock
16-JUL-06 Errors in file /usr/local/opt/oracle/admin/P2BL36A/udump/p2bl36a_ora_
18-JUL-06 ORACLE Instance P2BL36A - Can not allocate log, archival required
18-JUL-06 Thread 1 cannot allocate new log, sequence 265396 
18-JUL-06 All online logs needed archiving
18-JUL-06 ORACLE Instance P2BL36A - Can not allocate log, archival required
18-JUL-06 Thread 1 cannot allocate new log, sequence 265397
18-JUL-06 All online logs needed archiving
18-JUL-06 ORACLE Instance P2BL36A - Can not allocate log, archival required
18-JUL-06 Thread 1 cannot allocate new log, sequence 265398
18-JUL-06 All online logs needed archiving
23-JUL-06 Errors in file /usr/local/opt/oracle/admin/P2BL36A/udump/p2bl36a_ora_
25-JUL-06 Errors in file /usr/local/opt/oracle/admin/P2BL36A/bdump/p2bl36a_s001
25-JUL-06 ORA-07445: exception encountered: core dump [kpnrls()+148] [SIGBUS] [
25-JUL-06 ORA-03113: end-of-file on communication channel
25-JUL-06 ORA-02063: preceding line from FRD01
25-JUL-06 found dead shared server 'S001', pid = (21, 1)
27-JUL-06 alter tablespace TBS01
27-JUL-06 add datafile '/ora106/ORACLE/P2BL36A/dbf/tbs01P2BL36A_32.dbf' size 20
27-JUL-06 ORA-1119 signalled during: alter tablespace TBS01
27-JUL-06 add datafile '/ora106/ORACL...
27-JUL-06 alter tablespace TBS01
27-JUL-06 add datafile '/ora106/ORACLE/P2BL36A/dbf/tbs01P2BL36A_32.dbf' size 20
27-JUL-06 Completed: alter tablespace TBS01
27-JUL-06 add datafile '/ora106/ORACL
28-JUL-06 ORA-01555 caused by SQL statement below (Query Duration=59968 sec, SC
28-JUL-06 SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "FC_REQUEST_HISTORY_

Thanks

The scripts above are provided by Shawn M Ellinger.

Links