LogMiner Overview
데이터베이스에 행해진 DDL, DML과 같은 정보는 모두 log file에 남게 된다. 이 log file(online redo log, archived redo log)로부터 원래의 SQL 문장 및 undo를 위한 SQL 문장을 뽑아낼 수 있는 기능을 제공하는 LogMiner가 Oracle8i부터 포함되었다.
이 LogMiner를 이용하면 애플리케이션 차원의 논리적 결함(이를테면 봉급을 10% 향상 시키려고 했는데, 실수로 100% 향상 시킨 경우)이 발생한 시점을 찾아 그 이전으로 recovery를 시킬 수 있는 기준을 정하는 등의 작업을 할 수 있다.
Oracle9i LogMiner의 향상된 점들은 다음과 같다.
- New Dictionary Options
- Direct DDL statement support
- Tracking of DDL Statements
- Ability to skip log corruption
- Ability to show only rows belonging to committed transactions
또한 GUI환경의 LogMiner Viewer가 Enterprise Manager의 새로운 모듈로 추가되었다. (이 부분은 이 글에서 생략하도록 하겠다.)
New Dictionary Options
Redo log file의 내용을 쉽게 읽기 위해서는 database dictionary에 접근할 수 있어야 한다.
예를 들어 database dictionary 정보가 없다면,
INSERT INTO emp(ename, sal) VALUES (‘John Doe’,50000);
과 같은 SQL 문장에 대해 LogMiner를 통해서 살펴보면 아래와 같이 나오게 된다.
insert into Object#2581 (col#1, col#2) values (hextoraw(‘4a6f686e20446f65’), hextoraw(‘c306’));
이러한 문제점을 해결하기 위해 Oracle8i에서는 database dictionary를 특정 시점에 외부 파일로 기록하는 작업을 했었다.
Oracle9i에서는 redo log file에 기록해두는 방법과 online catalog를 접근하는 방법, 이렇게 두 가지 새로운 옵션이 추가되었다.
Extracting the Dictionary to a Flat File
Oracle8i에서 사용할 수 있는 유일한 방법이다. 이 방법은 redo log file에 기록해두는 방법에 비해 시스템자원을 적게 쓴다는 장점은 있지만, flat file에 기록하는 동안 다른 사용자가 DDL 문장을 수행할 가능성이 있기 때문에 결과적으로 flat file의 dictionary snapshot과 그 시점의 운영중인 dictionary, 이 둘 간의 불일치 문제를 야기시킬 수도 있다는 단점이 있다.
사용하는 방법은 다음과 같으며, 주기적으로 다른 파일 이름으로 back up을 받을 필요가 있다.
| EXECUTE sys.DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME => ‘orcldict.ora’, DICTIONARY_LOCATION => ‘/d02/ora9i/oracle/dbs/ora9i’); |
Note : init parameter에 UTL_FILE_DIR = /d02/ora9i/oracle/dbs/ora9i 과 같이 정의되어 있어야 한다.
Note : 테스트 시점 현재(2001/02/09, Oracle9i Beta shiphome release10), 이 문장을 수행하면 flat file이 무한정 커지면서 sql prompt가 떨어지지 않는 문제가 발생한다. 이는 NLS와 관련된 Bug.1622481로 등록되어 있다.
Extracting the Dictionary to Redo Log Files
Dictionary 정보를 redo log file에 기록하는 동안 어떠한 DDL 문장도 수행될 수 없기 때문에, dictionary snapshot과 그 시점의 운영중인 dictionary 정보는 항상 일치한다. 하지만 이 방법은 데이터베이스 자원을 이용하기 때문에 업무가 집중되는 시간을 피해서 해야 할 것이다. Flat file에 기록하는 것보다 빠르며, 보통의 경우 redo log file을 archiving하고 있으므로 flat file을 이용하는 경우에 비해 back up에 필요한 과정은 줄어든다.
사용하는 방법은 다음과 같다.
| EXECUTE sys.DBMS_LOGMNR_D.BUILD( OPTIONS => sys.dbms_logmnr_d.STORE_IN_REDO_LOGS); |
Note : 이 모드로 LogMiner를 시작할 때는 아래와 같은 option을 주어야 한다.
| EXECUTE sys.DBMS_LOGMNR.START_LOGMNR( OPTIONS => sys.dbms_logmnr.DICT_FROM_REDO_LOG); |
Using the Online Catalog
현재 운영중인 데이터베이스의 dictionary를 참조하게 할 수도 있다. 하지만 redo log file이 생성될 시점과 현재의 dictionary는 서로 다를 수 있다는 점을 고려해야 할 것이다. 또한 이 모드로는 다음에 설명할 ‘DDL tracking’을 사용할 수 없다.
이 모드를 사용할 때는 DBMS_LOGMNR_D.BUILD의 과정이 필요없으며 LogMiner를 시작할 때는 다음과 같이 사용하면 된다.
| EXECUTE sys.DBMS_LOGMNR.START_LOGMNR( OPTIONS => sys.dbms_logmnr. DICT_FROM_ONLINE_CATALOG); |
Direct DDL statement support
Oracle8i까지는 DDL에 대해서 LogMiner를 통해 조회하면 사용자가 직관적으로 알아보기가 힘들었다. 예를 들어 다음과 같은 DDL이 있었다고 하면,
SQL> drop table tab1;
LogMiner를 시작한 후 v$logmnr_contents에서 조회하면 해당 문장을 SQL_REDO 컬럼에서 바로 볼 수는 없으며 COL$, OBJ$, TAB$ 등에 대한 DML로 표시되어 있음을 보고 추정해야 하는 어려움이 있었다. (‘DROP TABLE’은 내부적으로 COL$, OBJ$ and TAB$에 대해 DELETE를 수행한다.)
| SQL> column seg_name format a15 trunc SQL> select seg_name, operation, scn, count(*) from v$logmnr_contents 2 where operation != ‘INTERNAL’ 3 group by seg_name, operation, scn 4 order by scn; SEG_NAME OPERATION SCN COUNT(*) |
하지만 Oracle9i에서는 이러한 DDL문장도 v$logmnr_contents의 SQL_REDO에 바로 기술되어진다. (아래의 ‘테스트’ 참조)
Tracking of DDL Statements
Oracle9i부터는 source dictionary가 flat file 혹은 redo log file에 있을 때, DDL_DICT_TRACKING 이라는 option을 사용하면 LogMiner 내부적으로 별도의 dictionary(LogMiner internal dictionary)를 두어 log mining 동안의 DDL 문장을 internal dictionary에 반영시킬 수 있는 기능이 제공된다.
다음의 예를 살펴보자.
| execute sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.DDL_DICT_TRACKING + sys.dbms_logmnr.NO_DICT_RESET_ONSELECT + sys.dbms_logmnr.DICT_FROM_REDO_LOGS); |
Note : dbms_logmnr.NO_DICT_RESET_ONSELECT – 매 SELECT에 대해 dictionary를 reload시키지 않는 option
위와 같은 option을 이용해 LogMiner를 수행하고 난 후, 다음의 SQL 문장을 수행하면 다음과 같은 결과가 나온다.
| SELECT sql_redo FROM sys.v$logmnr_contents;
SQL_REDO insert into “SCOTT”.”CUSTOMER”(“NAME”,”PHONE_DAY”,”PHONE_EVENING”) insert into “SCOTT”.”CUSTOMER”(“NAME”,”PHONE_DAY”,”PHONE_EVENING”) commit; alter table scott.customer drop (phone_evening) insert into “SCOTT”.”CUSTOMER”(“NAME”,”PHONE_DAY”) values (’Gabriel commit; |
위에서 알 수 있듯이, CREATE TABLE, ALTER TABLE 문장이 제대로 LogMiner의 내부 dictionary에 제대로 반영되어 있음을 알 수 있다. 최종적으로 내부 dictionary에는 scott.customer가 단지 두개의 컬럼만을 가지고 있다. (ALTER TABLE에 의해 phone_evening 컬럼이 삭제되었으므로)
Note : 이 상황에서 다시 SQL_REDO를 select하면 dbms_logmnr. NO_DICT_RESET_ONSELECT option에 의해 dictionary 정보가 reload되지 않으므로 처음의 두 INSERT 문장에 대해 읽기 쉽게 변환을 시켜주지 않는다. 이를 방지하려면 dbms_logmnr.NO_DICT_RESET_ONSELECT option을 주지 않으면 된다.
Ability to skip log corruption
Log corruption을 만나게 되면 기본적으로 LogMiner는 종료된다. 이 corruption이 중요치 않을 경우, 때로는 이를 무시하고 corruption 이후의 log에 대해서 계속 조사할 필요가 있을 것이다. 이를 가능하게 하려면 dbms_logmnr.start_logmnr()를 호출할 때 SKIP_CORRUPTION option을 주면 된다.
V$LOGMNR_CONTENTS view를 조회할 때 corruption 이후의 row들은 “Log File Corruption Encountered” 메시지로 marking되며, 최종적으로 얼마만큼의 블록들이 skip되었는지 알려준다.
Ability to show only rows belonging to committed transactions
기본적으로 LogMiner는 트랜잭션이 commit되었던지 rollback되었던지, log file에 기록된 모든 row들을 보여준다. 하지만 때로는 commit된 트랜잭션들에 대해서만 보기를 원할 것이다. 이를 위해서는 dbms_logmnr.start_logmnr() 호출시 COMMITED_DATA_ONLY option을 주면 된다.
Limitations
LogMiner의 DDL 문장 지원이 table drop / truncate에 대한 복구를 지원한다는 말은 아니다. (해당 문장에 대한 UNDO는 존재하지 않는다.) 다만 어느 시점으로 복구를 하면 되는지에 대한 기준을 제시할 수 있는 것이다.