千日夢의 Tistory

> 시나리오 ::

SCOTT계정으로 들어간 넘이 테이블의 데이터를 잘못 수정하여 테이블을 망가트린 뒤 커밋 명령을 실행하다..

SCOTT계정이 명령어를 친 시점 = LSN 23

관리자가 문제점을 발견한 시점 = LSN28

테이블을 망가뜨린 시점과 사용한 명령어를 찾아내는 기능을 로그 마이너라고 한다..!!!!

1. UTL_FILE_DIR   딕셔너리 화일의 경로지정(딕셔너리 화일에 REDO 로그 화일의 정보를 기록한다.)

Oracle>mkdir DICT

SQL>alter system set UTL_FILE_DIR='/Oracle/DICT' scope=spfile;                    //인스턴스 재시작 필요..;;

SQL>show parameter utl;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /Oracle/admin/ORA9/DICT

그리고, 이러한 로그마이너를 활용하기 위해서는 다음과 같은 패키지가 필요하다..

SQL>desc DBMS_LOGMNR

SQL>desc DBMS_LOGMNR_D

2. 패키지가 설치되어 있지 않으면 패키지를 설치한다.

+ DBMS_LOGMNR_D

로그마이너 딕셔너리 파일을 생성하기 위한 프로시저를 제공한다.

몇개의 프로시져가 있으나 Public인것은 Build 뿐이므로 우리는 Build프로시저만 사용하면 된다.

이 프로시저는 현재 DB의 딕셔너리 테이블에 질의하여 여기서 나온 정보를 가지고 텍스트기반의 파일을 생성한다.

이 외부 딕셔너리 파일은 차후 로그마이너가 로그파일 분석하기 위한 용도로 사용된다.

이 프로시저는 내부적으로 UTL_FILE패키지를 사용하기 때문에 파라미터파일에 'UTL_FILE_DIR'파라미터를 설정해야 한다.

'set outputserver on'명령을 사용하면 Build프로시저의 진행상황을 모니터링 할 수 있다.

SQL>@?/rdbms/admin/dbmslmd.sql

Procedure created.

No errors.

Grant succeeded.

PL/SQL procedure successfully completed.

Package created.

+ DBMS_LOGMR

이 패키지는 3가지 프로시저를 제공한다.

- add_logfile(name varchar2, options number)    분석할 Redo Log의 추가/제거

- start_logmnr(start_scn number, end_scn number, start_time number, end_time number, dictfilename varchar2, options number)

   분석한 시간대나 SCN범위를 지정, 분석에 사용할 Data Dictionary Extract를 지정

- end_logmnr()  마이닝 세션 종료. Redo 스트림을 분석하고 Dictionary Extract를 읽기 위해 사용한 메모리를 해제한다.

SQL>@?/rdbms/admin/dbmslm.sql

Procedure created.

No errors.

Grant succeeded.

PL/SQL procedure successfully completed.

Package created.

3. 모든 패키지가 정상적으로 설치 되어 있다면.. 아래의 명령을 통해 Dictionary 파일을 생성한다.

SQL>exec DBMS_LOGMNR_D.BUILD('logmnr_dic.ora','/Oracle/admin/ORA9/DICT');

PL/SQL procedure successfully completed.

4. 분석할 로그파일을 로그파일목록에 등록한다.

- 로그마이너 세션에서 'DBMS_LOGMNR.ADD_LOGFILE'프로시저를 사용하여 로그파일 목록에서 등록 또는 제거 할 수 있다.

- 주의 할 점은.. 처음 등록시에는 'DBMS_LOGMNR.NEW'를 사용하여 등록하고, 그 이후에는 'DBMS_LOGMNR.ADDFILE'으로

   등록해야 한다는 점이다. 제거는 'DBMS_LOGMNR.REMOVEFILE'을 사용한다.

SQL>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------
         3         ONLINE  /Oracle/oradata/ORA9/redo03.log

         2         ONLINE  /Oracle/oradata/ORA9/redo02.log

         1         ONLINE  /Oracle/oradata/ORA9/redo01.log

SQL> exec dbms_logmnr.add_logfile('/Oracle/oradata/ORA9/redo01.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/Oracle/oradata/ORA9/redo02.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/Oracle/oradata/ORA9/redo03.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

등록된 로그파일은 다음 명령으로 알아 볼 수 있다.

SQL> select log_id, filename from v$logmnr_logs;

    LOG_ID FILENAME
---------- ---------------------------------------------
        30 /Oracle/oradata/ORA9/redo02.log
        31 /Oracle/oradata/ORA9/redo03.log
        32 /Oracle/oradata/ORA9/redo01.log

5. 로그파일을 등록한뒤에는, DBMS_LOGMNR.START_LOGMNR 프로시저를 이용 로그파일을 읽어들인다.

- 분석할 Redo 레코드를 필터링 하기위해 시작/종료 SCN과 Time파라메터를 지정할 수 도 있다.

SQL>desc v$logmnr_parameters;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 START_DATE                                         DATE
 END_DATE                                           DATE
 START_SCN                                          NUMBER
 END_SCN                                            NUMBER
 INFO                                               VARCHAR2(32)
 STATUS                                             NUMBER

SQL> select * from v$logmnr_parameters;

START_DAT END_DATE   START_SCN    END_SCN INFO                     STATUS
--------- --------- ---------- ---------- -------------------- ----------
01-JAN-88 01-JAN-11          0          0                               0

SQL> exec dbms_logmnr.start_logmnr(DictFileName=>'/Oracle/admin/ORA9/DICT/logmnr_dict.ora');

PL/SQL procedure successfully completed.

6. 읽어들인 로그파일을 분석한다.

- V$LOGMNR_CONTENTS 뷰를 통해 내용을 질의한다.

- 주의할 점은 'DBMS_LOGMNR.START_LOGMNR'프로시저가 시작된 상태여야 가능하다.

SQL> select scn, timestamp, sql_undo, sql_redo from v$logmnr_contents where rownum<5;

       SCN TIMESTAMP SQL_UNDO             SQL_REDO
---------- --------- -------------------- ------------------------------
   5602124 15-JAN-07                      commit;
   5602134 15-JAN-07                      set transaction read write;
         0 01-JAN-88
   5602134 15-JAN-07 Unsupported          Unsupported

7. 로그마이너 세션을 종료한다.

SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.