首页 > 代码库 > oracle LogMiner配置使用
oracle LogMiner配置使用
一、安装LogMiner
1、@D:\app\product\11.1.0\db_1\RDBMS\ADMIN\dbmslm.sql
2、@D:\app\product\11.1.0\db_1\RDBMS\ADMIN\dbmslmd.sql
二、配置LogMiner文件夹
CREATE DIRECTORY utlfile AS ‘D:\app\oradata\practice\LOGMNR‘;
alter system set utl_file_dir=‘D:\app\oradata\practice\LOGMNR‘ scope=spfile;
三、重启数据库
四、创建字典文件
需要以DBA用户登录，创建到上面配置好的LogMiner文件夹中。
EXECUTE dbms_logmnr_d.build(dictionary_filename => ‘dictionary.ora‘, dictionary_location =>‘D:\app\oradata\practice\LOGMNR‘);
五、加入需分析的日志文件
Oracle的LogMiner可以分析在线（online）和归档（offline）两种日志文件，
加入分析日志文件使用dbms_logmnr.add_logfile过程，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。
BEGIN
dbms_logmnr.add_logfile(logfilename=>‘D:\app\oradata\orcl\REDO03.LOG‘,options=>dbms_logmnr.NEW);
dbms_logmnr.add_logfile(logfilename=>‘D:\app\oradata\orcl\REDO02.LOG‘,options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>‘D:\app\oradata\orcl\REDO01.LOG‘,options=>dbms_logmnr.ADDFILE);
END;
六、使用LogMiner进行日志分析
Oracle的LogMiner分析时分为无限制条件和限制条件两种，无限制条件中分析所有加入到分析列表日志文件，限制条件根据限制条件分析指定范围日志文件。
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>‘D:\app\oradata\practice\LOGMNR\dictionary.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---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ 1 1 169 52428800 1 NO INACTIVE 7189694 11-OCT-14 2 1 170 52428800 1 NO INACTIVE 7200026 11-OCT-14 3 1 171 52428800 1 NO CURRENT 7209631 11-OCT-14SQL> alter system switch logfile;System altered.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ 1 1 172 52428800 1 NO CURRENT 7212426 11-OCT-14 2 1 170 52428800 1 NO INACTIVE 7200026 11-OCT-14 3 1 171 52428800 1 NO ACTIVE 7209631 11-OCT-14另外一个session sqlplus scott/tiger SQL> delete from emp2 where deptno =20;5 rows deleted.SQL> commit;Commit complete.SQL> insert into emp2 select * from emp t where t.deptno !=10;11 rows created.SQL> commit;Commit complete.SQL>回到原sessionSQL> alter system switch logfile;System altered.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ 1 1 172 52428800 1 NO ACTIVE 7212426 11-OCT-14 2 1 173 52428800 1 NO CURRENT 7212451 11-OCT-14 3 1 171 52428800 1 NO ACTIVE 7209631 11-OCT-14SQL> begin 2 dbms_logmnr.add_logfile(logfilename=>‘D:\app\oradata\orcl\REDO01.LOG‘,options=>dbms_logmnr.NEW); 3 end; 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------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------delete from "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" IS NULL and "DEPTNO" = ‘20‘ and ROWID = ‘AAARiDAAEAAABgNAAC‘;......----启动supplemental log： SQL>alter database add supplemental log data; 关闭supplemental log： SQL>alter database drop supplemental log data; 查看 supplemental log： SQL>select supplemental_log_data_min from v$database;
oracle LogMiner配置使用