オラクルのアラートファイルの分析の自動化

アラートファイル。オラクルに存在する情報の中で一番重要はものたちを提供しています。特に、次のようなエラー情報はとても有効です。

ORA-04031: unable to allocate ORA-04031: unable to allocate 540 bytes of shared memory ("shared pool","DBMS_RLMGR_DR","sga heap(1,1)","library cache")
ORA-06508: PL/SQL: could not find program unit being called: "EXFSYS.DBMS_RLMGR_DR"
...
Errors in file c:\oracle\admin\ukja10\udump\ukja10_ora_11376.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kslgetl+95] [PC:0x469AEB] [ADDR:0x12C] [UNABLE_TO_READ] []

でも、運営環境のアラートファイルはあんまり大きいから、目だけで分析するのは不可能な場合が時々あります。私はこのような場合に、次のような自動化されたスクリプトを使用しています。

ed alert_analyze.sql

/* create background dump directory
col value new_value back_dump
@para background_dump_dest
create or replace directory back_dump_dir as '&back_dump';

drop table t_alert_analyze;

create global temporary table t_alert_analyze(
 reg_date   date,
 error_code  varchar2(10),
 message    varchar2(4000)
);

*/

define __ALERT_LOG = "&1"
define __START_DT = "&2"
define __END_DT = "&3"
define __PATTERN = "&4"

set serveroutput on

delete from t_alert_analyze;

declare
 v_date    date;
 v_start_dt  date;
 v_end_dt   date;
 v_err_code  varchar2(10);
 v_message   varchar2(4000);
 b_include   boolean := false;
begin
 select decode('&__START_DT', '%', sysdate - 100000, 
    to_date('&__START_DT', 'yyyy/mm/dd')) into v_start_dt from dual;
    
 select decode('&__END_DT', '%', sysdate + 1, 
    to_date('&__END_DT', 'yyyy/mm/dd')) into v_end_dt from dual;
    
 for r in (select column_value as txt 
   from table(get_trace_file3('BACK_DUMP_DIR', '&__ALERT_LOG'))) loop
  --dbms_output.put_line(r.txt);
  if regexp_like(r.txt, '[0-9][0-9]:[0-9][0-9]:[0-9][0-9] [0-9][0-9][0-9][0-9]') then
   v_date := to_date(r.txt, 'Dy Mon dd hh24:mi:ss yyyy');
   if b_include = false then
    if v_date between v_start_dt and v_end_dt then
     b_include := true;
    end if;
   end if;
  end if;
  
  if b_include then
   if r.txt like '%&__PATTERN%' then
    if r.txt like 'ORA-%' then -- error code exists
     v_err_code := substr(r.txt, 1, 9);
     v_message := substr(r.txt, 12);
    else
     v_err_code := '-';
     v_message := r.txt;
    end if;
    
    insert into t_alert_analyze(reg_date, error_code, message) 
     values(v_date, v_err_code, v_message);
   end if;
  end if;  
 
 end loop;
end;
/


select to_char(reg_date,'yyyy/mm/dd') as "when" , error_code, count(*)
from t_alert_analyze
group by to_char(reg_date,'yyyy/mm/dd'), error_code
order by 1 desc, 3 desc
;

このalert_analyze.sqlスクリプトを利用すれば、次のように日別、エラーコード別にどんなエラーが発生しているのかを手軽にわかることができます。

UKJA@ukja116> @alert_analyze alert_back.log % % %

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/08/18 -                   2
2009/08/17 -                   7
2009/08/16 -                   5
2009/08/15 -                   5
2009/08/13 -                   6
2009/08/12 -                  32
2009/08/12 ORA-07445           6
2009/08/11 -                 206
2009/08/11 ORA-00470           8
2009/08/11 ORA-07445           1
2009/08/11 ORA-19815           1

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/08/10 -                  14
2009/08/09 -                   6
2009/08/08 -                 331
2009/08/08 ORA-19815           2
2009/08/08 ORA-04031           1
2009/08/08 ORA-00604           1
2009/08/07 -                  97
2009/08/07 ORA-04031           9
2009/08/07 ORA-12012           5
2009/08/07 ORA-06512           1
2009/08/07 ORA-06508           1

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/08/06 -                   7
2009/08/05 -                   8
2009/08/05 ORA-1597            1
2009/08/04 -                 711
2009/08/04 ORA-01555           8
2009/08/04 ORA-1652:           2
2009/08/04 ORA-19815           2
2009/08/04 ORA-1543            2
2009/08/04 ORA-30013           2
2009/08/04 ORA-27056           1
2009/08/04 ORA-01265           1

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/08/04 ORA-30036           1
2009/08/02 -                  75
2009/08/01 -                   5
2009/07/31 -                  19
2009/07/30 -                   7
2009/07/29 -                   5
2009/07/28 -                   3
2009/07/27 -                 416
2009/07/27 ORA-27302          33
2009/07/27 ORA-27300          33
2009/07/27 ORA-27301          25

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/07/27 ORA-04030          16
2009/07/27 ORA-06512          12
2009/07/27 ORA-12012           6
2009/07/27 ORA-00604           4
2009/07/27 ORA-19815           1
2009/07/26 -                   5
2009/07/25 -                 252
2009/07/25 ORA-04030           2
2009/07/25 ORA-22303           1
2009/07/25 ORA-27302           1
2009/07/25 ORA-19815           1

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/07/25 ORA-27300           1
2009/07/24 -                 158
2009/07/24 ORA-19815           1
2009/07/23 -                  99
2009/07/23 ORA-04030           9
2009/07/23 ORA-00603           2
2009/07/23 ORA-00604           2
2009/07/22 -                 200
2009/07/22 ORA-959 s           1
2009/07/22 ORA-19815           1
2009/07/22 ORA-3249            1

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/07/21 -                  74
2009/07/20 -                   5
2009/07/19 -                   5
2009/07/18 -                   9
2009/07/17 -                  35

71 rows selected.

UKJA@ukja116> @alert_analyze alert_back.log % % 4031

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/08/08 ORA-04031           1
2009/08/07 ORA-04031           9

UKJA@ukja116> @alert_analyze alert_back.log 2009/08/08 % ORA-

when       ERROR_CODE   COUNT(*)
---------- ---------- ----------
2009/08/12 ORA-07445           6
2009/08/11 ORA-00470           8
2009/08/11 ORA-19815           1
2009/08/11 ORA-07445           1
2009/08/08 ORA-19815           2
2009/08/08 ORA-00604           1
2009/08/08 ORA-04031           1

オラクル性能問題をトラブルシューティングする時にはこのような自動化された技法をよく使用する必要があります。