达梦数据库使用dmlcvt命令找回更改前的数据

老刘学达梦

已于 2024-05-18 20:56:00 修改

阅读量281

点赞数 2

分类专栏：国产化数据库达梦文章标签：数据库前端 javascript

于 2024-05-18 17:29:39 首次发布

本文链接：https://blog.csdn.net/weixin_56866387/article/details/139027035

版权

国产化数据库同时被 2 个专栏收录

27 篇文章 1 订阅

订阅专栏

达梦

27 篇文章 1 订阅

订阅专栏

在生产系统上不小心修改了表数据后最快的方法是用闪回查询找回。但时间不能超过undo_retention（默认90秒）。其实最标准的处理方法是在其他机器上将数据库恢复到修改前的时刻。但数据库比较大时恢复时间较长。真实场景可能比较急。那么也可以分析归档日志找回修改前的状态。

分析归档日志的最常用方法是LOGMNR日志挖掘。但要求事先已经打开了附加日志RLOG_APPEND_LOGIC（默认不打开）。本文介绍另一种找回办法dmlcvt

录入测试数据

#登录数据库
[dmdba@node1 log]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.486(ms)
disql V8

#创建测试表
SQL> create table test1(a varchar(100));
executed successfully
used time: 18.696(ms). Execute id is 500.

#显示当前事务号
SQL> select trx_id from v$sessions where sess_id=sessid;
LINEID     TRX_ID
---------- --------------------
1          22079
used time: 1.073(ms). Execute id is 501.

#插入数据
SQL> insert into test1 values('LIUQINGYAN');
affect rows 1
used time: 0.581(ms). Execute id is 502.
SQL> commit;
executed successfully
used time: 3.441(ms). Execute id is 503.

#显示当前事务号
SQL> select trx_id from v$sessions where sess_id=sessid;
LINEID     TRX_ID
---------- --------------------
1          22080
used time: 0.241(ms). Execute id is 504.

#修改表数据
SQL> update test1 set a='LIUQINGYAN1';
affect rows 1
used time: 1.161(ms). Execute id is 505.
SQL> commit;
executed successfully
used time: 3.493(ms). Execute id is 506.

#切归档
SQL> alter system switch logfile;
executed successfully
used time: 14.133(ms). Execute id is 0.

打开dmsql日志

2024-05-18 04:17:01.376 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:0 stmt:0x7f1d50035178 appname:disql ip:::1) [ORA]: select trx_id from v$sessions where sess_id=sessid;
2024-05-18 04:17:01.376 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:NULL appname:disql) TRX: START
2024-05-18 04:17:01.377 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [SEL] select trx_id from v$sessions where sess_id=sessid;
2024-05-18 04:17:01.377 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [SEL] select trx_id from v$sessions where sess_id=sessid; EXECTIME: 0(ms) ROWCOUNT: 1(rows).
2024-05-18 04:17:24.864 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [ORA]: insert into test1 values('LIUQINGYAN');
2024-05-18 04:17:24.864 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [INS] insert into test1 values('LIUQINGYAN');
2024-05-18 04:17:24.865 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:NULL appname:disql) trx[22079] alloc pseg page[0, 1311], page_lsn[55069], n_pages[1]
2024-05-18 04:17:24.865 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [INS] insert into test1 values('LIUQINGYAN'); EXECTIME: 0(ms) ROWCOUNT: 1(rows).
2024-05-18 04:17:27.457 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [ORA]: commit;
2024-05-18 04:17:27.457 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:0x7f1d50035178 appname:disql ip:::1) [DML] commit;
2024-05-18 04:17:27.457 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:NULL appname:disql) TRX: COMMIT
2024-05-18 04:17:27.457 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22079 stmt:NULL appname:disql) trx[22079]: pseg_page_free_for_insert_only_trx free pseg page (0, 1311), page_lsn = 55074
2024-05-18 04:17:27.460 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:0 stmt:NULL appname:disql) TRX: COMMIT LSN[55073]
2024-05-18 04:17:27.460 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:0 stmt:0x7f1d50035178 appname:disql ip:::1) [DML] commit; EXECTIME: 3(ms).
2024-05-18 04:17:32.464 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:0 stmt:0x7f1d50035178 appname:disql ip:::1) [ORA]: select trx_id from v$sessions where sess_id=sessid;
2024-05-18 04:17:32.464 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:NULL appname:disql) TRX: START
2024-05-18 04:17:32.464 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:0x7f1d50035178 appname:disql ip:::1) [SEL] select trx_id from v$sessions where sess_id=sessid; EXECTIME: 0(ms) ROWCOUNT: 1(rows).
2024-05-18 04:17:50.981 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:0x7f1d50035178 appname:disql ip:::1) [ORA]: update test1 set a='LIUQINGYAN1';
2024-05-18 04:17:50.981 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:0x7f1d50035178 appname:disql ip:::1) [UPD] update test1 set a='LIUQINGYAN1';
2024-05-18 04:17:50.982 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:NULL appname:disql) trx[22080] alloc pseg page[0, 1311], page_lsn[55083], n_pages[1]
2024-05-18 04:17:50.982 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:0x7f1d50035178 appname:disql ip:::1) [UPD] update test1 set a='LIUQINGYAN1'; EXECTIME: 0(ms) ROWCOUNT: 1(rows).
2024-05-18 04:17:52.612 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:0x7f1d50035178 appname:disql ip:::1) [ORA]: commit;
2024-05-18 04:17:52.612 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:0x7f1d50035178 appname:disql ip:::1) [DML] commit;
2024-05-18 04:17:52.612 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:22080 stmt:NULL appname:disql) TRX: COMMIT
2024-05-18 04:17:52.615 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:0 stmt:NULL appname:disql) TRX: COMMIT LSN[55086]
2024-05-18 04:17:52.615 (EP[0] sess:0x7f1d50011978 thrd:14617 user:SYSDBA trxid:0 stmt:0x7f1d50035178 appname:disql ip:::1) [DML] commit; EXECTIME: 3(ms).

update test1 set a='LIUQINGYAN'语句开启了一个新事务，事务号：22080。系统为该事务在回滚段分配了1311号页面用于保存前映像，以保证万一rollback时可以回到事务开始前的状态。

日志分析

dmlcvt F_TYPE=2 F_PATH=/home/dmdba/arch/ARCHIVE_LOCAL1_0x61299DA6_EP0_2024-05-18_01-49-37.log rec_level=2 rec_data=1 out_path=/home/dmdba/rec ts_id=1 file_id=0
#ts_id=1   回滚段表空间
#file_id=0 表空间内第0号文件
#out_path  日志分析结果目录

日志分析节选如下：

#在ROLL.DBF文件为该事务分配1311号页面
PAGE_INIT:      (29, 1, 0, 1311)
WORD:   (1, 0, 1311, 8, 2, 65535)
DWORD:  (1, 0, 1311, 10, 4, 4294967295)
WORD:   (1, 0, 1311, 14, 2, 65535)
DWORD:  (1, 0, 1311, 16, 4, 4294967295)

#1311号页面用于保存22080事务的前映像（修改前的数据）
TRXID_H0:       (1, 0, 1311, 36, 4, 22080)
BYTE:   (1, 0, 1311, 42, 1, 0)
WORD:   (1, 0, 1311, 43, 2, 55)
WORD:   (1, 0, 1311, 45, 2, 55)
LINT64: (1, 0, 1311, 47, 8, 0)
PAGE_INIT:      (36, 1, 0, 528)
WORD:   (1, 0, 528, 8, 2, 65535)
DWORD:  (1, 0, 528, 10, 4, 4294967295)
WORD:   (1, 0, 528, 14, 2, 65535)
DWORD:  (1, 0, 528, 16, 4, 4294967295)
WORD:   (1, 0, 528, 36, 2, 248)
WORD:   (1, 0, 528, 38, 2, 0)
BYTE:   (1, 0, 528, 8153, 1, 0)
BYTE:   (1, 0, 528, 8154, 1, 0)
BYTE:   (1, 0, 528, 8155, 1, 0)
BYTE:   (1, 0, 528, 8156, 1, 0)
BYTE:   (1, 0, 528, 8157, 1, 0)
BYTE:   (1, 0, 528, 8158, 1, 0)
BYTE:   (1, 0, 528, 8162, 1, 0)
BYTE:   (1, 0, 528, 8163, 1, 0)
BYTE:   (1, 0, 528, 8164, 1, 0)
BYTE:   (1, 0, 528, 8165, 1, 0)
BYTE:   (1, 0, 528, 8166, 1, 0)
BYTE:   (1, 0, 528, 8167, 1, 0)
BYTE:   (1, 0, 528, 8168, 1, 0)
BYTE:   (1, 0, 528, 8169, 1, 0)
BYTE:   (1, 0, 528, 8170, 1, 0)
BYTE:   (1, 0, 528, 8171, 1, 0)
BYTE:   (1, 0, 528, 8172, 1, 0)
BYTE:   (1, 0, 528, 8173, 1, 0)
BYTE:   (1, 0, 528, 8174, 1, 0)
BYTE:   (1, 0, 528, 8175, 1, 0)
BYTE:   (1, 0, 528, 8176, 1, 0)
BYTE:   (1, 0, 528, 8177, 1, 0)
BYTE:   (1, 0, 528, 8178, 1, 0)
BYTE:   (1, 0, 528, 8179, 1, 0)
BYTE:   (1, 0, 528, 8180, 1, 0)
BYTE:   (1, 0, 528, 8181, 1, 0)
BYTE:   (1, 0, 528, 8182, 1, 0)
BYTE:   (1, 0, 528, 8183, 1, 0)
BYTE:   (1, 0, 528, 8153, 1, 128)
WORD:   (1, 0, 528, 38, 2, 1)
ANY:    (1, 0, 528, 40, 32, 0x40560000000000001F0500000000000000000000000000000000000000000000)
ANY:    (1, 0, 1311, 55, 64, 0x40000203040000003F560000000000000000000026040000FFFFFFFFFFFFFFFF7FFFFF0100000000000000010000000A004C495551494E4759414E0000003700)

#上一个事务（22079）写入的0x426表的数据：LIUQINGYAN
        UREC_UPD:       (seq:4, nrec_tid:22079, tabid:0x426(), rowid:1, n_keys:0)
                (id:0, 0x4C495551494E4759414E, [LIUQINGYAN])
WORD:   (1, 0, 1311, 45, 2, 119)

pkg_ver=1, pkg_type=0, use_hash=0, n_ep=1, dsc_seqno=0, db_magic=1630117286, p_db_magic=0, term_id=0, prev_term_id=0, c_seqno=10012, c_lsn=55084, l_seqno=10013, g_seqno=10013, prev_lsn=55084
min_lsn=55085, max_lsn=55086, pkg_len=512, data_off=256, real_file_off=196608, n_dps=0, n_pll=1, use_hash=0
BYTE:   (1, 0, 1311, 42, 1, 1)
ANY:    (1, 0, 1311, 119, 37, 0x25001202060000004056000000000000640000000000008002000000260400006800447700)

#22080事务描述结束
        UREC_TRX_END:   (seq:6, trxid:22080)

由上可见：

事务号（TRXID）22080

分配（PAGE_INIT）回滚页面 1311

22080事务修改前的数据：22079事务写入的‘LIUQINGYAN’。与dmsql日志中吻合。

注：tabid:0x426是表的十六进制对象号，转换成十进制是1062。

下面我们读一下ROLL.DBF文件

由上可见回滚文件中确实存在LIUQINGYAN。将1311号页面dd出来也可以找到LIUQINGYAN，而其他页面不存在。与日志分析结果吻合。

继续测试

录入数据

#更新数据
SQL> update test1 set a='LIUQINGTAO';
affect rows 1
used time: 6.593(ms). Execute id is 500.
SQL> commit;
executed successfully
used time: 3.684(ms). Execute id is 501.
SQL> alter system switch logfile;
executed successfully

#切归档
SQL> alter system switch logfile;
executed successfully
used time: 14.133(ms). Execute id is 0.

查看dmlog日志

2024-05-18 05:37:44.797 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:0 stmt:0x7fa81c035178 appname:disql ip:::1) [ORA]: update test1 set a='LIUQINGTAO';
2024-05-18 05:37:44.798 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:NULL appname:disql) TRX: START
2024-05-18 05:37:44.799 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:0x7fa81c035178 appname:disql ip:::1) [UPD] update test1 set a='LIUQINGTAO';
2024-05-18 05:37:44.800 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:NULL appname:disql) trx[26091] alloc pseg page[0, 1311], page_lsn[59648], n_pages[1]
2024-05-18 05:37:44.801 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:0x7fa81c035178 appname:disql ip:::1) [UPD] update test1 set a='LIUQINGTAO'; EXECTIME: 1(ms) ROWCOUNT: 1(rows).
2024-05-18 05:37:46.554 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:0x7fa81c035178 appname:disql ip:::1) [ORA]: commit;
2024-05-18 05:37:46.554 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:0x7fa81c035178 appname:disql ip:::1) [DML] commit;
2024-05-18 05:37:46.554 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:26091 stmt:NULL appname:disql) TRX: COMMIT
2024-05-18 05:37:46.557 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:0 stmt:NULL appname:disql) TRX: COMMIT LSN[59651]
2024-05-18 05:37:46.557 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:0 stmt:0x7fa81c035178 appname:disql ip:::1) [DML] commit; EXECTIME: 3(ms).
:38:59.187 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:0 stmt:0x7fa81c035178 appname:disql ip:::1) [ORA]: alter system switch logfile;
2024-05-18 05:39:13.932 (EP[0] sess:0x7fa81c011978 thrd:19497 user:SYSDBA trxid:0 stmt:NULL appname:disql) FREE SESSION
2024-05-18 05:39:16.743 (EP[0] sess:NULL thrd:NULL user:NULL trxid:NULL stmt:NULL)  trx[26091]: purg2_page free pseg page (0, 1311), page_lsn = 59682

update test1 set a='LIUQINGTAO'语句开启了一个新事务，事务号：26091。值得注意的是系统为该事务重用了1311号回滚页面用于保存前映像

日志分析

dmlcvt F_TYPE=2 F_PATH=/home/dmdba/arch/ARCHIVE_LOCAL1_0x61299DA6_EP0_2024-05-18_05-37-19.log rec_level=2 rec_data=1 out_path=/home/dmdba/rec ts_id=1 file_id=0

日志分析结果

PAGE_INIT:      (29, 1, 0, 1311)
WORD:   (1, 0, 1311, 8, 2, 65535)
DWORD:  (1, 0, 1311, 10, 4, 4294967295)
WORD:   (1, 0, 1311, 14, 2, 65535)
DWORD:  (1, 0, 1311, 16, 4, 4294967295)
TRXID_H0:       (1, 0, 1311, 36, 4, 26091)
BYTE:   (1, 0, 1311, 42, 1, 0)
WORD:   (1, 0, 1311, 43, 2, 55)
WORD:   (1, 0, 1311, 45, 2, 55)
LINT64: (1, 0, 1311, 47, 8, 0)
PAGE_INIT:      (36, 1, 0, 528)
WORD:   (1, 0, 528, 8, 2, 65535)
DWORD:  (1, 0, 528, 10, 4, 4294967295)
WORD:   (1, 0, 528, 14, 2, 65535)
DWORD:  (1, 0, 528, 16, 4, 4294967295)
WORD:   (1, 0, 528, 36, 2, 248)
WORD:   (1, 0, 528, 38, 2, 0)
BYTE:   (1, 0, 528, 8153, 1, 0)
BYTE:   (1, 0, 528, 8154, 1, 0)
BYTE:   (1, 0, 528, 8155, 1, 0)
BYTE:   (1, 0, 528, 8156, 1, 0)
BYTE:   (1, 0, 528, 8157, 1, 0)
BYTE:   (1, 0, 528, 8158, 1, 0)
BYTE:   (1, 0, 528, 8159, 1, 0)
BYTE:   (1, 0, 528, 8160, 1, 0)
BYTE:   (1, 0, 528, 8161, 1, 0)
BYTE:   (1, 0, 528, 8162, 1, 0)
BYTE:   (1, 0, 528, 8163, 1, 0)
BYTE:   (1, 0, 528, 8164, 1, 0)
BYTE:   (1, 0, 528, 8165, 1, 0)            
BYTE:   (1, 0, 528, 8180, 1, 0)
BYTE:   (1, 0, 528, 8181, 1, 0)
BYTE:   (1, 0, 528, 8182, 1, 0)
BYTE:   (1, 0, 528, 8183, 1, 0)
BYTE:   (1, 0, 528, 8153, 1, 128)
WORD:   (1, 0, 528, 38, 2, 1)
ANY:    (1, 0, 528, 40, 32, 0xEB650000000000001F0500000000000000000000000000000000000000000000)
ANY:    (1, 0, 1311, 55, 65, 0x410002030300000040560000000000000000000026040000FFFFFFFF001F05000037000100000000000000010000000B004C495551494E4759414E310000003700)
        UREC_UPD:       (seq:3, nrec_tid:22080, tabid:0x426(), rowid:1, n_keys:0)
                (id:0, 0x4C495551494E4759414E31, [LIUQINGYAN1])
WORD:   (1, 0, 1311, 45, 2, 120)

pkg_ver=1, pkg_type=0, use_hash=0, n_ep=1, dsc_seqno=0, db_magic=1630117286, p_db_magic=0, term_id=0, prev_term_id=0, c_seqno=11622, c_lsn=59649, l_seqno=11623, g_seqno=11623, prev_lsn=59649
min_lsn=59650, max_lsn=59651, pkg_len=512, data_off=256, real_file_off=162816, n_dps=0, n_pll=1, use_hash=0
BYTE:   (1, 0, 1311, 42, 1, 1)
ANY:    (1, 0, 1311, 120, 37, 0x2500120205000000EB65000000000000000000000000000000000000000000000000007800)
        UREC_TRX_END:   (seq:5, trxid:26091)

由上可见：

事务号（TRXID）26091

分配（PAGE_INIT）回滚页面 1311

26091事务修改前的数据：22080事务写入的‘LIUQINGYAN1’。与dmsql日志中吻合。

既然是重用1311页面，那么上一份数据肯定被覆盖了。

下面检查得到了验证

回滚文件里记录的数据变成了LIUQINGYAN1

老刘学达梦

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
达梦数据库使用dmlcvt命令找回更改前的数据

在生产系统上不小心修改了表数据后最快的方法是用闪回查询找回。但时间不能超过undo_retention（默认90秒）。其实最标准的处理方法是在其他机器上将数据库恢复到修改前的时刻。但数据库比较大时恢复时间较长。真实场景可能比较急。那么也可以分析归档日志知道修改前的状态。分析归档日志的最常用方法是LOGMNR日志挖掘。但要求事先已经打开了附加日志RLOG_APPEND_LOGIC（默认不打开）。本文介绍另一种找回办法dmlcvt
复制链接

扫一扫