达梦数据库学习笔记之 — 闪回查询
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 达梦数据库学习笔记之 — 闪回查询
达梦数据库也提供了类似Oracle 9i一样的闪回查询功能，不过仅仅是闪回查询（flashback version和flashback transaction），并不支持flashback drop table。这里我们简单测试一下。
首先需要打开flahsback 功能：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> alter system set 'enable_flashback'=1 both; DMSQL executed successfully used time: 6.523(ms). Execute id is 133. SQL> select PARA_NAME,PARA_VALUE,FILE_VALUE,DESCRIPTION from v$dm_ini where PARA_NAME like '%FLASH%'; PARA_NAME PARA_VALUE FILE_VALUE DESCRIPTION ---------------- ---------- ---------- ------------------------------------ ENABLE_FLASHBACK 1 1 Whether to enable flashback function used time: 5.796(ms). Execute id is 134. |
跟Oracle Database一样，达梦数据库的闪回也是利用回滚段来进行闪回查询进行构造，因此需要调整undo_rentention参数（参数名称跟oracle也一样）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> alter system set 'UNDO_RETENTION'=86401; alter system set 'UNDO_RETENTION'=86401; [-838]:Invalid parameter value. used time: 5.055(ms). Execute id is 0. SQL> alter system set 'UNDO_RETENTION'=86400; DMSQL executed successfully used time: 3.263(ms). Execute id is 137. SQL> select PARA_NAME,PARA_VALUE,FILE_VALUE,DESCRIPTION from v$dm_ini where PARA_NAME like '%UNDO%'; PARA_NAME PARA_VALUE FILE_VALUE DESCRIPTION --------------- ------------ ---------- ---------------------------------------------------------------------------------------- UNDO_EXTENT_NUM 4 4 Number of initial undo extents for each worker thread UNDO_RETENTION 86400.000000 90.000000 Maximum retention time in seconds for undo pages since relative transaction is committed used time: 5.531(ms). Execute id is 138. |
不过在达梦数据库中，对该参数进行了控制，最大值是86400，即1天。
接下来我们创建一个测试表试试闪回查询功能：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
SQL> create table test0826 as select * from dba_objects; executed successfully used time: 60.683(ms). Execute id is 146. SQL> select sysdate from dual; SYSDATE ---------------------------------------------------------------------------------------------------- 2021-08-26 16:31:32 used time: 1.245(ms). Execute id is 148. SQL> select owner,object_name,object_id from dba_objects where rownum < 3; OWNER OBJECT_NAME OBJECT_ID ------------ ------------ --------- BENCHMARKSQL BENCHMARKSQL 150995944 CTISYS CTISYS 150994948 used time: 9.601(ms). Execute id is 149. SQL> update test0826 set OBJECT_NAME='www.enmotech.com' where object_id=150995944; affect rows 1 used time: 1.653(ms). Execute id is 150. SQL> commit; executed successfully used time: 4.446(ms). Execute id is 151. SQL> select sysdate from dual; SYSDATE ---------------------------------------------------------------------------------------------------- 2021-08-26 16:32:45 used time: 0.618(ms). Execute id is 152. SQL> select COMMIT_TRXID,COMMIT_TIMESTAMP,OPERATION,TABLE_NAME,UNDO_SQL from V$FLASHBACK_TRX_INFO where OPERATION='U'; COMMIT_TRXID COMMIT_TIMESTAMP OPERATION TABLE_NAME -------------------- ---------------------------------------------------------------------------------------------------- --------- ---------- UNDO_SQL ------------------------------------------------------------------------- 841038 2021-08-26 01:32:40.494000 U TEST0826 UPDATE SYSDBA.TEST0826 SET OBJECT_NAME='BENCHMARKSQL' WHERE ROWID=9947649 used time: 2.894(ms). Execute id is 160. SQL> select owner,object_name,object_id 2 from test0826 when 3 TIMESTAMP '2021-08-26 01:32:30' 4 WHERE owner='BENCHMARKSQL' and object_id like '15099%' 5 / OWNER OBJECT_NAME OBJECT_ID ------------ ------------ --------- BENCHMARKSQL BENCHMARKSQL 150995944 used time: 2.813(ms). Execute id is 164. |
可以看到功能方面跟Oracle类似。可以用来应对一些误操作，比如dml的恢复。
总的来看，达梦数据库的闪回技术还是比较原始，停留在Oracle 9i这个水平。不过有总比没有好，实际上很多国产数据库连闪回功能都还不具备。
希望国产数据库越来越强。
Leave a Reply
You must be logged in to post a comment.