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

本文根据刘相兵大师的blog的脚本，并做了一些该进：

http://www.oracledatabase12g.com/archives/%E5%88%A9%E7%94%A8%E5%A4%96%E9%83%A8%E8%A1%A8%E5%AE%9E%E7%8E%B0sql%E6%9F%A5%E8%AF%A2oracle%E5%91%8A%E8%AD%A6%E6%97%A5%E5%BF%97alert-log.html

有同学问是否可以用SQL语句直接查询告警日志的内容，即创建一张包含Alert.log内容的表或视图

--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 noprintcol ora_error noprintcol msg_line format a132set pages 0 lines 300 trimspool on trim onalter 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 thedatepromptprompt ERROR IN ALERT LOG FILE - LAST 3 DAYSprompt =====================================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-%' or                                  ora_error like 'TNS-%'                                  )   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.trc10/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].

 

有些情况下，我们的alert_log 是按天来开始备份的，这样我们可以通过下面的脚本，制定某个报警日志文件，来查询这个报警文件备份中的错误信息：

--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 'drop table alert_log_view'||ins_name||' purge';  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 (''&alert_log'') )' ||                    ' reject limit unlimited ' ||                    ' noparallel nomonitoring ';end;/