达梦数据库归档日志挖掘

一.前言

在使用达梦数据库的过程中，经常会遇到对归档日志进行处理的场景，

比如，发现归档日志生成的过快，对业务又不太熟悉的情况下，我们可以分析归档日志，统计相应语句使用的频率来初步判断是那些操作导致的。

还有，如果有人误删了数据，又无法定位到那些时间段，或者那些具体的语句，也可以通过日志分析工具来找到。

DBMS_LOGMNR是达梦数据库日志挖掘功能使用的系统包，它可以方便的对数据库归档日志进行挖掘，重构出 DDL、 DML和DCL 等操作，方便审计及跟踪数据库的操作，并通过获取的信息进行更深入的分析。

目前 DBMS_LOGMNR 只支持对归档日志进行分析，在使用该方法之前，需要将数据库设置为归档模式，并将RLOG_APPEND_LOGIC选项置为 1 或 2。

使用包内的过程和函数之前，如果还未创建过系统包。请先调用系统过程创建系统包。默认安装达梦数据库后该包已自动创建。

二.主要流程

1. 搭建测试库

2．拷贝归档

3. 创建新的实例

4. 修改该实例db_magic为归档源库的db_magic

5. 进行日志挖掘

6. 查看日志挖掘结果

三. V$LOGMNR_CONTENTS视图与dmmdf工具介绍

1.V$LOGMNR_CONTENTS的关键的几列：

SCN BIGINT 当前记录的 LSN

START_SCN BIGINT 当前事务的起始 LSN

COMMIT_SCN BIGINT 当前事务的截止 LSN

TIMESTAMP DATETIME 当前记录的创建时间

START_TIMESTAMP DATETIME 当前事务的起始时间

COMMIT_TIMESTAMP DATETIME 当前事务的截止时间

OPERATION VARCHAR(32) 操作类型

USERNAME VARCHAR(128) 执行这条语句的用户名

SEG_OWNER VARCHAR(128) 操作的模式名

TABLE_NAME VARCHAR(128) 操作的表名

DATA_OBJ# INTEGER 对象 ID 号

SQL_REDO VARCHAR(4000) 客户端发送给数据库的 SQL 语句。

2.dmmdf工具的使用：

达梦数据库的dmmdf 工具可以对DM相关的文件的属性进行修改。 从dmmdf的帮助可以看出，支持的修改的文件类型有：dbf、rlog、original bak、bakset meta、bakset bkp、bakset，根据文件的不同，可修改的属性也有所不同，最主要的就是修改db_magic。

四.不同用户的操作频率统计实际操作演示

1.测试库的RLOG_APPEND_LOGIC需要修改为1，就可以进行日志挖掘。（新初始化一个库，修改成源库对应的dbmagic，即可；归档也不要开，直接开挖）

需要在停服务的情况下修改SYSTEM.DBF，DAMENG01.log，DAMENG02.log的dbmagic

2.首先在原数据库内获取db_magic

select db_magic;

1019736265

3.然后修改测试库的db_magic

cd /home/dmdba/dm/dmdbms/bin

./dmmdf TYPE=1 FILE=/dbdata/dmdata/DAMENG/SYSTEM.DBF

(1 1019736265 y)

./dmmdf TYPE=2 FILE=/dbdata/dmdata/DAMENG/DAMENG01.log

./dmmdf TYPE=2 FILE=/dbdata/dmdata/DAMENG/DAMENG02.log

(6 1019736265 y)

4.开始数据挖掘，开启数据库

./dmserverd start

加载归档日志

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-04-08_14-37-11.log');

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2048 );

select * from V$LOGMNR_CONTENTS limit 0,100;

DBMS_LOGMNR.END_LOGMNR();

5.以上是一个完整的流程，下面来具体分析搜索日志，因为只是临时加载日志，所以每当关掉会话后，就需要重新加载归档日志，当加载多个日志时需要用到以下小脚本。（注意不要在生产环境上分析日志，当分析的日志多的情况下会很慢）

6.使用脚本批量生成语句

for i in `ls`;do echo DBMS_LOGMNR.ADD_LOGFILE\(\'"/dbarch/dmarch/"$i\'\)\;;done

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-04-08_14-37-11.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-04-19_19-04-34.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-04-20_19-18-33.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-04-21_00-53-48.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-10_18-16-16.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-12_18-10-00.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-13_20-15-00.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-19_19-54-08.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-21_01-19-39.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-27_02-48-48.log');

DBMS_LOGMNR.ADD_LOGFILE('/dbarch/dmarch/ARCHIVE_LOCAL1_0x166EAEC[0]_2022-06-27_18-34-09.log');

7.开始日志分析

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2048 );

8.查询相应语句使用频率

select operation,SEG_OWNER,username,DATA_OBJ#,COUNT(operation) COUNT_DESC from V$LOGMNR_CONTENTS group by operation,SEG_OWNER,username,DATA_OBJ# ORDER BY COUNT_DESC DESC;

select * from V$LOGMNR_CONTENTS limit 0,100;

最后一列就是insert某张表的执行频率

将获取的信息取出，copy到源库中，查询对应的表名，模式名，用户名

select * from sysobjects where id in ('150994945','50331649','1077',’1093’);

或者直接查询SQL_REDO字段，也能获得很多信息。

select SQL_REDO from V$LOGMNR_CONTENTS limit 0,100;

9.结束日志分析

DBMS_LOGMNR.END_LOGMNR();

五.寻找删除操作实际操作演示

1. 先按第四章的1-3步执行

2. 在表中删除一条数据

3.切换下归档日志

 alter database archivelog current;

4.使用批量生成语句脚本（记得修改对应路径）

for i in `ls`;do echo DBMS_LOGMNR.ADD_LOGFILE\(\'"/home/dbarch/dmarch/"$i\'\)\;;done

将生成的语句执行

5.开始日志分析

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2048 );

6.利用模式名和表名找到对应的删除语句

select '

SELECT OPERATION_CODE , SCN, SQL_REDO , TIMESTAMP ,USERNAME,

SEG_OWNER, TABLE_NAME FROM V$LOGMNR_CONTENTS

 WHERE

SQL_REDO LIKE

''DELETE FROM "'||A.SCHID||'"."OBJ# '||A.ID||'"%'';' from sysobjects  a,sysobjects b where

a.schid=b.id and b.name='SYSDBA'

AND A.NAME='DMTES02';

这样查出来的语句与原始语句有所不同

例如：

原始语句：delete from  SYSDBA.dmtes02 where V1=5;

查出来的语句：DELETE FROM "150994945"."OBJ# 1350" WHERE "COL 1" = 5;

数字150994945对应的是模式名SYSDBA，

数字1350对应的是表名dmtes02，

COL 1对应的是第一列V1，

数字可以在源库的SYS.sysobjects表中找到相关信息。

7.利用语句查到相关信息，主要收集timestamp与username字段信息。

SELECT OPERATION_CODE , SCN, SQL_REDO , TIMESTAMP ,USERNAME,

SEG_OWNER, TABLE_NAME FROM V$LOGMNR_CONTENTS

 WHERE

SQL_REDO LIKE

'DELETE FROM "150994945"."OBJ# 1350"%';

8.在sysobjects表中找到用户名。

select * from sysobjects where ID in ('50331649');

9.在视图V$SESSION_HISTORY内找到用户对应的登入时间，就可以找到执行语句的ip,主机名，服务名等信息了。

select sess_id,user_name,create_time,clnt_host,appname,clnt_ip

 from V$SESSION_HISTORY where user_name='SYSDBA' and create_time <='2022-07-10 22:51:18.506000' order by create_time desc;

如果是一个长期删除的动作，也可以在V$SESSION视图内找到对应的ip,主机名，服务名等信息。

10.结束日志分析

DBMS_LOGMNR.END_LOGMNR();

六.结语

本文介绍了怎么去对达梦数据库的归档文件进行数据挖掘，具体使用的场景可以参考以上介绍的方法修改语句，如果文中哪里有误，欢迎指出，大家共同交流进步！

社区地址：https://eco.dameng.com