logminer進行資料探勘分析測試
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69996316/viewspace-2936739/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料探勘與預測分析(第2版)
- 資料探勘與分析（網際網路行業）行業
- 進行資料探勘常見的方法有哪些呢？
- 介面測試的時候如何生成隨機資料進行測試隨機
- 資料探勘-預測模型彙總模型
- 資料探勘面試筆試題（附答案）面試筆試
- 資料分析與資料探勘 - 04科學計算
- 萌新向Python資料分析及資料探勘 前言Python
- Yahoo前任資料官：資料探勘與分析技巧（下）IF
- 怎麼進行資料分析
- 使用pandas進行資料分析
- 大資料如何進行分析大資料
- 淺談大資料、資料分析、資料探勘的區別！大資料
- 快速入門pandas進行資料探勘資料分析[多維度排序、資料篩選、分組計算、透視表](一)排序
- 使用oracle的logminer同步資料Oracle
- 資料探勘——SVM（乳腺癌檢測）
- 大資料包表如何進行自動化測試大資料
- 《資料分析與資料探勘》--天津大學公開課
- 大資料時代，如何做資料探勘與分析！大資料
- 資料探勘與分析 概念與演算法演算法
- 如何用Python進行資料分析？Python
- 大資料分析該如何進行大資料
- 資料探勘之產品預測任務
- 對node工程進行壓力測試與效能分析
- 資料探勘與資料分析的主要區別是什麼
- 企業內部資料如何進行資料分析
- 利用Tushare資料介面+pandas進行股票資料分析
- 效能狗（Perfdog）測試與資料分析
- 用 Python 進行資料分析 pandas (一)Python
- 薦書 | 《利用Python進行資料分析》Python
- 如何對資料目標進行分析
- 教你用SQL進行資料分析SQL
- 如何更好的進行大資料分析大資料
- 對圖資料庫（Nebula）進行單元測試時的坑資料庫
- 圖資料探勘：社群檢測演算法（一）演算法
- 一個測試用例裡面有多套測試資料，如何用 beautifureport 分別對各組測試資料進行統計測試通過與否
- 【資料分析】針對家庭用電資料進行時序分析（1）
- 資料探勘（ TO DO LIST）