Oracle Logminer的测试使用

前言

版本：11.2.0.4.0

几个月前生产帮忙业务追溯数据异常时候用了下，一直想出个总结拖到现在。

实验

对 logminer强需求的要求开启附加日志，不开启会有一个问题下边实验会讲。

alter database add supplemental log data;

1.安装logminer工具（默认是安装的）

1.$ORACLE_HOME/rdbms/admin/dbmslm.sql
2.$ORACLE_HOME/rdbms/admin/dbmslmd.sql

这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

2..使用logminer功能一个重要的前提就是需要设置参数utl_file_dir，这个参数是需要重启DB才能生效的。

14:31:32 SYS@zkm(1)> alter system set utl_file_dir='/home/oracle/logminer/' scope=spfile;

System altered.

Elapsed: 00:00:00.00
14:33:33 SYS@zkm(1)> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
14:33:43 SYS@zkm(1)> startup;
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size                  2257352 bytes
Variable Size             478154296 bytes
Database Buffers          301989888 bytes
Redo Buffers                2596864 bytes
Database mounted.
Database opened.

3.创建用于存放logminer表的表空间并指定

14:37:48 SYS@zkm(1)> create tablespace log_mnr_tbs datafile '/u01/app/oracle/oradata/zkm/log_mnr_tbs01.dbf' size 1g autoextend off;

Tablespace created.

Elapsed: 00:00:21.21

14:38:43 SYS@zkm(1)> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('log_mnr_tbs');    

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.61

默认logminer的表是放在system表空间的，从操作规范上需要专门为此创建一个新表空间。

4.提取数据字典信息（不使用数据字典会使得挖出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，我们是无法直接理解的）

14:43:10 SYS@zkm(1)> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'logmnrtest.ora',dictionary_location => '/home/oracle/logminer/');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.43

这一步执行后会多出很多的表和其它的一些对象：

14:43:30 SYS@zkm(36)> col segment_name for a100
14:43:37 SYS@zkm(36)> set line 500
14:43:39 SYS@zkm(36)> select owner,segment_name from dba_segments where tablespace_name='LOG_MNR_TBS';

OWNER                     SEGMENT_NAME
------------------------- ----------------------------------------------------------------------------------------------------
SYSTEM                    LOGMNR_SPILL$
SYSTEM                    LOGMNR_SPILL$_PK
SYSTEM                    LOGMNR_SUBCOLTYPE$
SYSTEM                    LOGMNR_TAB$
SYSTEM                    LOGMNR_TABCOMPART$
SYSTEM                    LOGMNR_TABPART$
SYSTEM                    LOGMNR_TABSUBPART$
SYSTEM                    LOGMNR_TS$
SYSTEM                    LOGMNR_TYPE$
SYSTEM                    LOGMNR_UID$
SYSTEM                    LOGMNR_UID$_PK
SYSTEM                    LOGMNR_USER$
SYSTEM                    LOGMNRC_CONCOL_GG
SYSTEM                    LOGMNRC_CONCOL_GG_PK
SYSTEM                    LOGMNRC_CON_GG
SYSTEM                    LOGMNRC_CON_GG_PK
SYSTEM                    LOGMNRC_DBNAME_UID_MAP
SYSTEM                    LOGMNRC_DBNAME_UID_MAP_PK
SYSTEM                    LOGMNRC_GSBA
SYSTEM                    LOGMNRC_GSBA_PK
SYSTEM                    LOGMNRC_GSII
SYSTEM                    LOGMNRC_GSII_PK
SYSTEM                    LOGMNRC_GTCS
SYSTEM                    LOGMNRC_GTCS_PK
SYSTEM                    LOGMNRC_GTLO
SYSTEM                    LOGMNRC_GTLO_PK
SYSTEM                    LOGMNRC_I1CONGG
SYSTEM                    LOGMNRC_I1INDGG
SYSTEM                    LOGMNRC_I2CONGG
SYSTEM                    LOGMNRC_I2GTCS
SYSTEM                    LOGMNRC_I2GTLO
SYSTEM                    LOGMNRC_I2INDGG
SYSTEM                    LOGMNRC_I2SEQGG
SYSTEM                    LOGMNRC_I3GTLO
SYSTEM                    LOGMNRC_INDCOL_GG
SYSTEM                    LOGMNRC_INDCOL_GG_PK
SYSTEM                    LOGMNRC_IND_GG
SYSTEM                    LOGMNRC_IND_GG_PK
SYSTEM                    LOGMNRC_SEQ_GG
SYSTEM                    LOGMNRC_SEQ_GG_PK
SYSTEM                    LOGMNRP_CTAS_PART_MAP
SYSTEM                    LOGMNRP_CTAS_PART_MAP_I
SYSTEM                    LOGMNRP_CTAS_PART_MAP_PK
SYSTEM                    LOGMNR_AGE_SPILL$
SYSTEM                    LOGMNR_AGE_SPILL$_PK
SYSTEM                    LOGMNR_ATTRCOL$
SYSTEM                    LOGMNR_ATTRIBUTE$
SYSTEM                    LOGMNR_CCOL$
SYSTEM                    LOGMNR_CDEF$
SYSTEM                    LOGMNR_COL$
SYSTEM                    LOGMNR_COLTYPE$
SYSTEM                    LOGMNR_CON$
SYSTEM                    LOGMNR_DICTIONARY$
SYSTEM                    LOGMNR_DICTSTATE$
SYSTEM                    LOGMNR_ENC$
SYSTEM                    LOGMNR_ERROR$
SYSTEM                    LOGMNR_FILTER$
SYSTEM                    LOGMNR_GLOBAL$
SYSTEM                    LOGMNR_I1ATTRCOL$
SYSTEM                    LOGMNR_I1ATTRIBUTE$
SYSTEM                    LOGMNR_I1CCOL$
SYSTEM                    LOGMNR_I1CDEF$
SYSTEM                    LOGMNR_I1COL$
SYSTEM                    LOGMNR_I1COLTYPE$
SYSTEM                    LOGMNR_I1CON$
SYSTEM                    LOGMNR_I1DICTIONARY$
SYSTEM                    LOGMNR_I1ENC$
SYSTEM                    LOGMNR_I1ICOL$
SYSTEM                    LOGMNR_I1IND$
SYSTEM                    LOGMNR_I1INDCOMPART$
SYSTEM                    LOGMNR_I1INDPART$
SYSTEM                    LOGMNR_I1INDSUBPART$
SYSTEM                    LOGMNR_I1KOPM$
SYSTEM                    LOGMNR_I1LOB$
SYSTEM                    LOGMNR_I1LOBFRAG$
SYSTEM                    LOGMNR_I1LOGMNR_BUILDLOG
SYSTEM                    LOGMNR_I1NTAB$
SYSTEM                    LOGMNR_I1OBJ$
SYSTEM                    LOGMNR_I1OPQTYPE$
SYSTEM                    LOGMNR_I1PARTOBJ$
SYSTEM                    LOGMNR_I1PROPS$
SYSTEM                    LOGMNR_I1REFCON$
SYSTEM                    LOGMNR_I1SEED$
SYSTEM                    LOGMNR_I1SUBCOLTYPE$
SYSTEM                    LOGMNR_I1TAB$
SYSTEM                    LOGMNR_I1TABCOMPART$
SYSTEM                    LOGMNR_I1TABPART$
SYSTEM                    LOGMNR_I1TABSUBPART$
SYSTEM                    LOGMNR_I1TS$
SYSTEM                    LOGMNR_I1TYPE$
SYSTEM                    LOGMNR_I1USER$
SYSTEM                    LOGMNR_I2CDEF$
SYSTEM                    LOGMNR_I2COL$
SYSTEM                    LOGMNR_I2IND$
SYSTEM                    LOGMNR_I2INDPART$
SYSTEM                    LOGMNR_I2NTAB$
SYSTEM                    LOGMNR_I2OBJ$
SYSTEM                    LOGMNR_I2SEED$
SYSTEM                    LOGMNR_I2TAB$
SYSTEM                    LOGMNR_I2TABCOMPART$
SYSTEM                    LOGMNR_I2TABPART$
SYSTEM                    LOGMNR_I2TABSUBPART$
SYSTEM                    LOGMNR_I3COL$
SYSTEM                    LOGMNR_ICOL$
SYSTEM                    LOGMNR_IND$
SYSTEM                    LOGMNR_INDCOMPART$
SYSTEM                    LOGMNR_INDPART$
SYSTEM                    LOGMNR_INDSUBPART$
SYSTEM                    LOGMNR_INTEGRATED_SPILL$
SYSTEM                    LOGMNR_INTEG_SPILL$_PK
SYSTEM                    LOGMNR_KOPM$
SYSTEM                    LOGMNR_LOB$
SYSTEM                    LOGMNR_LOBFRAG$
SYSTEM                    LOGMNR_LOG$
SYSTEM                    LOGMNR_LOG$_FIRST_CHANGE#
SYSTEM                    LOGMNR_LOG$_FLAGS
SYSTEM                    LOGMNR_LOG$_PK
SYSTEM                    LOGMNR_LOG$_RECID
SYSTEM                    LOGMNR_LOGMNR_BUILDLOG
SYSTEM                    LOGMNR_NTAB$
SYSTEM                    LOGMNR_OBJ$
SYSTEM                    LOGMNR_OPQTYPE$
SYSTEM                    LOGMNR_PARTOBJ$
SYSTEM                    LOGMNR_PROCESSED_LOG$
SYSTEM                    LOGMNR_PROCESSED_LOG$_PK
SYSTEM                    LOGMNR_PROPS$
SYSTEM                    LOGMNR_REFCON$
SYSTEM                    LOGMNR_RESTART_CKPT$
SYSTEM                    LOGMNR_RESTART_CKPT$_PK
SYSTEM                    LOGMNR_RESTART_CKPT_TXINFO$
SYSTEM                    LOGMNR_RESTART_CKPT_TXINFO$_PK
SYSTEM                    LOGMNR_SEED$
SYSTEM                    LOGMNR_SESSION_ACTION$_PK
SYSTEM                    LOGMNR_SESSION_ACTIONS$
SYSTEM                    LOGMNR_SESSION_EVOLVE$
SYSTEM                    LOGMNR_SESSION_EVOLVE$_PK
SYSTEM                    SYS_IL0000001161C00010$$
SYSTEM                    SYS_IL0000001165C00008$$
SYSTEM                    SYS_IL0000001169C00011$$
SYSTEM                    SYS_IL0000001174C00009$$
SYSTEM                    SYS_IL0000001174C00012$$
SYSTEM                    SYS_IL0000001180C00009$$
SYSTEM                    SYS_LOB0000001161C00010$$
SYSTEM                    SYS_LOB0000001165C00008$$
SYSTEM                    SYS_LOB0000001169C00011$$
SYSTEM                    SYS_LOB0000001174C00009$$
SYSTEM                    SYS_LOB0000001174C00012$$
SYSTEM                    SYS_LOB0000001180C00009$$

148 rows selected.

Elapsed: 00:00:00.04
14:44:17 SYS@zkm(36)> select owner,table_name from dba_tables where tablespace_name='LOG_MNR_TBS';

OWNER                     TABLE_NAME
------------------------- ------------------------------
SYSTEM                    LOGMNR_SESSION_EVOLVE$
SYSTEM                    LOGMNR_GLOBAL$
SYSTEM                    LOGMNR_UID$
SYSTEM                    LOGMNRC_DBNAME_UID_MAP
SYSTEM                    LOGMNR_LOG$
SYSTEM                    LOGMNR_PROCESSED_LOG$
SYSTEM                    LOGMNR_SPILL$
SYSTEM                    LOGMNR_AGE_SPILL$
SYSTEM                    LOGMNR_RESTART_CKPT_TXINFO$
SYSTEM                    LOGMNR_ERROR$
SYSTEM                    LOGMNR_RESTART_CKPT$
SYSTEM                    LOGMNR_INTEGRATED_SPILL$
SYSTEM                    LOGMNR_FILTER$
SYSTEM                    LOGMNR_SESSION_ACTIONS$

14 rows selected.

Elapsed: 00:00:00.05

View Code

5.添加重做日志或者归档日志并开始挖掘

5.1手工添加日志

出现故障时，业务/开发会给出一个时间段或者时间点，通过以下SQL查找出包含这个时间点的归档日志。

SELECT THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,NAME FROM V$ARCHIVED_LOG WHERE TO_DATE('2020-12-30 09:17:04', 'YYYY-MM-DD HH24:MI:SS') BETWEEN FIRST_TIME AND NEXT_TIME;

如果此时归档日志已经被备份走不在归档路径里边，通过如下语句在RMAN中进行恢复。

RMAN> restore archivelog sequence 37268;
...
RMAN> restore archivelog sequence 32878;                                     
...
RMAN> restore archivelog sequence 32878 thread 2;
...

然后通过如下语句添加日志。

exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'+FRA_VG/xxxxxx/archivelog/2020_12_30/thread_1_seq_37268.427.1060525501', Options=>dbms_logmnr.new);
exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'+FRA_VG/xxxxxx/archivelog/2020_12_30/thread_2_seq_32878.410.1060526043', Options=>dbms_logmnr.addfile);

开始执行挖掘工作。

execute dbms_logmnr.start_logmnr(dictfilename=>'/ora/logminer/logmnr20201230.ora');

5.2开始挖掘并自动添加（我这次实验用的这种）

在Oracle数据库10g或以后的版本中，当针对生成重做日志文件的同一数据库使用LogMiner时，LogMiner会根据请求的时间或SCN范围扫描控制文件并确定所需的重做日志文件。

您不再需要将时间范围映射到一组明确的重做日志文件。为了进行此扫描，您需要使用continuous_mine选项并指定startscn或starttime。

BEGIN
    DBMS_LOGMNR.START_LOGMNR( STARTTIME => '2021-02-25 15:19:01',
        ENDTIME => '2021-02-25 15:21:00',
        OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE,
        dictfilename=>'/home/oracle/logminer/logmnrtest.ora');
END;
/

View Code

15:13:22 SYS@zkm(1)> BEGIN
15:13:23   2      DBMS_LOGMNR.START_LOGMNR( STARTTIME => '2021-02-25 14:31:32',
15:13:23   3          ENDTIME => '2021-02-25 14:44:17',
15:13:23   4            OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE,
15:13:23   5          dictfilename=>'/home/oracle/logminer/logmnrtest.ora');
15:13:23   6  END;
15:13:23   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.29

6.分析数据

v$logmnr_contents 视图保存了从归档日志中挖掘出来的数据，包括 REDO SQL 和 UNDO SQL。
v$logmnr_contents 视图只对当前会话有效，如果数据量比较大的话建议重建成一张普通表，建立索引之后再查询，这里我们创建新的表 LOG_MNR_TAB 来保存挖掘出来的数据。

创建一张新表存放v$logmnr_contents的内容，就可以使用plsqldev工具查询比较美观。

15:13:24 SYS@zkm(1)> create table log_mnr_tab as select * from v$logmnr_contents;

Table created.

Elapsed: 00:00:00.54
15:13:49 SYS@zkm(1)> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

可以看出列username，os_username等信息都是unknown。

这是因为没有开启附加日志，现在开启附加日志并且创建新表并插入记录在观察。

15:16:28 SYS@zkm(1)> drop table log_mnr_tab purge; 

Table dropped.

Elapsed: 00:00:00.43


15:19:01 SYS@zkm(1)> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Elapsed: 00:00:00.04
15:19:18 SYS@zkm(36)> conn zkm/oracle
Connected.
15:19:20 ZKM@zkm(36)> create table zkm (id int,name varchar2(20));

Table created.

Elapsed: 00:00:00.13
15:19:33 ZKM@zkm(36)> insert into zkm values(1,'a');

1 row created.

Elapsed: 00:00:00.07
15:19:46 ZKM@zkm(36)> commit;

Commit complete.

Elapsed: 00:00:00.00


注意：如果不重新提取数据字典，新的表也会是16进制的信息，这里演示
15:21:00 SYS@zkm(1)> BEGIN
15:21:08   2      DBMS_LOGMNR.START_LOGMNR( STARTTIME => '2021-02-25 15:19:01',
15:21:08   3          ENDTIME => '2021-02-25 15:21:00',
15:21:08   4            OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE,
15:21:08   5          dictfilename=>'/home/oracle/logminer/logmnrtest.ora');
15:21:08   6  END;
15:21:08   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.09
15:21:10 SYS@zkm(1)> create table log_mnr_tab as select * from v$logmnr_contents;

Table created.

Elapsed: 00:00:00.31

plsqldev查看如下：

16:18:33 SYS@zkm(1)> select object_id from dba_objects where owner='ZKM' and object_name='ZKM';                                                   

 OBJECT_ID
----------
    100829

Elapsed: 00:00:00.00

附加日志开了后，相关信息不再是unknown显示了。

16:21:07 SYS@zkm(1)> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

至于16进制的问题，重新提取数据字典就行了。

16:21:07 SYS@zkm(1)> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
16:21:31 SYS@zkm(1)> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'logmnrtest.ora',dictionary_location => '/home/oracle/logminer/');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.63
16:21:53 SYS@zkm(1)> BEGIN
16:22:07   2      DBMS_LOGMNR.START_LOGMNR( STARTTIME => '2021-02-25 15:19:01',
16:22:07   3          ENDTIME => '2021-02-25 15:21:00',
16:22:07   4            OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE,
16:22:07   5          dictfilename=>'/home/oracle/logminer/logmnrtest.ora');
16:22:07   6  END;
16:22:07   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.16
16:22:09 SYS@zkm(1)> drop table log_mnr_tab purge; 

Table dropped.

Elapsed: 00:00:00.13
16:22:28 SYS@zkm(1)> create table log_mnr_tab as select * from v$logmnr_contents;

Table created.

Elapsed: 00:00:00.41
16:22:32 SYS@zkm(1)> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01