태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Alert Log 파일 분석 자동화하기

오라클 2009.08.19 15:52
오라클이 제공하는 데이터 중 트러블슈팅에 있어서 가장 중요한 것이 바로 Alert 로그 파일입니다. 다음과 같은 에러들이 Alert 로그에 저장되며, 이 정보들을 통해 많은 것들을 알아낼 수 있죠.
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] []
운영 환경의 Alert 파일은 매우 큰 경우가 많기 때문에, 우리의 눈으로 분석하기에는 많은 한계가 있습니다. 그래서 다음과 같은 자동화된 Script를 이용하면 좀 더 손쉽게 원하는 정보를 추출할 수 있습니다.
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
Oracle의 성능 문제를 트러블슈팅할 때는 이런 간단한 자동화된 테크닉을 잘 이용할 필요가 있습니다. 생활이 더 편리해집니다. :)
저작자 표시
신고

'오라클' 카테고리의 다른 글

Preliminary Connection과 Direct Access  (3) 2009.08.25
CLOB Concatentaion 줄이기  (2) 2009.08.21
Alert Log 파일 분석 자동화하기  (10) 2009.08.19
ORA-4031 에러 만들기  (9) 2009.08.11
O3 책의 중요한 오류  (0) 2009.08.10
tags :
Trackback 0 : Comments 10
  1. 에너자이죠 2009.08.19 16:40 신고 Modify/Delete Reply

    잘 봤습니다. ^^

  2. 열심남 2009.08.20 14:12 신고 Modify/Delete Reply

    좋은 정보를 보고 함 따라해보려고 하는데...내공이 부족해서 break가..

    아래 function들은 어떤 function인지요?

    get_trace_file
    regexp_like

  3. 욱짜 2009.08.20 14:20 신고 Modify/Delete Reply

    get_trace_file은 여기에 정의되어 있습니다.

    http://sites.google.com/site/ukja/sql-scripts-1/c/get_trace_file

    regexp_like는 Oracle 10g부터 지원되는 Regular Expression 함수입니다. Text 파일을 Parsing할 때 정말 유용하죠. 9i에서 사용하시려면 좀 더 복잡하게 If 문을 사용해야 합니다.

  4. 열심남 2009.08.20 16:52 신고 Modify/Delete Reply

    앗 감사합니다. 책을 아직 다 못봤더니... ^^; 10g면...음...9i에는 좀더 고민을 해야겠군요..

  5. James 2009.08.20 17:36 신고 Modify/Delete Reply

    좀 다른 질문인데요, 위의 source를 보여주기 위해서 사용하는 text box는 어떻게 사용할 수 있나요?
    저도 tistory를 가지고 있는데 사용 방법을 몰라서요

  6. 박두레 2009.09.20 15:42 신고 Modify/Delete Reply

    좋은 글 잘 읽었습니다. 테스트 해봤는데 코드에 약간의 건의사항이 있어서 올려봅니다. alert_analyze 코드 50번째 라인에 else b_include := false; 를 넣어주지 않을 경우end date를 실행 변수로 넣어줘도 v_end_dt값이 먹지 않아 v_start_dt부터 sysdate까지 ORA-에러가 전부 나오게 되더군요. else 이하 문을 코드에 추가하는 것이 어떨까 합니다.

  7. mongmong 2010.04.27 16:19 신고 Modify/Delete Reply

    아래와 같이 에러가 발생합니다.
    alert_analyze 스크립트를 어떻게 수정하면 좋을까요? (11g)


    SQL> @alert_analyze alert_ZABBIX.log 2010/04/15 % ORA-
    Enter value for back_dump: abc
    old 1: create or replace directory back_dump_dir as '&back_dump'
    new 1: create or replace directory back_dump_dir as 'abc'

    Directory created.


    Table dropped.


    Table created.


    0 rows deleted.

    old 9: select decode('&__START_DT', '%', sysdate - 100000,
    new 9: select decode('2010/04/15', '%', sysdate - 100000,
    old 10: to_date('&__START_DT', 'yyyy/mm/dd')) into v_start_dt from dual;
    new 10: to_date('2010/04/15', 'yyyy/mm/dd')) into v_start_dt from dual;
    old 12: select decode('&__END_DT', '%', sysdate + 1,
    new 12: select decode('%', '%', sysdate + 1,
    old 13: to_date('&__END_DT', 'yyyy/mm/dd')) into v_end_dt from dual;
    new 13: to_date('%', 'yyyy/mm/dd')) into v_end_dt from dual;
    old 16: from table(get_trace_file3('BACK_DUMP_DIR', '&__ALERT_LOG'))) loop
    new 16: from table(get_trace_file3('BACK_DUMP_DIR', 'alert_ZABBIX.log'))) loop
    old 28: if r.txt like '%&__PATTERN%' then
    new 28: if r.txt like '%ORA-%' then
    from table(get_trace_file3('BACK_DUMP_DIR', 'alert_ZABBIX.log'))) loop
    *
    ERROR at line 16:
    ORA-06550: line 16, column 15:
    PL/SQL: ORA-00904: "GET_TRACE_FILE3": invalid identifier
    ORA-06550: line 15, column 12:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 18, column 18:
    PLS-00364: loop index variable 'R' use is invalid
    ORA-06550: line 18, column 3:
    PL/SQL: Statement ignored
    ORA-06550: line 28, column 7:
    PLS-00364: loop index variable 'R' use is invalid
    ORA-06550: line 28, column 4:
    PL/SQL: Statement ignored

    • 욱짜 2010.04.28 15:28 신고 Modify/Delete

      http://sites.google.com/site/ukja/sql-scripts-1/c/get_trace_file

      이 포스트에서 사용한 Script는 샘플이니 실제 운영 시스템에 사용할 때는 본인 스스로 검증이 필요합니다~

Write a comment

티스토리 툴바