安装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博客

安装和使用LogMiner工具

最新文章