1 本文主要测试logminer进行数据挖掘的三种方法，每种方法适用的环境不一样，故针对每一种方法进行实验。第一种方法为：设置数据库参数utl_file_dir，使用文本文件，但需要重启数据库，才可以真正进行数据挖掘。第二种方法为不需要设置utl_file_dir参数，进行在线日志挖掘。第三种方法为：怕影响生产环境，将相关日志拷贝到测试环境，进行日志挖掘。
2 验证logminer是否已安装
验证数据库是否有logminer安装包，没有则进行安装
如有有如下安装报，则认为logminer已经安装，否则则需要安装如下两个安装包
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql
SYS@rac11g1 > desc dbms_logmnr
PROCEDURE ADD_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
OPTIONS BINARY_INTEGER IN DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_LOGFILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
PROCEDURE START_LOGMNR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STARTSCN NUMBER IN DEFAULT
ENDSCN NUMBER IN DEFAULT
STARTTIME DATE IN DEFAULT
ENDTIME DATE IN DEFAULT
DICTFILENAME VARCHAR2 IN DEFAULT
OPTIONS BINARY_INTEGER IN DEFAULT
SYS@rac11g1 > desc dbms_logmnr_d
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN DEFAULT
DICTIONARY_LOCATION VARCHAR2 IN DEFAULT
OPTIONS NUMBER IN DEFAULT
PROCEDURE SET_TABLESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE VARCHAR2 IN
为了第一种环境测试，需要设置如下参数，并重启数据库。
CREATE DIRECTORY utlfile AS '/home/oracle/LOGMNR';
alter system set utl_file_dir='/home/oracle/LOGMNR' scope=spfile sid='*';
3 logminer测试表的准备，插入数据不要提交，查看相关事务信息
3.1 查看会话信息
SYS@rac11g1 > select s.sid,s.SERIAL#,p.pid,p.spid from v$session s,v$process p where s.paddr = p.addr and s.sid in (select distinct sid from v$mystat);
SID SERIAL# PID SPID
---------- ---------- ---------- ------------------------
31 49 38 12412
3.2 创建表，并插入数据，不提交，在另一个会话查看事务ID
SYS@rac11g1 > create table logminer_test(id number,name varchar2(30));
Table created.
SYS@rac11g1 > insert into logminer_test values(1,'xsc1');
1 row created.
SYS@rac11g1 > insert into logminer_test values(1,'xsc2');
1 row created.
3.3 查看未提交的事务
SID SERIAL# TO_CHAR(T.START_DAT MACHINE PROGRAM CLIENT_INFO tran_id
---------- ---------- ------------------- -------------------- ------------------------------------ -------------------- --------------------------
31 49 2023-02-20 09:45:32 linux1 sqlplus@linux1 (TNS V1-V3) 7.12.931
3.4 提交事务，准备使用logminer进程挖掘
SYS@rac11g1 > archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA2
Oldest online log sequence 143
Next log sequence to archive 144
Current log sequence 144
查看在线日志
GROUP# THREAD# A.BYTES/1024/1024 MEMBERS ARC STATUS MEMBER
---------- ---------- ----------------- ---------- --- --------------- ------------------------------------------------------------
1 1 50 2 YES ACTIVE +DATA2/rac11g/onlinelog/group_1.257.1081073045
1 1 50 2 YES ACTIVE +FRA2/rac11g/onlinelog/group_1.257.1081073047
2 1 50 2 NO CURRENT +DATA2/rac11g/onlinelog/group_2.258.1081073047
2 1 50 2 NO CURRENT +FRA2/rac11g/onlinelog/group_2.258.1081073049
3 2 50 2 YES INACTIVE +DATA2/rac11g/onlinelog/group_3.265.1081075181
3 2 50 2 YES INACTIVE +FRA2/rac11g/onlinelog/group_3.259.1081075181
4 2 50 2 YES INACTIVE +DATA2/rac11g/onlinelog/group_4.266.1081075181
4 2 50 2 YES INACTIVE +FRA2/rac11g/onlinelog/group_4.260.1081075183
查看当日的归档日志：
ASMCMD> cd 2023_02_20
ASMCMD> ls
thread_1_seq_141.347.1129282081
thread_1_seq_142.348.1129282083
thread_1_seq_143.349.1129283881
thread_1_seq_144.350.1129283995
thread_1_seq_145.351.1129284011
ASMCMD> pwd
+FRA2/RAC11G/ARCHIVELOG/2023_02_20
4 第一种方法的测试
由于本次实验之开启了测试环境的节点一，故目前只添加节点1的在线日志及归档日志
4.1 添加在线日志及相关归档日志
BEGIN
dbms_logmnr.add_logfile(logfilename=>'+FRA2/rac11g/onlinelog/group_1.257.1081073047',options=>dbms_logmnr.NEW);
dbms_logmnr.add_logfile(logfilename=>'+FRA2/rac11g/onlinelog/group_2.258.1081073049',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_141.347.1129282081',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_142.348.1129282083',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_143.349.1129283881',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_144.350.1129283995',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_145.351.1129284011',options=>dbms_logmnr.ADDFILE);
END;
/
4.2 设置文本文件的名称
SYS@rac11g1 > EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary_analysis.ora',
dictionary_location =>'/home/oracle/LOGMNR');
PL/SQL procedure successfully completed.
4.3 无限制条件
EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/LOGMNR/dictionary_analysis.ora');
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
4.4 害怕耗费资源多，指定时间，进行分析
EXECUTE dbms_logmnr.start_logmnr(DictFileName => dictfilename=>'/home/oracle/LOGMNR/dictionary_analysis.ora', StartTime =>to_date('2023-2-20 09:30:00','YYYY-MM-DD HH24:MI:SS'),EndTime =>to_date('2023-2-11 23:59:59','YYYY-MM-DD HH24:MI:SS '));
4.5 查看事务执行的SQL，同测试的事务一致
SYS@rac11g1 > SELECT sql_redo FROM v$logmnr_contents where SEG_NAME='LOGMINER_TEST';
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create table logminer_test(id number,name varchar2(30));
insert into "SYS"."LOGMINER_TEST"("ID","NAME") values ('1','xsc1');
insert into "SYS"."LOGMINER_TEST"("ID","NAME") values ('1','xsc2');
根据事务ID
v$logmnr_transaction
SELECT SESSION#,SERIAL#,sql_redo,XID,XIDUSN,XIDSLT,XIDSQN FROM v$logmnr_contents where SEG_NAME='LOGMINER_TEST';
SESSION# SERIAL# SQL_REDO XID XIDUSN XIDSLT XIDSQN
---------- ---------- ------------------------------------------------------------ ---------------- ---------- ---------- ----------
0 0 create table logminer_test(id number,name varchar2(30)); 09000100D1040000 9 1 1233
0 0 insert into "SYS"."LOGMINER_TEST"("ID","NAME") values ('1',' 07000C00A3030000 7 12 931
xsc1');
0 0 insert into "SYS"."LOGMINER_TEST"("ID","NAME") values ('1',' 07000C00A3030000 7 12 931
xsc2');
4.6 关闭日志挖掘
SYS@rac11g1 > EXECUTE dbms_logmnr.END_LOGMNR;
PL/SQL procedure successfully completed.
5 使用在线目录进行日志挖掘，此方法不用设置utl_file_dir，也不用重启数据库
5.1 加载相关在线日志及归档日志
SYS@rac11g1 > BEGIN
2 dbms_logmnr.add_logfile(logfilename=>'+FRA2/rac11g/onlinelog/group_1.257.1081073047',options=>dbms_logmnr.NEW);
3 dbms_logmnr.add_logfile(logfilename=>'+FRA2/rac11g/onlinelog/group_2.258.1081073049',options=>dbms_logmnr.ADDFILE);
4 dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_141.347.1129282081',options=>dbms_logmnr.ADDFILE);
5 dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_142.348.1129282083',options=>dbms_logmnr.ADDFILE);
6 dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_143.349.1129283881',options=>dbms_logmnr.ADDFILE);
7 dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_144.350.1129283995',options=>dbms_logmnr.ADDFILE);
8 dbms_logmnr.add_logfile(logfilename=>'+FRA2/RAC11G/ARCHIVELOG/2023_02_20/thread_1_seq_145.351.1129284011',options=>dbms_logmnr.ADDFILE);
9 END;
10 /
PL/SQL procedure successfully completed.
5.2 启用在线目录日志挖掘
SYS@rac11g1 > EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
5.3 查看相关事务执行的SQL
SYS@rac11g1 > SELECT SESSION#,SERIAL#,sql_redo,XID,XIDUSN,XIDSLT,XIDSQN FROM v$logmnr_contents
where SEG_NAME='LOGMINER_TEST';
SESSION# SERIAL# SQL_REDO XID XIDUSN XIDSLT XIDSQN
---------- ---------- ------------------------------------------------------------ ---------------- ---------- ---------- ----------
0 0 create table logminer_test(id number,name varchar2(30)); 09000100D1040000 9 1 1233
0 0 insert into "SYS"."LOGMINER_TEST"("ID","NAME") values ('1',' 07000C00A3030000 7 12 931
xsc1');
0 0 insert into "SYS"."LOGMINER_TEST"("ID","NAME") values ('1',' 07000C00A3030000 7 12 931
xsc2');
5.4 关闭日志挖掘
SYS@rac11g1 > EXECUTE dbms_logmnr.END_LOGMNR;
PL/SQL procedure successfully completed.
6 业务数据太多，怕影响生产系统，将相关归档日志导出到测试系统，进行日志挖掘
6.1 进行数据挖掘，在执行相关SQL时需要在数据库层面开启附加日志，如果不开启，则报如下错误
原生产库汇报如下错误：
SYS@rac11g1 > begin
2 dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-01354: Supplemental log data must be added to run this command
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6110
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6208
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 2
SYS@rac11g1 > alter database add supplemental log data;
Database altered.
测试环境进行数据挖掘，汇报如下错误：
SYS@orcl>begin
2 dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_redo_logs);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-01347: Supplemental log data no longer found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2
[oracle@oracle11 ~]$ oerr ora 1347
01347, 00000, "Supplemental log data no longer found"
// *Cause: The source database instance producing log files for this LogMiner
// session was altered to no longer log supplemental data.
// *Action: Destroy this Logminer session. Re-enable supplemental log data
// on the source system and create a new LogMiner session.
根据如上信息，认为源端没有开启附加日志，导致无法在测试数据库进行数据挖掘。于是重新进行测试。
6.2 开启附加日志后，在生产环境重新准备测试数据，如下：
SYS@rac11g1 > archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA2
Oldest online log sequence 157
Next log sequence to archive 158
Current log sequence 158
select s.sid,s.SERIAL#,p.pid,p.spid from v$session s,v$process p where s.paddr = p.addr
and s.sid in (select distinct sid from v$mystat);
SID SERIAL# PID SPID
---------- ---------- ---------- ------------------------
156 43 33 11496
create table logminer_test1(id number,name varchar2(30));
insert into logminer_test1 values(1,'xsc1');
insert into logminer_test1 values(1,'xsc2');
SID SERIAL# TO_CHAR(T.START_DAT MACHINE PROGRAM CLIENT_INFO tran_id
---------- ---------- ------------------- -------------------- ------------------------------------ -------------------- --------------------------
156 43 2023-02-22 14:49:10 linux1 sqlplus@linux1 (TNS V1-V3) 9.20.1263
6.3 将生产库的数据字典，导出到归档日志
begin
dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
end;
/
6.4 查看导出数据字典的起止位置，根据如下信息，导出的归档日志为160 和 161
SYS@rac11g1 > select * from v$archived_log where dictionary_begin= 'YES' or dictionary_end = 'YES'
order by dest_id,thread#,sequence#;
+FRA2/rac11g/archivelog/2023_02_22/ thread_1_seq_160.366.1129474249
1 1 160 1 20-AUG-21 1081073038 2050450 22-FEB-23 2051208 22-FEB-23 33868 512 ARCH ARCH NO YES NO NO A 22-FEB-23 YES NO NO 0 1 2133292110 NO NO NO NO
104 1129474252
+FRA2/rac11g/archivelog/2023_02_22/t hread_1_seq_161.367.1129474253
1 1 161 1 20-AUG-21 1081073038 2051208 22-FEB-23 2051782 22-FEB-23 767 512 FGRD FGRD NO YES NO NO A 22-FEB-23 NO YES NO 0 1 2133292110 NO NO NO NO
6.5 将 生产库相关的归档日志拷贝到测试环境
cp thread_1_seq_158.364.1129474245 /tmp/archive_test
cp thread_1_seq_159.365.1129474247 /tmp/archive_test
cp thread_1_seq_160.366.1129474249 /tmp/archive_test
cp thread_1_seq_161.367.1129474253 /tmp/archive_test
oracle@linux1 archive_test]$ scp * root@192.168.4.81:/tmp/archive_test/
The authenticity of host '192.168.4.81 (192.168.4.81)' can't be established.
RSA key fingerprint is ab:d1:1a:7c:2b:91:ec:7c:ca:e0:a3:ee:78:63:19:c0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.4.81' (RSA) to the list of known hosts.
root@192.168.4.81's password:
thread_1_seq_158.364.1129474245 100% 2048 2.0KB/s 00:00
...
6.6 加载生产库的归档日志到测试环境：
SYS@orcl>BEGIN
2 dbms_logmnr.add_logfile(logfilename=>'/tmp/archive_test/thread_1_seq_158.364.1129474245',
options=>dbms_logmnr.NEW);
3 dbms_logmnr.add_logfile(logfilename=>'/tmp/archive_test/thread_1_seq_159.365.1129474247',
options=>dbms_logmnr.ADDFILE);
4 dbms_logmnr.add_logfile(logfilename=>'/tmp/archive_test/thread_1_seq_160.366.1129474249',
options=>dbms_logmnr.ADDFILE);
5 dbms_logmnr.add_logfile(logfilename=>'/tmp/archive_test/thread_1_seq_161.367.1129474253',
options=>dbms_logmnr.ADDFILE);
6 END;
7 /
PL/SQL procedure successfully completed.
6.7 指定logminer进行数据挖掘时的数据字典信息来源于redo日志
SYS@orcl>begin
2 dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_redo_logs);
3 end;
4 /
PL/SQL procedure successfully completed.
6.8 查询相关事务，同测试的一致。
SELECT SESSION#,SERIAL#,sql_redo,XID,XIDUSN,XIDSLT,XIDSQN FROM v$logmnr_contents where SEG_NAME='LOGMINER_TEST1';
SESSION# SERIAL# SQL_REDO XID XIDUSN XIDSLT XIDSQN
---------- ---------- ---------------------------------------------------------------------- ---------------- ---------- ---------- ----------
156 43 create table logminer_test1(id number,name varchar2(30)); 0600150009050000 6 21 1289
156 43 insert into "SYS"."LOGMINER_TEST1"("ID","NAME") values ('1','xsc1'); 09001400EF040000 9 20 1263
156 43 insert into "SYS"."LOGMINER_TEST1"("ID","NAME") values ('1','xsc2'); 09001400EF040000 9 20 1263