众所周知数据库的DML操作会记录在REDO日志中，如果开了归档REDO可以存储的更久，有时当闪回查询无法使用，或需要从日志中挖掘过去某操作时间或操作人信息，做恢复依据。在oracle提供数据库挖掘logminer使用dbms_logmnr, 在国产数据库达梦中同样支持，而且语法和oracle几乎一样。
测试
- 开启归档
alter database mount;
ALTER DATABASE ADD ARCHIVELOG 'DEST = /home/dm8/arch, TYPE = local,FILE_SIZE = 128, SPACE_LIMIT = 2048';
alter database archivelog;
alter database open;Note: 在达梦中数据库模式可以在生命周期内open来回切mount（维护模式），不用重启实例，与oracle不同。
SQL> select * from v$database;
LINEID NAME CREATE_TIME ARCH_MODE LAST_CKPT_TIME STATUS$ ROLE$ MAX_SIZE TOTAL_SIZE DSC_NODES OPEN_COUNT STARTUP_COUNT LAST_STARTUP_TIME
---------- ----- ------------------- --------- ------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- -------------------- -------------------
1 anbob 2025-07-22 17:57:03 Y 2025-08-11 18:24:43 4 0 0 52736 1 12 8 2025-07-30 20:30:31
SQL> select * from v$dm_arch_ini;
LINEID ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH ARCH_CURR_DEST ARCH_FLUSH_BUF_SIZE
---------- -------------- --------- -------------- -------------- ---------------- -------------- --------------- ------------- --------------- ------------------ -------------- -------------------
ARCH_RESERVE_TIME ARCH_LOCAL_SHARE ARCH_LOCAL_SHARE_CHECK ARCH_SEND_DELAY ARCH_DEST_IP ARCH_DEST_PORT ARCH_DEST_ID ARCH_ASM_MIRROR ARCH_ASM_STRIPING ARCH_RECOVER_TIME ARCH_CENTER_ID
----------------- ---------------- ---------------------- --------------- ------------ -------------- ------------ --------------- ----------------- ----------------- --------------
ARCH_FAILOVER ARCH_SUBSCRIBE_MODE ARCH_THRESHOLD_PER_SECOND ARCH_STANDBY_APPLY
------------- ------------------- ------------------------- ------------------
1 ARCHIVE_LOCAL1 LOCAL /home/dm8/arch 128 0 1 NULL Y NULL NULL /home/dm8/arch 2
0 0 0 0 NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL
SQL> select * from v$rlog;
LINEID CKPT_LSN FILE_LSN FLUSH_LSN CUR_LSN NEXT_SEQ N_MAGIC DB_MAGIC FLUSH_PAGES FLUSHING_PAGES CUR_FILE
---------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------- -----------
CUR_OFFSET CKPT_FILE CKPT_OFFSET FREE_SPACE TOTAL_SPACE SUSPEND_TIME UPD_CTL_LSN N_RESERVE_WAIT TOTAL_FLUSH_PAGES TOTAL_FLUSH_TIMES
-------------------- ----------- -------------------- -------------------- -------------------- ------------ -------------------- -------------- -------------------- --------------------
TOTAL_ECPR_FLUSH_PAGES GLOBAL_NEXT_SEQ N_PRIMAY_EP PRIMARY_DB_MAGIC CKPT_N_PRIMAY_EP CKPT_PRIMARY_DB_MAGIC MIN_EXEC_VER MIN_DCT_VER CLSN_OPEN_ID DB_OPEN_ID ALMOST_FULL_STATUS
---------------------- -------------------- ----------- -------------------- ---------------- --------------------- ------------ ----------- ------------ ----------- ------------------
PTX_RESERVED RESERVE_THRESHOLD RESERVE_SIZE
-------------------- ----------------- ------------
1 50601 50746 50746 50746 4001 7 853592382 0 0 0
15282176 0 15220736 4294897664 4294959104 NULL 0 0 640 69
0 4001 0 0 0 0 V8.1.1.1 4 8 8 SAFE
0 0 32767
SQL> select * from v$rlogfile;
LINEID GROUP_ID FILE_ID PATH CLIENT_PATH CREATE_TIME RLOG_SIZE MIN_EXEC_VER MIN_DCT_VER
---------- ----------- ----------- --------------------------------------- ----------- -------------------------- -------------------- ------------ -----------
1 2 0 /home/dm8/dmdbms/data/anbob/anbob01.log anbob01.log 2025-07-22 17:57:03.000000 2147483648 V8.1.1.1 4
2 2 1 /home/dm8/dmdbms/data/anbob/anbob02.log anbob02.log 2025-07-22 17:57:03.000000 2147483648 V8.1.1.1 4
- 配置附加日志
SQL> show parameter RLOG_APPEND_LOGIC;
LINEID PARA_NAME PARA_VALUE
---------- ----------------- ----------
1 RLOG_APPEND_LOGIC 0
SQL> alter system set 'RLOG_APPEND_LOGIC'=2;
DMSQL executed successfully
used time: 8.385(ms). Execute id is 3802.
SQL> show parameter RLOG_APPEND_LOGIC;
LINEID PARA_NAME PARA_VALUE
---------- ----------------- ----------
1 RLOG_APPEND_LOGIC 2
RLOG_APPEND_LOGIC参数介绍
是否启用在日志中记录逻辑操作的功能，取值范围0、1、2、3、4
0：不启用；1、2、3、4启用。1：如果有主键列，记录UPDATE和DELETE操作时只包含主键列信息，若没有主键列则包含所有列信息；
2：不论是否有主键列，记录UPDATE和DELETE操作时都包含所有列的信息；
3：记录UPDATE时包含更新列的信息以及ROWID，记录DELETE时只有ROWID；
4：只生成事务以及DDL相关的逻辑日志；
- 做一些DML操作
SQL> create table test_logmnr(id int primary key, name varchar(10));
executed successfully
used time: 38.587(ms). Execute id is 3808.
SQL> insert into test_logmnr values(1,'anbob');
affect rows 1
used time: 18.818(ms). Execute id is 3809.
SQL> insert into test_logmnr values(2,'weejar');
affect rows 1
used time: 0.754(ms). Execute id is 3810.
SQL> delete from test_logmnr where id=2;
affect rows 1
used time: 15.301(ms). Execute id is 3811.
SQL> update test_logmnr set name=name||'.com' where id=1;
affect rows 1
used time: 14.085(ms). Execute id is 3812.
SQL> commit;
executed successfully
used time: 1.079(ms). Execute id is 3813.
SQL> select * from test_logmnr;
LINEID ID NAME
---------- ----------- ---------
1 1 anbob.com
- 日志挖掘
SQL> select RECID, SEQUENCE#,FIRST_TIME,NEXT_TIME,BLOCKS,BLOCK_SIZE,NAME from v$archived_log;
LINEID RECID SEQUENCE# FIRST_TIME NEXT_TIME BLOCKS BLOCK_SIZE NAME
---------- ----------- ----------- -------------------------- -------------------------- ----------- ----------- --------------------------------------------------------------------
1 NULL 1 2025-07-28 18:13:17.419246 2025-07-28 18:17:45.352318 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-13-17.log
2 NULL 2 2025-07-28 18:22:48.030077 2025-07-29 17:59:40.692663 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-22-48.log
3 NULL 3 2025-07-29 17:59:44.261038 2025-07-30 20:30:10.040709 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-29_17-59-44.log
4 NULL 4 2025-07-30 20:30:31.731090 2025-07-30 20:41:27.369344 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-30-31.log
5 NULL 5 2025-07-30 20:41:45.693322 2025-07-30 20:41:45.694952 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-41-45.log
6 NULL 6 2025-07-30 21:00:31.822479 2025-07-30 21:00:31.822479 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_21-00-31.log
6 rows got
used time: 1.570(ms). Execute id is 3815.
SQL> alter system switch logfile;
executed successfully
SQL> select RECID, SEQUENCE#,FIRST_TIME,NEXT_TIME,BLOCKS,BLOCK_SIZE,NAME from v$archived_log;
LINEID RECID SEQUENCE# FIRST_TIME NEXT_TIME BLOCKS BLOCK_SIZE NAME
---------- ----------- ----------- -------------------------- -------------------------- ----------- ----------- --------------------------------------------------------------------
1 NULL 1 2025-07-28 18:13:17.419246 2025-07-28 18:17:45.352318 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-13-17.log
2 NULL 2 2025-07-28 18:22:48.030077 2025-07-29 17:59:40.692663 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-22-48.log
3 NULL 3 2025-07-29 17:59:44.261038 2025-07-30 20:30:10.040709 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-29_17-59-44.log
4 NULL 4 2025-07-30 20:30:31.731090 2025-07-30 20:41:27.369344 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-30-31.log
5 NULL 5 2025-07-30 20:41:45.693322 2025-07-30 20:41:45.694952 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-41-45.log
6 NULL 6 2025-07-30 21:00:31.822479 2025-08-11 18:37:39.799113 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_21-00-31.log
6 rows got
SQL> select RECID, SEQUENCE#,FIRST_TIME,NEXT_TIME,BLOCKS,BLOCK_SIZE,NAME from v$archived_log;
LINEID RECID SEQUENCE# FIRST_TIME NEXT_TIME BLOCKS BLOCK_SIZE NAME
---------- ----------- ----------- -------------------------- -------------------------- ----------- ----------- --------------------------------------------------------------------
1 NULL 1 2025-07-28 18:13:17.419246 2025-07-28 18:17:45.352318 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-13-17.log
2 NULL 2 2025-07-28 18:22:48.030077 2025-07-29 17:59:40.692663 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-22-48.log
3 NULL 3 2025-07-29 17:59:44.261038 2025-07-30 20:30:10.040709 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-29_17-59-44.log
4 NULL 4 2025-07-30 20:30:31.731090 2025-07-30 20:41:27.369344 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-30-31.log
5 NULL 5 2025-07-30 20:41:45.693322 2025-07-30 20:41:45.694952 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-41-45.log
6 NULL 6 2025-07-30 21:00:31.822479 2025-07-30 21:00:31.822479 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_21-00-31.log
6 rows got
used time: 1.570(ms). Execute id is 3815.
SQL> alter system switch logfile;
executed successfully
SQL> select RECID, SEQUENCE#,FIRST_TIME,NEXT_TIME,BLOCKS,BLOCK_SIZE,NAME from v$archived_log;
LINEID RECID SEQUENCE# FIRST_TIME NEXT_TIME BLOCKS BLOCK_SIZE NAME
---------- ----------- ----------- -------------------------- -------------------------- ----------- ----------- --------------------------------------------------------------------
1 NULL 1 2025-07-28 18:13:17.419246 2025-07-28 18:17:45.352318 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-13-17.log
2 NULL 2 2025-07-28 18:22:48.030077 2025-07-29 17:59:40.692663 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-22-48.log
3 NULL 3 2025-07-29 17:59:44.261038 2025-07-30 20:30:10.040709 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-29_17-59-44.log
4 NULL 4 2025-07-30 20:30:31.731090 2025-07-30 20:41:27.369344 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-30-31.log
5 NULL 5 2025-07-30 20:41:45.693322 2025-07-30 20:41:45.694952 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-41-45.log
6 NULL 6 2025-07-30 21:00:31.822479 2025-08-11 18:37:39.799113 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_21-00-31.log
6 rows got
SQL> alter system archive log current;
executed successfully
used time: 1.605(ms). Execute id is 0.
SQL> select RECID, SEQUENCE#,FIRST_TIME,NEXT_TIME,BLOCKS,BLOCK_SIZE,NAME from v$archived_log;
LINEID RECID SEQUENCE# FIRST_TIME NEXT_TIME BLOCKS BLOCK_SIZE NAME
---------- ----------- ----------- -------------------------- -------------------------- ----------- ----------- --------------------------------------------------------------------
1 NULL 1 2025-07-28 18:13:17.419246 2025-07-28 18:17:45.352318 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-13-17.log
2 NULL 2 2025-07-28 18:22:48.030077 2025-07-29 17:59:40.692663 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-28_18-22-48.log
3 NULL 3 2025-07-29 17:59:44.261038 2025-07-30 20:30:10.040709 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-29_17-59-44.log
4 NULL 4 2025-07-30 20:30:31.731090 2025-07-30 20:41:27.369344 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-30-31.log
5 NULL 5 2025-07-30 20:41:45.693322 2025-07-30 20:41:45.694952 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_20-41-45.log
6 NULL 6 2025-07-30 21:00:31.822479 2025-08-11 18:37:39.799113 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_21-00-31.log
7 NULL 7 2025-08-11 18:42:43.398488 2025-08-11 18:43:12.360132 NULL NULL /home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-08-11_18-42-43.log
Note: 达梦数据的switch log file并未生成新归档
SQL> DBMS_LOGMNR.ADD_LOGFILE('/home/dm8/arch/ARCHIVE_LOCAL1_0x32E0C93E_EP0_2025-07-30_21-00-31.log');
DMSQL executed successfully
used time: 31.794(ms). Execute id is 3820.
SQL> DBMS_LOGMNR.START_LOGMNR(OPTIONS=>16 , STARTTIME=>TO_DATE('2025-08-11 18:12:43','YYYY-MM-DD HH24:MI:SS') , ENDTIME=>TO_DATE('2025-08-11 18:42:43','YYYY-MM-DD HH24:MI:SS'));
DMSQL executed successfully
used time: 14.248(ms). Execute id is 3824.
| Options | 对应值 | 说明 |
|---|---|---|
| COMMITTED_DATA_ONLY | 2 | 仅从已交的事务的日志中挖掘信息 |
| DICT_FROM_ONLINE_CATALOG | 16 | 使用在线字典 |
| NO_SQL_DELIMITER | 64 | 拼写的 SQL 语句最后不添加分隔符 |
| NO_ROWID_IN_STMT | 2048 | 拼写的 SQL 语句中不包含 ROWID |
- 查看挖掘记录
SQL> select SQL_REDO,TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,ROW_ID from V$LOGMNR_CONTENTS where table_name= 'TEST_LOGMNR';
LINEID SQL_REDO TIMESTAMP START_TIMESTAMP COMMIT_TIMESTAMP OPERATION ROW_ID
---------- ------------------------------------------------------------------------------------------- -------------------------- -------------------------- -------------------------- --------- ------------------
1 INSERT INTO "SYSDBA"."TEST_LOGMNR"("ID", "NAME") VALUES(1, 'anbob'); 2025-08-11 18:33:58.266000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 INSERT AAAAAAAAAAAAAAAAAB
2 INSERT INTO "SYSDBA"."TEST_LOGMNR"("ID", "NAME") VALUES(2, 'weejar'); 2025-08-11 18:34:17.043000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 INSERT AAAAAAAAAAAAAAAAAC
3 DELETE FROM "SYSDBA"."TEST_LOGMNR" WHERE "ID" = 2 AND "NAME" = 'weejar'; 2025-08-11 18:34:34.151000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 DELETE AAAAAAAAAAAAAAAAAC
4 UPDATE "SYSDBA"."TEST_LOGMNR" SET "NAME" = 'anbob.com' WHERE "ID" = 1 AND "NAME" = 'anbob'; 2025-08-11 18:34:58.183000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 UPDATE AAAAAAAAAAAAAAAAAB
SQL> select SQL_REDO,TIMESTAMP ,START_TIMESTAMP ,COMMIT_TIMESTAMP ,OPERATION ,ROW_ID from V$LOGMNR_CONTENTS where table_name= 'TEST_LOGMNR';
LINEID SQL_REDO TIMESTAMP START_TIMESTAMP COMMIT_TIMESTAMP OPERATION ROW_ID
---------- ------------------------------------------------------------------------------------------- -------------------------- -------------------------- -------------------------- --------- ------------------
1 INSERT INTO "SYSDBA"."TEST_LOGMNR"("ID", "NAME") VALUES(1, 'anbob'); 2025-08-11 18:33:58.266000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 INSERT AAAAAAAAAAAAAAAAAB
2 INSERT INTO "SYSDBA"."TEST_LOGMNR"("ID", "NAME") VALUES(2, 'weejar'); 2025-08-11 18:34:17.043000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 INSERT AAAAAAAAAAAAAAAAAC
3 DELETE FROM "SYSDBA"."TEST_LOGMNR" WHERE "ID" = 2 AND "NAME" = 'weejar'; 2025-08-11 18:34:34.151000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 DELETE AAAAAAAAAAAAAAAAAC
4 UPDATE "SYSDBA"."TEST_LOGMNR" SET "NAME" = 'anbob.com' WHERE "ID" = 1 AND "NAME" = 'anbob'; 2025-08-11 18:34:58.183000 2025-08-11 18:33:58.266000 2025-08-11 18:35:00.415000 UPDATE AAAAAAAAAAAAAAAAAB
SQL> select sql_redo,sql_undo from V$LOGMNR_CONTENTS where table_name= 'TEST_LOGMNR';
LINEID SQL_REDO SQL_UNDO
---------- ------------------------------------------------------------------------------------------- --------
1 INSERT INTO "SYSDBA"."TEST_LOGMNR"("ID", "NAME") VALUES(1, 'anbob'); NULL
2 INSERT INTO "SYSDBA"."TEST_LOGMNR"("ID", "NAME") VALUES(2, 'weejar'); NULL
3 DELETE FROM "SYSDBA"."TEST_LOGMNR" WHERE "ID" = 2 AND "NAME" = 'weejar'; NULL
4 UPDATE "SYSDBA"."TEST_LOGMNR" SET "NAME" = 'anbob.com' WHERE "ID" = 1 AND "NAME" = 'anbob'; NULL
used time: 1.510(ms). Execute id is 3830.
- 终止归档日志文件分析
DBMS_LOGMNR.END_LOGMNR();
Note: 同oracle一样v$logmnr_log 和v$logmnr_contents是会话级数据，数据在TEMP空间中，断开会话会自动释放。
— over —