Oracle日志挖掘命令(三)
f_1_3_8w4proqh_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_4_8w4psx1j_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_5_8w4q3sol_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_6_8w4qc0yo_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_7_8w564nnh_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_8_8w56c7gz_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_9_8w56d13f_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_10_8w56mrcn_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_11_8w5bskbb_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_12_8w5bstfx_.arc YES YES
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_13_8w5byo0v_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_14_8w5klf20_.arc NO NO
/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_15_8w5klxm9_.arc NO NO
13 rows selected.
1.3.2.4做DDL操作
SCOTT用户下删除一个表
drop table test;
1.3.2.5将使用日志建立分析列表
因为在Drop一个表之前，我已经把数据字典信息写入到了/u01/logmnr/newdict.ora中，所以此时我只要将当前日志和几个归档日志加入分析列表即可。
execute dbms_logmnr.add_logfile(logfilename=>'/u01/oradata/mike/redo01.log',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_15_8w5klxm9_.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_14_8w5klf20_.arc',options=>
dbms_logmnr.addfile);
1.3.2.6使用数据字典进行分析
execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/logmnr/newdict.ora');
1.3.2.6查询查看分析结果
select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents
where seg_name='TEST'and seg_owner='SCOTT';
结果：
SCN TO_CHAR(TIMESTAMP,'
-----------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
1384524 2013-06-20 15:14:59
ALTER TABLE"SCOTT"."TEST" RENAME TO"BIN$35G0QjKlCuLgRAgAJ3D0FA==$0" ;
1384527 2013-06-20 15:14:59
drop table test AS"BIN$35G0QjKlCuLgRAgAJ3D0FA==$0" ;
1389502 2013-06-20 17:06:50
create table test(id int) tablespace stu;
1389516 2013-06-20 17:07:00
insert into"SCOTT"."TEST"("ID") values ('99');
1389980 2013-06-20 17:18:56
ALTER TABLE"SCOTT"."TEST" RENAME TO"BIN$35G0QjKmCuLgRAgAJ3D0FA==$0" ;
1389982 2013-06-20 17:18:56
drop table test AS"BIN$35G0QjKmCuLgRAgAJ3D0FA==$0" ;
6 rows selected.
1.3.2.7结束logmnr
execdbms_logmnr.end_logmnr;
1.4日志挖掘总结
show parameter utl
alter system set utl_file_dir='/u01/logmnr'scope=spfile;（设置参数并重启实例）