로그 마이너(Log Miner)

데이터베이스에 행해진 DML,DDL과 같은 정보는 모두 log file에 남게 됩니다. 이 log file(online redo log, archive log)로 부터 원래의 SQL 문장 및 UNDO를 위한 SQL문장을 뽑아낼 수 있는 기능을 제공하는 LogMiner기능을 Oracle 8i부터 사용할 수 있습니다. 

LogMiner의 기능 자체가 Drop/Truncate에 대한 복구를 지원한다는 말이 아니라 다만 어느 시점으로 복구를 하면 되는지에 대한 기준을 제시할 수 있습니다.

 

이점

- 애플리케이션 수준에서 발생한 오류와 같은 데이터베이스의 논리적 손상이 시작되었을 수 있는 시기를 파악 가능

- 트랜잭션 수준에서 세분화 된 복구를 수행하기 위해 취해야 할 조치를 결정할 수 있음

- 추세 분석을 통한 성능 튜닝 및 용량 계획, 업데이트 및 삽입이 가장 많은 테이블을 결정할 수 있음

- 로그 마이너를 통해 데이터베이스에서 실행 된 모든 DML 및 DDL의 실행된 순거 및 실행 한 사람을 추적할 수 있음

 

구성

로그마이너를 사용하려면 4가지 구성을 해야합니다.

- 소스 데이터베이스 : 로그마이너가 분석할 리두 로그 파일을 생성하는 데이터베이스

- 마이닝 데이터베이스 : 로그마이너가 분석할 때 사용하는 데이터베이스(소스와 동일하게 사용 가능)

- 로그마이너 디렉토리 : internal Object ID 대신에 테이블과 컬럼이름을 제공하게 해줍니다.

- 리두로그 파일 : 분석을 위해 사용 할 Online Redo file 또는 Archive Redo log파일

 

요구사항

- 소스 데이터베이스와 마이닝 데이터베이스는 모두 동일한 하드웨어 플랫폼에서 실행되어야 합니다.

- 소스데이터베이스와 마이닝 데이터베이스는 동일해도 되고, 다른 DB여도 됩니다.

- 마이닝 데이터베이스는 소스 데이터베이스와 동일한 버전 또는 그 이후의 버전에서 실행되어야 합니다.

- 마이닝 데이터베이스는 소스 데이터베이스에서 사용하는 것과 동일한 캐릭터셋(혹은 상위 캐릭터셋)을 사용해야 합니다.

 

순서

---

1.로그 마이너 디렉토리 지정.

 - DBMS_LOGMNR_D.BUILD 프로시저를 사용하여 디렉토리를 지정

 

SQL> exec dbms_logmnr_d.build(dictionary_filename=>'log.dat',dictionary_location=>'/home/oracle/logmnr');

 

2.분석을 위해 리두 로그 파일을 지정.

- DBMS_LOGMNR.ADD_LOGFILE 프로시저를 사용하여 log file의 리스트를 생성

- 숫자의 의미 1= 신규등록, 2=파일삭제, 3=추가등록

SQL> exec dbms_logmnr.add_logfile('/app/oracle/oradata/ysbae2/redo01.log',1);

3.LOGMINER 시작.

- DBMS_LOGMNR.START_LOGMNR프로시저를 사용

 

SQL>exec dbms_logmnr.start_logmnr (dictfilename=>'/home/oracle/logmnr/log.dat',
options=>dbms_logmnr.ddl_dict_tracking+dbms_logmnr.committed_data_only);

 

4.V$LOGMNR_CONTENTS 뷰를 사용해서 쿼리 조회.

SQL> select * from v$logmnr_contents

5.LOGMINER 세션 종료.

- DBMS_LOGMNR.END_LOGMNR 프로시저를 사용

SQL> exec dbms_logmnr.end_logmnr();

---

 

LogMiner Directory 옵션

 

1.Online Catalog

리두 로그 파일이 생성 된 소스 데이터베이스에 액세스할 수 있고, 컬럼의 정의가 변경될 것으로 예상되지 않을 때 사용을 권장.

효율적이고 사용하기 쉬운 옵션

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

2.Redo log file 로 추출

소스 데이터베이스에 대한 액세스 권한이 없을 것으로 예상되거나 컬럼의 정의가 변경될 것으로 예상될때 사용 권장.

SQL> EXECUTE DBMS_LOGMNR_D.BUILD( -
   OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

3.Flat file로 추출

이전 버전과의 호환성을 위해 유지. 이 옵션은 트랜잭션 일관성을 보장하지 않음. 오라클은 1번 혹은 2번을 권장

Flat file로 추출하려면 DBMS_LOGMNR_D.BUILD와 함께 STORE_IN_FLAT_FILE 옵션을 사용하면 됩니다.

Flat File로 추출되는 동안 DDL이 발생하지 않는지 확인이 필요합니다.

 

Flat File 추출 과정

1.DBMS_LOGMNR_D.BUILD프로시저를 통해 디렉토리를 지정해야 합니다. 그러려면 초기화 파라미터  UTL_FILE_DIR에 경로가 설정되어 있어야 합니다. 

spfile사용 시 경로 설정법

SQL>alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;

SQL>shutdown immediate;

SQL>startup;

 

2. DBMS_LOGMNR_D.BUILD 프로시저 사용

dictionary_filename과 location을 설정합니다.

SQL> exec dbms_logmnr_d.build(dictionary_filename=>'log.dat',dictionary_location=>'/home/oracle/logmnr');

 

의사결정 트리

 

Redo log File Option

리두 로그 파일의 데이터를 마이닝하려면 마이닝할 리두 로그 파일에 대한 정보가 필요하게 됩니다. 이러한 리두 로그 파일에 있는 데이터베이스 변경 사항은 V$LOGMNR_CONTENTS뷰를 통해 확인할 수 있습니다.

로그마이너가 분석 할 리두 로그 파일목록을 자동,수동으로 생성하도록 할 수 있습니다.

 

자동(Automatic)

CONTINUOUS_MINE 옵션을 통해 로그마이너를 시작할 때 자동으로 생성하도록 할 수 있습니다.

(이 옵션을 사용하려면 Archive log 모드여야 합니다)

 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( 
   STARTTIME => '01-Jan-2003 08:30:00', 
   ENDTIME => '01-Jan-2003 08:45:00', 
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + 
   DBMS_LOGMNR.CONTINUOUS_MINE);

수동(Manually)

DBMS_LOGMNR.ADD_LOGFILE 프로시저를 사용하여 로그마이너를 시작하기 전에 리두 로그 파일 목록을 수동으로 생성합니다. 이 방법을 사용하면 로그마이너를 소스 데이터베이스에 연결할 필요가 없습니다.

 

새로운 리두 로그 파일을 추가하기 위해서는 DBMS_LOGMNR.NEW 옵션을 사용합니다.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 
   LOGFILENAME => '/oracle/logs/log1.f', 
   OPTIONS => DBMS_LOGMNR.NEW);

하나 이상의 리두 로그 파일을 추가하기 위해서는 DBMS_LOGMNR.ADDFILE 옵션을 사용합니다.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 
   LOGFILENAME => '/oracle/logs/log2.f', 
   OPTIONS => DBMS_LOGMNR.ADDFILE);

 

 

관련 View

- V$LOGMNR_DICTIONARY : 사용중인 dictionary file
- V$LOGMNR_PARAMETERS : LogMiner에 Setting된 현재의 parameter의 값
- V$LOGMNR_LOGS : 분석되고 있는 redo log file
- V$LOGMNR_CONTENTS : 현재 분석되고 있는 redo log file의 내용

 

 

 

FLAT FILE LOG MIMER 테스트 기준

[Oracle 11.2.0.1]

사전준비사항

UTL_FILE_DIR 설정

SQL>alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
SQL>shutdown immediate;
SQL>startup;

 

SUPPLEMENTAL LOG 설정 활성화(비활성화)

SQL> alter database add(drop) supplemental log data;

 

테스트

 

0.UTL_FILE_DIR 확인

SYS@ysbae> show parameter utl

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
create_stored_outlines               string                            (null)
utl_file_dir                         string                            /home/oracle/log_mgr

 

1.SUPPLEMENTAL LOG 설정 활성화

SYS@ysbae> select supplemental_log_data_min from v$database;

SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES

1 row selected.

 

2.테스트 테이블 생성 및 데이터 생성

SYS@ysbae> create table test.log_mgr2 (no number,name varchar2(10));

Table created.

 

SYS@ysbae> insert into test.log_mgr2 values (1,'aaa');

1 row created.

 

SYS@ysbae> insert into test.log_mgr2 values (2,'bbb');

1 row created.

 

SYS@ysbae> insert into test.log_mgr2 values (3,'ccc');

1 row created.

 

SYS@ysbae> commit;

 

SYS@ysbae> update test.log_mgr2 set name='ddd' where no=3;

1 row updated.

 

SYS@ysbae> commit;

 

3.로그마이너 디렉토리 생성

 

SYS@ysbae> exec dbms_logmnr_d.build(dictionary_filename => 'log2.dat', dictionary_location => '/home/oracle/log_mgr');

LogMnr Dictionary Procedure started

LogMnr Dictionary File Opened

Procedure executed successfully - LogMnr Dictionary Created

 

PL/SQL procedure successfully completed.

 

4. 현재 Current log를 확인 후 add logfile 수행

SYS@ysbae> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME              NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------
         1          1         80   52428800        512          2 NO        INACTIVE                                               2386507 11-OCT-20            2408051 12-OCT-20
         2          1         81   52428800        512          2 NO        INACTIVE                                               2408051 12-OCT-20            2413348 12-OCT-20
         3          1         83   52428800        512          2 NO        CURRENT                                                2413504 12-OCT-20         2.8147E+14 (null)
         4          1         82   10485760        512          2 NO        INACTIVE                                               2413348 12-OCT-20            2413504 12-OCT-20

 

SYS@ysbae> exec dbms_logmnr.add_logfile('/app/oracle/oradata/ysbae/redo03.log',1);

 

PL/SQL procedure successfully completed.

 

SYS@ysbae> select db_name,filename from v$logmnr_logs;

 

DB_NAME                  FILENAME

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

YSBAE                    /app/oracle/oradata/ysbae/redo03.log

 

 

5.로그마이너 수행

SYS@ysbae> exec dbms_logmnr.start_logmnr(dictfilename => '/home/oracle/log_mgr/log2.dat');

 

PL/SQL procedure successfully completed.

 

6.DML 수행 정보 확인

 

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

Session altered.

SYS@ysbae> select timestamp,username,operation,sql_Redo from v$logmnr_contents where seg_name='LOG_MGR2';

TIMESTAMP           USERNAME   OPERATION  SQL_REDO
------------------- ---------- ---------- --------------------------------------------------
2020-10-12:14:00:08 UNKNOWN    DDL        create table test.log_mgr2 (no number,name varchar
                                          2(10));

2020-10-12:14:00:42 UNKNOWN    INSERT     insert into "TEST"."LOG_MGR2"("NO","NAME") values
                                          ('1','aaa');

2020-10-12:14:07:54 UNKNOWN    INSERT     insert into "TEST"."LOG_MGR2"("NO","NAME") values
                                          ('2','bbb');

2020-10-12:14:08:03 UNKNOWN    INSERT     insert into "TEST"."LOG_MGR2"("NO","NAME") values
                                          ('3','ccc');

2020-10-12:14:08:42 UNKNOWN    UPDATE     update "TEST"."LOG_MGR2" set "NAME" = 'ddd' where
                                          "NAME" = 'ccc' and ROWID = 'AAAS6YAAHAAAACHAAC';

5 rows selected.

 

 

 

참고 : haisins.epac.to/wordpress/?p=2494

Oracle 로그 마이너 – DBA의 정석

참고 : goalker.tistory.com/106

Log Miner 활용하기

참고 : docs.oracle.com/cd/B28359_01/server.111/b28319/logminer.htm#i1005553

Using LogMiner to Analyze Redo Log Files

참고 : dbtech.co.kr/bbs/?bo_c=1020&bo_v=94

참고 : docs.oracle.com/cd/B28359_01/server.111/b28319/logminer.htm#i1005606