使用LogMiner分析Oracle的redo日志和归档

共计 5384 个字符，预计需要花费 14 分钟才能阅读完成。

Oracle LogMiner 是 Oracle 公司从产品 8i 以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得 Oracle 在线 / 归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的 DML 和 DDL 语句。该工具特别适用于调试、审计或者回退某个特定的事务。

LogMiner 分析工具实际上是由一组 PL/SQL 包和一些动态视图（Oracle8i 内置包的一部分）组成，它作为 Oracle 数据库的一部分来发布是 8i 产品提供的一个完全免费的工具。但该工具和其他 Oracle 内建工具相比使用起来显得有些复杂，主要原因是该工具没有提供任何的图形用户界面（GUI）。

1、安装 LogMiner

在使用 LogMiner 之前需要确认 Oracle 是否带有进行 LogMiner 分析包，一般来说 Windows 操作系统 Oracle10g 以上都默认包含。如果不能确认，可以 DBA 身份登录系统，查看系统中是否存在运行 LogMiner 所需要的 dbms_logmnr、dbms_logmnr_d 包，如果没有需要安装 LogMiner 工具，必须首先要运行下面这样两个脚本：

a.$ORACLE_HOME/rdbms/admin/dbmslm.sql

b.$ORACLE_HOME/rdbms/admin/dbmslmd.sql

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

2、修改数据库参数

– 数据库版本
sys@ORCL>select * from v$version;
 
BANNER
———————————————————–
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production
– 为数据库添加最小附加日志
sys@ORCL>alter database add supplemental log data;
 
Database altered.
 
sys@ORCL>select supplemental_log_data_min from v$database;
 
SUPPLEMENTAL_LOG_DATA_MI
————————
YES
– 设置 utl_file_dir 参数
sys@ORCL>alter system set utl_file_dir=’/home/oracle/logminer’ scope=spfile;
 
System altered.
– 重启数据库
sys@ORCL>shutdown immediate
sys@ORCL>startup
sys@ORCL>show parameter utl_file_dir
 
NAME                    TYPE                VALUE
———————————— ——————————— ——————————
utl_file_dir                string                  /home/oracle/logminer

3、准备测试数据

sys@ORCL>conn zx/zx
Connected.
zx@ORCL>create table log_miner (id number,name varchar2(10));
 
Table created.
 
zx@ORCL>insert into log_miner values(1,’zx’);
 
1 row created.
 
zx@ORCL>insert into log_miner values(1,’lx’);
 
1 row created.
 
zx@ORCL>insert into log_miner values(1,’xx’);
 
1 row created.
 
zx@ORCL>commit;
 
Commit complete.

4、创建数据字典

zx@ORCL>conn / as sysdba
Connected.
sys@ORCL>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
 
sys@ORCL>exec dbms_logmnr_d.build(DICTIONARY_FILENAME=>’dictionary.ora’,DICTIONARY_LOCATION=>’/home/oracle/logminer’);
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully – LogMnr Dictionary Created
 
PL/SQL procedure successfully completed.
 
sys@ORCL>!ls -l /home/oracle/logminer/dictionary.ora
-rw-r–r– 1 oracle oinstall 41483316 Nov 11 21:08 /home/oracle/logminer/dictionary.ora

LogMiner 工具实际上是由两个新的 PL/SQL 内建包（(DBMS_LOGMNR 和 DBMS_ LOGMNR_D）和四个 V$ 动态性能视图（视图是在利用过程 DBMS_LOGMNR.START_LOGMNR 启动 LogMiner 时创建）组成。在使用 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’));

创建数据字典的目的就是让 LogMiner 引用涉及到内部数据字典中的部分时为他们实际的名字，而不是系统内部的 16 进制。数据字典文件是一个文本文件，使用包 DBMS_LOGMNR_D 来创建。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发生变化，这时就需要重新创建该字典文件。另外一种情况是在分析另外一个数据库文件的重作日志时，也必须要重新生成一遍被分析数据库的数据字典文件。

5、确认当前处理联机状态的 redo 日志

sys@ORCL>select group#,status from v$log;
 
    GROUP# STATUS
———- ————————————————
    1 INACTIVE
    2 CURRENT
    3 INACTIVE
sys@ORCL>select group#,member from v$logfile;
 
    GROUP# MEMBER
———- —————————————————————————————————-
    3 /u02/app/oracle/oradata/orcl/redo03.log
    2 /u02/app/oracle/oradata/orcl/redo02.log
    1 /u02/app/oracle/oradata/orcl/redo01.log

可以看出 redo02 处于 current 状态

6、加入需要分析的日志

– 第一个日志文件使用 dbms_logmnr.new
sys@ORCL>exec dbms_logmnr.add_logfile(LOGFILENAME=>’/u02/app/oracle/oradata/orcl/redo02.log’,OPTIONS=>dbms_logmnr.new);
 
PL/SQL procedure successfully completed.
– 如果需要加入其他的日志使用如下语句
exec dbms_logmnr.add_logfile(LOGFILENAME=>’/u02/app/oracle/oradata/orcl/redo03.log’,OPTIONS=>dbms_logmnr.addfile);

7、使用 LogMiner 进行分析

sys@ORCL>exec dbms_logmnr.start_logmnr(DICTFILENAME=>’/home/oracle/logminer/dictionary.ora’);
 
PL/SQL procedure successfully completed.
 
sys@ORCL>col seg_name for a15
sys@ORCL>col seg_owner for a15
sys@ORCL>col operation for a20
sys@ORCL>col sql_redo for a180
sys@ORCL>set linesize 200
sys@ORCL>select seg_owner,seg_name,operation,sql_redo from v$logmnr_contents where seg_owner=’ZX’ and seg_name=’LOG_MINER’;
 
SEG_OWNER  SEG_NAME    OPERATION        SQL_REDO
————— ————— ——————– ——————————————————————————–
ZX      LOG_MINER  DDL          create table log_miner (id number,name varchar2(10));
ZX      LOG_MINER  INSERT          insert into “ZX”.”LOG_MINER”(“ID”,”NAME”) values (‘1′,’zx’);
ZX      LOG_MINER  INSERT          insert into “ZX”.”LOG_MINER”(“ID”,”NAME”) values (‘1′,’lx’);
ZX      LOG_MINER  INSERT          insert into “ZX”.”LOG_MINER”(“ID”,”NAME”) values (‘1′,’xx’);

以上为分析在线 redo 日志的过程，分析归档日志的步骤同样，只是在添加日志文件的时候把在线 redo 日志换为归档日志即可。归档日志也可以传输到其他数据库服务器上进行数据分析，但分析时需要使用源库的字典文件。

LogMiner 使用注意事项：

源库与 Mining 数据库必须运行在同样的硬件平台上，windows 的不可以传到 Linux 上进行分析

Mining 数据库的版本大于等于源库的数据库版本

Mining 数据库的字符集需要与源库一致或是源库的超集

参考文档：http://www.linuxidc.com/Linux/2017-02/140526.htm

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1557

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-02/140525.htm