alert file 에서 error 개수 확인 방법

출처 : [http://ukja.tistory.com/240 욱짜님 블로그]

         평소 자주 찾아보는 욱짜님 블로그에 있는 설명 그대로를 테스트 해본 결과 입니다.

1. bdump의 경로를 알아보고 지정.

SQL> col value new_value back_dump

SQL> select value from v$parameter where name = 'background_dump_dest';

VALUE

--------------------------------------------------------------------------------

/oracle/oradata/PROD/admin/bdump

SQL> create or replace directory back_dump_dir as '&back_dump';

old   1: create or replace directory back_dump_dir as '&back_dump'

new   1: create or replace directory back_dump_dir as '/oracle/oradata/PROD/admin/bdump'

Directory created.

SQL>

2. get_trace_file3생성. -> 불러올 파일을 읽는 함수 같군요.

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

SQL> create or replace type varchar2_array as table of varchar2(4000);

  2  /

Type created.

SQL> create or replace function get_trace_file3(dir_name in varchar2, file_name in varchar2)

  2  return varchar2_array

  3  pipelined

  4  as

  5    v_handle  utl_file.file_type;

  6    v_line      varchar2(2000);

  7  begin

  8  v_handle := utl_file.fopen(dir_name, file_name, 'R', 32767);

  9   loop    

 10     begin 

 11       utl_file.get_line(v_handle, v_line); 

 12     exception when no_data_found then      

 13       exit;        

 14     end;           

 15   pipe row(v_line);

 16       end loop;    

 17                    

 19      utl_file.fclose(v_handle);

 18           

 20    return;

 21  end;     

 22  /

Function created.

SQL>

3. global temporary table 생성 -> commit 또는 섹션종료시에 데이터가 삭제되는 전역임시테이블생성

SQL> create global temporary table t_alert_analyze

  2  (

  3     reg_date date,

  4     error_code varchar2(10),

  5     message varchar2(4000)

  6  );

Table created.

SQL>

4. alert_analyze.sql script 실행

SQL>@alert_analyze.sql (or @alert_analyze.sql alert_PROD.log % % %)

* alert_analyze.sql 의 파일 내용.

define __ALERT_LOG = "&1"  -> alert 파일 명(ex. alert_PROD.log)

define __START_DT = "&2"  -> 검색 시작 날짜(2009/07/01 or %(default)) : sysdate - 100000

define __END_DT = "&3"    -> 검색 끝 날짜(2009/07/01 or %(default)) : sysdate + 1

define __PATTERN = "&4"  -> alert 파일에 대한 특정 패턴 (default 설정 '%')

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

where error_code like '%ORA%'

group by to_char(reg_date,'yyyy/mm/dd'), error_code

order by 1 desc, 3 desc

;

나의 의견 : 

설명이 잘 나와있었음에도 불구하고 내공이 부족하여, 많은 시행착오로 드디어 성공하였습니다.

위 쿼리문들을 보고 공부를 해두어야 할 것이 몇 가지가 생겨나서 적어봅니다.

1. 전역 임시 테이블에 관하여...

   (기본적인 사항은 알고 있었지만, 좀 더 세밀하게 파악을 해보도록 하겠습니다.)

2. 중첩테이블에 대해서...

  (이런 것이 있다는 것만 알았지 명령어가 무엇인지, 어떤 기능을 가지고 있는지, 등등 

   현재로써는 아는 것이 하나도 없습니다.)

3. regexp_like에 대해서...

  (음.. 이건 오라클에서 정규화를 쓸수있는 함수라고는 알고 있었고, 예전부터 이에 대해 공부를

   해야겠다고 마음만 갖고 있었는데 이 함수가 이곳에 또 나와있어서 하루 빨리 공부를 

   해야하지 않을까라는 생각이 들었습니다.)

추후 위 사항들에 대해서 글을 남기도록 하겠습니다.