logminer日志挖掘操作步骤

alter system set utl_file_dir = '/backup' scope = spfile; 重启数据库才有效 实验数据 SQL> create table x2 (a number);

Table created SQL> insert into x2 values (1);

1 row created.

SQL> insert into x2 values (2);

1 row created.

SQL> update x2 set a=3 where a=2;

1 row updated..

SQL> update x2 set a=3 where a=1;    

1 row updated.

SQL> commit;

Commit complete. SQL> alter system switch logfile;

System altered. 首次添加要分析的日志 execute dbms_logmnr.add_logfile (logfilename=>'/home/orcl/app/fast_recovery_area/ORCL/archivelog/2015_06_03/o1_mf_1_3_bpxgpcwn_.arc',options=>dbms_logmnr.new); 分析归档可以在v$archived_log视图中找到归档名 分析重做日志可以在v$logfile视图中找日志名（只能分析未归档重做日志，不然会系统重复错误） 再次添加 execute dbms_logmnr.add_logfile (logfilename=>'/home/orcl/app/fast_recovery_area/ORCL/archivelog/2015_06_03/o1_mf_1_2_bpxgpcwn_.arc',options=>dbms_logmnr.addfile）; 如果要挖掘的归档日志文件很多可以使用下面语句解放劳动力 select 'exec dbms_logmnr.add_logfile(LogFileName=>'''||name||''',Options=>dbms_logmnr.addfile);' from v$archived_log; 删除挖掘日志 execute dbms_logmnr.add_logfile (logfilename=>'/home/orcl/app/fast_recovery_area/ORCL/archivelog/2015_06_03/o1_mf_1_2_bpxgpcwn_.arc',options=>dbms_logmnr.removefile);执行存储过程dbms_logmnr_d.build执行存储过程dbms_logmnr_d.build创建挖掘日志文件的物理文件路径 execute dbms_logmnr_d.build('estlogminer.ora','/backup'); 启动logminer execute dbms_logmnr.start_logmnr(dictfilename=>'/backup/testlogminer.ora',options=>dbms_logmnr.ddl_dict_tracking);指明了字典物理文件'/backup/testlogminer.ora 按SCN来过滤 execute dbms_logmnr.start_logmnr(dictfilename=>'/backup/testlogminer.ora',startscn=>50,endscn=>100); 按时间过滤 execute dbms_logmnr.start_logmnr(dictfilename=>'/backup/testlogminer.ora',starttime=>to_date('03-Jun-2015 14:20:20' ,'DD-MON-YY HH:MI:SS'),starttime=>to_date('03-Jun-2015 17:20:20' ,'DD-MON-YY HH:MI:SS')); 开启logminer之后 select filename from v$logmnr_logs;可以查询logminer正在分析哪些日志 可以在v$logmnr_contents中在线分析挖掘日志; 结束logminer  SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29989552/viewspace-1684385/，如需转载，请注明出处，否则将追究法律责任。