Log Miner 활용과 Redo log 장애

Log Miner 활용과 Redo log 장애

○ Log Miner

- Oracle 8i 버전부터 Binary 형식으로 되어있는 Redo log File의 내용을 조회할 수 있도록 Log Miner를 제공

- Redo log File에 있는 내용을 추출해서 SQL로 변환한 후 사용자가 SQL 문장으로 조회해서 원하는 내용 확인할 수 있음

- Redo log File 내부에는 데이터가 변경된 시점을 정확하게 알 수 있기 때문에 변경이력을 추적해서 원래 값으로

돌려야 할 경우 등에 유용

 

○ Log Miner의 제약 사항

- Log Miner는 8i 이후 버전에서만 작동

- 분석 대상 인스턴스와 같은 플랫폼에서 동작하고 같은 Character set을 사용하는 db에서 생성된 Redo Log File만 분석할 수 있음

- 분석 대상 인스턴스와 Redo log가 생성된 인스턴스는 DB_BLOCK_SIZE가 동일해야 한다.

다를 경우 Corruption 발생 내용이 기록

- Supplemental logging이 enable되어 있어야 함

 

○ supplemental logging

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

- supplemental log 기능을 사용할 경우 redo log 생성 양이 많아지기 때문에 아카이브 로그 모드를 사용 할 경우 아카이브 경로를

충분하게 주고 사용해야 한다

- 기본 값은 disable

 

min level supplemental logging 조회 및 변경

 

# 조회

select supplemental_log_data_min from v$database;

 

# enable

alter database add supplemental log data;

 

# disalbe

alter database drop supplemental log data;

 

# 특정 테이블에만 적용

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

 

# 특정 테이블에만 적용 해제

alter table scott.emp drop supplemental log data (all) columns;

 

# 특정 컬럼 logging 확인

select * from dba_log_groups;

 

○ Log Miner 설정하기

1. Log Miner 전용 Dictionary 생성

2. Log Miner로 분석할 로그를 보냄

(redo log buffer, archive log) = 등록

3. Log miner에서 분석

4. SQL 로 분석된 결과 조회(v$)

 

○ Log Miner Dictionary 생성

- Log Miner가 Redo log file을 분석해서 그 안에 있는 테이블 이름이나 컬럼 이름들이 있는 부분을 딕셔너리를 확인해서

볼 수 있도록 변환해줌

- SQL parse 단계에서 사용하는 딕셔너리가 아닌 Log Miner가 사용하는 별도의 딕셔너리

- 다른 서버에서 만들면 안 되고 분석을 해야하는 Redo log file이 만들어진 DB에 생성되어야 한다.

- 만약 딕셔너리가 없을 경우 테이블 이름이나 컬럼명이 나오는 부분이 변환할 수 없기 때문에 헥사 값으로 보여주게 된다.

 

○ Dictionary File 생성하는 방법 세 가지

1. Online Catalog 사용

  - Redo log file이 생성된 Source Database에 접근이 가능한 경우

  - 관련 table들에 컬럼 정의가 변경이 없을 경우

  - exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

 

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

  - Redo Log File이 생성된 Source Database에 접근이 불가능할 경우 사용

  - 9i 이상에서 지원됨

  - DB는 Archive mode 여야 한다

  - 딕셔너리를 추출하는 동안 어떠한 DDL도 허용되지 않음

  - exec dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

 

3. Flat File에 저장하는 방법 ★ 가장 많이 쓰임 ★

  - 이전 버전과의 호환성을 보장하기 때문에 가장 많이 사용하는 방법

  - exec dbms_logmnr_d.build(‘dict.dat’,‘/home/oracle/logmnr/’, -> dbms_logmnr_d.store_in_flat_file);

 

○ flat file 로 딕셔너리 생성 실습

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

SQL > !mkdir /oracle11/logminer

SQL > shutdown immediate;

# vi $ORACLE_HOME/dbs/inittestdb.ora

utl_file_dir=/oracle11/logminer 추가

 

-- 2) DB를 start

select *

from v$parameter

where name like '%utl_file_dir%';

-- 3) 딕셔너리 생성

select a.group#, a.member, b.bytes/1024/1024 mb, b.sequence# "seq#", b.status, b.archived arc

from v$logfile a , v$log b

where a.group# = b.group#

order by 1,2;

exec dbms_logmnr_d.build(dictionary_filename=>'dict.dat',dictionary_location=>'/oracle11/logminer');

 

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

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

exec dbms_logmnr.add_logfile('/oracle11/oracle/oradata/testdb/redo01.log',1);

exec dbms_logmnr.add_logfile('/oracle11/oracle/oradata/testdb/redo02.log',3);

exec dbms_logmnr.add_logfile('/oracle11/oracle/oradata/testdb/redo03.log',3);

 

 

-- 무한 루프 현상 조치

 

-- Log Miner 등록 확인

select db_name, filename from v$logmnr_logs;

 

-- 문제가 되는 로그 삭제

exec dbms_logmnr.add_logfile('/oracle11/oracle/oradata/testdb/redo03.log',2);

 

-- 무한 루프중인 로그가 current 이면 current, inactive, inactive 상태로 맞춰준 후 다시 실행한다.

alter system switch logfile;

alter system checkpoint;

 

exec dbms_logmnr.add_logfile('/oracle11/oracle/oradata/testdb/redo03.log',3);

 

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

--drop table 장애가 발생했을 경우 Log Miner을 활용하여

--어떻게 Redo LogFile에서 해당 정보를 찾아내는지 실습

 

--step 1. 현재 상태 확인

select a.group#, a.member, b.bytes/1024/1024 mb, b.sequence# "seq#", b.status, b.archived arc

from v$logfile a , v$log b

where a.group# = b.group#

order by 1,2;

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

create table scott.test1 (no number);

insert into scott.test1 values(1);

commit;

 

drop table scott.test1 purge;

 

-- 현재 current log 확인(1번 그룹에서 진행 중)

SQL> @log.sql;

 

--step 3 .Log Miner를 시작해서 log를 분석한 후 결과를 조회한다. (단, temporary space가 있어야 함!)

create temporary tablespace temp2 tempfile '/oracle11/oracle/oradata/testdb/temp02.dbf' size 20M;

 

alter database default temporary tablespace temp2;

 

select tablespace_name from dba_temp_files;

 

select file_name from dba_temp_files;

 

exec dbms_logmnr.start_logmnr(dictfilename=>'/oracle11/logminer/dict.dat',

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

 

-- logminer 내용 확인

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

 

select timestamp, username, operation, sql_redo

from v$logmnr_contents

where seg_name='TEST1';