[準備]
1.Package file
@oracle_home/rdbms/admin/dbmslmd.sql

2.Dictionary file build
.initSID.ora file change
utl_file_dir = /oracle/logs
[logmnr]
1.dic.ora 檔案生成
execute dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/oracle/ANT/data4/logmnr');

2.新增需要使用的log檔案
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT161.log', options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT162.log', options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT163.log', options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT160.log', options=>dbms_logmnr.addfile);
3.開始進行分析
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/ANT/data4/logmnr/dict.ora');

select username ,operation,sql_redo
from v＄logmnr_contents
where operation='DDL'

SELECT SEG_OWNER,SEG_NAME,OPERATION,TO_CHAR(TIMESTAMP,'YYYY/MM/DD HH24:MI:SS')"TIME"
SQL_REDO,SQL_UNDO
FROM V＄LOGMNR_CONTENTS
WHERE SEG_OWNER='SAHRIS'
AND SEG_NAME = 'KAOKE'
AND OPERATION='DELETE'

select sql_undo from v＄logmnr_contents
where SEG_NAME = 'KAOKE'
AND seg_owner='SAHRIS'
AND OPERATION='DELETE'

4.dbms_logmnr.end_logmnrA[@more@]

oracle9i 中使用 logmnr

相關文章