Proracle’s Weblog

Remove err from alert log ?

step 1 : Create procedure
=========================
create or replace procedure ssas_externalalertlog_test(in_instance in varchar2) as
path_bdump varchar2(4000);
name_alert varchar2(100) := ‘alert_’ || in_instance || ‘.log’;
beginselect
value into path_bdump
from
sys.v_$parameter
where
name = ‘background_dump_dest’;

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 ‘ ||
‘ (MSG_LINE (1:1000) CHAR(1000)) ‘ ||
‘ ) ‘ ||
‘ location (”’ || name_alert || ”’) )’ ||
‘ reject limit unlimited ‘ ||
‘ noparallel nomonitoring ‘;
end;
/

step 2 : execute the procedure
==============================
execute ssas_externalalertlog_test(‘pcshpay’)

step 3 : issue this sql statement to isolate the errors to be removed
=====================================================================
create table a as
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
order by thedate
/

step 4 : now spool this to a new clean alert log
================================================
set pages 0 heading off feedback off lines 3000 trimspool on trim on tab off
col lineno noprint

select * from (
select rownum as lineno, msg_line from ssas_alertlog
) x where lineno not in (select lineno from a)
order by 1
/

step 5 : drop the objects
=========================
drop table ssas_alertlog;
drop procedure ssas_externalalertlog_test;
drop directory ssas_bdump;
drop table a;

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: