提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
前言
DBMS_LOGMNR是达梦数据库日志挖掘功能使用的系统包，它可以方便的对数据库归档日志进行挖掘，重构出 DDL、 DML和DCL 等操作，方便审计及跟踪数据库的操作，并通过获取的信息进行更深入的分析。
一、LOGMNR用法
目前 DBMS_LOGMNR 只支持对归档日志进行分析，需要配置归档信息和开启RLOG_APPEND_LOGIC参数
RLOG_APPEND_LOGIC参数代表是否启用在日志中记录逻辑操作的功能
| 选项 | 说明 |
|---|---|
| 0 | 不启用 |
| 1 | 如果有主键列，记录update和delete操作时只包含主键列信息，若没有主键列则包含所有列信息 |
| 2 | 不论是否有主键列，记录update和delete操作时都包含所有列的信息 |
| 3 | 记录update时包含更新列的信息以及rowid，记录delete时只有rowid |
二、配置环境
1.配置归档
SQL> alter database mount;
alter database mount;
第1 行附近出现错误[-510]:系统处于MOUNT状态.
已用时间: 1.365(毫秒). 执行号:0.
SQL> alter database archivelog;
操作已执行
已用时间: 101.622(毫秒). 执行号:0.
SQL> alter database add archivelog 'dest=/opt/dmdbms/data/DAMENG/arch,type=local,file_size=64,space_limit=10240';
操作已执行
已用时间: 2.406(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 290.330(毫秒). 执行号:0.
2.配置RLOG_APPEND_LOGIC
SQL> alter system set 'RLOG_APPEND_LOGIC'=1 MEMORY;
DMSQL 过程已成功完成
已用时间: 6.456(毫秒). 执行号:700.
3.检查配置情况是否正确
使用下面的语句来查看当前环境配置情况
SQL> select para_name, para_value
from v$dm_ini
where para_name in ('ARCH_INI','RLOG_APPEND_LOGIC');2 3
行号 PARA_NAME PARA_VALUE
---------- ----------------- ----------
1 RLOG_APPEND_LOGIC 1
2 ARCH_INI 1
已用时间: 7.383(毫秒). 执行号:701.
4.准备测试环境
SQL> create table T50 (id int,name varchar(50));
insert into T50 values(1,'zhangsan');
insert into T50 values(2,'lisi');
insert into T50 values(3,'wangwu');
insert into T50 values(4,'czk');
insert into T50 values(5,'gg');
update T50 set id = id + 100 where id <100;
update T50 set id=15 where name='gg';
commit;
delete from T50 where id=103;
commit;操作已执行
已用时间: 5.008(毫秒). 执行号:702.
SQL> 影响行数 1
已用时间: 0.706(毫秒). 执行号:703.
SQL> 影响行数 1
已用时间: 0.271(毫秒). 执行号:704.
SQL> 影响行数 1
已用时间: 0.232(毫秒). 执行号:705.
SQL> 影响行数 1
已用时间: 0.308(毫秒). 执行号:706.
SQL> 影响行数 1
已用时间: 0.475(毫秒). 执行号:707.
SQL> 影响行数 5
已用时间: 1.506(毫秒). 执行号:708.
SQL> 影响行数 1
已用时间: 0.920(毫秒). 执行号:709.
SQL> 操作已执行
已用时间: 1.296(毫秒). 执行号:710.
SQL> 影响行数 1
已用时间: 1.351(毫秒). 执行号:711.
5.切换归档
SQL> alter system archive log current;
操作已执行
已用时间: 2.268(毫秒). 执行号:0.
三、配置LOGMNR
1.查看目前的归档信息
SQL> SELECT NAME , FIRST_TIME , NEXT_TIME , FIRST_CHANGE# , NEXT_CHANGE# FROM
V$ARCHIVED_LOG;2
行号 NAME FIRST_TIME
---------- ---------------------------------------------------------------------------------- --------------------------
NEXT_TIME FIRST_CHANGE# NEXT_CHANGE#
-------------------------- -------------------- --------------------
1 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_12-16-18.log 2022-06-01 12:16:18.409705
2022-06-01 12:20:08.235961 35865 37364
2 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_12-20-09.log 2022-06-01 12:20:09.516316
2022-06-01 13:52:39.609487 37365 39239
3 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_13-52-43.log 2022-06-01 13:52:43.234621
2022-06-01 13:53:24.358957 39240 39261
行号 NAME FIRST_TIME
---------- ---------------------------------------------------------------------------------- --------------------------
NEXT_TIME FIRST_CHANGE# NEXT_CHANGE#
-------------------------- -------------------- --------------------
4 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_13-53-28.log 2022-06-01 13:53:28.258643
2022-06-01 14:30:43.468079 39262 40086
5 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_14-30-44.log 2022-06-01 14:30:44.663696
2022-06-01 14:44:07.418117 40087 40356
6 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_14-44-09.log 2022-06-01 14:44:09.161394
2022-06-01 14:46:30.810640 40357 40404
行号 NAME FIRST_TIME
---------- ---------------------------------------------------------------------------------- --------------------------
NEXT_TIME FIRST_CHANGE# NEXT_CHANGE#
-------------------------- -------------------- --------------------
7 /opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_14-46-33.log 2022-06-01 14:46:33.243886
2022-06-01 14:46:33.243886 40405 40621
7 rows got
已用时间: 0.628(毫秒). 执行号:716.
1.添加归档日志文件
利用add_logfile可以添加一份或者多份需要分析的归档信息
利用remove_logfile可以移除需要分析的归档信息
SQL> DBMS_LOGMNR.ADD_LOGFILE('/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_12-16-18.log');
DBMS_LOGMNR.ADD_LOGFILE('/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_12-20-09.log');
DBMS_LOGMNR.ADD_LOGFILE('/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_13-52-43.log');
DBMS_LOGMNR.ADD_LOGFILE('/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_13-53-28.log');
DBMS_LOGMNR.ADD_LOGFILE('/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_14-30-44.log');DMSQL 过程已成功完成
已用时间: 2.294(毫秒). 执行号:901.
SQL> DMSQL 过程已成功完成
已用时间: 0.334(毫秒). 执行号:902.
SQL> DMSQL 过程已成功完成
已用时间: 0.439(毫秒). 执行号:903.
SQL> DMSQL 过程已成功完成
已用时间: 0.116(毫秒). 执行号:904.
2.查看添加归档信息情况
SQL> SELECT LOW_SCN, NEXT_SCN, LOW_TIME, HIGH_TIME, LOG_ID, FILENAME FROM
V$LOGMNR_LOGS;2
行号 LOW_SCN NEXT_SCN LOW_TIME HIGH_TIME LOG_ID
---------- -------------------- -------------------- -------------------------- -------------------------- -----------
FILENAME
----------------------------------------------------------------------------------
1 35865 37364 2022-06-01 12:16:17.878000 2022-06-01 12:18:40.989000 0
/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_12-16-18.log
2 37365 39239 2022-06-01 13:52:27.039000 2022-06-01 13:52:27.057000 1
/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_12-20-09.log
3 39240 39261 2022-06-01 13:53:15.996000 2022-06-01 13:53:15.996000 2
/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_13-52-43.log
行号 LOW_SCN NEXT_SCN LOW_TIME HIGH_TIME LOG_ID
---------- -------------------- -------------------- -------------------------- -------------------------- -----------
FILENAME
----------------------------------------------------------------------------------
4 39262 40086 2022-06-01 14:28:15.082000 2022-06-01 14:28:15.092000 3
/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_13-53-28.log
5 40087 40356 2022-06-01 14:41:20.870000 2022-06-01 14:41:20.871000 4
/opt/dmdbms/data/DAMENG/arch/ARCHIVE_LOCAL1_0x3329F2AC_EP0_2022-06-01_14-30-44.log
已用时间: 1.629(毫秒). 执行号:905.
3.启动归档日志文件分析
OPTIONS参数参考如下表所列的可选模式，各模式可以通过 + 或者按位或来进行组合。其它位的值如 1、4、8 等目前不支持，配置后不会报错，但是没有效果。例如，组合全部模式，则取值2+16+64+2048=2130，那么 OPTIONS 值就是 2130。
| 选项 | 对应值 | 说明 |
|---|---|---|
| COMMITTED_DATA_ONLY | 2 | 仅从已交的事务日志中挖掘信息 |
| DICT_FROM_ONLINE_CATALOG | 16 | 使用在线字典 |
| NO_SQL_DELIMITER | 64 | 拼写的sql语句最后不添加分隔符 |
| NO_ROWID_IN_STMT | 2048 | 拼写的sql语句中不包含ROWID |
SQL> DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128 , STARTTIME=>TO_DATE('2022-06-1
12:16:00','YYYY-MM-DD HH24:MI:SS') , ENDTIME=>TO_DATE('2022-06-03 12:21:02','YYYY-MM-DD HH24:MI:SS'));2
DMSQL 过程已成功完成
已用时间: 8.570(毫秒). 执行号:906.
4.查看归档日志分析结果
SQL> SELECT OPERATION_CODE , SCN, SQL_REDO , TIMESTAMP ,SEG_OWNER, TABLE_NAME FROM
V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'SYSDBA' AND OPERATION_CODE IN (3,1,2);2
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- ------------------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
1 1 40015 INSERT INTO "SYSDBA"."T50"("ID", "NAME") VALUES(5, 'gg')
2022-06-01 14:28:15.087000 SYSDBA T50
2 3 37305 UPDATE "SYSDBA"."T_TEST" SET "ID" = 101 WHERE "ID" = 1 AND "NAME" = 'zhangsan'
2022-06-01 12:18:40.926000 SYSDBA T_TEST
3 3 37307 UPDATE "SYSDBA"."T_TEST" SET "ID" = 102 WHERE "ID" = 2 AND "NAME" = 'lisi'
2022-06-01 12:18:40.926000 SYSDBA T_TEST
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- ------------------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
4 3 37309 UPDATE "SYSDBA"."T_TEST" SET "ID" = 103 WHERE "ID" = 3 AND "NAME" = 'wangwu'
2022-06-01 12:18:40.926000 SYSDBA T_TEST
5 3 39256 UPDATE "SYSDBA"."T_TEST" SET "ID" = 15 WHERE "ID" = 5 AND "NAME" = 'gg'
2022-06-01 13:53:15.996000 SYSDBA T_TEST
6 3 40018 UPDATE "SYSDBA"."T50" SET "ID" = 101 WHERE "ID" = 1 AND "NAME" = 'zhangsan'
2022-06-01 14:28:15.088000 SYSDBA T50
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- ------------------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
7 3 40020 UPDATE "SYSDBA"."T50" SET "ID" = 102 WHERE "ID" = 2 AND "NAME" = 'lisi'
2022-06-01 14:28:15.088000 SYSDBA T50
8 3 40022 UPDATE "SYSDBA"."T50" SET "ID" = 103 WHERE "ID" = 3 AND "NAME" = 'wangwu'
2022-06-01 14:28:15.088000 SYSDBA T50
9 3 40024 UPDATE "SYSDBA"."T50" SET "ID" = 104 WHERE "ID" = 4 AND "NAME" = 'czk'
2022-06-01 14:28:15.088000 SYSDBA T50
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- ------------------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
10 3 40026 UPDATE "SYSDBA"."T50" SET "ID" = 105 WHERE "ID" = 5 AND "NAME" = 'gg'
2022-06-01 14:28:15.088000 SYSDBA T50
11 3 40029 UPDATE "SYSDBA"."T50" SET "ID" = 15 WHERE "ID" = 105 AND "NAME" = 'gg'
2022-06-01 14:28:15.089000 SYSDBA T50
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- --------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
12 2 37316 DELETE FROM "SYSDBA"."T_TEST" WHERE "ID" = 103 AND "NAME" = 'wangwu'
2022-06-01 12:18:40.988000 SYSDBA T_TEST
13 2 40036 DELETE FROM "SYSDBA"."T50" WHERE "ID" = 103 AND "NAME" = 'wangwu'
2022-06-01 14:28:15.092000 SYSDBA T50
14 1 37281 INSERT INTO "SYSDBA"."T_TEST"("ID", "NAME") VALUES(1, 'zhangsan')
2022-06-01 12:18:40.826000 SYSDBA T_TEST
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- --------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
15 1 37289 INSERT INTO "SYSDBA"."T_TEST"("ID", "NAME") VALUES(2, 'lisi')
2022-06-01 12:18:40.848000 SYSDBA T_TEST
16 1 37297 INSERT INTO "SYSDBA"."T_TEST"("ID", "NAME") VALUES(3, 'wangwu')
2022-06-01 12:18:40.900000 SYSDBA T_TEST
17 1 39223 INSERT INTO "SYSDBA"."T_TEST"("ID", "NAME") VALUES(4, 'czk')
2022-06-01 13:52:27.039000 SYSDBA T_TEST
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- --------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
18 1 39231 INSERT INTO "SYSDBA"."T_TEST"("ID", "NAME") VALUES(5, 'gg')
2022-06-01 13:52:27.056000 SYSDBA T_TEST
19 1 40003 INSERT INTO "SYSDBA"."T50"("ID", "NAME") VALUES(1, 'zhangsan')
2022-06-01 14:28:15.085000 SYSDBA T50
20 1 40006 INSERT INTO "SYSDBA"."T50"("ID", "NAME") VALUES(2, 'lisi')
2022-06-01 14:28:15.086000 SYSDBA T50
行号 OPERATION_CODE SCN SQL_REDO
---------- -------------- -------------------- --------------------------------------------------------------------
TIMESTAMP SEG_OWNER TABLE_NAME
-------------------------- --------- ----------
21 1 40009 INSERT INTO "SYSDBA"."T50"("ID", "NAME") VALUES(3, 'wangwu')
2022-06-01 14:28:15.086000 SYSDBA T50
22 1 40012 INSERT INTO "SYSDBA"."T50"("ID", "NAME") VALUES(4, 'czk')
2022-06-01 14:28:15.086000 SYSDBA T50
22 rows got
已用时间: 2.102(毫秒). 执行号:907.
5.关闭归档日志分析
SQL> DBMS_LOGMNR.END_LOGMNR();
DMSQL 过程已成功完成
已用时间: 0.375(毫秒). 执行号:908.
总结
如果你还有其他问题，欢迎到达梦社区来提问~
社区地址：https://eco.dameng.com
1313
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
