安裝和使用LogMiner工具

安裝LogMiner工具

以SYSDBA身份執行
[oracle@localhost ~ Thu Apr 28]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 28 15:57:33 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@EXAM> @$ORACLE_HOME/rdbms/admin/dbmslm.sql;

Package created.
Grant succeeded.

sys@EXAM> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;

Package created.

sys@EXAM>

第一個指令碼用來建立DBMS_LOGMNR包，該包用來分析日誌檔案。
第二個指令碼用來建立DBMS_LOGMNR_D包，該包用來建立資料字典檔案。

預設一般都安裝了的吧可以分析日誌檔案異機分析的話指定原資料庫的字典檔案即可

使用LogMiner工具
1、建立資料字典檔案（data-dictionary）

（1）首先在引數檔案中，指定資料字典檔案的位置，也就是新增一個引數UTL_FILE_DIR，

UTL_FILE_DIR = ($ORACLE_BASE/utlfiledir) ,重新啟動資料庫，使新加的引數生效。

[oracle@localhost ~ Thu Apr 28]$ mkdir $ORACLE_BASE/utlfiledir
[oracle@localhost ~ Thu Apr 28]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 28 15:59:47 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@EXAM> alter system set utl_file_dir='/home/ora10g/utlfiledir' scope=spfile;

System altered.

sys@EXAM> startup force;
ORACLE instance started.

Total System Global Area 176160768 bytes
Fixed Size                  1218388 bytes
Variable Size              83888300 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
sys@EXAM> show parameter utl_file_dir;

NAME TYPE VALUE
------------------------------------ ----------------------

------------------------------
utl_file_dir string /home/ora10g/utlfiledir

sys@EXAM> exec dbms_logmnr_d.build('logmnr_1.ora','/home/ora10g/utlfiledir');
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

注意事項：定義utl_file_dir 引數最好使用完整路徑,使用預定義環境變數會導致無法開啟文

件目錄

2、建立要分析的日誌檔案列表：獲取歸檔或者聯機日誌列表
sys@EXAM> set linesize 220
col size for 999.99 heading "SIZE"
col applied for a5 heading "APP"
col deleted for a3 heading "DEL"
col status for a3 heading "ST"
col fal for a5
col name for a40
col SEQ for 999 heading "SEQ#"
col REG for a4
col archival_thread# for 99 heading "THR#"
sys@EXAM> select sequence# "SEQ",name,first_change# "FIRST",next_change# "NEXT",blocks*block_size/1024/1024 "size",archival_thread#,applied,registrar "REG",deleted,status,fal from v$archived_log;

SEQ# NAME FIRST NEXT SIZE

ARCHIVAL_THREAD# APP REG DEL ST FAL
---- ---------------------------------------- ---------- ---------- -------

---------------- ----- ---- --- --- -----
6 /home/new/1_6_747929818.dbf 610480 619684 12.86

1 NO ARCH NO A NO
6 610480 619684 12.86

1 NO ARCH YES D NO
6 610480 619684 12.86

1 NO ARCH YES D NO
7 /home/new/1_7_747929818.dbf 619684 619700 .00

1 NO ARCH NO A NO
7 619684 619700 .00

1 NO ARCH YES D NO
7 619684 619700 .00

1 NO ARCH YES D NO
8 /home/new/1_8_747929818.dbf 619700 619717 .01

1 NO ARCH NO A NO
8 619700 619717 .01

1 NO ARCH YES D NO
8 619700 619717 .01

1 NO ARCH YES D NO
9 /home/new/1_9_747929818.dbf 619717 647196 10.85

1 NO ARCH NO A NO
9 619717 647196 10.85

1 NO ARCH YES D NO
9 619717 647196 10.85

1 NO ARCH YES D NO
10 /home/new/1_10_747929818.dbf 647196 681609 20.75

1 NO ARCH NO A NO
10 647196 681609 20.75

1 NO ARCH YES D NO
11 /home/new/1_11_747929818.dbf 681609 695099 48.47

1 NO ARCH NO A NO
11 681609 695099 48.47

1 NO ARCH YES D NO
12 /home/new/1_12_747929818.dbf 695099 710083 48.47

1 NO ARCH NO A NO
12 695099 710083 48.47

1 NO ARCH YES D NO
13 /home/new/1_13_747929818.dbf 710083 738902 27.14

1 NO ARCH NO A NO
13 710083 738902 27.14

1 NO ARCH YES D NO
14 /home/new/1_14_747929818.dbf 738902 739218 .06

1 NO ARCH NO A NO
14 738902 739218 .06

1 NO ARCH YES D NO
15 /home/new/1_15_747929818.dbf 739218 739220 .00

1 NO ARCH NO A NO
15 739218 739220 .00

1 NO ARCH YES D NO
16 /home/new/1_16_747929818.dbf 739220 739225 .00

1 NO ARCH NO A NO
16 739220 739225 .00

1 NO ARCH YES D NO
17 /home/new/1_17_747929818.dbf 739225 739275 .00

1 NO ARCH NO A NO
17 739225 739275 .00

1 NO ARCH YES D NO
18 /home/new/1_18_747929818.dbf 739275 762561 48.47

1 NO ARCH NO A NO
18 739275 762561 48.47

1 NO ARCH YES D NO
19 /home/new/1_19_747929818.dbf 762561 777385 48.47

1 NO ARCH NO A NO
19 762561 777385 48.47

1 NO ARCH YES D NO
20 /home/new/1_20_747929818.dbf 777385 801870 18.28

1 NO ARCH NO A NO
20 /home/ora10g/flash_recovery_area/EXAM/ar 777385 801870 18.28

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_20_6tktdnlb_
     .arc

21 /home/new/1_21_747929818.dbf 801870 814146 48.47

1 NO ARCH NO A NO
21 /home/ora10g/flash_recovery_area/EXAM/ar 801870 814146 48.47

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_21_6tktrx92_
     .arc

22 /home/new/1_22_747929818.dbf 814146 845578 45.25

1 NO ARCH NO A NO
22 /home/ora10g/flash_recovery_area/EXAM/ar 814146 845578 45.25

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_22_6tkzxz30_
     .arc

23 /home/new/1_23_747929818.dbf 845578 848294 4.71

1 NO ARCH NO A NO
23 /home/ora10g/flash_recovery_area/EXAM/ar 845578 848294 4.71

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_23_6tl1ccp4_
     .arc

24 /home/new/1_24_747929818.dbf 848294 848297 .01

1 NO ARCH NO A NO
24 /home/ora10g/flash_recovery_area/EXAM/ar 848294 848297 .01

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_24_6tl1ck82_
     .arc

25 /home/new/1_25_747929818.dbf 848297 848301 .01

1 NO ARCH NO A NO
25 /home/ora10g/flash_recovery_area/EXAM/ar 848297 848301 .01

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_25_6tl1cq1p_
     .arc

26 /home/new/1_26_747929818.dbf 848301 848502 1.11

1 NO ARCH NO A NO
26 /home/ora10g/flash_recovery_area/EXAM/ar 848301 848502 1.11

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_26_6tl1jrv8_
     .arc

27 /home/new/1_27_747929818.dbf 848502 873111 13.95

1 NO ARCH NO A NO
27 /home/ora10g/flash_recovery_area/EXAM/ar 848502 873111 13.95

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_27_6tl421hn_
     .arc

28 /home/new/1_28_747929818.dbf 873111 890072 48.47

1 NO ARCH NO A NO
28 /home/ora10g/flash_recovery_area/EXAM/ar 873111 890072 48.47

       1 NO   ARCH NO A    NO
     chivelog/2011_04_16/o1_mf_1_28_6tlc2k6p_
     .arc

29 /home/new/1_29_747929818.dbf 890072 907452 48.47

1 NO ARCH NO A NO
30 /home/new/1_30_747929818.dbf 907452 944572 41.78

1 NO ARCH NO A NO
31 /home/new/1_31_747929818.dbf 944572 951374 48.47

1 NO ARCH NO A NO
32 /home/new/1_32_747929818.dbf 951374 974104 8.99

1 NO ARCH NO A NO
32 /home/ora10g/flash_recovery_area/EXAM/ar 951374 974104 8.99

       1 NO   ARCH NO A    NO
     chivelog/2011_04_23/o1_mf_1_32_6v45xlty_
     .arc

33 /home/new/1_33_747929818.dbf 974104 985473 48.47

1 NO ARCH NO A NO
34 /home/new/1_34_747929818.dbf 985473 1001378 48.47

1 NO ARCH NO A NO
34 /home/ora10g/flash_recovery_area/EXAM/ar 985473 1001378 48.47

       1 NO   ARCH NO A    NO
     chivelog/2011_04_23/o1_mf_1_34_6v4kv76m_
     .arc

35 /home/new/1_35_747929818.dbf 1001378 1017712 48.47

1 NO ARCH NO A NO
35 /home/ora10g/flash_recovery_area/EXAM/ar 1001378 1017712 48.47

       1 NO   ARCH NO A    NO
     chivelog/2011_04_23/o1_mf_1_35_6v4tfdqh_
     .arc

36 /home/new/1_36_747929818.dbf 1017712 1040900 11.08

1 NO ARCH NO A NO
36 /home/ora10g/flash_recovery_area/EXAM/ar 1017712 1040900 11.08

       1 NO   ARCH NO A    NO
     chivelog/2011_04_23/o1_mf_1_36_6v4xn4yc_
     .arc

37 /home/new/1_37_747929818.dbf 1040900 1041272 .18

1 NO FGRD NO A NO
37 /home/ora10g/flash_recovery_area/EXAM/ar 1040900 1041272 .18

       1 NO   FGRD NO A    NO
     chivelog/2011_04_23/o1_mf_1_37_6v4xxdsq_
     .arc

38 /home/new/1_38_747929818.dbf 1041272 1048574 25.45

1 NO ARCH NO A NO
38 /home/ora10g/flash_recovery_area/EXAM/ar 1041272 1048574 25.45

       1 NO   ARCH NO A    NO
     chivelog/2011_04_23/o1_mf_1_38_6v5194cy_
     .arc

39 /home/new/1_39_747929818.dbf 1048574 1076514 22.33

1 NO ARCH NO A NO
39 /home/ora10g/flash_recovery_area/EXAM/ar 1048574 1076514 22.33

       1 NO   ARCH NO A    NO
     chivelog/2011_04_28/o1_mf_1_39_6vl2cgjd_
     .arc

40 /home/new/1_40_747929818.dbf 1076514 1082494 48.47

1 NO ARCH NO A NO
40 /home/ora10g/flash_recovery_area/EXAM/ar 1076514 1082494 48.47

       1 NO   ARCH NO A    NO
     chivelog/2011_04_28/o1_mf_1_40_6vl4kl0p_
     .arc

41 /home/new/1_41_747929818.dbf 1082494 1108808 30.27

1 NO ARCH NO A NO
41 /home/ora10g/flash_recovery_area/EXAM/ar 1082494 1108808 30.27

       1 NO   ARCH NO A    NO
     chivelog/2011_04_28/o1_mf_1_41_6vl80739_
     .arc

72 rows selected.

sys@EXAM> exec dbms_logmnr.add_logfile('/home/new/1_40_747929818.dbf',dbms_logmnr.new);
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
再次新增歸檔日誌
sys@EXAM> exec dbms_logmnr.add_logfile('/home/new/1_40_747929818.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

新增聯機日誌

sys@EXAM> set linesize 200
col GROUP format 99
col sequence# format 999 heading "SEQ"
col blocksize for 99999 heading "SIZE"
col members for 99 heading "MEM"
col archived for a3 heading "ARC"
col status for a10
col TYPE for a10
col MEM for a60 heading "FILE"
sys@EXAM> select f.member "MEM",f.type "TYPE",l.group# "GROUP",sequence#,bytes,members,archived,l.status "STATUS" from v$log l,v$logfile f where l.group#=f.group# order by l.group#;

MEM
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

----------------------------------
TYPE GROUP SEQ BYTES MEM ARC ST
---------- ----- ---- ---------- --- --- ----------
/home/ora10g/oradata/exam/redo01.log
ONLINE 1 41 52428800 2 YES INACTIVE

/home/new/redo0102.log
ONLINE 1 41 52428800 2 YES INACTIVE

/home/ora10g/oradata/exam/redo02.log
ONLINE 2 42 52428800 1 NO CURRENT

/home/ora10g/oradata/exam/redo03.log
ONLINE 3 40 52428800 1 YES INACTIVE

sys@EXAM> exec dbms_logmnr.add_logfile('/home/ora10g/oradata/exam/redo02.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

3,檢視加入分析的聯機和歸檔日誌

sys@EXAM> set linesize 220
col log_id for 9999
col filename for a40
col low_time for a20
col high_time for a20
col low_scn for 9999999999
col next_scn for 9999999999
col blocksize for 9999
col filesize/1024 for 99999

sys@EXAM> select log_id,filename,low_time,high_time,low_scn,next_scn,blocksize,filesize/1024 from v$logmnr_logs;

LOG_ID FILENAME LOW_TIME HIGH_TIME

LOW_SCN NEXT_SCN SIZE FILESIZE/1024
------ ---------------------------------------- --------------------

-------------------- ----------- ----------- ----- -------------
40 /home/new/1_40_747929818.dbf 28-APR-11 28-APR-11

1076514 1082494 512 49636.5
41 /home/new/redo0102.log 28-APR-11 28-APR-11

1082494 1108808 512 31000
42 /home/ora10g/oradata/exam/redo02.log 28-APR-11 01-JAN-88

1108808 ########### 512 31000

Elapsed: 00:00:00.00

4,刪除新增的日誌

sys@EXAM> exec dbms_logmnr.add_logfile('/home/new/1_40_747929818.dbf',dbms_logmnr.removefile);

PL/SQL procedure successfully completed.

5,開始日誌分析

（1）無限制條件，即用資料字典檔案對要分析的日誌檔案所有內容做分析：

sys@EXAM> exec dbms_logmnr.start_logmnr;

PL/SQL procedure successfully completed.

（2）帶限制條件：

可以用scn號或時間做限制條件（指定dictfilename資料字典檔案以免產生的日誌分析使用內

部資料表示法很難閱讀）

exec dbms_logmnr.start_logmnr(startscn=>xxxxxxxxx,
                              endscn=>xxxxxxxx,
                              dictfilename=>'xxxxxxx',
                              starttime=>'xxxxxxxxx',
                              endtime=>'xxxxx');

6、分析後釋放記憶體：

sys@EXAM> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

檢視相關檢視
sys@EXAM> col table_name for a30
sys@EXAM> col table_name for a30

sys@EXAM> select * from dict where table_name like '%LOGMNR%';

TABLE_NAME
------------------------------
COMMENTS
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

------------------------------------------------------
DBA_LOGMNR_LOG

DBA_LOGMNR_SESSION

DBA_LOGMNR_PURGED_LOG

V$LOGMNR_CONTENTS
Synonym for V_$LOGMNR_CONTENTS

V$LOGMNR_PARAMETERS
Synonym for V_$LOGMNR_PARAMETERS

V$LOGMNR_DICTIONARY
Synonym for V_$LOGMNR_DICTIONARY

V$LOGMNR_LOGS
Synonym for V_$LOGMNR_LOGS

V$LOGMNR_STATS
Synonym for V_$LOGMNR_STATS

V$LOGMNR_DICTIONARY_LOAD
Synonym for V_$LOGMNR_DICTIONARY_LOAD

GV$LOGMNR_CONTENTS
Synonym for GV_$LOGMNR_CONTENTS

GV$LOGMNR_PARAMETERS
Synonym for GV_$LOGMNR_PARAMETERS

GV$LOGMNR_DICTIONARY
Synonym for GV_$LOGMNR_DICTIONARY

GV$LOGMNR_LOGS
Synonym for GV_$LOGMNR_LOGS

V$LOGMNR_LOGFILE
Synonym for V_$LOGMNR_LOGFILE

V$LOGMNR_PROCESS
Synonym for V_$LOGMNR_PROCESS

V$LOGMNR_LATCH
Synonym for V_$LOGMNR_LATCH

V$LOGMNR_TRANSACTION
Synonym for V_$LOGMNR_TRANSACTION

V$LOGMNR_REGION
Synonym for V_$LOGMNR_REGION

V$LOGMNR_CALLBACK
Synonym for V_$LOGMNR_CALLBACK

V$LOGMNR_SESSION
Synonym for V_$LOGMNR_SESSION

GV$LOGMNR_LOGFILE
Synonym for GV_$LOGMNR_LOGFILE

GV$LOGMNR_PROCESS
Synonym for GV_$LOGMNR_PROCESS

GV$LOGMNR_LATCH
Synonym for GV_$LOGMNR_LATCH

GV$LOGMNR_TRANSACTION
Synonym for GV_$LOGMNR_TRANSACTION

GV$LOGMNR_REGION
Synonym for GV_$LOGMNR_REGION

GV$LOGMNR_CALLBACK
Synonym for GV_$LOGMNR_CALLBACK

GV$LOGMNR_SESSION
Synonym for GV_$LOGMNR_SESSION

GV$LOGMNR_STATS
Synonym for GV_$LOGMNR_STATS

GV$LOGMNR_DICTIONARY_LOAD
Synonym for GV_$LOGMNR_DICTIONARY_LOAD

29 rows selected.

相關包及過程

sys@EXAM> 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@EXAM> 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

建立資料字典的目的: 讓LogMiner引用涉及到內部資料字典中的部分時為他們實際的名字，而

不是系統內部的16進位制。資料字典檔案是一個文字檔案，使用包 DBMS_LOGMNR_D來建立。如果

我們要分析的資料庫中的表有變化，影響到庫的資料字典也發生變化，這時就需要重新建立該

字典檔案。另外一種情況是在分析另外一個資料庫檔案的重作日誌時，也必須要重新生成一遍

被分析資料庫的資料字典檔案。在使用LogMiner工具分析redo log檔案之前，可以使用

DBMS_LOGMNR_D 包將資料字典匯出為一個文字檔案。該字典檔案是可選的，但是如果沒有它，

LogMiner解釋出來的語句中關於資料字典中的部分（如表名、列名等）和數值都將是16進位制的

形式，我們是無法直接理解的。
例如，下面的SQL語句：

INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, '張三');

LogMiner解釋出來的結果將是下面這個樣子:

insert into Object#308(col#1, col#2) values (hextoraw('c30rte567e436'), hextoraw

('4a6f686e20446f65'));

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-694033/，如需轉載，請註明出處，否則將追究法律責任。

安裝和使用LogMiner工具

相關文章