Read the alert log with SQL

Read the alert log with SQL

 

(1) alert 파일을 재료로 external table 생성하기

 

[oracle@ora10gr2 ~]$ sqlplus system/oracle

 

SQL> show parameter instance_name

 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl

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

 

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/bdump

 

SQL> -- 디렉토리 객체 생성

SQL> create or replace directory b_dump_dir as '/u01/app/oracle/admin/orcl/bdump';

 

SQL> -- external table 생성

SQL> create table t_alert_log (text_line varchar2(500)) organization external
         (type oracle_loader default directory b_dump_dir location ('alert_orcl.log'));

                                                                                                                                                                                            

SQL> -- alert 파일 내용을 질의로 확인

SQL> select * from t_alert_log where rownum <= 20;
 

 

(2) 사용자 정의 함수 및 쿼리를 사용해서 질의에 편리한 View 생성

 

SQL>

 

create or replace function alert_log_date( text in varchar2 )
  return date
is
  invaliddate  exception;
  pragma exception_init(invaliddate, -1846);
begin
  return to_date(text,'dy mon dd hh24:mi:ss yyyy','nls_date_language=american');
exception
  when invaliddate then return null;
end;
/

 

SQL>

create or replace view alert_log
as
select row_num,
         last_value(low_row_num ignore nulls)
         over(order by row_num rows between unbounded preceding
         and current row) start_row,
         last_value(alert_date  ignore nulls)
         over(order by row_num rows between unbounded preceding
         and current row) alert_date,
         alert_text
from (select rownum row_num,
                 nvl2(alert_log_date(text_line),rownum,null) low_row_num,
                 alert_log_date(text_line) alert_date,
                 text_line alert_text
      from t_alert_log);

 

 

SQL> set pages 40
SQL> set lines 200
SQL> col alert_text format a100

SQL> select * from alert_log;

 

(3) 활용 예제

 

SQL> -- 최근 한시간 동안 추가된 내용 확인

SQL> select * from alert_log
         where alert_date > sysdate - 1/24;

 

SQL> -- 지난 한달동안 추가된 내용 가운데 "ORA-"라는 글자를 포함하는 라인 확인

SQL> select * from alert_log
          where start_row in (select start_row from alert_log
                                          where regexp_like(alert_text,'ORA-'))
          and alert_date > trunc(sysdate,'mon');

출처 : http://blog.naver.com/gseducation/20097676952