[B/R] 로그마이너 (Log Miner)

◆ 정의
  - 8i부터 추가된 기능
  - dbms_logmnr과 dbms_logmnr_d패키지를 사용
  - 오라클의 리두로그파일의 내용을 사람이 볼 수 있도록 변환해 주는 유틸리티
  - 9i 이상부터 DDL도 지원함


◆ 필요성
  - 리두로그파일이나 아카이브로그파일에 있는 내용을 보고 싶을 때 유용
  - 로그마이너로 리두로그와 아카이브로그를 읽어들이게 되면, DB에서 해당 로그파일의 생성시점에 일어났던 모든 DML 및 DDL(9i이상) 문을 확인할 수 있으므로, 특정 트랜잭션의 발생시점이나, 실행한 유저 등을 확인할 수 있고, UNDO SQL을 추출해내어 해당 쿼리를 롤백하는데 사용할 수 있다.


◆ 로그마이너로 가능한 것들
  - DB에 작성된 변경사항들의 기록
  - Insert, Update, Delete, Commit, Rollback, DDL 또는 Index 작업
  - SCN(System Change Number)
  - 변경을 포함하는 트랜잭션 식별
  - 특정 트랜잭션이 커밋되는 SCN
  - 변경된 객체의 테이블 및 스키마 명칭
  - DML 또는 DDL문을 발생시킨 사용자 정보
  - 리두 레코드들을 생성하는 동등한 SQL을 나타낼 수 있는 재생성된 SQL(SQL_REDO)
  - 변경사항의 실행 취소를 위해 필요한 SQL을 제공하는 재생성된 SQL(SQL_UNDO)


◆ 로그마이너가 지원하는 오브젝트(10g부터 추가됨)
  - IOT


◆ 로그마이너가 지원하는 데이터타입(10g부터 추가됨)
  - LONG
  - Multibyte CLOB
  - NCLOB


◆ 주의사항
  - DB는 아카이브 로그 모드(Archive log mode)이어야 함
  - 분석하고자 하는 Instance와 같은 DB Character set, 동일한 H/W를 사용해야 함
  - 분석하고자 하는 Instance와 로그가 생성된 Instance는 동일한 DB_BLOCK_SIZE이어야 함. 만약 다르다면 아카이브로그에 Corruption이 발생했다는 메시지가 출력됨.


◆ Supplemental Logging 옵션 활성화
DB Level에서 추가적인 로그기록 옵션을 활성화함.

SQL> alter database add supplemental log data;

◆ 필요 패키지 설치(설치가 안되어 있을 경우)

SQL> @?/rdbms/admin/dbmslm.sql          // dbms_logmnr 패키지 생성

SQL> @?/rdbms/admin/dbmslmd.sql        // dbms_logmnr_d 패키지 생성

◆ 딕셔너리 파일이 생성될 경로 설정

$ vi $ORACLE_HOME/dbs/initSID.ora
utl_file_dir='/data/logmnr'

딕셔너리 파일 경로는 원하는 대로 설정하면 된다.


◆ 딕셔너리 파일 생성

SQL> exec dbms_logmnr_d.build('dict', '/data/logmnr');

/data/logmnr 경로에 dict라는 이름의 딕셔너리 파일을 생성한다. 경로는 파라미터파일에서 지정한 것이고, dict 는 아무거나 줘도 된다.

만약 딕셔너리 파일 생성시 에러(ORA-06532, ORA-06512)가 나면 위 패키지의 dbmslmd.sql 내용을 수정하고 재생성한다.

$ vi $ORACLE_HOME/rdbms/admin/dbmslmd.sql
TYPE col_desc_array IS VARRAY(513) OF col_description 을
TYPE col_desc_array IS VARRAY(1000) OF col_description 로 수정한다.
SQL> @?/rdbms/admin/dbmslmd.sql

9i부터 딕셔너리 파일은 위 방식같이 외부에 딕셔너리 파일을 생성하는 Flat 방식 이외에 온라인 리두로그에 딕셔너리 정보를 저장하는 방식이 추가되었다.
Target DB 상에서는 온라인 딕셔너리를 이용하면 되지만, 다른 DB에서 분석을 할 경우는 딕셔너리 정보를 리두로그 및 Flat파일로 생성해 분석한다.

● Flat file 방식
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict', dictionary_location=>'/data/logmnr', options=>dbms_logmnr_d.store_in_flat_file);

● 온라인 리두로그 저장 방식
SQL> exec dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);

◇ dbms_logmnr 패키지가 제공하는 3가지 프로시저

● add_logfile(name varchar2, options number)
  - 분석할 리두로그의 추가/제거

● start_logmnr(start_scn number, end_scn number, start_time date, end_time date, dictfilename varchar2, options binary_integer)
  - 분석할 시간대나 SCN범위를 지정, 분석에 사용할 Data Dictionary Extract를 지정

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

◆ 분석할 로그파일 등록

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo03.log', 1);

온라인 리두로그파일(redo03.log)을 등록(1)한다.

  ◇ 숫자의 의미

1 : 새 파일 등록
2 : 기존 파일 삭제
3 : 다른 파일 추가

  ◇ 다른 표현

● 새 파일 등록
SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo03.log', options=>dbms_logmnr.new);

● 기존 파일 삭제
SQL> exec dbms_logmnr.remove_logfile('/home/oracle/oradata/testdb/redo03.log');

● 다른 파일 추가
SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo03.log', options=>dbms_logmnr.addfile);

◆ 등록한 로그파일 분석

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/data/logmnr/dict');

로그를 분석할 Target DB의 정보를 /data/logmnr/dict 딕셔너리파일에서 찾고, 위에서 등록한 로그파일을 분석함.
파라미터들과 다양한 옵션을 줄 수 있다.

  ◇ 파라미터

● startScn
  - 형식 : number
  - 기본값 : 0
  - 조회 시 startScn보다 크거나 같은 레코드를 리턴.

● endScn
  - 형식 : number
  - 기본값 : 0
  - 조회 시 endScn보다 작거나 같은 레코드를 리턴.

● startTime
  - 형식 : date
  - 기본값 : '01-JAN-1988'
  - 조회 시 startTime보다 크거나 같은 레코드를 리턴.

● endTime
  - 형식 : date
  - 기본값 : '31-DEC-2110'
  - 조회 시 endTime보다 작거나 같은 레코드를 리턴.

● DictFileName
  - 형식 : varchar2
  - 기본값 : ' '
  - Target DB의 정보를 포함한 딕셔너리 파일 등록.

● Options
  - 형식 : binary_integer
  - 기본값 : 0
  - 아래 옵션 값 참고 ↓

  ◇ 옵션

● 사용법
options=>옵션1 + 옵션2 + 옵션3

● COMMITTED_DATA_ONLY
  - 커밋된 트랜잭션에 대해서만 보여줌.

● SKIP_CORRUPTION
  - 8i에서는 Log corruption 발생 시 로그마이너가 종료되고 분석을 위해 다시 시도해야 했으나, 9i부터는 해당 옵션을 주어 Redo log block corruption 발생 시 corruption된 부분을 skip하고 계속 분석을 시도함.

● DDL_DICT_TRACKING
  - 온라인 딕셔너리 참조 상황에서는 사용 불가능.
  - DDL문장을 추적 할 수 있다.

● DICT_FROM_ONLINE_CATALOG
  - 9i부터는 외부 딕셔너리 파일 생성 없이, 현재 사용중인 온라인 카탈로그 딕셔너리로 분석 가능.

● DICT_FROM_REDO_LOGS
  - 등록한 로그파일 안에 저장된 딕셔너리에서 정보를 찾음.

● NO_SQL_DELIMITER
  - SQL_REDO와 SQL_UNDO 컬럼값은 세미콜론을 포함하는데, 이를 PL/SQL구문에서 사용시 세미콜론을 없애주는 옵션.

● NO_ROWID_IN_STMT
  - 10g부터 SQL_REDO와 SQL_UNDO 컬럼에서 ROWID를 나타내지 않도록 하는 옵션. ROWID를 나타내지 않으면 이 SQL들을 ROWID가 다른 환경에서도 사용할 수 있으므로 유용.

● PRINT_PRETTY_SQL
  - SQL_REDO와 SQL_UNDO 컬럼값이 하나의 라인으로 작성되어 읽기가 어려운 단점 해결을 위해, 가독성을 높여주는 옵션.

● CONTINUOUS_MINE
  - 분석시 파라미터를 startTime 또는 startScn을 주었다면 해당 옵션을 주면 오라클이 컨트롤파일에서 이후에 필요한 연속적인 time 또는 scn을 찾는다.
  - 분석시 파라미터를 endTime 또는 endScn을 주었다면 반드시 분석된 내용 조회하기 전에는 SET ARRAYSIZE 1;을 해주어야 한다. 안해주면 결과값이 보이지 않는다.

◆ 분석된 내용 중 찾고자 하는 내용 조회

SQL> set line 170
SQL> col timestamp for a20
SQL> col seg_owner for a10
SQL> col username for a10
SQL> col sql_redo for a50

SQL> select to_char(timestamp, 'YYYY-MM-DD:HH24:MI:SS') as timestamp, seg_owner, username, sql_redo
   from v$logmnr_contents
   where sql_redo like '%drop%'
   seg_owner = 'SCOTT';

SCOTT의 세그먼트들 중 drop이 들어간 레코드를 조회하여 그때의 시간, 세그먼트 소유자, 해당 트랜잭션을 날린 사용자, 해당 SQL을 보여줌.


◆ 로그마이너 관련 뷰

● V$LOGMNR_CONTENTS
  - 현재 분석되고 있는 로그파일의 내용
  - TIMESTAMP : 변경이 가해진 시간(timestamp)
  - COMMIT_TIMESTAMP : 트랜잭션에 커밋이 수행된 시간(timestamp)
  - USERNAME : 트랜잭션을 수행한 사용자
  - SQL_REDO : Redo 레코드를 생성하는 SQL문장과 동일한 재생성 문장.
  - SQL_UNDO : 변경내역을 UNDO하기 위해 필요한 SQL문장을 보여주는 재생성 문장. DDL은 NULL로 표시.
  - SEG_OWNER
  - SEG_NAME
  - ROW_ID

● V$LOGMNR_DICTIONARY
  - 사용 중인 딕셔너리 파일

● V$LOGMNR_LOGS
  - 분석에 사용되고 있는 로그파일

● V$LOGMNR_PARAMETERS
  - 로그마이너에 세팅된 현재 파라미터 값

◆ 로그마이너 세션 종료

SQL> exec dbms_logmnr.end_logmnr();