日志挖掘器使用 dbms_logmnr

安装LogMiner工具

                     @$ORACLE_HOME\rdbms\admin\dbmslm.sql;              以SYSDBA身份运行
                     @ $ORACLE_HOME\rdbms\admin\dbmslmd.sql;

第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。

第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

默认一般都安装了的吧  可以分析8i的日志文件   异机分析的话指定原数据库的字典文件即可

1、创建数据字典文件（data-dictionary）

（1）首先在参数文件中，指定数据字典文件的位置，也就是添加一个参数UTL_FILE_DIR，

UTL_FILE_DIR = ($ORACLE_BASE/utlfiledir) ,重新启动数据库，使新加的参数生效。

[oracle@localhost ~]$ echo  $ORACLE_BASE
/boot/u01/app/oracle

SYS@primary/2011-04-23 19:59:52> alter system set utl_file_dir=' $ORACLE_BASE/utlfiledir' scope=spfile;
SYS@primary/23-APR-11>show parameter utl_file_dir

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
utl_file_dir                         string     $ORACLE_BASE/utlfiledir
SYS@primary/23-APR-11>

SYS@primary/23-APR-11> exec dbms_logmnr_d.build('logmnr_1.ora','$ORACLE_BASE/utlfiledir');
BEGIN dbms_logmnr_d.build('logmnr_1.ora','$ORACLE_BASE/utlfiledir'); END;

*
ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6003
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6093
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

SYS@primary/23-APR-11> exec dbms_logmnr_d.build('logmnr_1.ora','/boot/u01/app/oracle/utlfiledir');
BEGIN dbms_logmnr_d.build('logmnr_1.ora','/boot/u01/app/oracle/utlfiledir'); END;

*
ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29280: invalid directory path
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6003
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6093
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
SYS@primary/23-APR-11> alter system set utl_file_dir='/boot/u01/app/oracle/utlfiledir' scope=spfile;

System altered.

Elapsed: 00:00:00.01
SYS@primary/23-APR-11> startup force
ORACLE instance started.

Total System Global Area  376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             327158452 bytes
Database Buffers           41943040 bytes
Redo Buffers                6197248 bytes
Database mounted.
Database opened.
SYS@primary/23-APR-11> show parameter utl_file

NAME                                 TYPE                  VALUE
------------------------------------             ----------
utl_file_dir                         string     /boot/u01/app/oracle/utlfiledi
                                 

SYS@primary/23-APR-11> exec dbms_logmnr_d.build('logmnr_1.ora','/boot/u01/app/oracle/utlfiledir');

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.38

注意事项： 定义utl_file_dir 参数最好使用完整路径,使用预定义环境变量会导致无法打开文件目录

2、 创建要分析的日志文件列表：获取归档或者联机日志列表
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#"
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
/
 179 /boot/u02/oradata/1_179_747684876.dbf       1323541    1323746     .17    1 NO    ARCH NO  A   NO
 179 /boot/u03/oradata/1_179_747684876.dbf       1323541    1323746     .17    1 NO    ARCH NO  A   NO
 179 /boot/u04/oradata/1_179_747684876.dbf       1323541    1323746     .17    1 NO    ARCH NO  A   NO
 180 /boot/u02/oradata/1_180_747684876.dbf       1323746    1327948    5.32    1 NO    ARCH NO  A   NO
 180 /boot/u03/oradata/1_180_747684876.dbf       1323746    1327948    5.32    1 NO    ARCH NO  A   NO
 180 /boot/u04/oradata/1_180_747684876.dbf       1323746    1327948    5.32    1 NO    ARCH NO  A   NO
 181 /boot/u02/oradata/1_181_747684876.dbf       1327948    1328067     .03    1 NO    ARCH NO  A   NO
 181 /boot/u03/oradata/1_181_747684876.dbf       1327948    1328067     .03    1 NO    ARCH NO  A   NO
 181 /boot/u04/oradata/1_181_747684876.dbf       1327948    1328067     .03    1 NO    ARCH NO  A   NO
 182 /boot/u02/oradata/1_182_747684876.dbf       1328067    1349872    1.12    1 NO    ARCH NO  A   NO
 182 /boot/u03/oradata/1_182_747684876.dbf       1328067    1349872    1.12    1 NO    ARCH NO  A   NO
 182 /boot/u04/oradata/1_182_747684876.dbf       1328067    1349872    1.12    1 NO    ARCH NO  A   NO
 183 /boot/u02/oradata/1_183_747684876.dbf       1349872    1370172     .24    1 NO    ARCH NO  A   NO
 183 /boot/u03/oradata/1_183_747684876.dbf       1349872    1370172     .24    1 NO    ARCH NO  A   NO
 183 /boot/u04/oradata/1_183_747684876.dbf       1349872    1370172     .24    1 NO    ARCH NO  A   NO



SYS@primary/23-APR-11> exec dbms_logmnr.add_logfile('/boot/u04/oradata/1_182_747684876.dbf',dbms_logmnr.new);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
再次添加归档日志
SYS@primary/23-APR-11> exec dbms_logmnr.add_logfile('/boot/u03/oradata/1_183_747684876.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
添加联机日志


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"
select f.member "MEM",f.type "TYPE",l.group# "GROUP",sequence#,blocksize,
members,archived,l.status "STATUS"
 from v$log l, v$logfile f where l.group#=f.group# order by l.group#;

FILE                                                         TYPE       GROUP  SEQ   SIZE MEM ARC ST
------------------------------------------------------------ ---------- ----- ---- ------ --- --- ----------
/boot/u02/oradata/ORCL_1/onlinelog/o1_mf_1_6soypgvj_.log     ONLINE         1  182    512   3 YES INACTIVE
/boot/u03/oradata/ORCL_1/onlinelog/o1_mf_1_6soyph3t_.log     ONLINE         1  182    512   3 YES INACTIVE
/boot/u04/oradata/ORCL_1/onlinelog/o1_mf_1_6soypjgh_.log     ONLINE         1  182    512   3 YES INACTIVE
/boot/u02/oradata/ORCL_1/onlinelog/o1_mf_2_6sovhfc8_.log     ONLINE         2  183    512   3 YES INACTIVE
/boot/u03/oradata/ORCL_1/onlinelog/o1_mf_2_6sovhfrz_.log     ONLINE         2  183    512   3 YES INACTIVE
/boot/u04/oradata/ORCL_1/onlinelog/o1_mf_2_6sovhhr0_.log     ONLINE         2  183    512   3 YES INACTIVE
/boot/u02/oradata/ORCL_1/onlinelog/o1_mf_3_6sxxhvhc_.log     ONLINE         3  184    512   3 NO  CURRENT
/boot/u03/oradata/ORCL_1/onlinelog/o1_mf_3_6sxxhvtq_.log     ONLINE         3  184    512   3 NO  CURRENT
/boot/u04/oradata/ORCL_1/onlinelog/o1_mf_3_6sxxhxw4_.log     ONLINE         3  184    512   3 NO  CURRENT

9 rows selected.

Elapsed: 00:00:00.01


SYS@primary/23-APR-11> exec dbms_logmnr.add_logfile('/boot/u04/oradata/ORCL_1/onlinelog/o1_mf_3_6sxxhxw4_.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.
查看加入分析的联机和归档日志
Elapsed: 00:00:00.00
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
select log_id,filename,low_time,high_time,low_scn,
next_scn,blocksize,filesize/1024 from v$logmnr_logs
  3  /

LOG_ID FILENAME                                 LOW_TIME             HIGH_TIME                LOW_SCN    NEXT_SCN  SIZE FILESIZE/1024
------ ---------------------------------------- -------------------- -------------------- ----------- ----------- ----- -------------
   182 /boot/u04/oradata/1_182_747684876.dbf    23-APR-11            23-APR-11                1328067     1349872   512         1145
   183 /boot/u03/oradata/1_183_747684876.dbf    23-APR-11            23-APR-11                1349872     1370172   512          245
   184 /boot/u04/oradata/ORCL_1/onlinelog/o1_mf 23-APR-11            01-JAN-88                1370172 ###########   512            0
       _3_6sxxhxw4_.log


3 rows selected.

Elapsed: 00:00:00.00

删除添加的日志

SYS@primary/23-APR-11> exec dbms_logmnr.add_logfile('/boot/u04/oradata/1_182_747684876.dbf',dbms_logmnr.removefile);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

开始日志分析

（1）无限制条件，即用数据字典文件对要分析的日志文件所有内容做分析：

SYS@primary/23-APR-11>exec dbms_logmnr.start_logmnr;

       PL/SQL procedure successfully completed.

          Elapsed: 00:00:00.02
（2）带限制条件：

可以用scn号或时间做限制条件（指定dictfilename数据字典文件  以免产生的日志分析使用内部数据表示法  很难阅读）

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

4、分析后释放内存：

SQL> 

PL/SQL procedure successfully completed

相关视图

SYS@primary/23-APR-11>col table_name for a30

       SYS@primary/23-APR-11>col comments for a40
SYS@primary/23-APR-11>select * from dict where table_name like '%LOGMNR%';

TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------
DBA_LOGMNR_LOG
DBA_LOGMNR_PURGED_LOG
DBA_LOGMNR_SESSION
V$LOGMNR_CALLBACK              Synonym for V_$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS              Synonym for V_$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY            Synonym for V_$LOGMNR_DICTIONARY
V$LOGMNR_DICTIONARY_LOAD       Synonym for V_$LOGMNR_DICTIONARY_LOAD
V$LOGMNR_LATCH                 Synonym for V_$LOGMNR_LATCH
V$LOGMNR_LOGFILE               Synonym for V_$LOGMNR_LOGFILE
V$LOGMNR_LOGS                  Synonym for V_$LOGMNR_LOGS
V$LOGMNR_PARAMETERS            Synonym for V_$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS               Synonym for V_$LOGMNR_PROCESS
V$LOGMNR_REGION                Synonym for V_$LOGMNR_REGION
V$LOGMNR_SESSION               Synonym for V_$LOGMNR_SESSION
V$LOGMNR_STATS                 Synonym for V_$LOGMNR_STATS
V$LOGMNR_TRANSACTION           Synonym for V_$LOGMNR_TRANSACTION
GV$LOGMNR_CALLBACK             Synonym for GV_$LOGMNR_CALLBACK
GV$LOGMNR_CONTENTS             Synonym for GV_$LOGMNR_CONTENTS
GV$LOGMNR_DICTIONARY           Synonym for GV_$LOGMNR_DICTIONARY
GV$LOGMNR_DICTIONARY_LOAD      Synonym for GV_$LOGMNR_DICTIONARY_LOAD
GV$LOGMNR_LATCH                Synonym for GV_$LOGMNR_LATCH
GV$LOGMNR_LOGFILE              Synonym for GV_$LOGMNR_LOGFILE
GV$LOGMNR_LOGS                 Synonym for GV_$LOGMNR_LOGS
GV$LOGMNR_PARAMETERS           Synonym for GV_$LOGMNR_PARAMETERS
GV$LOGMNR_PROCESS              Synonym for GV_$LOGMNR_PROCESS
GV$LOGMNR_REGION               Synonym for GV_$LOGMNR_REGION
GV$LOGMNR_SESSION              Synonym for GV_$LOGMNR_SESSION
GV$LOGMNR_STATS                Synonym for GV_$LOGMNR_STATS
GV$LOGMNR_TRANSACTION          Synonym for GV_$LOGMNR_TRANSACTION

29 rows selected.

Elapsed: 00:00:00.91
   
相关包及过程

SYS@primary/23-APR-11>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@primary/23-APR-11>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/24890594/viewspace-693266/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24890594/viewspace-693266/