오라클 로그마이너 사용 및 설정 방법에 대해서 정리한다.
로그마이너는 Oracle 8i부터 사용 가능한 기본 내장 툴이며, 이를 사용하여 리두(아카이브) 로그를 분석하여 DML/DDL 이력을 확인 가능하다.
로그마이너를 어디에 사용할 수 있을까~?
- 데이터 복구 : 사용자의 실수 혹은 어플리케이션의 에러에 의해 잘못 커밋한 데이터를 복구 가능하다.
- CDC, ETL : 몇몇 CDC, ETL 제품군에서 소스 데이터베이스의 변경 데이터를 추적하기 위해 로그마이너를 사용한다.
- [추가] AWS의 DMS 서비스의 CDC도 오라클의 LogMiner를 사용한다.
- 그 밖에 DML/DDL의 이력으로 할 수 있는 일들... 성능 분석, 튜닝 및 감사(Audit)을 위해 사용 가능 할 것이다.
LogMiner Dictionary
로그마이너를 사용하기 위해서는 Dictionary에 대해서 알아야한다.
Dictionary는 로그마이너가 리두 로그를 분석하는 과정에서 오브젝트 이름과 데이터 타입과 관련된 중요한 정보를 제공한다. Dictionary를 사용하지 않아도 분석은 가능하나 아래의 '[그림-1]'처럼 SQL 조회시 테이블 이름이 오브젝트 ID('OBJ$ 322099')로, 컬럼 데이터 값이 바이너리(HEXTORAW)로 출력된다.
리두 로그를 정상 분석하기 위해서는 로그마이너에게 Dictionary를 제공해야 하며 이를 위한 방법은 세 가지가 있다.
- Online Catalog 사용(DICT_FROM_ONLINE_CATALOG)
- Online Catalog를 사용하며 별도의 Dictionary 추출(Build) 작업이 필요하지 않음
- Database가 Open된 상태에서 사용 가능
- DDL_DICT_TRACKING 옵션 사용 불가
- DDL 발생 이전의 리두 로그에 대해서는 정상적인 분석이 진행되지 않음(바이너리 값 출력)
- 리두 로그에 딕셔너리 추출(STORE_IN_REDO_LOGS)
- 리두 로그에 Dictionary를 추출하여 저장 함
- 아카이브 모드가 활성화 되어야 함
- STORE_IN_FLAT_FILE 보다 Dictionary 추출에 리소스를 적게 사용 함
- Dictionary 추출 시 DDL 발생을 방지하여 일관성(Consistency)을 보장 함. 즉, DDL이 빈번하게 발생하는 시간대에는 Dictionary 추출을 자제
- 플랫 파일(Flat File)로 딕셔너리 추출(STORE_IN_FLAT_FILE)
- 지정한 파일에 Dictionary를 추출 하여 저장 함
- STORE_IN_REDO_LOGS 보다 Dictionary 추출에 리소스 사용이 많음
- 추출 된 Dictionary에 대한 일관성(Consistency)을 보장하지 않으므로 DDL이 발생하지 않을 때 추출 하기를 권장 함
- 하위 버전과의 호완을 위해 주로 사용
DICT_FROM_ONLINE_CATALOG는 현재 Online Catalog에 부합하는 리두 로그를 분석하는데 큰 문제가 없지만, DDL 발생 시점 이전(즉, 현재 Online Catalog와 다른 상황)의 리두 로그 분석시 오브젝트 아이디와 바이너리 값이 출력되게 된다. 그래서 STORE_IN_REDO_LOGS와 STORE_IN_FLAT_FILE 방식으로 주기적인 Dictionary를 생성해서 보관 후, 과거 시점(즉, DDL 발생 이전 시점)의 리두 로그에 대해서도 해당 Dictionary를 사용하여 분석 할 수 있도록 할 수 있다. 이에 대해서는 'Dictionary 별 실행 방법 및 특징'에 대해서 조금 더 자세히 알아보도록 한다.
아래의 표는 각 Dictionary 옵션 별 특징을 정리한 표이다.
|
DICT_FROM_ONLINE_CATALOG |
STORE_IN_REDO_LOGS |
STORE_IN_FLAT_FILE |
|
|
설명 |
Online 카탈로그 사용 |
Redo 로그에 Dictionary 추출 |
지정 파일에 Dictionary 추출 |
|
Dictionary 생성 필요 |
불필요 |
필요 |
필요 |
|
Database 요구 사항 |
Open 되어야 함 |
Archive mode 활성화 |
UTL_FILE_DIR 파라미터 설정 |
|
Dictionary 생성 중 DDL 발생시 Consistency 보장 여부 |
- |
보장 |
보장 않음 (DDL 발생하지 않는 시간에 추출 권장) |
|
추출 시 Database 리소스 사용 |
- |
극소 |
소 |
|
그 밖의 제약 사항 |
DDL 발생 이전의 리두 로그 분석 시 오브젝트 이름이 ID로 출력되고 값은 바이너리로 출력 됨. |
- |
- |
|
Dictionary 크기(Oracle 11g) |
- |
- |
약 4600개 테이블 = 86 MB |
|
Dictionary 추출 시간(Oracle 11g) |
- |
약 4600개 테이블 = 3.61(초) |
약 4600개 테이블 = 6.32(초) |
Dictionary 별 실행 방법 및 특징
DICT_FROM_ONLINE_CATALOG
온라인 카탈로그 사용시 로그마이너 실행 방법은 아래와 같다.
| 분석 대상 로그 파일 추가 → 로그마이너 시작 → 리두 로그 조회 → 로그마이너 세션 종료 |
|
-- 분석 대상 리두 로그 파일 추가 EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/home/oracle/ora_base/oradata/orcl/redo01.log', OPTIONS => DBMS_LOGMNR.NEW);
-- 로그마이너 시작 (DICT_FROM_ONLINE_CATALOG 옵션 필수)
-- 리두 로그 조회 SELECT * FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME='T';
-- 로그마이너 세션 종료 EXECUTE DBMS_LOGMNR.END_LOGMNR(); |
온라인 카탈로그를 사용하는 방법은 매우 간단하다. Dictionary를 빌드할 필요도 없고, 원하는 리두로그만 추가한 이후에 시작하고 조회하면 된다. 다만, 이미 언급하였듯이 온라인 카탈로그를 사용할때 제약사항은 DDL 발생 이전 리두 로그에 대해서는 정확한 분석이 되지 않는다.
위의 [그림-2]를 보고 DDL 발생에 따라 어떠한 DML이 정상 혹은 비정상으로 출력되는지 설명하고자 한다. 'DML n', 'DDL n', '조회 n'은 시간 순서대로 발생한다. 즉, '조회 2'의 시점에서는 'DDL 1', 'DML 2', 'DDL 2', 'DML 3'가 실행되지 않았음을 의미한다. 각각의 조회 시점별 DML SQL문이 정상(Object 이름, 컬럼 데이터 텍스트 값) 혹은 비정상(Object ID, 컬럼 데이터 바이너리 값) 출력 되는지 아래의 표를 참고하기 바란다.
| 조회 시점 | DML 1 | DML 2 | DML 3 |
| 조회 1 | 조회 안됨(DML 발생 전) | 조회 안됨(DML 발생 전) | 조회 안됨(DML 발생 전) |
| 조회 2 | 정상 | 조회 안됨(DML 발생 전) | 조회 안됨(DML 발생 전) |
| 조회 3 | 비정상 | 조회 안됨(DML 발생 전) | 조회 안됨(DML 발생 전) |
| 조회 4 | 비정상 | 정상 | 조회 안됨(DML 발생 전) |
| 조회 5 | 비정상 | 비정상 | 조회 안됨(DML 발생 전) |
| 조회 6 | 비정상 | 비정상 | 정상 |
위의 표를 참고하면 온라인 카탈로그 사용시 로그마이너가 어떻게 동작하는지 명확하게 이해할 것이다. 온라인 카탈로그는 현재 테이블 딕셔너리와 동일한 로그는 정상 분석하지만 그렇지 않을 경우 정상 분석되지 않는다.
STORE_IN_REDO_LOGS
리두 로그에 Dictionary 추출 및 저장시 아래의 방법으로 로그마이너를 실행할 수 있다.
| DICTIONARY 빌드 → V$ARCHIVED_LOG 에서 DICTIONARY 포함 로그 파일 찾기 → 분석 대상 로그 파일 추가 → 로그마이너 시작 → 리두 로그 조회 → 로그마이너 세션 종료 |
|
-- Dictionary 빌드 (STORE_IN_REDO_LOGS 옵션 필수) EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
set linesize 150 column name format a50
-- V$ARCHIVED_LOG에서 DICTIONARY 포함 로그 파일 찾기 SELECT NAME, TO_CHAR(FIRST_TIME, 'HH:MI:SS'), TO_CHAR(NEXT, 'HH:MI:SS'), DICTIONARY_BEGIN, DICTIONARY_END FROM V$ARCHIVED_LOG ORDER BY FIRST_CHANGE# ASC;
-- 분석 대상 로그 파일 추가
-- 로그마이너 시작(DICT_FROM_REDO_LOGS 옵션 필수)
-- 리두 로그 조회 SELECT * FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME='T';
-- 로그마이너 세션 종료 |
리두 로그에 Dictionary를 추출하여 저장할 때에는 Dictionary 크기 만큼 리두 로그를 차지하게 된다. 리두 로그가 쉽게 스위치 될 수 있으며 하나 이상의 리두로그 걸쳐있을 수 있다. 해당 방식으로 Dictionary를 보관할 경우, 어떤 리두 로그들이 Dictionary를 포함하는지 어떻게 알 수 있을까? 해답은 V$ARCHIVED_LOG의 DICTIONARY_BEGIN, DICTIONARY_END 컬럼에 있다.
- DICTIONARY_BEGIN 컬럼
- 'YES'일 경우, 해당 로그 파일이 Dictionary의 시작 부분을 포함하고 있음을 의미함
- DICTIONARY_END 컬럼
- 'YES'일 경우, 해당 로그 파일이 Dictionary의 끝 부분을 포함하고 있음을 의미함
리두 로그로 추출된 Dictionary를 사용할 경우, DICTIONARY_BEGIN='YES'인 로그 파일부터 DICTIONARY_END='YES'인 로그 파일들을 DBMS_LOGMNR.ADD_LOGFILE을 사용하여 추가하여야 한다. 그렇지 않을 경우 START_LOGMNR(DICT_FROM_REDO_LOGS 명시) 수행시 Dictionary를 찾을 수 없다고 에러가 발생하면서 정상 시작이 되지 않을 것이다.
아래의 [그림-3]은 V$ARCHIVED_LOG 뷰 조회 결과이다.
'SCN 131299000' 이후의 리두 로그를 분석하기 위해서는 Dictionary를 포함하는 '1_7191_959431694.arc' 로그 파일을 추가하여야 한다. 한가지 팁을 말하자면, 'SCN 131300182' 이후를 분석하기 위해서 '1_7191_959431694.arc' 로그 파일은 필수로 추가해야 하지만, '1_7192_959431694.arc', '1_7193_959431694.arc', '1_7194_959431694.arc'는 추가하지 않아도 된다.
리두 로그에 Dictionary를 추출하여 저장하는 방식으로 동작할 경우, DDL이 발생할 경우 어떻게 동작할까? 결론부터 얘기하자면 DDL 발생시 기존 Dictionary로는 분석할 수 없으며 DDL 발생 이후에 수행된 DML은 비정상으로 출력될 것이다. 아래의 [그림-4]를 보게 되면, add column 이후에 수행된 DML의 컬럼 값이 바이너리로 출력되는 것을 확인 할 수 있다.
위의 현상을 해결 방법은 LOGMNR_START 수행시, DDL_DICT_TRACKING 옵션을 사용하면 정상으로 동작 할 것이다. 해당 옵션은 로그마이너가 내부에 유지하는 'Internal Dictionary'를 DDL이 발생할 때 마다 자동으로 재빌드 하는 옵션이다(실제 리두 로그에 추출된 Dictionary가 변경되는 것이 아니다). 다만 주의 할 점은, DDL_DICT_TRACKING 옵션 사용 시에는 Dictioanry를 포함하는 리두 로그부터 순차적으로 발생한 모든 리두 로그를 다 추가해 주어야 한다. 그렇지 않을 경우, Missing 리두 로그가 있다고 하여 정상 시작이 되지 않을 것이다(ORA-01291: missing logfile).
아래의 [그림-5]는 DDL_DICT_TRACKING 옵션 사용으로 SQL_REDO 문이 정상 출력되는 것을 확인할 수 있다.
STORE_IN_FLAT_FILE
해당 옵션을 사용하기 위해서는 UTL_FILE_DIR 파라미터를 설정 하여야한다.
|
$ ALTER SYSTEM SET UTL_FILE_DIR='/home/oracle/dict' scope=spfile; $ shutdown immediate $ startup |
사용자 지정 파일에 Dictionary 추출시 아래의 방법으로 로그마이너를 실행할 수 있다.
| DICTIONARY 빌드 → 분석 대상 로그 파일 추가 → 로그마이너 시작 → 리두 로그 조회 → 로그마이너 세션 종료 |
|
-- Dictionary 빌드 (STORE_IN_FLAT_FILE 생략 가능) EXEC DBMS_LOGMNR_D.BUILD('dict4.dat','/home/oracle/dict', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
-- 로그 파일 추가
-- 리두 로그 조회 SELECT * FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME='T';
|
Dictionary 빌드와 시작시 DICTFILENAME 파라미터를 명시하는 것 이외에는 '리두 로그 파일에 딕셔너리 추출' 방식과 사용법이 동일하다.
V$LOGMNR_CONTENTS 조회시 특징
V$LOGMNR_CONTENTS 뷰 조회시 특징은 다음과 같다.
-
V$LOGMNR_CONTENTS 테이블은 일반 VIEW와 다름
-
사용자가 쿼리로 조회시에만 리두 로그를 순차적(Sequential)으로 분석하여 해당 내용이 출력되고 DB에는 저장하지 않음. 즉, 매번 쿼리 할때마다 분석 후 출력 됨.
-
메모리 사용량은 출력되는 Row의 개수에 비례하지 않음.
-
조회 시간은 분석 대상 리두 로그의 크기에 비례.
-
만약 반복적인 조회가 필요한 경우라면, 임시(Temporary) 테이블 생성 후 재사용 할것을 권장.
유용한 옵션들
CONTINUOUS_MINE
기존에는 EXECUTE DBMS_LOGMNR.ADD_LOGFILE 프로시저로 분석 대상 리두 로그를 추가 하였지만, CONTINUOUS_MINE 옵션 사용시, 자동으로 리두 로그 파일을 추가할 수 있다. 다만, 해당 옵션과 함께 STARTTIME(STARTSCN), ENDTIME(ENDSCN) 파라미터를 사용하여 분석 대상의 범위를 명확히 지정해 주어야 한다.
NO_ROWID_IN_STMT
DELETE, UPDATE DML에 대해서 ROWID가 항상 출력 되는데 이를 출력되지 않게 한다.
COMMITTED_DATA_ONLY
오직 커밋 된 SQL문만 출력되도록 한다.
[References]
[1] (Oracle 10g official) https://docs.oracle.com/cd/B14117_01/server.101/b10825/logminer.htm#i1006321
[2] (Oracle 11g official) https://docs.oracle.com/cd/B28359_01/server.111/b28319/logminer.htm#i1005553
'Database > Fundamental' 카테고리의 다른 글
| [Oracle] Supplemental Logging 개념과 사용법 (0) | 2019.05.17 |
|---|
댓글