DM 日志挖掘

摘要本文介绍了基于日志挖掘的恢复技术和审计功能：通过日志挖掘找到误操作发生的准确时间点，为后续的恢复操作提供可靠的依据。

日志挖掘概述

LogMiner 是达梦公司提供的一个非常有用的日志分析工具，使用该工具可以轻松获取 DM 归档日志文件中的具体内容，更是可以分析出所有对于数据库操作的 DML 和 DDL 语句。因此该工具特别适用于调试、审计或者回退某个特定的事务。

LogMiner 分析工具实际上是由一组 DMPL/SQL 包和一些动态视图组成的，它作为 DM 数据库的一部分来发布，是一个完全免费的工具。
其主要用途有：

跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
回退数据库的变化：回退特定的变化数据。
优化和扩容计划：可以通过分析日志文件中的数据进而分析数据的增长模式。

使用 DBMS_LOGMNR 包对归档日志进行挖掘，可以重构出 DDL 和 DML 等操作语句，并通过获取的信息进行更深入的分析。

要实现日志挖掘，除了将常规的物理 redo 日志记录下来以外，还需要按照特定的格式将对数据库进行的逻辑操作存储下来，专门用于 DBMS_LOGMNR 包的挖掘，获取数据库系统的历史执行语句。
这部分逻辑日志同时也记录在重做日志文件中。

前提条件—配置相关参数

配置数据库运行于归档模式。
在 dm.ini 文件中将 RLOG_APPEND_LOGIC 选项设为 1 或者 2。
- 设置为 1 时，如果有主键列，记录 UPDATE 和 DELETE 操作时只包含主键列信息；反之，则包含所有列信息。
- 设置为 2 时，不论是否有主键列，记录 UPDATE 和 DELETE 操作时都包含所有列的信息。设置为此参数会大幅增加日志量，生产环境慎用。
- 设置为 3 时，记录 UPDATE 操作时包含更新列的信息以及 ROWID，记录 DELETE 操作时只有 ROWID 。

如果目的是查找准确的恢复点，可以选择 1 或者 3，如果是以日常审计为目的，应设置为 2，当然也可以在表级开启记录全日志。

将所有表的逻辑日志记录到重做日志文件中，在 dm.ini 中配置 RLOG_IGNORE_TABLE_SET=1

如果只想记录部分表，则 RLOG_IGNORE_TABLE_SET=0，然后在建表或者修改表的语法中使用 ADD LOGIC LOG。这种方式适合对重点表进行审计。

是否启用在日志中记录系统表逻辑操作的功能，启用RLOG_APPEND_LOGIC 后有效。
RLOG_APPEND_SYSTAB_LOGIC 取值为 0 或 1。

以下三个相关参数都属于系统级动态参数：

RLOG_IGNORE_TABLE_SET
RLOG_APPEND_LOGIC
RLOG_APPEND_SYSTAB_LOGIC

可以按照如下参数配置成全表级开启逻辑日志，排除系统表，产生的额外逻辑日志量适中。

alter system set 'RLOG_APPEND_LOGIC'=1;
alter system set 'RLOG_IGNORE_TABLE_SET'=1;
alter system set 'RLOG_APPEND_SYSTAB_LOGIC'=0;

注：DM 数据库目前只支持对归档日志的分析

关于达梦归档日志的说明

ORACLE 数据库在有日志切换时，才会形成归档日志文件。而达梦数据库是在将 redo 日志写入联机日志文件后，再由专用的归档线程异步地将其写入归档文件。也就是说 DM 数据库并不是等到联机日志切换发生时才形成归档日志。

程序在运行过程中可以强行删除正在使用的归档日志文件，一旦删除，除非 mount 再 open 数据库，否则将没有归档日志输出。

达梦数据库中归档模式下，按照预设的大小例如 64M 生成一个归档日志文件，当执行归档切换命令时，该归档文件的大小会变成实际日志占用的大小，比如 200K。并且后续如果有 redo 需要再次写入归档日志文件时，才会看到由于切换生成的最新归档日志文件。

日志挖掘相关系统过程

调用系统过程创建日志挖掘相关的 PACKAGE

SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_LOGMNR');

日志挖掘相关函数
- PROCEDURE ADD_LOGFILE --增加日志文件
- COLUMN_PRESENT --判断某列是否被包含在指定的一行逻辑记录中
- END_LOGMNR --结束 LOGMNR
- MINE_VALUE --以字符串的格式来获取某一条日志中包含的指定列的值
- REMOVE_LOGFILE --从日志列表中移除某个日志文件
相关重要视图
- V$LOGMNR_CONTENTS --显示当前会话日志分析内容
- V$LOGMNR_LOGS --显示当前会话需要分析的归档日志文件
- V$LOGMNR_PARAMETERS --显示当前会话
- START_LOGMNR --启动日志文件分析的参数

应用场景举例：结合日志挖掘精准定位恢复点

场景：开发人员某天执行了 DML 操作，删除若干条核心数据，但具体时间无法准确提供，需要找到误删除的数据，重新插入原表。

解决方案一：通过日志挖掘技术可以准确找到误删时间点，再结合最近的备份，在备份机上还原数据库到指定的时间点，然后使用 dmexp/dmimp 工具将表数据导入导出。

解决方案二：结合日志挖掘，找到删除事务发生的时间点或者事务 ID，如果数据库闪回功能打开，并且误操作发生时间没有超过 undo 日志保留时间，可以使用 selet 的方式找到之前的数据。

不管哪种方案，尽快找到误操作时间点非常关键。接下来我们通过以下测试表模拟整个恢复过程。

测试表 T1 共有 10 条数据：
3.1.png

测试环境下准备好以上数据后，做一次日志切换，执行后续操作，再次切换归档，后面生成的日志将会放置在这次新生成的归档日志文件中，便于分析。
3.2.jpg

模拟误操作删除 ID > 1 的数据并提交：
3.3.png

此时表 T1 只剩余一条记录

做一次日志切换，将写入该 redo 的日志文件内容切换出来，生产环境下直接找到对应的归档日志文件
3.4.png

开始日志挖掘，找出误操作对应的事务 ID 和准确的时间点（以下操作是在达梦提供的 manger 可视化工具中完成的），3 个步骤即可完成挖掘：
3.5.png

3.6.png

通过以上视图可以准确查询到误操作事务发生时的 XID。用 10 进制表示为 65409，时间戳也能显示出来。

如果使用解决方案一则如下两个参数需事先设置正确：

alter system set 'ENABLE_FLASHBACK'=1; --开启闪回功能

alter system set 'UNDO_RETENTION'=3600 scope=both; --undo日志的保留时间，单位秒

目前的测试环境下已正确配置，故使用如下查询附加上 DM 数据库的特殊关键字，即可查询出误删除的数据如下：

3.7.png

可以看出删除前的数据显示出来。使用 create table t_tmp as select * from test1.t1 when trxid 65409 将数据导出到一个中间表，后续进行相应处理。

如果没有开启闪回功能或者 undo 数据的保留时间过期，那么无法通过上述方式找回数据。
但可以通过视图中事务开始的 START_SCN 值，再加上全库备份结合归档，将数据库异机恢复到该事务发生的前一时刻，然后使用 dexp/dimp 将正确的表数据导出导入，从而达到数据恢复的目的。

以下是恢复数据库的三条核心语句，特别是第二条中的 LSN 值非常关键，将其设置成等于 START_SCN。

RESTORE DATABASE ‘/dm/data/PROD/dm.ini’ FROM BACKUPSET ;
RECOVER DATABASE ‘/dm/data/PROD/dm.ini’ WITH ARCHIVEDIR ‘/dm/arch’ UNTIL LSN <LSN值>;
RECOVER DATABASE ‘/dm/data/PROD/dm.ini’ UPDATE DB_MAGIC;

目前日志挖掘相关视图中没有包含 undo_sql，只能找到对应的 redo_sql，需要通过 redo_sql 信息推断出 undo_sql。但实际情况中，对于有主键的表进行更新和删除操作，无法通过 V$LOGMNR_CONTENTS 视图得到必须的前映像。除非将 RLOG_APPEND_LOGIC 设置成 2，但这样会造成日志量成倍增长且需要写脚本来推导出相应 undo_sql。

应用场景之-数据库审计:

达梦数据库中对表的所有 DML 和 DLL 操作都存在对应的操作码。可以选择指定的时间段，按操作码分类，即可统计出哪种类型的操作最频繁，有没有存在非法操作等信息。

场景一

统计某时段 DML 语句的执行频度

处理方式：根据日志挖掘结果中代表操作类型的字段，分类汇总分析。

CREATE TABLE T3(ID NUMBER, NAME VARCHAR(10));
DDL 操作对应的 operation_code 为 5

3.8.png

分别执行增删改查操作，查看日志情况。得到 insert 的操作码为 1，update 的操作码为 3，delete 的操作码为 2

3.9.jpg

3.10.png

3.11.png

统计某一时段的增删改的语句就可以写成：

select case
  when operation_code=1 then '插入记录'
  when operation_code=3 then '更新记录'
  when operation_code=2 then '删除记录'
  end as 操作类型,
count(*) 操作次数 from v$logmnr_contents where operation_code in (1, 2, 3)
group by operation_code order by count(*);

也可按如下生成更加易读的视图:
create table dml_statistics as
select '插入记录' as 操作类型, 0 as 操作次数 from dual union
select '更新记录' as 操作类型, 0 as 操作次数 from dual
union
select '删除记录' as 操作类型, 0 as 操作次数 from dual;

select t.操作类型, nvl(t1.操作次数,0) from dml_statistics t,
 (select case
   when operation_code=1 then '插入记录'
   when operation_code=3 then '更新记录'
   when operation_code=2 then '删除记录'
   end as 操作类型,
count(*) as 操作次数 from v$logmnr_contents where operation_code in (1, 2, 3)
group by operation_code) t1 
where t.操作类型 = t1.操作类型(+);

3.12.jpg

实际生产中按照这种方式，纵向以小时为单位，分析出一天当中哪些时段的 DML 操作最为频繁，有无明显异常时段。

场景二

找出某时段是谁或者是哪个应用非法更新了某张重要表的数据

处理方式：根据日志挖掘结果的相关字段，找出非法操作责任人。

生成测试表

3.13.png

模拟创建另一个用户，来执行这个非法 update 动作

create user droper identified by droper123; --创建模拟更新用户
conn sysdba/Dameng123:5237;
grant select,update on test1.t3 to droper; --授予更改权限

切换到sysdba用户下做一次日志切换动作

切换到drop而用户下执行非法更新操作，id=6的行的name列修改为   mongodb

3.14.jpg

再次切换出归档日志做分析、处理。

通过日志挖掘找到相关删除线索
使用上述日志挖掘的方法，可以快速找到是哪个用户，在什么时间做的更新操作，但并没有展示发出指令的机器和操作系统相关信息。如果客户端的相关信息能够显示在视图中，将有助于定位运行在哪个机器上的哪个应用，排查应用 bug。

3.15.png

对应的 SQL_REDO 语句为：

UPDATE "TEST1"."T3" SET "NAME" = 'mongodb' WHERE "ID" = 6 AND "NAME" = 'redis'
这里表没有定义主键，所以条件后面来了所有的列值，改视图中目前不支持undo_sql,期待后续版本加入。

也可使用日志挖掘包中提供的过程得到某个 REDO_SQL 中包含的列的具体值。
下图显示更改后的值是 mongodb，尽管 REDO_SQL 中包含了前映像，但无法挖出 undo 值。

3.16.png

总结

LOGMINER 对于数据库管理员（DBA）来讲是个功能非常强大的工具，也是在日常工作中经常要用到的一个工具，借助该工具，可以得到大量关于数据库活动的信息。

LOGMINER 最重要的用途之一就是不用全部恢复数据库就可以恢复数据库的某个变化。另外，该工具还可用于监视或者审计用户的活动，比如 DBA 可以利用 logminer 工具查看到谁曾经修改了哪些数据以及这些数据在修改前的状态。