logmnr 설정

로그에서 원하는 정보를 추출하기 위해 아래와 같이 작업을 진행함

select supplemental_log_data_min  from v$database;

alter database add supplemental log data;  전체 활성화

alter database drop supplemental log data; 

alter table scott.emp add supplemental log data (all) columns;  특정 테이블

1. 오라클 파라미터 설정

   UTL_FILE_DIR=/tmp

2. Dict 생성

   begin

dbms_logmnr_d.build(

dictionary_filename => 'testdict.ora',

dictionary_location => '/tmp'

);

   end;

   /

3. Add log

   begin

dbms_logmnr.add_logfile(

options => DBMS_LOGMNR.new,

logfilename => '/oracle/archive/test0001.arc'

);

dbms_logmnr.add_logfile(

options => DBMS_LOGMNR.addfile,

logfilename => '/oracle/archive/test0002.arc'

);

dbms_logmnr.add_logfile(

options => DBMS_LOGMNR.addfile,

logfilename => '/oracle/data/redo01.log'

);

   end;

   /

 

  exec dbms_logmnr.add_logfile('/dev/raw/raw23', 1)    1: new, 2: delete, 3: add

   select db_name, filename from v$logmnr_logs; 

4. start logmnr

   begin

dbms_logmnr.start_logmnr(

dictfilename => '/tmp/testdict.ora' );

dbms_logmnr.start_logmnr(

dictfilename => '/tmp/testidict.ora',

starttime => to_date('01-07-2017 11:00:00', 'DD-MM-YYYY HH24:MI:SS'),

endtime => to_date('01-07-2017 12:00:00', 'DD-MM-YYYY HH24:MI:SS'));

dbms_logmnr.start_logmnr(

dictfilename => '/tmp/testidict.ora',

startscn => 100,

endscn => 150

);

   end;

   /

   begin

dbms_logmnr.start_logmnr(

options => dbms_logmnr.dict_from_online_catalog,

starttime => to_date('01-07-2017 11:00:00', 'DD-MM-YYYY HH24:MI:SS'),

endtime => to_date('01-07-2017 12:00:00', 'DD-MM-YYYY HH24:MI:SS')

);

   end;

   /

5. query log

   alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';

   select scn, operation, sql_redo, sql_undo

   from v$logmnr_contents;

   select seg_owner, seg_name,  count(*) as hits

   from v$logmnr_contents

   where seg_name not like '%$'

   group by seg_owner, seg_name;

6. stop logmnr

   begin

dbms_logmnr.end_logmnr;

   end;

   /