logminer的使用

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:/oracle/logminer'   scope=spfile;  
  3.重启数据库  
  SQL>shutdown   immediate  
  SQL>startup  
  4.创建数据字典文件    
  SQL>EXECUTE   dbms_logmnr_d.build(dictionary_filename   =>   'sqltrace.ora',dictionary_location   =>   'd:/oracle/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;