logminer操作步骤

千家信息网最后更新 2024年11月29日logminer操作步骤前提：
1 数据可以归档模式也可以非归档模式。
2 需要调整supplemental_log_data_min才能进行日志挖掘。
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
sys@oratest1(test-for-lihb)> alter database add supplemental log data;
Database altered.
sys@oratest1(test-for-lihb)> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
3 日志挖掘是基于session的，如果session退出，需要重新挖掘。




步骤：
1 查看当前在线日志文件使用情况：
sys@oratest1(test-for-lihb)> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
1 1 565 52428800 5122 NO CURRENT 8490175 2017-11-13 10:37:37 2.8147E+14
2 1 563 52428800 5122 NO INACTIVE 8481985 2017-11-13 07:00:208489600 2017-11-13 10:21:05
3 1 564 52428800 5122 NO INACTIVE 8489600 2017-11-13 10:21:058490175 2017-11-13 10:37:37

2 创建路径
sys@oratest1(test-for-lihb)> CREATE DIRECTORY utlfile AS '/home/oracle/logmnr';
Directory created.

3 修改参数
sys@oratest1(test-for-lihb)> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
System altered.

4 建/home/oracle/logmnr目录，重启数据库使utl_file_dir参数生效
[oracle@test-for-lihb ~]$ mkdir -p /home/oracle/logmnr
sys@oratest1(test-for-lihb)> shutdown immediate
sys@oratest1(test-for-lihb)> startup

5 创建数据字典文件
sys@oratest1(test-for-lihb)> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logmnr');
PL/SQL procedure successfully completed.

6 查看日志文件物理位置（也可以查看归档文件）
sys@oratest1(test-for-lihb)> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------------------------------ ------------------------------------------------------------ ---
1 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log NO
1 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_1_dhl9h8bn_.log YES
2 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log NO
2 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_2_dhl9h91y_.log YES
3 ONLINE /opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log NO
3 ONLINE /opt/app/oracle/fast_recovery_area/ORATEST1/onlinelog/o1_mf_3_dhl9h9y1_.log YES
6 rows selected.

7 模拟scott用户插入数据
scott@oratest1(70)> truncate table T;
Table truncated.

scott@oratest1(70)> insert into t values (10086);
1 row created.

scott@oratest1(70)> insert into t values (10087);
1 row created.

scott@oratest1(70)> insert into t values (10088);
1 row created.

scott@oratest1(70)> insert into t values (10089);
1 row created.

scott@oratest1(70)> commit;
Commit complete.

8 加入日志文件（一个日志组如果有多个成员，只需要加入一个即可，因为同一日志组所有成员内容是相同的。）
第一个日志文件参数是dbms_logmnr.NEW
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_3_dhl9h98x_.log',options=>dbms_logmnr.NEW);
END;
/

后续的日志文件参数是dbms_logmnr.ADDFILE
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_1_dhl9h7tz_.log',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'/opt/app/oracle/oradata/ORATEST1/onlinelog/o1_mf_2_dhl9h8mc_.log',options=>dbms_logmnr.ADDFILE);
END;
/

9 开始日志挖掘
无限制挖掘：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');
特定时间段挖掘（未测试）：
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora',StartTime =>to_date('2013-6-8 00:00:00','YYYY-MM-DD HH24:MI:SS')EndTime =>to_date(''2013-6-8 23:59:59','YYYY-MM-DD HH24:MI:SS '));

10 查看挖掘结果
sys@oratest1(test-for-lihb)> select sql_redo from v$logmnr_contents where username='SCOTT';
。。。
insert into "SCOTT"."T"("ID") values ('10086');
insert into "SCOTT"."T"("ID") values ('10087');
insert into "SCOTT"."T"("ID") values ('10088');
insert into "SCOTT"."T"("ID") values ('10089');
。。。。

11 关闭日志挖掘。
EXECUTE DBMS_LOGMNR.END_LOGMNR;