logminer的安装:
创建DBMS_LOGMNR:
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
Package created.
Grant succeeded.
创建DBMS_LOGMNR_D
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
Package created.
首先检查一下supplemental logging是否开启
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
没有开启,将其启动
SQL> alter database add supplemental log data;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
设置参数: UTL_FILE_DIR, 数据字典物理文件存放的路径,注意路径的权限!
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29989552/viewspace-1684385/