一.前言
在使用达梦数据库的过程中,经常会遇到对归档日志进行处理的场景,
比如,发现归档日志生成的过快,对业务又不太熟悉的情况下,我们可以分析归档日志,统计相应语句使用的频率来初步判断是那些操作导致的。
还有,如果有人误删了数据,又无法定位到那些时间段,或者那些具体的语句,也可以通过日志分析工具来找到。
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