1)查看是否已经安装

SQL> desc DBMS_LOGMNR

SQL> desc DBMS_LOGMNR_D

如果没有内容，需要进行setup

2)安装LogMiner,运行下列脚本

SQL> !ls -al $ORACLE_HOME/rdbms/admin/ |grep dbmslm

-rw-r--r-- 1 oracle dba 4924 Feb 21 2006 dbmslmd.sql

-rw-r--r-- 1 oracle dba 17938 Sep 17 2011 dbmslm.sql

-rw-r--r-- 1 oracle dba 7215 Oct 16 2005 dbmslms.sql

3)查看补充日志情况

select LOG_MODE,

FORCE_LOGGING,

supplemental_log_data_min,

supplemental_log_data_pk,

supplemental_log_data_ui,

supplemental_log_data_fk,

supplemental_log_data_all

from v$database;

LOG_MODE FOR SUPPLEME SUP SUP SUP SUP

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

ARCHIVELOG NO NO NO NO NO NO

数据库处于归档模式，但是force_logging没有开启,补充日志也没有开启

alter database force logging;

alter database add supplemental log data;

alter database add supplemental log data (primary key, unique index,foreign key,all) columns;

4)配置

4.1)创建数据字典（data dictionary）文件

SQL> show parameter UTL_FILE_DIR

NAME TYPE VALUE

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

utl_file_dir string

该参数用于指定一个或者多个目录Oracle用户PLSQL的文件I/O

SQL> alter system set utl_file_dir='$ORACLE_HOME/logs' scope=both;

alter system set utl_file_dir='$ORACLE_HOME/logs' scope=both

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

该参数只能在spfile级别上修改

SQL> alter system set utl_file_dir='/u01/app/oracle/product/11.2.0.3/dbhome_1/logs' scope=spfile;

System altered.

重启数据库使得参数生效

SQL> startup force

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 1056966856 bytes

Database Buffers 1073741824 bytes

Redo Buffers 4947968 bytes

Database mounted.

Database opened.

4.2)生成数据字典注：在线分析不需要此步骤

SQL> !mkdir $ORACLE_HOME/logs

SQL> !touch file $ORACLE_HOME/logs/sqltrace.ora

[oracle@wdctl1001 logs]$ chmod 777 sqltrace.ora

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => '/u01/app/oracle/product/11.2.0.3/dbhome_1/logs/sqltrace.ora', dictionary_location => '/u01/app/oracle/product/11.2.0.3/dbhome_1/logs');

PL/SQL procedure successfully completed.

注意，上面1,3两步中，地址不要用$ORACLE_HOME变量，不然会报错，如：

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'sqltrace.ora', dictionary_location => '$ORACLE_HOME/logs');

BEGIN dbms_logmnr_d.build(dictionary_filename => 'sqltrace.ora', dictionary_location => '$ORACLE_HOME/logs'); 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 5972

ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6062

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12

ORA-06512: at line 1

SQL> show parameter utl_file_dir

NAME TYPE VALUE

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

utl_file_dir string $ORACLE_HOME/logs

4.3)添加日志

SQL> col member for a40

SQL> select group#,member from v$logfile;

GROUP# MEMBER

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

1 +DATA1/hztest1/redo01.log

2 +DATA1/hztest1/redo02.log

3 +DATA1/hztest1/redo03.log

4 +DATA1/hztest1/redo04.log

5 +DATA1/hztest1/redo05.log

6 +DATA1/hztest1/redo06.log

6 rows selected.

在我的案例中group4 ,5,6是当前的redo thread(thread2)，所以对这些redo添加日志

SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'+DATA1/hztest1/redo04.log',Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_logmnr.add_logfile( LogFileName=>'+DATA1/hztest1/redo05.log',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_logmnr.add_logfile( LogFileName=>'+DATA1/hztest1/redo06.log',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

4.4)分析

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

--使用生成的数据字典分析

EXECUTE

dbms_logmnr.start_logmnr(DictFileName=>'/u01/app/oracle/product/11.2.0.3/dbhome_1/logs/sqltrace.ora');

SQL> select THREAD#,group#,sequence#,first_change#,next_change#,first_time,next_time from v$log

THREAD# GROUP# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME NEXT_TIME

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

1 1 109 9642389 9642391 2013-05-29 02:49:35 2013-05-29 02:49:35

1 2 110 9642391 9642504 2013-05-29 02:49:35 2013-05-29 02:49:37

1 3 108 9642243 9642389 2013-05-29 02:44:36 2013-05-29 02:49:35

2 4 28 9788062 2.8147E+14 2013-05-30 02:18:59

2 5 26 9747205 9767827 2013-05-30 02:03:21 2013-05-30 02:18:15

2 6 27 9767827 9788062 2013-05-30 02:18:15 2013-05-30 02:18:59

--通过设置起始SCN和截至SCN来限制要分析日志的范围

EXECUTE dbms_logmnr.start_logmnr(StartScn => 9747205,EndScn => 9767827, PTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

4.5)查看分析结果

select START_SCN,COMMIT_SCN,OPERATION,status from v$logmnr_contents

START_SCN COMMIT_SCN OPERATION STATUS

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

INSERT 0

9747794 9747798 COMMIT 0

START 0

UPDATE 0

9747799 9747800 COMMIT 0

START_SCN COMMIT_SCN OPERATION STATUS

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

START 0

UPDATE 0

9747801 9747802 COMMIT 0

ITPub博客

LogMiner的创建和使用

最新文章