logminer进行数据挖掘分析测试

来源：这里教程网时间：2026-03-03 18:24:16 作者：

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 添加在线日志及相关归档日志BEGINdbms_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 将生产库的数据字典，导出到归档日志 begindbms_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)? yesWarning: 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

编辑推荐：

返回列表

logminer进行数据挖掘分析测试

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推