1.创建DBMS_LOGMNR包
SQL>@D:/oracle/product/10.2.0/db_1/rdbms/admin/dbmslms.sql
SQL>@D:/oracle/product/10.2.0/db_1/rdbms/admin/dbmslm.sql
SQL>@D:/oracle/product/10.2.0/db_1/rdbms/admin/dbmslmd.sql
SQL>@D:/oracle/product/10.2.0/db_1/rdbms/admin/prvtlm.plb
2.修改初始化参数UTL_FILE_DIR,指定分析数据的存放处
SQL>alter system set UTL_FILE_DIR='d:\logminer\' scope=spfile;
3.重启数据库
SQL>shutdown immediate
SQL>startup
SQL>Alter session set NLS_LANGUAGE=American;
4.创建数据字典文件
SQL>EXECUTE dbms_logmnr_d.build(dictionary_filename => 'sqltrace.ora',dictionary_location => 'd:\logminer\');
5、增加日志文件
EXECUTE dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG', dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG', dbms_logmnr.addfile);
EXECUTE dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG', dbms_logmnr.addfile);
6、无条件开始分析
EXECUTE dbms_logmnr.start_logmnr(DictFileName => 'd:\oracle\logminer\sqltrace.ora');
有时间范围分析
EXECUTE dbms_logmnr.start_logmnr(DictFileName => 'd:\oracle\logminer\sqltrace.ora', StartTime => to_date('2007-11-20 22:14:00','YYYY-MM-DD HH24:MI:SS'), EndTime => to_date('2007-11-20 22:59:59','YYYY-MM-DD HH24:MI:SS '));
7、查询redo语句
SELECT sql_redo FROM v$logmnr_contents;
8、结束分析
execute dbms_logmnr.end_logmnr;
