一、安装LogMiner
1、@D:appproduct11.1.0db_1RDBMSADMINdbmslm.sql
2、@D:appproduct11.1.0db_1RDBMSADMINdbmslmd.sql
二、配置LogMiner文件夹
CREATE DIRECTORY utlfile AS ‘D:apporadatapracticeLOGMNR‘;
alter system set utl_file_dir=‘D:apporadatapracticeLOGMNR‘ scope=spfile;
三、重启数据库
四、创建字典文件
需要以DBA用户登录，创建到上面配置好的LogMiner文件夹中。
EXECUTE dbms_logmnr_d.build(dictionary_filename => ‘dictionary.ora‘, dictionary_location =>‘D:apporadatapracticeLOGMNR‘);
五、加入需分析的日志文件
Oracle的LogMiner可以分析在线（online）和归档（offline）两种日志文件，
加入分析日志文件使用dbms_logmnr.add_logfile过程，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。
BEGIN
dbms_logmnr.add_logfile(logfilename=>‘D:apporadataorclREDO03.LOG‘,options=>dbms_logmnr.NEW);
dbms_logmnr.add_logfile(logfilename=>‘D:apporadataorclREDO02.LOG‘,options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>‘D:apporadataorclREDO01.LOG‘,options=>dbms_logmnr.ADDFILE);
END;
六、使用LogMiner进行日志分析
Oracle的LogMiner分析时分为无限制条件和限制条件两种，无限制条件中分析所有加入到分析列表日志文件，限制条件根据限制条件分析指定范围日志文件。
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>‘D:apporadatapracticeLOGMNRdictionary.ora‘);
OR
execute dbms_logmnr.start_logmnr(options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
补充日志：
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> select supplemental_log_data_min from v$database;
七、示例：
SQL>select*from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------11169524288001 NO INACTIVE 718969411-OCT-1421170524288001 NO INACTIVE 720002611-OCT-1431171524288001 NO CURRENT720963111-OCT-14 SQL>alter system switch logfile; System altered. SQL>select*from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------11172524288001 NO CURRENT721242611-OCT-1421170524288001 NO INACTIVE 720002611-OCT-1431171524288001 NO ACTIVE 720963111-OCT-14 另外一个session sqlplus scott/tiger SQL>deletefrom emp2 where deptno =20; 5 rows deleted. SQL>commit; Commit complete. SQL>insertinto emp2 select*from emp t where t.deptno !=10; 11 rows created. SQL>commit; Commit complete. SQL> 回到原session SQL>alter system switch logfile; System altered. SQL>select*from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------11172524288001 NO ACTIVE 721242611-OCT-1421173524288001 NO CURRENT721245111-OCT-1431171524288001 NO ACTIVE 720963111-OCT-14 SQL>begin2 dbms_logmnr.add_logfile(logfilename=>‘D:apporadataorclREDO01.LOG‘,options=>dbms_logmnr.NEW); 3end; 4/ PL/SQL procedure successfully completed. SQL>execute dbms_logmnr.start_logmnr(options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only); PL/SQL procedure successfully completed. SQL>select sql_redo from v$logmnr_contents t where t.seg_name =‘EMP2‘; SQL_REDO ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------deletefrom "SCOTT"."EMP2" where "EMPNO" =‘7369‘and "ENAME" =‘SMITH‘and "JOB" =‘CLERK‘and "MGR" =‘7902‘and "HIREDATE" = TO_DATE(‘17-DEC-80‘, ‘DD-MON-RR‘) and "SAL" =‘800‘and "COMM" ISNULLand "DEPTNO" =‘20‘and ROWID =‘AAARiDAAEAAABgNAAC‘; ...... ---- 启动supplemental log： SQL>alterdatabaseadd supplemental log data; 关闭supplemental log： SQL>alterdatabasedrop supplemental log data; 查看 supplemental log： SQL>select supplemental_log_data_min from v$database;
原文：http://www.cnblogs.com/Alex-Zeng/p/4019554.html
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！