「YashanDB个人版体验」YashanDB&Oracle闪回功能对比及使用示例

原创 muggle 2023-11-23

579

1. 前言

在数据库对象操作过程中，数据库开发、运维人员常常担心误操作造成数据错乱或丢失。征对此问题，数据库厂家都会设计一套数据安全保护措施以确保出现误操作时进行数据找回。下面我们来对比下YashanDB与Oracle数据库提供的闪回功能，同时体验下YashanDB闪回功能。

2. 闪回功能对比

在闪回功能上，YashanDB提供了flashback table，支持表级truncate闪回，非常Nice。Oracle则提供了flashback database,flashback table，Oracle flashback database中的restore point在主备切换时非常好用，Oracle不支持truncate闪回。

综合来说，YashanDB表级闪回支持更全面，Oracle则在库级闪回上有优势。

下面是两个数据库闪回功能支持的详细场景清单：

操作类型	操作类型	操作类型	YashanDB	Oracle	YashanDB关键依赖
TABLE	DML	Insert	支持	支持	UNDO
		delete	支持	支持	UNDO
		update	支持	支持	UNDO
	DDL	truncate	支持	不支持	回收站
		alter table	不支持	不支持	/
		drop table	支持	支持	回收站
		drop table with purge	不支持	不支持	/
DATABASE		database	不支持	支持	/

3. 闪回语法介绍

3.1 YashanDB语法图

YashanDB flashback table支持通过SCN、timestamp、before truncate/drop方式进行闪回，同时，可以通过as of timestamp to_timestamp方式回看表历史数据变化情况，非常Nice。

3.2 Oracle 语法图

3.2.1 Flashback database

Oracle flashback database功能支持将数据库闪回到某一特定时间点。在进行主备切换时，通过创建restore point将数据库整体快照，可快速将数据库恢复到某一时间点，非常Nice!

3.2.2 Flashback table

Oracle flashback table功能支持scn、timestamp、restore point,before drop方式对表进行闪回操作。

4. YashanDB闪回示例

征对Oracle的闪回，大家都耳熟能详，在此不再作演示，下着着重演示下YashanDB闪回的使用。

4.1 前置条件

需要拥有DBA权限，或者闪回相关系统权限。
在当前时间和目标闪回时间之间，表的结构不得发生变化。
表上的行迁移已开启，这表明发生闪回后rowid会发生变化。如果应用程序依赖于rowid，则不能闪回表。
对于被truncate的数据（包括truncate表或truncate分区），必须在truncate之前回收站已开启。在闪回时可能存在表truncate后已经插入了新的数据，需注意这些新数据将被放入回收站中。
对于被drop的表，必须在drop之前回收站已开启。
在当前时间和目标闪回时间之间，未对回收站执行purge。
可以执行闪回表的时间点由撤销保持期（UNDO_RETENTION）决定，建议将该参数设置为86400秒（24小时）或更长。

4.2 注意事项

如在drop表的时候加了purge指令，删除的表不会放入回收站，而是直接释放所占空间，将无法闪回。
当表空间满时，即使不加purge指令，删除的表也不会放到回收站中。
LSC表无闪回功能。

4.3 环境准备

--表级truncate,drop操作依赖项
show parameter RECYCLEBIN_ENABLED
alter system set RECYCLEBIN_ENABLED=on scope=both;
--建用户及赋权
create user muggle identified by "A12345678a";
--连接权限
grant create session to muggle;
--对象权限
grant RESOURCE  to muggle;
--闪回权限
grant FLASHBACK ANY TABLE  to muggle;
--系统视图权限
GRANT SELECT_CATALOG_ROLE TO MUGGLE;
--建表
create table test(id number,name varchar2(40));
--插入数据
insert into test values(1,'muggle');
commit;
--开启表行迁移
alter table muggle.test enable row movement;

4.4 delete基于timestamp闪回示例

delete闪回依赖于undo数据，若需找回更长时间数据，应配置合理的undo空间，undo保留时间。在误删数据后，我们可以通过as of timestamp to_timestamp找到数据误删时间，通过flashback table to timestamp to_timestamp方式进行数据闪回。下面进行闪回演示：

本次演示用到的SQL脚本：

--回收站功能可为关闭
show parameter RECYCLEBIN_ENABLED
--查询当前表数据内容
select * from muggle.test;
--查询系统时间
select sysdate from dual;
--模拟删除数据
delete from muggle.test;
commit;
--通过timestamp确认数据删除时间
select * from muggle.test as of  timestamp to_timestamp('2023-11-23 10:46:04','yyyy-mm-dd hh24:mi:ss');
--闪回数据
flashback table muggle.test to timestamp to_timestamp('2023-11-23 10:46:04','yyyy-mm-dd hh24:mi:ss');
--查看闪回数据内容
select * from muggle.test;

操作日志

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               OFF                                                             

1 row fetched.
SQL> 
SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> select sysdate from dual;

SYSDATE                          
-------------------------------- 
2023-11-23 10:46:04             

1 row fetched.

SQL> delete from muggle.test;

1 row affected.

SQL> commit;

Succeed.

SQL> select * from muggle.test as of  timestamp to_timestamp('2023-11-23 10:46:04','yyyy-mm-dd hh24:mi:ss');

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> flashback table muggle.test to timestamp to_timestamp('2023-11-23 10:46:04','yyyy-mm-dd hh24:mi:ss');

Succeed.

SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

4.5 update基于scn闪回示例

update闪回依赖于undo数据，若需找回更长时间数据，应配置合理的undo空间，undo保留时间。在误修改数据后，我们可以通过as of timestamp/scn找到数据误操作时间，通过flashback table to timestamp/scn方式进行数据闪回。下面进行闪回演示：

本次操作用到的SQL脚本：

--回收站功能可为关闭
show parameter RECYCLEBIN_ENABLED
--查询当前表数据内容
select * from muggle.test;
--查询系统时间
select sysdate from dual;
--查询系统当前SCN
select current_scn from v$database;
--模拟更新数据
update muggle.test set name='scott' ;
commit;
--通过scn确认数据更新时间
select * from muggle.test as of scn 503278933774180352;
--闪回表至数据更新前
flashback table muggle.test to scn 503278933774180352;
--查询闪回后数据
select * from muggle.test;

操作日志：

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               OFF                                                             

1 row fetched.



SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> select sysdate from dual;

SYSDATE                          
-------------------------------- 
2023-11-23 10:50:30             

1 row fetched.

SQL> select current_scn from v$database;

          CURRENT_SCN 
--------------------- 
   503278933774180352

1 row fetched.

SQL> update muggle.test set name='scott' ;

1 row affected.

SQL> commit;

Succeed.

SQL>  select * from muggle.test as of scn 503278933774180352;        

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> flashback table muggle.test to scn 503278933774180352;

Succeed.

SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

4.6 update基于timestamp回看数据示例

在实际工作中，表数据是实时变化的，虽然flashback table可以将数据闪回到历史的某一时刻，但后续的数据变化也就不在了，征对此问题，可以通过flashback query方式查询历史版本数据，然后回写至库表，以避免后续数据丢失。update闪回依赖于undo数据，若需找回更长时间数据，应配置合理的undo空间，undo保留时间。下面进行flashback query闪回演示：

本次操作用到的SQL脚本：

--回收站功能可为关闭
show parameter RECYCLEBIN_ENABLED
--查询当前表数据内容
select * from muggle.test;
--查询系统时间
select sysdate from dual;
--模拟更新数据
update muggle.test set name='scott';
commit;
--模拟写入数据
insert into muggle.test values(2,'tiger');
commit;
--查询表数据
select * from muggle.test;
--通过时间点查询历史数据
select * from muggle.test as of timestamp to_timestamp('2023-11-23 10:31:45','yyyy-mm-dd hh24:mi:ss') ;
--回退更新数据
update muggle.test set name='muggle' where id=1;
commit;
--查看更正后的数据
select * from muggle.test;

操作日志：

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               OFF                                                             

1 row fetched.
SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> select sysdate from dual;

SYSDATE                          
-------------------------------- 
2023-11-23 10:31:45             

1 row fetched.

SQL> update muggle.test set name='scott';

1 row affected.

SQL> commit;

Succeed.

SQL> insert into muggle.test values(2,'tiger');

1 row affected.

SQL> commit;

Succeed.

SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          2 tiger                                    
          1 scott                                    

2 rows fetched.

SQL> select * from muggle.test as of timestamp to_timestamp('2023-11-23 10:31:45','yyyy-mm-dd hh24:mi:ss') ;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> update muggle.test set name='muggle' where id=1;

1 row affected.

SQL> commit;

Succeed.


SQL>  select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          2 tiger                                    
          1 muggle                                   

2 rows fetched.

4.7 truncate闪回示例

YashanDB对表进行truncate时，若回收站（RECYCLEBIN_ENABLED=ON）开启，则会将表放到回收站，要对truncate的表进行闪回，可参照以下步骤进行操作：

本次用到的SQL脚本

--查看回收站功能是否开启
show parameter RECYCLEBIN_ENABLED
--开启回收站功能
alter system set RECYCLEBIN_ENABLED=off scope=both;
--查看回收站功能是否开启
show parameter RECYCLEBIN_ENABLED
--查看表数据
select * from muggle.test;
--查看系统时间
select sysdate from dual;
--查看数据库scn
select current_scn from v$database;
--truncate表数据
truncate table muggle.test;
--查询回收站对象情况
SELECT OWNER,OBJECT_NAME,OPERATION,RECYCLEBIN_TIME,RECYCLEBIN_SCN  FROM USER_RECYCLEBIN  WHERE object_name='TEST';
--通过时间点回看删除前数据
select * from muggle.test as of timestamp to_timestamp('2023-11-23 11:09:55','yyyy-mm-dd hh24:mi:ss'); 
--查看对象最后一次ddl时间
select object_name,created,last_ddl_time from user_objects where object_name='TEST';
--闪回表数据
flashback table muggle.test to before truncate;
--查看闪回后表数据
select * from  muggle.test ;

操作日志：

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               OFF                                                             

1 row fetched.

SQL> alter system set RECYCLEBIN_ENABLED=on scope=both;

Succeed.

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               ON                                                              

1 row fetched.

SQL> 

SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL>  select sysdate from dual;

SYSDATE                          
-------------------------------- 
2023-11-23 11:09:55             

1 row fetched.

SQL> select current_scn from v$database;

          CURRENT_SCN 
--------------------- 
   503283871265443840

1 row fetched.

SQL> truncate table muggle.test;

Succeed.

SQL> SELECT OWNER,OBJECT_NAME,OPERATION,RECYCLEBIN_TIME,RECYCLEBIN_SCN  FROM USER_RECYCLEBIN  WHERE object_name='TEST';

OWNER                                                            OBJECT_NAME                                                      OPERATION     RECYCLEBIN_TIME                                                         RECYCLEBIN_SCN 
---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- --------------------- 
MUGGLE                                                           TEST                                                             TRUNCATE      2023-11-23:11:07:18                                                 503283871265443840

1 row fetched.

SQL>  select * from muggle.test as of timestamp to_timestamp('2023-11-23 11:09:55','yyyy-mm-dd hh24:mi:ss'); 

         ID NAME                                      
----------- ----------------------------------------- 

0 rows fetched.

SQL> select object_name,created,last_ddl_time from user_objects where object_name='TEST';

OBJECT_NAME                                                      CREATED                          LAST_DDL_TIME                    
---------------------------------------------------------------- -------------------------------- -------------------------------- 
TEST                                                             2023-11-23 11:01:22              2023-11-23 11:10:05             

1 row fetched.

SQL> flashback table muggle.test to before truncate;

Succeed.

SQL> select * from  muggle.test ;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL>

4.8 drop闪回示例

YashanDB对表进行drop时，若回收站（RECYCLEBIN_ENABLED=ON）开启，且drop table未添加purge关键定，则会将表放到回收站，要对drop的表进行闪回，可参照以下步骤进行操作：

本次操作用到的SQL脚本：

--查看回收站功能是否开启
show parameter RECYCLEBIN_ENABLED
--开启回收站功能
alter system set RECYCLEBIN_ENABLED=on scope=both;
--查看回收站功能是否开启
show parameter RECYCLEBIN_ENABLED
--查看表数据
select * from muggle.test;
--查看系统时间
select sysdate from dual;
--查看数据库scn
select current_scn from v$database;
--drop表
drop table muggle.test;
--查询回收站对象情况
SELECT OWNER,OBJECT_NAME,ORIGINAL_NAME,RECYCLEBIN_TIME,RECYCLEBIN_SCN  FROM USER_RECYCLEBIN where ORIGINAL_NAME='TEST';
--通过时间点回看drop表数据
select * from muggle.test as of timestamp to_timestamp('2023-11-23 11:12:21','yyyy-mm-dd hh24:mi:ss'); 
--闪回表数据
flashback table muggle.test  to before drop rename to test_old;
--查看闪回后表数据
select * from muggle.test_old;

操作日志：

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               OFF                                                             

1 row fetched.

SQL> alter system set RECYCLEBIN_ENABLED=on scope=both;

Succeed.

SQL> show parameter RECYCLEBIN_ENABLED

NAME                                                             VALUE                                                            
---------------------------------------------------------------- ---------------------------------------------------------------- 
RECYCLEBIN_ENABLED                                               ON                                                              

1 row fetched.

SQL> 
SQL> select * from muggle.test;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

SQL> select sysdate from dual;

SYSDATE                          
-------------------------------- 
2023-11-23 11:12:21             

1 row fetched.

SQL> select current_scn from v$database;

          CURRENT_SCN 
--------------------- 
   503284783173111808

1 row fetched.

SQL> drop table muggle.test;

Succeed.


SQL> SELECT OWNER,OBJECT_NAME,ORIGINAL_NAME,RECYCLEBIN_TIME,RECYCLEBIN_SCN  FROM USER_RECYCLEBIN where ORIGINAL_NAME='TEST';

OWNER                                                            OBJECT_NAME                                                      ORIGINAL_NAME                                                    RECYCLEBIN_TIME                                                         RECYCLEBIN_SCN 
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- --------------------- 
MUGGLE                                                           BIN$2306                                                         TEST                                                             2023-11-23:11:11:01                                                 503284783173111808

1 row fetched.

SQL> select * from muggle.test as of timestamp to_timestamp('2023-11-23 11:12:21','yyyy-mm-dd hh24:mi:ss'); 

[1:15]YAS-02012 table or view does not exist

SQL> flashback table muggle.test  to before drop rename to test_old;

Succeed.

SQL> select * from muggle.test_old;

         ID NAME                                      
----------- ----------------------------------------- 
          1 muggle                                   

1 row fetched.

5.总结

总体上来说，YashanDB与Oracle的闪回技术都非常好用，都是运维过程中常用的功能。

Oracle的闪回技术功能全面，尤其flashback database在主备切换、应用发布、应急演练等场景都非常有用。flashback table中甚至支持flashback query version查询，相当的强大。遗憾的是，Oracle不支持表级truncate闪回，恢复truncate数据需依赖备份集，非常麻烦。期待Oracle能尽早支持表级truncate闪回。

YashanDB支持表级truncate闪回，这为数据库开发/运维人员带来了巨大的方便，只要在数据库中开启回收站（RECYCLEBIN_ENABLED=ON），并将UNDO_RETENTION=86400(24小时)进行合理设置，BOSS再也不担心宝宝们误删数据了。通过测试发现：YashanDB暂不支持flashback database功能，期待尽早补齐。

最后修改时间：2023-11-30 13:28:20

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Muggle's DBALife（共18篇）

Muggle's DBALife,DBA生活每一天！