logminer進行資料探勘分析測試

xueshancheng發表於2023-02-23

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章