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].

Posted

in

by

Tags:

Comments

6 responses to “Script:利用外部表实现SQL查询Oracle告警日志Alert.log”

  1. Charlie 木匠 (@mujiang) Avatar
    Charlie 木匠 (@mujiang)

    昨天看Tom的新书(一年前的旧书了), 93~96页, 有类似解决方案.

  2. fsm Avatar
    fsm

    这个太牛了,居然可以不用shell就能做。

  3. casper0511 Avatar
    casper0511

    第二段sql报错:
    ORA-01846: not a valid day of the week

    修改字符集后正常。alter session set nls_date_language = ‘american’;

    1. Maclean Liu Avatar

      good! 已经修复了该问题。

  4. chengwill Avatar
    chengwill

    Hello

    That’s a perfect implementation for analysis ORACLE DB& RAC alert logs mothod.

    I have used it on our RAC environment ,it’s working fine!

    Thank you provide it!

  5. Youhai Avatar
    Youhai

    SQL> select “LINENO”, “THEDATE”, “ORA_ERROR”, “MSG_LINE”
    2 from (select *
    3 from (select lineno,
    4 msg_line,
    5 thedate,
    6 max(case
    7 when (ora_error like ‘ORA-%’ or
    8 ora_error like ‘PLS-%’) then
    9 rtrim(substr(ora_error, 1, instr(ora_error, ‘ ‘) – 1),
    10 ‘:’)
    11 else
    12 null
    13 end) over(partition by thedate) ora_error
    14 from (select lineno,
    15 msg_line,
    16 max(thedate) over(order by lineno) thedate,
    17 lead(msg_line) over(order by lineno) ora_error
    18 from (select rownum lineno,
    19 substr(msg_line, 1, 132) msg_line,
    20 case
    21 when msg_line like
    22 ‘___ ___ __ __:__:__ ____’ then
    23 to_date(msg_line,
    24 ‘Dy Mon DD hh24:mi:ss yyyy’)
    25 else
    26 null
    27 end thedate
    28 from ALERT_LOG_VIEW))))
    29 where ora_error is not null
    30 and thedate >= (trunc(sysdate) – 3)
    31 order by thedate
    32 /
    to_date(msg_line,
    *
    第 23 行出现错误:
    ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
    ORA-01846: 周中的日无效

Leave a Reply

Your email address will not be published. Required fields are marked *