Oracle LogMiner 의활용 Tips Getting the most out of MetaLink 천봉격, 김주연 한국오라클 ( 주 ) 제품지원실 기술적인질문은채팅으로 이번세미나에선 Oracle 8i, 9i, 10g 에서 Oracle Logminer 의활용팁에대해알아보도록하겠습니다. 본세미나에서는 Oracle 에서제공되는 Logminer 을사용하여 Online/Offline Redo Logs 을분석함으로써 Logical Corruption, 사용자에러, DML 추적, 성능변화에대한정보를추출함으로써 Database 관리자에게도움을드리고자합니다.
목 차 LogMIner 소개 Redo Logs 와 Dictionary LogMiner Dictionary 추출방법 9i LogMiner Features LogMiner Options 요약 Supplemental Logging 제약사항 일반적인 LogMiner 수행단계
LogMiner 소개 Online/Offline Redo Logs 에서 Data 추출 분석하려는 Redo Logs 지정실행 Redo Logs 검색후 V$LOGMNR_CONTENTS 통하여출력 LogMiner 는 8.1 버전또는상위버전에서만실행이가능하지만 Oracle 8.0 버전의 Redo Logs 파일을분석할수있습니다. 분석하고자하는 Instance 와같은 Database Character Set, 동일한 Hardware 을사용해야합니다. 분석하고자하는 Instance 와 Log 가생성된 Instance 는 Block Size(DB_BLOCK_SIZE) 가동일해하며, 만일 Block Size 가다르다면 Archive Log 에 Corruption 이발생했다는메시지가출력됩니다.
1. LogMiner 개요 Log 파일에는 Oracle Database 에서발생한작업에관련된많은양의유용한정보가저장되어있지만 Oracle 8i 이전에는이정보를얻기위한방법이없었습니다. LogMiner 는 Online 혹은 Offline Redo Log 파일에대한읽기 / 분석 / 해석을위해서 SQL 문장을사용하여수행할수있습니다. Log 파일의분석작업결과는다음과같은용도로사용될수있습니다. 첫째 Transaction 별, 사용자별, 테이블별, 시간대별로 Database 에가해진변경작업에대한추적을할수있습니다. 즉, 어떤사용자가자료를수정했는지또한그작업을수행하기이전의데이타값은무엇이었으며작업이후의데이타값이어떤값으로변경되었는지확인할수있습니다. 데이타변경과과거시점의자료추적기능및그작업을취소 (Undo) 할수있는기능은보안과관리에매우중요한수단으로사용될수있습니다. 둘째논리적인자료의변조 ( 예를들면, 실수로자료를지우고계속작업을진행하는경우 ) 가언제발생했는지를정확히알수있으며, 변조된이전시점까지일관성이보장된상태로 Database 를복원하기위해서어떤방법으로 Recovery 를시작할것인지를결정하는데있어서중요한역할을합니다. 셋째다양한형태의시간적추이에따른 Application 사용과자료의패턴분석이가능하므로 Tuning 과성능계획을수립하기위한보조자료로사용될수있습니다. 2. LogMiner 세부사항 LogMiner 는 Log 파일을추출하여, 그안에저장되어있는내용을 Database 에가해진논리적인작업을표현하는 SQL 문장으로변환합니다. V$LOGMNR_CONTENTS View 는본래의작업을표현하는 SQL (SQL_REDO Column) 과그작업을취소할수있는 SQL(SQL_UNDO Column) 을보여줍니다. SQL_UNDO 를실행함으로써원래의상태로되돌릴수있습니다. 기타 V$LOGMNR_CONTENTS 를통해얻어낼수있는정보는다음과같습니다. - 언제논리적인변경이발생했는지를확인하고, 그것에대한복구작업 (Incomplete recovery) 을수행하기위해필요한 SCN(system change number) 또는시간정보를얻어냅니다. - 특정 table에발생한변경과특정사용자로부터발생한변경을확인합니다.
Redo Logs 와 Dictionary 1. Redo Logs 2. Dictionary 기능 LogMiner 을시작하기전에 LogMiner 가 Redo Logs 와 Dictionary 파일들과함께어떻게작동하는지에대한원리를이해하는것은중요하며, 이것은정확한결과를얻어서시스템자원을계획하는데도움을줄수있습니다.
1. Redo Logs Redo Logs 파일지정과 V$LOGMNR_CONTENTS 최소의 Supplemental Logging 활성화 V$LOGMNR_CONTENTS 의순차적추출 여과범위또는검색조건 LogMiner 사용시분석하고자하는 Redo Logs 파일명을지정해야하며, 명시한 Redo Logs 로부터정보를검색하여 V$LOGMNR_CONTENTS View 을통하여결과를출력합니다. 정확한결과를얻기위해서최소한의 Supplemental Logging 이활성화가되어야하며, V$LOGMNR_CONTENTS View 을통한 SQL 문장을실행시분석된 Redo Log 파일정보를순차적으로파일로추출됩니다. Redo Log 파일에서분석된정보는 V$LOGMNR_CONTENTS View 의 Rows 값들로출력되며, 이과정은여과범위또는검색조건이만족되거나 Redo Log 파일의끝까지검색할때까지계속됩니다. Redo Logs 파일의필수요건은다음과같습니다. Redo Logs 파일은 8.0 버전에서제공될수있지만, 9.0.1 버전에서소개된 LogMiner 의몇가지기능은단지 9i 또는이후버전에서만동작을합니다. 9.2 버전에서 LOB 과 LONG Datatypes 들이지원되지만 9.2 Database 에서생성된 Redo Logs 파일에서만가능한기능입니다. Redo Logs 파일들은 LogMiner 가실행되는 Database 와동일한 Character Set 을사용해야합니다. 일반적으로 Redo Logs 파일들을분석하기위해서는 Redo Logs 가생성된같은 Database 에서생성된 Dictionary 파일이필요합니다. LogMiner Dictionary 을위해 Online Catalog 을사용한다면, 단지 LogMiner 가기동된 Database 에서만 Redo Logs 을분석할수있습니다. LogMiner 는분석하고자하는 Redo Logs 을생성한같은 Hardware 에서실행하는것을권장합니다. LogMiner 실행시정확한 Redo Logs 을지정하는것이중요합니다. 현재 LogMiner Session 에서어떠한 Redo Logs 을분석할지결정하기위해각각의 Redo Log 의열을포함하는 V$LOGMNR_LOGS View 을조회할수있습니다.
1.2.2 LogMiner Dictionary 기능 Redo Log 분석시 Dictionary 사용권고 Database 시점이동일한 Dictionary 사용 Object 해석을위한 Dictionary 사용권장 Dictionary 부재시 Hexa 값으로출력됨. 다른서버에서 Redo Logs 분석시 Dictionary 사용 Redo Log 파일들의전체내용을분석하려면 Dictionary 를사용한 PL/SQL Package 생성을권고하며, Database 정보와그시점에생성된 Dictionary 파일을사용합니다. 내부오브젝트식별자들과 DataType, 외부데이터포맷해석을위해 Dictionary 를필요로하며, 반드시필요하지않지만권장사항입니다. Dictionary 파일이없다면, 상응하는 SQL 문장생성을위해오브젝트이름과 Column 값들을 Hexa 로내부오브젝트 ID 값을생성합니다. Source Database 에접속하지않고다른 Database Instance 에서 Redo Logs 을분석하려면 Dictionary 파일을사용해야합니다. 다음은 Dictionary 파일이없을경우입니다. 실제실행된 SQL 문장 : INSERT INTO emp(name, salary) VALUES ('John Doe', 50000); LogMiner 생성문장 : insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'), hextoraw('c306'));
LogMiner Dictionary 추출방법 1. Online Catalog 사용 2. Redo Log 파일들로부터 LogMiner Dictionary 추출 3. Flat 파일로 LogMiner Dictionary 추출 4. LogMiner Dictionary 의사결정선택 Tree
1. Online Catalog 사용 Redo Log 파일들이생성된 Source Database 접근이가능할경우. 관련 Table 들에 Column 정의에변경이없을경우. - 가장효율적이며사용이용이한기능이다. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(- OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); Online Catalog 사용은분석할 Logs 을추가후 DBMS_LOGMNR.START_LOGMNR Procedure 에 DICT_FROM_ONLINE_CATALOG Option 을사용하여 LogMiner 가 Dictionary 생성없이 Database Data Dictionary 을사용할수있습니다.
2. Redo Log 로 Dictionary 추출 Source Database 접근불가능한경우 Flat 파일추출보다속도향상 Multiple Redo logs 포함 관련 Redo Logs 가 Archive 된경우 SQL> execute DBMS_LOGMNR_D.BUILD(options => - dbms_logmnr_d.store_in_redo_logs); 사용가능한경우는 Source Database 접근이되지않는경우, 시간이소요되는작업이며, Database 사용자가많지않은경우, Flat 파일로추출하는것보다빠른경우, Dictionary 크기가고려사항이며, 여러개의 Redo Logs 을포함할수있는경우, 관련 Redo Log 파일들이 Archive 되어있는경우로다음의 Query 을사용하여 Redo Log 파일의시작과끝을추출된 Dictionary 에서확인합니다. SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'; SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES'; LogMiner Dictionary 을 Redo Log 파일에저장하기위해서는다음의제약사항이존재합니다. - DBMS_LOGMNR_D.BUILD 는 9i 버전에서실행되어야합니다. - Database 는 Archive Mode 로운영되어야합니다. - COMPATIBLE Parameter 는 9.0.1 로설정되어야합니다. - Dictionary 는분석된 Redo Logs 와같은 Database 에속해야합니다. - Dictionary 을추출하는동안어떠한 DDL 도허용되지않습니다. SQL> execute DBMS_LOGMNR_D.BUILD(options => - dbms_logmnr_d.store_in_redo_logs);
3. Flat 파일로 Dictionary 추출 이전버전과상호호환성보장 Transaction 일관성보장하지않음 SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', - > '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); 이기능은이전버전과상호호환성을보장하며, Transaction 의일관성은보장하지는않습니다. Oracle 은 Flat 파일로 Dictionary 추출을권장하지않으며, Online Catalog 을이용하거나 Redo Log 파일로 Dictionary 추출을권장합니다. SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', - > '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); STORE_IN_FLAT_FILE 기능을명시하지않고파일명이나위치를지정할수있으며, 이결과는동일합니다. Flat 파일로 Dictionary 추출예제를설명하겠습니다. 1. initsid.ora 파일에 UTL_FILE_DIR 의경로를설정합니다. UTL_FILE_DIR=/oracle/logminer 2. Dictionary 생성을위해서 DBMS_LOGMNR_D Package 을사용합니다. Dictionary 가일관성있는 Snapshot 을가졌는지확인하며, 생성할동안 DDL 을허용되지않습니다. SQL> execute DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/logminer', - options => dbms_logmnr_d.store_in_flat_file); 3. DBMS_LOGMNR.ADD_LOGFILE Procedure 을사용하여분석할 Logs List 을만듭니다. SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/ora/ora901/oradata/V901/redo01.log',- options => dbms_logmnr.new); SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/ora/ora901/oradata/V901/redo02.log',- options => dbms_logmnr.addfile); 4. 방금생성한 Dictionary 을명시하고분석을시작합니다.
4. LogMiner Dictionary 선별을위한의사결정 Tree Source Database 접근이가능할것인가? 예 아니오 Database 가 Write Mode 로접근이가능할것인가? 예 아니오 Instance 가기동될것인가? 예 Column 정의가변경될것인가? 아니오 Column 정의가변경될수있을것인가? 아니오 아니오 예 Database 가 Open 될것인가? 예 예 Online Catalog 의 Dictionary 사용 Redo Log 파일의 Dictionary 사용 추출된 Flat 파일의 Dictionary 사용 상황별로 LogMiner Dictionary 을선택을도와주는의사결정 Tree 입니다.
9i LogMiner Oracle LogMiner Viewer GUI 인터페이스제공 Redo Logs 로의 Dictionary 파일생성가능 COMMITTED_DATA_ONLY 사용 DDL 지원 Redo Corruption Skip 기능 9.2 버전부터 Oracle LogMiner Viewer 란 GUI 인터페이스를통하여 LogMiner 기능을접근할수있는 Option 이제공되며, Oracle Enterprise Manager 의일부분입니다. 모든 LogMiner 테이블들은 Default 로 SYSTEM Tablespace 에생성이되며, 다음의문장을사용하여모든 LogMiner 테이블들을또다른 Tablespace 에생성할수있습니다. SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE( <tablespace_name> ); Commit 된 Transaction 들만검색하기위해 COMMITTED_DATA_ONLY Option 을명시하지않으면, SCN 순서로모든 Rows 가출력되며, SCN 순서는일반적으로 Media Recovery 시에적용됩니다.
9I 의 LogMiner New feature 를정리하면다음과같습니다. - DDL 지원단, 9i 이상에서생성된 Redo/Archive Log 파일만분석가능합니다. (V$LOGMNR_CONTENTS 의 OPERATION Column 에서 DDL 을확인합니다.) - LogMiner 분석을위해생성한 Dictioanry 정보를 Online Redo 에저장가능하며, 반드시 Archive Log Mode 로운영중이어야합니다. DBMS_LOGMNR_D.BUILD 를사용하여 Dictionary 파일을생성하며, 기존 Flat 파일또는 Redo Log 에도생성이가능합니다. 예 ) Flat file SQL> EXECUTE dbms_logmnr_d.build (DICTIONARY_FILENAME => 'dictionary.ora',dictionary_location => '/oracle/database',options => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); 예 ) Redo log SQL> EXECUTE dbms_logmnr_d.build (OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); - Redo Log Block Corruption 이발생하였을경우 Corruption 된부분을 Skip 하고분석할수있습니다 8i 에서는 Log Corruption 발생시 LogMiner 가종료되고분석을위해다시시도해야했지만, 9i 에서는 DBMS_LOGMNR.START_LOGMNR 의 SKIP_CORRUPTION Option 으로 Skip 이가능합니다. - Commit 된 Transaction 에대해서만출력할수있습니다. (DBMS_LOGMNR.START_LOGMNR 의 COMMITTED_DATA_ONLY Option 을사용합니다.) - Index Clustered 와연관된 DML 을분석할수있습니다. (8i 에서는제공되지않았던기능입니다.) - Chained 와 Migrated Rows 분석이가능합니다. LogMiner Views - V$LOGMNR_CONTENTS - 현재분석되고있는 Redo Log 파일의내용입니다. - V$LOGMNR_DICTIONARY - 사용중인 Dictionary 파일입니다. - V$LOGMNR_LOGS - 분석되고있는 Redo Log 파일입니다. - V$LOGMNR_PARAMETERS - LogMiner 에 Setting 된현재의 Parameter 값입니다.
Features (1) DDL_DICT_TRACKING Option 을사용한 DDL 문장추적 -Flat 또는 Redo Logs 의 Dictionary 일경우 - Default : 비활성화예제 : SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING); 또는 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.DICT_FROM_REDO_LOGS); 9.0.1 버전에서소개된 LogMiner 의몇가지기능은 9i 또는그상위버전에서생성된 Redo Logs 에서만동작합니다. DDL_DICT_TRACKING Option 을사용한 DDL 문장추적은 Default 로비활성화되어있으며, 활성화하려면 LogMiner 기동시 DDL_DICT_TRACKING Option Parameter 을사용합니다. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING); DDL 문장확인은다음과같이할수있습니다. SQL> SELECT USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME = SCOTT AND OEPRATION = DDL ; DDL_DICT_TRACKING Option 사용시고려사항은 DICT_FROM_ONLINE_CATALOG 과함께사용하지않으며, Database 가기동되어야만합니다.
Features (2) 재구성된 SQL 문장실행 - DBMS_LOGMNR.NO_SQL_DELIMITER 사용한세미콜론종결비활성화 출력된 Data 구성 - PRINT_PRETTY_SQL 사용한읽기쉬운출력형식 Redo Logs 로부터실데이타추출 - LogMiner 의실제Data 값기준Data 조회 - Redo Logs 로부터 LogMiner Data 추출 DBMS_LOGMNR.MINE_VALUE - DBMS_LOGMNR.COLUMN_PRESENT 재구성된 SQL 문장실행은 SQL_REDO 와 SQL_UNDO 문장들이세미콜론 (;) 으로종결되며, 세미콜론종결을비활성화시키려면 LogMiner 기동시 DBMS_LOGMNR.NO_SQL_DELIMITER Option 을사용합니다. 출력된 Data 구성은재구성된 SQL 문장이많은 Columns 을포함하여읽기가어려울경우, LogMiner 는 DBMS_LOGMNR.PRINT_PRETTY_SQL Option 을사용하여해결할수있습니다. 예제 ) insert into "SCOTT"."EMP" values "EMPNO": 5505, "ENAME": "Parker", "SAL": 9000, "DEPTNO": NULL; Redo Logs 로부터 LogMiner Data 추출은 DBMS_LOGMNR.MINE_VALUE 와 DBMS_LOGMNR.COLUMN_PRESENT 의두함수에의해서수행되며, 이두함수는 DBMS_LOGMNR Package 의일부입니다. V$LOGMNR_CONTENTS View 의 REDO_VALUE, 와 UNDO_VALUE Column 에의해제공된 Mine 함수를지원합니다. DBMS_LOGMNR.MINE_VALUE 와 DBMS_LOGMNR.COLUMN_PRESENT 함수들은 V$LOGMNR_CONTENTS 의 Column 값보다더상세한분석을위해서 Redo Log 파일로부터실제데이타접근을위한함수입니다. MINE_VALUE 함수예제 ). WHERE OPERATION = UPDATE AND DBMS_LOGMNR.COLUMN_PRESENT(REDO_VALUE, SCOTT.EMP.SAL ) = 1 AND DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, SCOTT.EMP.SAL ) = 1 AND (DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'SCOTT.EMP.SAL ) > DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'SCOTT.EMP.SAL ));
Features (3) MINE_VALUE 함수로부터 NULL 반환 반환된 Data 선별 - Commit 된 Transaction 만출력 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY); - 시간기준으로선별 - SCN 기준으로선별 MINE_VALUE 함수가 NULL 값을반환한다면명시한 Column 이 Redo 나 Undo 에존재하지않거나있지만 NULL 값을가진경우이며, 이것을구분하기위해서 DBMS_LOGMNR.COLUMN_PRESENT 함수를사용합니다. DBMS_LOGMNR.COLUMN_PRESENT 함수를사용하여 1 이출력되면 Redo 또는 Undo 에해당 Column 이존재하는경우이며, 0 이출력되면존재하지않는경우입니다. MINE_VALUE 함수NULL 값반환예제 Select * from V$logmnr_contents WHERE OPERATION = 'UPDATE' AND DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') > DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.EMPLOYEES.SALARY'); 위와같이조회시에조건에맞는값이없다면 WHERE OPERATION = 'UPDATE' AND DBMS_LOGMNR.COLUMN_PRESENT(REDO_VALUE, 'HR.EMPLOYEES.SALARY') = 1 AND DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, 'HR.EMPLOYEES.SALARY') = 1; COLUMN_PRESENT 함수사용으로 Redo 또는 Undo 에값이존재여부확인 V$LOGMNR_CONTENTS View 에반환된정보의선별과추출성능에관련된몇가지방법을사용할수있습니다. 이기능은 LogMiner 기동시명시해야합니다. 이기능을사용하면단지 Commit 된 Transactions 만 V$LOGMNR_CONTENTS View 에출력되며, Rollback 된 Transactions, 진행중인 Transactions, 내부 Operations 을제외합니다. 활성화하려면 LogMiner 기동시명시해야합니다. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
Features (4) Redo Corruptions Skip - 기동시 SKIP_CORRUPTION 을사용하여 Skip - Corrupt 된 Redo 을만날때관련정보출력 -Skip 된 Records 는진행중인 Transactions 은반영되지않음 - Corruption 시비정상종료방지 DBMS_LOGMNR.START_LOGMNR 에 SKIP_CORRUPTION Option 을사용하면, Redo Logs 파일의 Corruptions 발생후 V$LOGMNR_CONTENTS View 조회시 Corruption 을 Skip 할수있습니다. Corrupt 된 Redo 을만날때마다 OPERATION Column 에 CORRUPTED_BLOCKS 란값을출력하며, STATUS Column 에 1343 을출력하고, INFO Column 의 Blocks 번호는 Skip 됩니다. Skip 된 Records 는 Corrupt 된 Blocks 중에진행중인 Transactions 의변경정보를포함할수있으며, 이변경은 V$LOGMNR_CONTENTS View 의 Data 에반영이되지않습니다. 사용목적은 Redo Log 파일에서처음 Corruption 을만났을때비정상종료되는것을막기위한것입니다. 다음은 Redo Corruptions Skip 예제로원하는 Redo Log 파일들을 SKIP_CORRUPTION Option 설정없이추가합니다. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(logfilename => /usr/oracle/data/db1arch_1_16_482701534.log' options => DBMS_LOGMNR.NEW); SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(); SQL> SELECT operation, status FROM V$LOGMNR_CONTENTS; ERROR at line 3: ORA-00368: checksum error in redo log block ORA-00353: log corruption near block 6 change 73528 time 11/06/2002 ORA-00334: archived log: /usr/oracle/data/dbarch1_16_482701534.log - LogMiner 을재기동후 SKIP_CORRUPTION Option 을명시합니다. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(options => DBMS_LOGMNR.SKIP_CORRUPTION); SQL> SELECT operation, status, info FROM V$LOGMNR_CONTENTS; OPERATION STATUS INFO -------------------- ------------ --------------------------- CORRUPTED_BLOCKS 1343 corrupt blocks 4 to 19 skipped
LogMiner Options 요약 DICT_FROM_ONLINE_CATALOG DICT_FROM_REDO_LOGS CONTINUOUS_MINE (Time and SCN range Scan) COMMITTED_DATA_ONLY SKIP_CORRUPTION NO_SQL_DELIMITER PRINT_PRETTY_SQL STARTIME, ENDTIME STARSCN, ENDSCN 위의항목들은 DBMS_LOGMNR.START_LOGMNR 에명시할수있는 Option Parameter 들입니다. 10g 에서 CONTINUOUS_MINE 에확장된기능이추가되었습니다. 9.2 버전에서소개된 CONTINUOUS_MINE 은사용하기전에 DBMS_LOGMNR.ADD_LOGFILE 을사용하여해당세션에서최소한하나의 Archive Logfile 을직접추가해주어만했지만, 10g 는최소한 START_TIME 과 START_SCN Parameter 만으로사용이가능합니다. 이것은 LogMiner 가 Controlfile 을읽어서요청한시간과 SCN 을만족하는 Archive Logfile 을자동으로결정합니다. CONTINUOUS_MINE Option 과 END_TIME,END_SCN 을같이사용할경우에 SQL*PLUS 세션에 'SET ARRAYSIZE 1' 을사용해야합니다. 그렇지않으면 SQL 내부버퍼에 Full 이발생하면서 V$logmnr_contents 조회시 Hang 현상이나타날수있습니다.
예제 ) A. 현재부터 2 시간동안 SCOTT.EMP Table 의변경 (username, operation) 을추적합니다. SQL> EXEC DBMS_LOGMNR.START_LOGMNR(- > STARTTIME => SYSDATE, - > ENDTIME => SYSDATE + 2/24, - > OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + - > DBMS_LOGMNR.CONTINUOUS_MINE); SQL> SET ARRAYSIZE 1; SQL> SELECT username,operation FROM v$logmnr_contents WHERE seg_owner='scott' AND table_name='emp'; Logs 생성후자동으로 LogMiner 세션에 Archive File 을자동으로추가하고, 위의문장을출력합니다. B. 2 시간전부터종료시간은정하지않는동안 SCOTT.EMP Table 의변경 (username, operation) 을추적합니다. (Records 는 DML 이생성되면서계속출력합니다.) SQL> EXEC DBMS_LOGMNR.START_LOGMNR( STARTTIME => SYSDATE - 2/24, - > OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + - > DBMS_LOGMNR.CONTINUOUS_MINE); SQL> SET ARRAYSIZE 1; SQL> SELECT username,operation FROM v$logmnr_contents WHERE seg_owner='scott AND table_name='emp'; 참고 : LogMiner 에 ENDTIME 만명시하는것은정상적인사용법이아니며, 최소한 STARTTIME 은연속적인 Mining 을위한최소요구사항입니다.
Supplemental Logging (1) Application 의추가Column 정보필요성 - Application 의필요 - Database 의 ROWID 이동성 - 전체 Row 의변경값추적 9.0.1 Supplemental Logging 기능은 8.1.7 이하사용불가 9.2 와 10g 는 Default 로 Supplemental Logging 기능지원 Instance 와 Media Recovery 을위한 Data 는 Redo Log 파일들에자동기록되지만, Redo를참조하려는 Application 들은 Redo Log 파일에기록되어야하는추가 Columns 들이필요할수있습니다. 9.0.1 버전의 Supplemental Logging 기능은 8.1.7 이하의모든버전에서사용할수없습니다. LogMiner 기능전체를사용하고자한다면 Supplemental Logging 기능을활성화해야합니다. 추가 Columns 들이필요하게되는상황은다음과같습니다. 다른 Database 에재구성된 SQL 문장을적용하려는 Application 은 V$LOGMNR_CONTENTS View 에서출력된재구성문장이 ROWID 가아닌 Primary Key 와같이 Unique 하게확인할수있는 Columns Set 에의해서 Update 문장을확인해야할경우 Database 의 ROWID 는또다른 Database 에는값이다르며의미가없을경우 Application 은단지수정된 Columns 이아닌기록된전체 Row 의 Before-Image 가필요할수있으며, Row 값의변경을추적하는것이더중요할수있을경우가있습니다. 이기능은 9i 버전에서는 DML 의일부분으로실제변경되지않은 Columns 정보를기록하는기능으로다른 Database 에 Tables Copys 본을유지하는데유용합니다. 9i 이전에는 LogMiner 는 WHERE 절의 ROWID 값으로 Columns 의변경과 Row 확인작업을하였지만, ROWIDs 는다른 Database 에서는의미가없었습니다. 그래서다른 Database 에서 LogMiner 을사용하여 SQL 추출이가능하지않았으며, ROWID 는 V$LOGMNR_CONTENTS.sql_red 포함되었지만필요하면삭제될수도있었습니다.
Supplemental Logging (2) Supplemental Log Groups 두가지유형 - 비조건적 Supplemental Log Groups - 조건적 Supplemental Log Groups Log Groups 은 System 자동또는 User 정의생성 - Database-Level Supplemental Logging - Table-Level Supplemental Logging 비조건적 Supplemental Log Groups 은명시된 Columns 의 Before-Images 는 Row 가 Update 될때마다기록되며, Update 는지정된 Columns 과관련없이기록됩니다. 이것은 Log Group 에연속적으로참조됩니다. 조건적 Supplemental Log Groups 은지정된모든 Columns 의 Before Images 는 Log Group 이적어도하나의 Columns 이 Update 가될경우에만기록이됩니다. Log Groups 은 System 자동또는 User 정의로생성될수있으며, Supplemental Logging 유형에는 Database-Level Supplemental Logging 과 Table-Level Supplemental Logging Level 이있습니다. 최소의 Supplemental Logging 은 LogMiner 가 DML 변경과관련된 Redo Operations 을확인하고, 그룹화하고, 통합하기위해필요한최소량의정보를기록합니다. Cluster Tables 과같이 Chained Rows 와다양한 Storage 정렬을지원하기위한충분한정보획득을보장합니다. 최소의 Supplemental Logging 을활성화하기위한문장은다음과같습니다. SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Supplemental Logging (3) Supplemental Logging 의제약사항 - Index clusters, Chained rows 와 Migrated rows - Direct-path inserts (ARCHIVELOG mode 활성화되어있어도지원불가 ) - LogMiner Dictionary 을 Redo Log 파일들로추출 - DDL 추적 - 검증키정보를가진 SQL_REDO 와 SQL_UNDO 생성 - LONG 과 LOB Datatypes Row Chaining 은단일테이블상의특정 Row 의길이가증가해서더이상동일한 Data Block 에들어갈수없을때발생합니다. 이때 RDBMS 는또다른 Data Block 을찾습니다. 이 Data Block 은원래 Block 과연결되어있으며이것을 Chained Rows 라고합니다. 이경우 Data Block 이하나의 I/O 작업과동일한양을수행하기위해두개의 I/O를사용해야한다는점에서데이터베이스성능을빠르게약화시킬수있습니다. Data Block 상의하나의 Row 는길이가증가하면서갱신되며, Block 의 Freespace 가 0 % 일때, Row 는 Migration 이발생합니다. 전체 Row 가들어갈만한크기의새로운 Block 에 Row 에대한 Data 가 Migration 되며이것을 Migrated Rows 라고합니다. 이경우도 Row 에대한정보를가져오기위해하나이상의 Data Block을반드시읽어야하므로 I/O 성능이저하됩니다.
Database Level 검증키 Logging(1) SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; PRIMARY KEY 는비조건적인 Supplemental Log Group 으로 System 에서자동생성된다. Table 에 Primary Key 가없지만, Non-Null Unique Index Key 조건또는 Index Keys 을하나또는두개이상을가지고있다면, Unique Index Keys 중하나가변경된 Row 을유일하게확인하는수단으로선택된다. Table 이 Primary Key 와 Non-Null Unique Index Key 모두없을경우에는 LONG 이나 LOB 을제외한모든 Columns 이추가적으로기록되며, 이것은그 Row 을위해ALL Supplemental Logging 을명시한것과동일하다.
Database Level 검증키 Logging(2) Oracle 은 Database-Level Primary Key 을위한 Supplemental Logging 을할경우, 모든또는대부분의 Tables 들을 Primary 또는 Unique Index Keys 설정을권고한다. Database Level Primary Key Logging 을활성화 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; Unique Index 조건적 Supplemental Log Group 활성화 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; Foreign Key 조건적 Supplemental Log Group 활성화 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY ) COLUMNS; Database Level 검증키 Logging Database Level Supplemental Logging 은 Primary Keys 기록을지정할수있으며, Unique Indexes 또는두가지모두지정이가능합니다. 이기능이활성화되면, DML 이수행될때마다, Primary Keys 나 Unique Indexes 와관련된 Columns 이 DML 과관련이없더라도기록이됩니다. Primary Keys 와 Unique Indexes 모두를위한 Database Level Supplemental Logging 을다음과같이실행합니다. SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; 위의실행은아직 Parse 가되지않은문장에만영향을주며, Cursor Cache 영역의모든 DML Cursors 을 Invalid 상태로놓기때문에 Cache 을다시 Valid 할때까지성능이저하될수있습니다.
1. EMPID 라는 Primary Key 을가진 EMP Table 이있습니다. --> Primary Key Columns 에대한 Supplemental Logging 이활성화되었다면, EMP Table 에어떠한문장도 EMPID Column 을기록합니다. SQL> select * from emp; EMPID SAL ---------- ---------- 10 100000 SQL> update emp set sal=150000; 1 row updated. Supplemental Logging 이설정되지않은 V$LOGMNR_CONTENTS.sql_redo 은다음과같습니다. SQL> update "SCOTT"."EMP" set "SAL" = '150000' where "SAL" = '100000' and ROWID ='AAABOaAABAAANZ/AAA'; 이전에 test_always 란 Log Group 으로설정이되어있다면다음과같습니다. SQL> update "SCOTT"."EMP" set "SAL" = '150000' where "EMPID" = '10' and "SAL" ='100000' and ROWID = 'AAABOaAABAAANZ/AAA'; 2. Database Level Supplemental Logging 을비활성화하는문장은다음과같습니다. SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
Table Level 검증키 Logging SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; 위의문장을실행할경우, Database 의 Table 에하나의 Column 이변경되면, LOBs, LONGs 와 ADTs 을제외한그 Column 을포함하는모든 Row 는 Redo Log 파일에기록된다. 사용자정의비조건적 Supplemental Log Groups SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID) ALWAYS; 사용자정의조건적 Supplemental Log Groups SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME,DEPARTMENT_ID); Table Level 검증키 Logging Table Level Supplemental Logging 은사용자가 Log Groups 을정의하고 Redo 변화중에어느 Columns 을항상기록할지지정할수있으며, 이것은 Table 대 Table 기준으로수행됩니다. ALWAYS 키워드는하나의 Row 가 Update 되면, 그 Group 의모든 Columns 들이기록되게할때사용하며, 만일 ALWAYS 가기술되지않으면, Group 의 Columns 들중에적어도하나가 Update 될때해당 Columns 들이기록됩니다.
CREATE TABLE 또는 ALTER TABLE 문장을사용하여 Log Group 을정의합니다. 1. Log Group 을가진 Table 을생성합니다. SQL> CREATE TABLE test_log(c1 number, c2 number, c3 number, SUPPLEMENTAL LOG GROUP test_always(c1,c2) always); SQL> select * from test_log; C1 C2 C3 ---------- ---------- ---------- 1 2 3 2. Log Group 에속하지않은 Column 을변경합니다. SQL> update test_log set c3=99; 1 row updated. Table Level Supplemental Logging 이없을경우, v$logmnr_contents.sql_redo 은다음과같습니다. SQL> update "SCOTT"."TEST_LOG" set "C3" = '99' where "C3" = '3' and ROWID = 'AAABOZAABAAANZ6AAA'; 이전에 test_always Log Group 으로설정이되었다면, v$logmnr_contents.sql_redo 은다음과같습니다. SQL> update "SCOTT"."TEST_LOG" set "C3" = '99' where "C1" = '1' and "C2" = '2' and "C3" = '3' and ROWID = 'AAABOZAABAAANZ6AAA'; 3. 또다른 Log Group 을정의합니다. SQL> alter table test_log add supplemental log group test_sometimes(c1,c2); 4.1 Log Group 에속한 Column 을변경합니다. SQL> update test_log set c2=10; 1 row updated. v$logmnr_contents.sql_redo 은다음과같습니다. SQL> update "SCOTT"."TEST_LOG" set "C2" = '10' where "C1" = '1' and "C2" = '2' and ROWID = 'AAABLtAABAAANYgAAA'; 4.2 Log Group 에속하지않은 Column 을변경합니다. ( 어떠한정보도기록되지않습니다.) 5. Log Groups 을위한두가지의 View 을제공합니다. ALL_/USER_/DBA_LOG_GROUPS - Table 에관한 Group 정의를기록정보입니다. ALL_/USER_/DBA_LOG_GROUP_COLUMNS - Log Group 에명시된 Columns 정보입니다. 6. Log Group 삭제하려면 ALTER TABLE 문장입니다. SQL> ALTER TABLE test_log DROP SUPPLEMENTAL LOG GROUP test_always;
제약사항 지원되지않는 Objects (1) Oracle 8i - Index-organized tables - Clustered tables/indexes - Non-scalar data types - direct path insert operations Oracle 9i - Simple and nested abstract datatypes (ADTs) - Collections (nested tables and VARRAYs) - Object Refs - Index organized tables (IOTs) -Cluster Key 값을가진 CTAS Table (CREATE TABLE AS SELECT ) Index Organized Talbe(IOT) 는 Oracle 8 에서처음소개된새로운 object이입니다. IOT 는 Table Data 를 Primary Key 에대한 B*Tree Index 에적재하는구조를갖습니다. IOT 의 Index Row 들은인덱스키값과 Non-Key 값을포함하며, Index 에는 ROWID 정보가없습니다. IOT 의장점은 Range Search, Exact Match를수행하는경우일반적인 Table 보다빠른 Key-Based Access 가가능하며, Full Table Scan 시 Primary Key 에대한 Full Index Scan이이루어지므로자동적인 Ordering 이이루어집니다. 또한 Index Key Column 과 Rowid 에대한 Storage 중복을피할수있어 Storage 가절약됩니다. Clustered Tables 란여러테이블들에공통되는 Column 이있을때그컬럼의데이타는공유하면서나머지 Table 의 Row 들을하나의 Block 에기록하는저장방법을말한다. 이것은다른테이블의 Row 들을하나의 Block 에저장하므로 Clustered table 간의 Join 속도향상과 Cluster Key 는한번만저장되므로 Storage 의향상됩니다. Clustered Indexes 란 Cluster 생성후 Cluster Key 값에대한 Index Entry 값을생성하는것을말합니다. Non-Scalar Data Types 이란실수로표현할수없은 Datatype 즉 Varchar2, Char, Date 등을제외한 Long, Long Raw, Lob(Clob, Blob,NClob) 등을말하며, 10g 버전에서는지원이됩니다. Direct Path Insert Operations 란 SGA 의 Buffer Cache 를거치지않고직접 Oracle Data 를구성하고입력하는방법으로, 이기능은 SQL*Loader 의 Direct Load 와비슷한기능입니다. 이방법은 Undo Entry 를생성하지않으므로기본 Insert 보다속도가빠르며, Serial 과 Parallel Mode 를둘다사용할수있습니다.
[ 예제 1] APPEND hint의사용 SQL> insert /*+ APPEND */ into emp select * from t_emp; SQL> commit; direct insert 후에바로 select 를하기전에먼저 commit; 을해야 data 를확인할수있습니다. [ 예제2] PARALLEL hint의사용 SQL> alter session enable parallel dml; SQL> insert /*+ PARALLEL(emp,10) */ into emp select /*+ PARALLEL(t_emp,10) */ * from t_emp; SQL> commit; 위와같이 Direct Path Insert 는대량의데이타를한꺼번에입력하는경우에사용하는것이좋습니다. LogMiner 는 Database 에정보가있다고하더라도 Direct Path Insert 에대한사항은다루지않습니다. Simple and nested abstract datatypes (ADTs) 은 User 가정의한추상 Datatype 을말합니다. Collections (nested tables and VARRAYs) 은 ORACLE 8 이상부터 nested table 과 varray 라는두가지집합 Type 을말합니다. 집합 (collections) 은 3GL program 에서 Array 와같이작용하며, 다른점이라면 1차원밖에는제공되지않으며, integer 값에의해 indexing 될수있습니다. NESTED TABLE 은 TABLE TYPE의 item 을 nested table 이라고부르며, nested table 은복수개의column을포함할수있으며, variable, parameter, result, attribute, column으로사용될수있습니다. VARRAY 는또다른 TYPE VARRAY 이며, VARRAY 의 maximum size는 define시에지정한값으로결정됩니다. Object Refs 는오브젝트을참조할수있는값을가진데이터타입을말합니다.
제약사항 지원되지않는 Objects (2) Oracle 10g - BFILE, XMLTYPE datatype - Simple and nested abstract datatypes (ADTs) - Collections (nested tables and VARRAYs) - Object refs - Index-organized tables (IOTs) with LOB columns - Tables using table compression ( 압축기능 ) 9.2 버전또는그상위버전에서생성된 Redo Log 파일을사용하면 LOB 과 LONG Datatypes 이지원됩니다. LogMiner 는 Unsupported Datatypes 과 Table Storage 속성은지원하지않습니다.
LogMiner 관련 Views V$LOGMNR_CONTENTS -SQL_REDO -SQL_UNDO V$LOGMNR_DICTIONARY V$LOGMNR_LOGS V$LOGMNR_PARAMETERS V$LOGMNR_CONTENTS - Insert, Update, Delete 와 DDL 과같은작업이수행되면변경이된다. - SCN : SCN Column 에의해서변경된다. - Commit_SCN : Commit_SCN 에의해서변경된다. - XIDUSN, XIDSLT 와 XIDSQN : Transaction 에의해서변경된다. - Table, Schema, Username - SQL_REDO : Redo Records 을생성하는 SQL 문장과동일한재생성문장이다. - SQL_UNDO : 변경을 Undo 하기위해서필요한 SQL 문장을보여주는재생성문장이며, DDL 은 NULL 로표시된다. - SEG_OWNER - SEG_NAME - ROW_ID - TABLESPACE 등
일반적인 LogMiner 수행단계 1. Supplemental Logging 활성화 2. LogMiner Dictionary 추출 (Online Catalog 을사용하지않음 ) 3. 분석을위한 Redo Log 지정 4. LogMiner 기동 5. V$LOGMNR_CONTENTS 조회 6. LogMiner Session 종료 SQL> EXECUTE SYS.DBMS_LOGMNR.END_LOGMNR; 1. LogMiner 는 Mount 나 Open Database Mode 에서사용이가능합니다. 2. Redo Log 파일들의 List 을생성하고, New List 의시작을알리기위하여 DBMS_LOGMNR.ADD_LOGFILE Procedure 에 New Option 을명시합니다. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log1.f', OPTIONS => DBMS_LOGMNR.NEW); 3. 필요하다면 DBMS_LOGMNR.ADD_LOGFILE Procedure 의 ADDFILE Option 을명시하여더많은 Redo Log 파일들을추가합니다. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f', OPTIONS => DBMS_LOGMNR.ADDFILE); 이 Options Parameter 는추가 Redo Log 파일들을추가할때필요한 Log 를선택적으로사용하며, 다음과같이사용할수있습니다. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=> '/oracle/logs/log2.f'); 4. 10g 버전에서는원하면다음과같이 DBMS_LOGMNR.REMOVE_LOGFILE Procedure 을사용하여 /oracle/logs/log2.f Redo Log 파일을 List 에서삭제할수있습니다. SQL> EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f');
5. LogMiner 기동하고, LogMiner Dictionary 파일을생성하고, 분석할 Redo Log 파일들을명시한후, 다음과같은단계로 LogMiner 을기동해야합니다. Oracle 은 LogMiner Dictionary Option 을명시하는것을권고하며, 명시하지않을경우, 내부 Object 확인자, Object 이름의 Datatypes 과외부 Data Formats 을해석할수없는내부 Object IDs와 Hex Data 값으로현재의 Data 가출력됩니다. 추가로 MINE_VALUE 와 COLUMN_PRESENT 함수들은 Dictionary 없이사용할수없습니다. SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/oracle/database/dictionary.ora'); 6. V$LOGMNR_CONTENTS 을통하여추출한 Rows 들을조회할수있습니다. 7. LogMiner Session 을다음과같이종료합니다. LogMiner Packages 들은 SYS Schema 소유이며, SYS 로접속되어있지않다면다음과같이실행을한다. SQL>EXECUTE DBMS_LOGMNR.START_ENDMNR
참조자료 Oracle8i Administrator's Guide Chapter-7 : Managing Archived Redo Logs Topic : Using LogMiner to Analyze Online and Archived Redo Logs Oracle9i Release 2 (9.2) Database Administrator's Guide Chapter-9 Using LogMiner to Analyze Redo Logs Oracle10g Database Utilities Part IV : Other Utilities, Chapter : 19 Using LogMiner to Analyze RedoLogs 본세미나자료는다음의자료들을참조하여작성되었습니다. 기타상세한설명이필요한경우에참조해서확인해보실수있습니다.