1.创建DBMS_LOGMNR包[@more@]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:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG', dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile('D:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG', dbms_logmnr.addfile);
EXECUTE dbms_logmnr.add_logfile('D:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG', dbms_logmnr.addfile);

6、无条件开始分析
EXECUTE dbms_logmnr.start_logmnr(DictFileName => 'd:oraclelogminersqltrace.ora');

有时间范围分析
EXECUTE dbms_logmnr.start_logmnr(DictFileName => 'd:oraclelogminersqltrace.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;

ITPub博客

logminer的使用

最新文章