alert.log mysql_Script:利用外部表实现SQL查询Oracle告警日志Alert.log

有同学问是否可以用SQL语句直接查询告警日志的内容，即创建一张包含Alert.log内容的表或视图。 实际上之前已经有人这样做了(http://t.cn/SwGvq9)，只需要运行一个存储过程即可达到目的， 这里我对原有的语句做了一些改良， 直接执行PL/SQL块即可无需创建存储过程了，而且现在支持RAC了。

--drop table alert_log_view;

--drop directory bdump;

declare

path_bdump varchar2(4000);

name_alert varchar2(4000);

ins_name varchar2(200);

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 = 'instance_name';

select value

into ins_name

from sys.v_$parameter

where name = 'instance_number';

if ins_name = '0' then

ins_name := '';

end if;

execute immediate 'create or replace directory bdump'||ins_name||' as ''' || path_bdump || '''';

execute immediate 'create table ALERT_LOG_VIEW' || ins_name ||

' (MSG_line varchar2(4000) ) ' ||

' organization external ' || ' (type oracle_loader ' ||

' default directory bdump' || ins_name ||

' access parameters ( ' ||

' records delimited by newline ' || ' nobadfile ' ||

' nologfile ' || ' nodiscardfile ' || ' skip 0 ' ||

' READSIZE 10485760 ' || ' FIELDS LDRTRIM ' ||

' REJECT ROWS WITH ALL NULL FIELDS ' ||

' (MSG_LINE (1:1000) CHAR(1000)) ' || ' ) ' ||

' location (''' || name_alert || ''') )' ||

' reject limit unlimited ' ||

' noparallel nomonitoring ';

end;

/

执行以上PL/SQL代码，会创建名为bdump$SID的目录 和ALERT_LOG_VIEW$SID的外部表(如RAC中的1号实例PROD1，则为ALERT_LOG_VIEW1,单实例single instance则为 ALERT_LOG_VIEW)， 需要时直接查询ALERT_LOG_VIEW即可，譬如要从告警信息中找出最近三天ORA-错误的记录:

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_language = 'american';

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 ALERT_LOG_VIEW))))

where ora_error is not null

and thedate >= (trunc(sysdate) - 3)

order by thedate

示例输出

10/11/2011 03:15:49 Thu Nov 10 03:15:49 2011

Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11105):

ORA-00700: soft internal error, arguments: [kgerev1], [600], [600], [700], [], [], [], [], [], [], [], []

Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11105/VPROD1_ora_5547_i11105.trc

Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11106):

ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /s01/orabase/diag/rdbms/vprod/VPROD1/incident/incdir_11106/VPROD1_ora_5547_i11106.trc

10/11/2011 03:15:52 Thu Nov 10 03:15:52 2011

Dumping diagnostic data in directory=[cdmp_20111110031552], requested by (instance=1, osid=5547), summary=[incident=11105].