Oracle alert.log to Table

The following script creates two tables: read_alert andread_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 thealert.log file: select alert_date, substr(alert_text,1, 69) from alert_log;