Proracle’s Weblog

October 23, 2008

How to read oracle alert log file using external table

Filed under: useful — proracle @ 12:43 pm
Tags:
step 1 : Create procedure
=========================
create or replace procedure ssas_externalalertlog(in_instance in varchar2) as
path_bdump varchar2(4000);
name_alert varchar2(100) := 'alert_' || in_instance || '.log';
begin
select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';

--select
--'alert_' || value || '.log' into name_alert
--from
--sys.v_$parameter
--where
--name = 'db_name';

execute immediate 'create or replace directory ssas_BDUMP as ''' ||
path_bdump || '''';
execute immediate
'create table ssas_ALERTLOG ' ||
' (MSG_line varchar2(4000) ) ' ||
' organization external ' ||
' (type oracle_loader ' ||
' default directory ssas_BDUMP ' ||
' access parameters ( ' ||
' records delimited by newline ' ||
' nobadfile ' ||
' nologfile ' ||
' nodiscardfile ' ||
' skip 0 ' ||
' READSIZE 1048576 ' ||
' FIELDS LDRTRIM ' ||
' REJECT ROWS WITH ALL NULL FIELDS ' ||
' (MSG_LINE (1:1000) CHAR(1000)) ' ||
' ) ' ||
' location (''' || name_alert || ''') )' ||
' reject limit unlimited ' ||
' noparallel nomonitoring ';
end;
/

step 2 : execute the procedure
==============================
execute ssas_externalalertlog('XXX')

step 3 : issue this sql statement
=================================
col lineno noprint
col ora_error noprint
col msg_line format a132
set pages 0 lines 300 trimspool on trim on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set sql_trace=false;
break on thedate
prompt
prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS
prompt =====================================
select "LINENO","THEDATE","ORA_ERROR","MSG_LINE"
from (
select *
from (
select lineno,
msg_line,
thedate,
max( case when (ora_error like 'ORA-%' or ora_error like 'PLS-%')
then rtrim(substr(ora_error,1,instr(ora_error,' ')-1),':')
else null
end ) over (partition by thedate) ora_error
from (
select lineno,
msg_line,
max(thedate) over (order by lineno) thedate,
lead(msg_line) over (order by lineno) ora_error
from (
select rownum lineno,
substr( msg_line, 1, 132 ) msg_line,
case when msg_line like '___ ___ __ __:__:__ ____'
then to_date( msg_line, 'Dy Mon DD hh24:mi:ss yyyy' )
else null
end thedate
from ssas_alertlog
)
)
)
)
where ora_error is not null
and thedate >= (trunc(sysdate) - 3)
order by thedate
/

step 4 : drop the external table
================================
drop table ssas_alertlog
/
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: