Log Miner 활용하기

Log miner

 -> redo log file 의 내용을 sql 문장으로 변환해 사용자가 sql 문장으로 조회해서 원하는 내용을 확인하는 것

log miner 조회 순서

 -> 1. log miner 전용 Dictionary 생성

      2. log miner 로 분석할 로그 보냄 (redo log buffer, archive log) = 등록

      3. log miner 에서 분석

      4. sql 로 분석 결과 조회

supplemental logging 기능

 -> 데이터에 DML이 발생할 경우 redo log 에 추가적인 내용을 기록 ( 기본값 보다 자세한 내용 기록 )

 - 최소 기록 (disable)  : 기본 모드       

 - 자세히 기록 (enable)                     

supplemental logging 활성화 여부 조회

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

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

NO              <- 기본값 

활성화 시키기

SYS> alter database add supplemental log data ;

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

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

YES

비활성화 시키기

SYS> alter database drop supplemental log data ;

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

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

NO

특정 테이블만 supplemental log 적용

SYS> alter table scott.emp add supplemental log data (all) columns ;

Log Miner 실습하기 1. - drop table 정보 찾기

step 1. 현재 상태 확인

SYS> @log

GROUP#  MEMBER                                            MB        seq  STATUS   arc

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

     1       /data/temp3/redo01.log                           5          1 CURRENT  NO

     2       /data/temp3/redo02.log                           5          0 UNUSED   YES

     3       /data/temp3/redo03.log                           5          0 UNUSED   YES

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

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

YES                        <-- 활성화 시키고 실습 시작

step 2. 새로운 테이블 생성 후 데이터 입력하고 삭제

SQL> create table scott.test1 (no number);

Table created.

SQL> insert into scott.test1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table scott.test1 purge;

Table dropped.

SQL> @log

  6  ;

GROUP#  MEMBER                                            MB        seq  STATUS   arc

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

     1       /data/temp3/redo01.log                           5          1 CURRENT  NO     <-- 1번 그룹에서 진행 중

     2       /data/temp3/redo02.log                           5          0 UNUSED   YES

     3       /data/temp3/redo03.log                           5          0 UNUSED   YES

step 3. Log Miner 딕셔너리를 생성

 -> log miner 가 redo log file 을 분석해 그 안에 있는 테이블 이름이나 컬럼 이름들이 있는 부분을 딕셔너리를 확인해서 사용자가 볼 수 있도록 변환해줌

 ( 여기서 딕셔너리는 SQL Parse 단계에서 사용하는 딕셔너리가 아니라 Log Miner 가 사용하는 별도의 딕셔너리를 말함 )

딕셔너리 생성방법 3가지

1. Online Catalog 사용

2. Redo Log File 자체에 저장하는 방법

3. Flat file 에 저장하는 방법

※ 1,2 의 방법은 잘 사용 안함

1) DB 전체를 종료한 후 Parameter file 에 딕셔너리 파일의 위치를 아래와 같이 지정

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@localhost ~]$ vi /app/oracle/product/11g/dbs/inittestdb.ora

utl_file_dir=/app/oracle/logminer 

2) DB 시작

[oracle@localhost ~]$ exit

exit

SQL> startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             293604248 bytes

Database Buffers          121634816 bytes

Redo Buffers                6086656 bytes

Database mounted.

Database opened.

3) 아래와 같이 딕셔너리 생성

SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict.dat',-

> dictionary_location=>'/app/oracle/logminer');

PL/SQL procedure successfully completed.

4) Log Miner 에 분석할 로그 추가

SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo01.log',1);      

 -- 위 명령어 마지막 숫자의 의미는 1= 신규등록    2= 파일삭제   3= 추가등록

PL/SQL procedure successfully completed.

2,3 번 그룹을 추가 등록

SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo02.log',3);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo03.log',3);

BEGIN dbms_logmnr.add_logfile('/data/temp3/redo03.log',3); END;

*

ERROR at line 1:

ORA-01289: cannot add duplicate logfile /data/temp3/redo03.log

ORA-06512: at "SYS.DBMS_LOGMNR", line 68

ORA-06512: at line 1

3번 그룹을 추가할 때 위와 같은 에러가 나면 로그파일이 UNUSED 상태라서 그럼

alter system switch logfile 과 alter system checkpoint 를 실행시켜 Inactive 와 current 상태로 만들어주자

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> @log

  6  ;

GROUP# MEMBER                                          MB        seq STATUS   arc

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

     1 /data/temp3/redo01.log                           5          4 CURRENT  NO

     2 /data/temp3/redo02.log                           5          2 INACTIVE YES

     3 /data/temp3/redo03.log                           5          3 INACTIVE YES

위의 에러를 해결하고 redo log file 을 다시 등록해주고 log miner 에 등록된 log file을 확인

SQL> select db_name,filename from v$logmnr_logs;

DB_NAME         FILENAME

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

TESTDB          /data/temp3/redo01.log

TESTDB          /data/temp3/redo02.log

TESTDB          /data/temp3/redo03.log

5) Log Miner 를 시작해서 log 를 분석한 후 결과를 조회

SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/app/oracle/logminer/dict.dat',-

> options=>dbms_logmnr.ddl_dict_tracking+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SQL> col username for a10

SQL> col operation for a10

SQL> col sql_redo for a50

SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

Session altered.

Log Miner 실습하기 2 - Update table 시간 확인하기

step1. 현재 상태 조회하기

SQL> @log

  6  ;

GROUP# MEMBER                                          MB        seq STATUS   arc

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

     1 /data/temp3/redo01.log                           5         19 INACTIVE YES

     2 /data/temp3/redo02.log                           5         20 INACTIVE YES

     3 /data/temp3/redo03.log                           5         21 CURRENT  NO          <-- redo03 에서 작업

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

step2. 신규 테이블 생성 후 데이터 입력하고 update

SQL> create table scott.test2(no number,name varchar2(10));

Table created.

SQL> insert into scott.test2 values(1,'aaa');

1 row created.

SQL> insert into scott.test2 values(2,'bbb');

1 row created.

SQL> insert into scott.test2 values(3,'ccc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test2;

        NO NAME

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

         1 aaa

         2 bbb

         3 ccc

SQL> update scott.test2 set name='ddd';

3 rows updated.

SQL> select * from scott.test2;

        NO NAME

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

         1 ddd

         2 ddd

         3 ddd

SQL> commit;

Commit complete.

step3. Log Miner 딕셔너리를 생성

SQL> show parameter utl

NAME                               TYPE        VALUE

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

create_stored_outlines        string

utl_file_dir                         string      /app/oracle/logminer

SQL> exec dbms_logmnr_d.build(dictionary_filename =>'dict2.dat',-

> dictionary_location=>'/app/oracle/logminer');

PL/SQL procedure successfully completed.

step4. 분석을 해야 할 Redo Log File 을 등록한 후 분석

SQL> @log

  6  ;

GROUP# MEMBER                                          MB        seq STATUS   arc

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

     1 /data/temp3/redo01.log                           5         19 INACTIVE YES

     2 /data/temp3/redo02.log                           5         20 INACTIVE YES

     3 /data/temp3/redo03.log                           5         21 CURRENT  NO          

SQL> select db_name,filename from v$logmnr_logs;             <--3번이 current 이기에 3번 등록

DB_NAME         FILENAME

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

TESTDB          /data/temp3/redo03.log

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/app/oracle/logminer/dict2.dat')

PL/SQL procedure successfully completed.

step5. 분석결과 조회

SQL> alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';

Session altered.

SQL> select timestamp,username,operation,sql_redo

  2  from v$logmnr_contents

  3  where seg_name='TEST2'

  4  ;