Goodus 기술노트 [23 회 ] Logminer Author 서강혁, 정철우 Creation Date 2007-08-30 Last Updated 2007-08-30 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 1 2007-08-30 서강혁, 정철우 문서최초작성 2 3
Contents 1. Logminer...3 1.1. Logminer 란?... 3 1.1.1. Logminer 로가능한것들... 3 1.1.2. Logminer 의제약사항... 4 1.2. Logminer 를사용하기위한준비사항... 4 1.3. Logminer 활용예제... 4 1.3.1. 사용자실수로데이터를변경 (delete, insert, update) 후 Logminer 로복구하기... 5 1.3.2. 특정시점에서데이터가변경되었을때 Logminer 로유저확인하기... 8 1.3.3. Toad 에서 Logminer 쉽게사용하기... 9 1.3.4. Logminer 시발생하는 Error 및조치방법... 15 1.4. V$LOGMNR_CONENTS... 16-2 -
1. Logminer 1.1. Logminer 란? Logminer 는 Oracle 8i 이상에서사용가능한 tool 로써, Oracle 에기본내장되어있으며, Redo log 와 Archive log file 의내용을읽어들이는데사용할수있다. 1.1.1. Logminer로가능한것들 Logminer 로 Redo 와 Archive log file 을읽어들이게되면, DB 에서해당 Log file 의생성시점에일어났던모든 DML 및 DDL(9i 이상지원 ) 문을확인할수있으므로, 특정트랙잭션의발생시점이나, 실행한유저등을확인할수있고, UNDO SQL 을추출해내어해당쿼리를 Rollback 하는데사용할수있다. l Database 에작성된변경사항들의기록 l 유형 (INSERT, UPDATE, DELETE, COMMIT/ROLLBACK, DDL 또는 INDEX 작업 ) l 그와같은변경이발생하는 SCN (System Change Number) l 변경을포함하는트랜잭션식별 l 특정트랜잭션이커밋되는 SCN l 변경된객체의테이블및스키마명칭 l DML 또는 DDL 문을발생시킨사용자정보 l Redo 레코드들을생성하는 동등한 SQL 을나타낼수있는재생성된 SQL (SQL_REDO) l 변경사항의실행취소를위해필요한 SQL 을제공하는재생성된 SQL (SQL_UNDO) - 3 -
1.1.2. Logminer의제약사항 1) LONG and LOB data type 2) Object types 3) Nested tables 4) Object Refs 5) IOT(Index-Organized Table) 6) Chianed row or migrate row 1.2. Logminer 를사용하기위한준비사항 l UTL_FILE_DIR Parameter 설정 : init parameter file에설정해주어야하며, 그용도는 Log를읽을때생성되는 Dictionary 정보를저장하는데사용되는, Flat file의저장공간으로사용된다. UTL_FILE_DIR = /oracle/ora9/dict scope=spfile; l LogMiner 를위한 dictionary file 생성 ( 또는 Online Redo log) =>Object 정보를저장하기위한 Flat file 용도이다. SQL> execute dbms_logmnr_d.build (dictionary_filename => 'dictionary.ora', - dictionary_location => '/data/ora9/dict', options => dbms_logmnr_d.store_in_flat_file); l 분석할 Redo log 또는 Archive log file l Database 가 ArchiveLog Mode 이어야한다. l 필요에따라서 Mining 하고자하는 Table 이 supplemental logging 이되어야한다. l Archive Log 및 Flat Dictionary File 에대한 read 권한이있어야한다. l 최소 CPU 1장정도의 resource 을사용한다. ( 대략 Logminer 1개의 DB Session 당 1개의 CPU 사용함 ) l 최소 Memory 을 10MB 에서 100MB 정도사용한다. (Redo Size 을 100MB일경우 ) l Mining 데이터를 Table 로저장시 Tablespace 확인후작업을해야하며추후꼭 Drop 처리를해야한다 1.3. Logminer 활용예제 관련 View 및 Package 1) V$LOGMNR_CONTENTS - 현재분석되고있는 log file 의내용 2) V$LOGMNR_DICTIONARY - 사용중인 dictionary file 3) V$LOGMNR_LOGS 분석에사용되고있는 log file 4) V$LOGMNR_PARAMETERS - LogMiner 에 Setting 된현재의 parameter 의값 5) dbms_logmnr Package (start, end, new, addfile, remove) - 4 -
1.3.1. 사용자실수로데이터를변경 (delete, insert, update) 후 Logminer로복구하기 l 데이터삭제하기 SQL> delete adress where name=' 정철우 '; 1 row deleted. SQL> select * from adress where name=' 정철우 '; no rows selected 위와같이 data 가 delete 된경우해당시점을명확히알고있거나최소한해당날짜 라도정확히알고있다면다음과같은방법으로해당데이터를복구할수있다. l Dictionary file 을등록한다. (UTL_FILE_DIR Parameter 설정되어있어야함 ) SQL> execute dbms_logmnr_d.build - (dictionary_filename => 'dictionary.ora', - dictionary_location => '/oracle/ora9/dict', - options => dbms_logmnr_d.store_in_flat_file); l 해당시점의 logfile 을확인 SQL> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS'; Session altered. SQL> select group#, sequence#, status, first_time from v$log GROUP# SEQUENCE# STATUS FIRST_TIME ----------------------------------- ---------- ---------------- ------------------- 1 1259 INACTIVE 2007-08-29 21:09:00 2 1261 CURRENT 2007-08-30 15:43:12 3 1260 INACTIVE 2007-08-29 21:12:55 여기서 group# 과 first_time( 해당 logfile 의기록이시작된시간 ) 을확인해보면몇번 logfile 이해당시점인지확인할수있다. Ex) 15:50 분에복구해야할트랜잭션이있었다면 2 번 redo log file 을읽어와야함을알수가있다. 한가지주의할점은 redo log file 의경우파일의내용이 log switch 가발생하면서계속변동이발생하기때문에현시점이아닌특정시점의내용을 redo log 에서는찾기란거의불가능하다. 위의 v$log 에서확인한시간도 DB 가운영중이라면계속변동이발생하기때문에, archive log file 을사용하여야정확한시점의내용을찾을수가있다. 가장확실한방법은 Logminer 에해당 log file 을등록후 v$logmnr_logs 뷰를조회하는것이다. - 5 -
exec dbms_logmnr.add_logfile('/data/ora9/redo03.log', dbms_logmnr.new); exec dbms_logmnr.add_logfile('/data/ora9/redo01.log', dbms_logmnr.addfile); exec dbms_logmnr.add_logfile('/data/ora9/redo02.log', dbms_logmnr.addfile); SQL> select filename, low_time, high_time from v$logmnr_logs; FILENAME LOW_TIME HIGH_TIME ------------------------------ ------------------- ------------------- /data/ora9/redo01.log 2007-08-29 21:09:00 2007-08-29 21:12:55 /data/ora9/redo03.log 2007-08-29 21:12:55 2007-08-30 15:43:12 /data/ora9/redo02.log 2007-08-30 15:43:12 1988-01-01 00:00:00 ->CURRENT ARCHIVE LOG FILE 도위와같은방법으로확인하거나, 아래의뷰를이용하여완료시간을확인하면된다. SQL> exec dbms_logmnr.add_logfile('/data/arch/1_1259.dbf', dbms_logmnr.new); exec dbms_logmnr.add_logfile('/data/arch/1_1260.dbf', dbms_logmnr.addfile); exec dbms_logmnr.add_logfile('/data/arch/1_1261.dbf', dbms_logmnr.addfile); SQL> select filename, low_time, high_time from v$logmnr_logs; FILENAME LOW_TIME HIGH_TIME ------------------------------ ---------------------------------------------------- ------------------- /data/arch/1_1259.dbf 2007-08-29 21:09:00 2007-08-29 21:12:55 /data/arch/1_1260.dbf 2007-08-29 21:12:55 2007-08-30 15:43:12 /data/arch/1_1261.dbf 2007-08-30 15:43:12 2007-08-30 16:18:36 또는 SQL> select name, completion_time from v$archived_log; NAME COMPLETION_TIME (archive 완료시간 ) ------------------------------ - -------------------------------------- /data/arch/1_1259.dbf 2007-08-29 21:12:56 /data/arch/1_1260.dbf 2007-08-30 15:43:12 /data/arch/1_1261.dbf 2007-08-30 16:18:36 여기서대략 8 월 29~30 일경에해당데이터가삭제된것을알고있다면다음과같은 방법으로복구가가능하다. SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; SQL> execute dbms_logmnr.start_logmnr( - dictfilename => '/oracle/ora9/dict/dictionary.ora', - starttime => '2007-08-29 22:00:00', - - 6 -
endtime => '2007-08-30 16:15:00') SQL> select sql_undo, sql_redo from v$logmnr_contents 2 where username='test' 3 and seg_name='adress' 4 and operation ='DELETE'; SQL_UNDO SQL_REDO ---------------------------------------- ---------------------------------------- insert into "TEST"."ADRESS"("NAME","MOBI LE","HOME","WORK","EMAIL","MEMO","BIRTHD AY") values (' 정철우 ','010-9999-0000','0 2-2603-5596','070-7017-4194','roka1002@y ahoo.co.kr','oracle','1978-01-01'); delete from "TEST"."ADRESS" where "NAME" = ' 정철우 ' and "MOBILE" = '011-9901-781 1' and "HOME" = '02-2603-5596' and "WORK " = '070-7017-4194' and "EMAIL" = 'roka1 002@yahoo.co.kr' and "MEMO" = 'ORACLE' a nd "BIRTHDAY" = '1978-01-01' and ROWID = 'AAAGINAAGAAAAAPAAo'; SQL_REDO 는사용자가 row 를삭제했던구문이고, SQL_UNDO 는이를 Rollback 위한 구문이므로, SQL_UNDO 를실행해주면해당 row 를복구할수있다. SQL> insert into "TEST"."ADRESS"("NAME","MOBILE","HOME","WORK","EMAIL","MEMO","BIRTHDAY") values (' 정철우 ','010-9999-0000','02-0000-0000','070-7017-4194','roka1002@o.co.kr','ORACLE','1900-01-01'); 1 row created. Commit; SQL> select name, mobile from adress where name=' 정철우 '; NAME MOBILE -------------------- -------------------- 정철우 010-9999-0000 정상적으로 row 가복구됨을확인할수있다. SQL> exec dbms_logmnr.end_logmnr -> Logminer 세션종료. 만약 delete, insert, update 명령등으로해당테이블의모든데이터를변경했다면 Sql_undo 는 1 건이아닌해당테이블의 row 수만큼생성이된다. 이것은 rowid 단위로 log 가기록되기때문이다. 만약삭제된해당시점을정확히모를경우, 가용한 ARCHIVE LOG 를전부등록한후 - 7 -
execute dbms_logmnr.start_logmnr(dictfilename => '/oracle/ora9/dict/dictionary.ora'); 로 logmnr 세션을시작해서 v$logmnr_contents view 를찾으면된다. 이때 ARCHIVE LOG FILE 의개수와크기에따라더많은시간을필요로한다. 데이터가변경된것이많아서찾기가힘들때는해당결과값을 spool 명령어로저장후 PC 의검색기능등을이용해찾는게빠르다. 1.3.2. 특정시점에서데이터가변경되었을때 Logminer 로유저확인하기 SQL> drop table adress; Table dropped. SQL> select * From adress; ERROR at line 1: ORA-00942: table or view does not exist Logminer 를이용하면해당테이블을 DROP 한것이어느 Client 에서접근하여언제 작업하였는지를확인할수있다. 단이때 DDL 문 (ALTER, DROP, CREATE) 은 UNDO 정보는제공되지않으므로, 변경 된테이블의복구는굿어스기술노트 19 회에서소개한 FLASHBACK 이나 CLONE DB 등을이용하여복구하도록한다. 1 번챕터의데이터복구하기와같이 DICTIONARY FILE 생성과분석할 ARCHIVE LOGFILE 을등록하도록한다. 그다음 alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; execute dbms_logmnr.start_logmnr( - dictfilename => '/oracle/ora9/dict/dictionary.ora', - starttime => '2007-08-29 22:00:00', - endtime => '2007-08-30 16:55:00') SQL> select timestamp, session_info, sql_undo, sql_redo from v$logmnr_contents where username='test' and seg_name='adress' and sql_redo like 'drop%'; TIMESTAMP SESSION_INFO SQL_UNDO SQL_REDO ------------------- ---------------------------------------------------------------------------------------- ---------- -------------------- 2007-08-30 16:54:52 login_username=test client_info= OS_username=oracl drop table adress; e9 Machine_name=test.nt OS_terminal=pts/1 OS_proce ss_id=19298 OS_program name=sqlplus@test.nt (TNS V1-V3) - 8 -
위의결과값을보시면 SQL_UNDO 란의공백을확인할수있다.(DDL 은 UNDO 안됨 ) 그리고 TIMESTAMP 컬럽에서해당 table 이 drop 된시간과 session_info 컬럼에서접속한 client 의 Machine_name 을확인하여어느유저가테이블을 drop 했는지를확인할수가있는것이다. 1.3.3. Toad에서 Logminer 쉽게사용하기 DBA 가주로사용하는 TOAD 나 ORANGE 같은 Tool 을사용하면좀더손쉽게 Logminer 에접근하는것이가능하다. 이러한 tool 을사용했을때의장점은 GUI 의직관적인인터페이스로손쉽게사용이가능하며, Logminer 시의부하가모두 tool 을실행하는 PC 에걸림으로써, DB 서버에부하를주지않으며, 결과값을엑셀파일이나, 텍스트파일등다양한포맷으로변환해, 필터등을이용해쉽게원하는값을추출할수있다는데있다. 여기에서는 Toad(9.0) 를이용한 Logminer 사용법을알아보도록하자. (Orange 도거의비슷함 ) 1. Logminer 시작 2. Dictionary file 설정 - 9 -
여기서 Use Dictionary in redo logs 를선택하면밑의 dictionary 생성창이활성화된다. 일반적으로, 기본적으로선택되어있는 online 방식을사용하면된다. 3. 분석할 Redo log 또는 Archive log file 선택 4. FTP 를이용하여 DB 서버에서 PC 로 Logfile download - 10 -
첫번째그림에서 Connect 버튼을누르면밑의 server setting 창이열리면서 FTP 로 LOGIN 할수있는정보를요구한다. (ARCHIVE DEST 는자동적으로찾음 ) 여기서정상적으로접속이이루어지면오른쪽화면처럼 ARCHIVE LOG LIST 가나오고여기서분석할 ARCHIVE 를선택하면된다. ( 연속된 FILE 을여러개선택가능 ) 5. 분석할 ARCHIVE LOG FILE 목록 다음과같이정상적으로분석할 LOG FILE 목록이보이게된다. 6. 분석할 FILE 의 SCN 및 TIMESTAMP 확인 다음과같이자동으로해당 LOG FILE 의첫번째파일의시작 SCN 및 TIMESTAMP 와마지막파일의 SCN 및 TIMESTAMP 가보여지게되며, 사용자가범위안에서임의 로원하는값을수정할수있다. - 11 -
7. Log 에서읽어올 Colum 값선택 다음과같이읽어올 Colum 값을선택할수있다. 해당트랜잭션의발생시간 ( TIMESTAMP) 트랜잭션을발생시킨세션의정보 (Session info) 해당오라클 USER 명 (Username) 실행된 SQL 정보 (SQL REDO) ROLLBACK 할 SQL 정보 (SQL_UNDO) Colum 을선택후초록색화살표를클릭하면 Logminer 가시작이된다. 8. Logminer 결과값화면 위화면에서마우스오른쪽버튼를누른후 Save Grid 를선택. - 12 -
9. 해당결과값을 EXCEL file format 으로변환 ` Save Grid 를선택하면 Excel format 으로 Logminer 결과값을저장할수있다. 10. 생성된 EXCEL File Excel file 로변환후 Filter 등을이용하면손쉽게원하는값만을찾는것이가능하다. - 13 -
11. Logminer 시 DB 서버 ( 위 ) 와 Tool 를실행하는 PC( 아래 ) 의부하비교 위의그림을보면 Toad 를이용하여 Logminer 진행시 DB 서버 ( 위 ) 에는 CPU Idle 이 99% 로 부하가거의없고 clinet ( 아래 ) 에는 Toad 에서 CPU 를 99% 이용함을볼수있다. - 14 -
1.3.4. Logminer시발생하는 Error 및조치방법 ERROR at line 1: ORA-01282: date range specified is invalid ORA-06512: at "SYS.DBMS_LOGMNR", line 53 ORA-06512: at line 1 원인 : 지정한날짜및시간이해당 LOGFILE 의범위에없을때발생. 조치 : 날짜및시간을적절한값으로수정 (v$logmnr_logs view 참고 ) ERROR at line 1: ORA-01291: missing logfile ORA-06512: at "SYS.DBMS_LOGMNR", line 53 ORA-06512: at line 1 원인 : 잘못된 Logfile 을등록했을때발생조치 : Logfile 의이름및연속된 logfile 인지확인 ERROR at line 1: ORA-01861: literal does not match format string ORA-06512: at line 1 원인 : 잘못된포맷 ( 날짜시간등 ) 을사용했을때발생조치 : alter session set nls_date_format 명령으로정확한 DATE 값지정 ERROR at line 1: ORA-01284: file /data/arch/1_1257.log cannot be opened ORA-00308: cannot open archived log '/data/arch/1_1257.log' ORA-27037: unable to obtain file status 원인 : 해당파일에접근할수없을때발생조치 : 정확한디렉토리경로및권한확인 ERROR at line 1: ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents 원인 : Logminer 세션이종료된상태이다. 조치 : SQL PLUS 에서 EXIT 로 Logout 하면발생하며, 이때 Dictionary file 등록부터다시진행하여야한다. - 15 -
1.4. V$LOGMNR_CONENTS API Type Description SCN NUMBER System change number (SCN) when the database change was made CSCN NUMBER System change number (SCN) when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen TIMESTAMP DATE Timestamp when the database change was made COMMIT_TIMESTAMP DATE Timestamp when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen THREAD# NUMBER Number of the thread that made the change to the database XIDUSN NUMBER Transaction ID undo segment number of the transaction that generated the change XIDSLT NUMBER Transaction ID slot number of the transaction that generated the change XIDSQN NUMBER Transaction ID sequence number of the transaction that generated the change SEG_OWNER VARCHAR2(32) Owner of the modified segment SEG_NAME VARCHAR2(256) Name of the modified data segment TABLE_NAME VARCHAR2(32) Name of the modified table (in case the redo pertains to a table modification) SEG_TYPE NUMBER Type of the modified data segment 0 = UNKNOWN 1 = INDEX 2 = TABLE ex) SEG_NAME = ZORD_SVC 19 = TABLE PARTITION ex) SEG_NAME = ZORD_SVC_HST,PR_2007 20 = INDEX PARTITION 34 = TABLE SUBPARTITION All other values = UNSUPPORTED SEG_TYPE_NAME VARCHAR2(32) UNKNOWN INDEX TABLE - 16 -
TABLE PARTITION UNSUPPORTED TABLE_SPACE ROW_ID VARCHAR2(18) Row ID of the row modified by the change (only meaningful if the change pertains to a DML) SESSION# NUMBER Session number of the session that made the change USERNAME VARCHAR(30) Name of the user who executed the transaction SESSION_INFO VARCHAR2(4000) Information about the database session that executed the transaction Contains process information, machine name from which the user logged in etc (login_username, client_info, OS_username, Machine_name, OS_program_name) EX) login_username=apps client_info= OS_username=invb01 Machine_name=nngmpbt1 OS_terminal= OS_process_id=17465 OS_program_name=ZINVBCAL00010@nngmpbt1 (TNS V1-V3) ROLLBACK NUMBER 1 = if the redo record was generated because of a partial or a full rollback of the associated transaction 0 = otherwise OPERATION VARCHAR2(32) User level SQL operation that made the change INSERT = change was caused by an insert statement UPDATE = change was caused by an update statement DELETE = change was caused by a delete statement DDL = change was caused by a DDL statement START = change was caused by the start of a transaction COMMIT = change was caused by the commit of a transaction ROLLBACK = change was caused by a full rollback of a transaction SELECT_FOR_UPDATE = operation was a - 17 -
SELECT FOR UPDATE statement INTERNAL = change was caused by internal operations initiated by the database UNSUPPORTED = change was caused by operations not currently supported by LogMiner OPERATION_CODE NUMBER Number of the operation code 0 = INTERNAL 1 = INSERT 2 = DELETE 3 = UPDATE 5 = DDL 6 = START 7 = COMMIT 25 = SELECT_FOR_UPDATE 36 = ROLLBACK 255 = UNSUPPORTED SQL_REDO VARCHAR2(4000) Reconstructed SQL statement that is equivalent to the original SQL statement that made the change SQL_UNDO VARCHAR2(4000) Reconstructed SQL statement that can be used to undo the effect of the original statement that made the change - 18 -