目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、Oracle闪回详解，点击前往
2、Oracle闪回技术详解，点击前往
3、参考书籍：《涂抹Oracle 三思笔记之一步一步学Oracle》
4、参考书籍：《Oracle Database 11g数据库管理艺术》
5、Flashback Transaction Backout，点击前往
6、关于oracle闪回数据归档的总结，点击前往
7、参考书籍：《Oracle Database 9i10g11g编程艺术深入数据库体系结构(第2版)》
Oracle数据库闪回技术
文章快速说明索引
Oracle闪回的概述
Oracle闪回的使用
闪回查询
闪回表(insert delete update)
闪回删除
闪回数据库(truncate/多表数据变更)
闪回数据归档
文章快速说明索引
学习目标：
目的：因为接下来想在PostgreSQL上实践实现一下 闪回操作 ，但是就目前而言 PostgreSQL尚不支持此功能。德哥的git和其他PostgreSQL社区爱好者也曾分享过一些 类似的 闪回简易实现，大家有兴趣也可以去看看！本文主要记录在Oracle数据库上面的闪回功能的使用体验和基于开发者的设计思考，以期在PostgreSQL数据库上面支持此功能！
学习内容：（详见目录）
1、Oracle数据库的闪回技术
学习时间：
2020年9月16日03:36:25 - 2020年9月27日23:33:51
学习产出：
1、Oracle数据库闪回技术学习
2、CSDN 技术博客 1篇
3、PostgreSQL数据库闪回功能实现设计思考
Oracle闪回的概述
闪回概述
Flashback 即 数据库闪回操作，它是Oracle自9i版本才开始提供的一项新特性，在Oracle 10g中对功能进行了增强。在10g之前只提供了 闪回查询 的功能，进入10g之后又提供了对事务、对表的恢复，以及真正具有恢复意味的闪回数据库的功能。在Oracle11g之后这些功能日趋更加完善。闪回技术是Oracle强大数据库备份恢复机制的一部分，在数据库发生逻辑错误的时候，闪回技术能提供快速且最小损失的恢复（多数闪回功能都能在数据库联机状态下完成）。需要注意的是，闪回技术旨在快速恢复逻辑错误，对于物理损坏或是介质丢失的错误，闪回技术就回天乏术了，还是得借助于Oracle一些高级的备份恢复工具如RAMN去完成。
闪回特性的优点在于语法简单、操作方便和功能强大，其作用在于快速恢复数据和查询历史数据。在Oracle 10g中的闪回功能可以分成以下三个方面：
闪回查询(Flashback Query) ：通过查询UNDO段， 能够重现操作之前的数据
闪回表(Flashback Table) ：该特性与10g中新推出的另外一项新增特性Recycle Bin(回收站) 对应， 默认情况下表对象及其关联的索引等对象在DROP后并没有物理删除， 而是标记为删除(类似在Windows中删除文件时， 文件移向“回收站”的概念)，如果你想对这类表进行恢复，只需要简单的命令即可，而且该操作只修改数据字典，不管要恢复的对象占用多大空间，恢复效率极高
闪回数据库(Flashback Database) ：该功能十分强大， 真正实现了不需要备份的恢复(严格地讲还是有备份， 只不过这个备份操作不由DBA做， 而是Oracle自动进行)
详细内容展开如下表所示：（基于Oracle 11g的闪回技术）
闪回技术 闪回级别 场景描述 功能描述 对象依赖 是否影响数据
Flashback Database Database 表截断、逻辑错误、其他多表意外事件 将整个数据库倒退到一个特定的时刻 闪回日志、undo log YES
Flashback Table Table 更新、删除、插入记录 将表返回到过去的一个状态 还原数据、undo log YES
Flashback DROP Drop 删除表 撤销DROP TABLE命令并恢复被删除的表 recyclebin YES
Flashback QUERY Query 当前数据和历史数据对比 检索过去某一时刻(或时间间隔)的数据 还原数据、undo log NO
Flashback Version Query Version Query 比较行版本 同上 同上 NO
Flashback Transaction Query Transaction Query 比较事务 同上 同上 NO
Flashback Transaction Backout Transaction Backout 撤销事务 在Database Control中单击一下， 撤销一个事务以及它依赖的所有事务 undo log YES
Flashback Data Archive Archive DDL、DML 存储对一个表所做更改的历史，可用它来构造旧版本数据的查询和用于审计用途 归档日志 YES
下面来看一下闪回功能的启停：
主要操作步骤如下：
1、数据库处于归档模式
2、开启归档日志
3、设置合理的闪回区
4、开启flashback并检查：alter database flashback on/off;
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2213736 bytes Variable Size 1090521240 bytes Database Buffers 486539264 bytes Redo Buffers 7434240 bytes Database mounted. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 54 Current log sequence 56 SQL> alter database archivelog; # 注意看下面的 Enabled 和 上面的 Disabled Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 54 Next log sequence to archive 56 Current log sequence 56 SQL> alter system set db_recovery_file_dest='/home/oracle/app/flash_recovery_area' scope=both; System altered. SQL> alter system set db_recovery_file_dest_size=60G scope=both; System altered. SQL> alter system set db_flashback_retention_target=4320 scope=both; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 54 Next log sequence to archive 56 Current log sequence 56 SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL>
如上，我们成功开启了数据库的闪回功能，下面我们开始准备闪回所需的数据：
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-26 10:19:21 SQL> SQL> create table test1 (id int,name varchar(16),curtime TIMESTAMP); Table created. SQL> insert into test1 values (1,'post',sysdate); 1 row created. SQL> insert into test1 values (2,'orac',sysdate); 1 row created. SQL> insert into test1 values (3,'mysq',sysdate); 1 row created. SQL> insert into test1 values (4,'redi',sysdate); 1 row created. SQL> SQL> set linesize 800 SQL> set pagesize 900 SQL> select * from test1; ID NAME CURTIME ---------- ---------------- ---------------------------- 1 post 26-SEP-20 10.20.07.000000 AM 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL>
Oracle闪回的使用
闪回使用
闪回查询
闪回查询操作：允许用户查询过去某个时间点的数据，用以重构由于意外删除或更改的数据，但是表中的数据不会变化。于是我们就可以查询过去某个时间点的数据库状态和表中当时的数据。其工作原理为：Oracle 会提取所需要的撤销数据（前提是撤销是可用的，即撤销数据还没被覆盖）进行回滚，但这种回滚是临时的，仅针对当前session可见。
SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.20.07.000000 AM 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL> commit; Commit complete. SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-26 10:22:29 SQL> delete from test1 where id = 1; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL> SQL> select * from test1 as of timestamp to_timestamp('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss'); ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.20.07.000000 AM 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL>
如上一条被删除的数据就被我们查回来了，但是建议不要使用时间 而是scn：
SQL> select timestamp_to_scn(to_date('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss')) from dual; TIMESTAMP_TO_SCN(TO_DATE('2020-09-2610:22:29','YYYY-MM-DDHH24:MI:SS')) ---------------------------------------------------------------------- 2254386 SQL> SQL> select * from test1 as of scn 2254386; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.20.07.000000 AM 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL> select * from test1 ; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL>
如上，这个闪回查询 顾名思义，没有改变现表状态的功能 我们可以很清楚的看到数据不会变化。
闪回表(insert delete update)
闪回表操作：闪回表就是对表的数据做回退，回退到之前的某个时间点（也即：可将某个表回退到过去某个时间点的状态和数据内容）。其工作原理为：和上面一样Oracle会先去查询撤销段，提取过去某个时间点之后的所有变更，构*转这些变更的SQL语句进行回退。闪回操作是一个单独的事务，所以若由于撤销数据过期之类的原因导致无法闪回，整个操作会回滚，不会存在不一致的状态。它这里利用的也是undo的历史数据，与undo_retention设置有关，默认是1440分钟（1天）。但是sys用户表空间不支持闪回表，示例如下：
SQL> flashback table test1 to timestamp to_timestamp('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss'); flashback table test1 to timestamp to_timestamp('2020-09-26 10:22:29','yyyy-mm-dd hh24:mi:ss') * ERROR at line 1: ORA-08185: Flashback not supported for user SYS SQL>
OK，我们换一个用户开始同样的操作：
SQL> CREATE user song identified by sys; User created. SQL> show user USER is "SYS" SQL> alter user song identified by 123456; User altered. SQL> grant connect,resource,dba to song; Grant succeeded. SQL> show user USER is "SYS" SQL> connect song/123456 Connected. SQL>
然后我们在其他用户下面执行这个闪回表操作（需要注意的是：要想表闪回，需要允许表启动行迁移(row movement)
），实例如下：
SQL> show user USER is "SONG" SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-26 10:53:01 SQL> create table test1 (id int,name varchar(16),curtime TIMESTAMP); Table created. SQL> insert into test1 values (1,'post',sysdate); 1 row created. SQL> insert into test1 values (2,'orac',sysdate); 1 row created. SQL> insert into test1 values (3,'mysq',sysdate); 1 row created. SQL> insert into test1 values (4,'redi',sysdate); 1 row created. SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL> commit; Commit complete. SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-26 10:54:10 SQL> delete from test1 where id = 1; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test1; ID NAME CURTIME ---------- ---------------- ---------------------- 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL> select * from test1 as of timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss'); ID NAME CURTIME ---------- ---------------- ---------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL> flashback table test1 to timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss'); flashback table test1 to timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss') * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled SQL> select row_movement from dba_tables where table_name='test1' and owner='song'; no rows selected SQL> alter table test1 enable row movement; Table altered. SQL> flashback table test1 to timestamp to_timestamp('2020-09-26 10:54:10','yyyy-mm-dd hh24:mi:ss'); Flashback complete. SQL> select * from test1; ID NAME CURTIME ---------- ---------------- ---------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL> alter table test1 disable row movement; Table altered. SQL>
如上开启row movement，因为启用表闪回首先要在表上支持行移动（在数据字典中设置标识来标识该操作可能会改变行ID，即同一条数据闪回成功后主键都一样，但行ID其实已经发生变化了）。
闪回表可能会失败，有可能有以下几种情况：
违反了数据库约束：比如用户不小心删除了子表中的数据，现在想利用闪回表技术进行回退，恰好在这中间，父表中与该数据对应的那条记录也被删除了，在这种情况下，由于违反了外键约束，导致闪回表操作失败了
撤销数据失效：比如用于支撑闪回操作的撤销数据被覆盖了，这种情况闪回表操作自然会失败
闪回不能跨越DDL：在闪回点和当前点之间，表结构有过变更，这种情况闪回操作也会失败
注：上述闪回功能都是基于撤销数据（undo log）的，而撤销数据是会被重写的（Expired会被重写，Active不会被重写），所以在需要使用上面闪回功能去恢复数据的时候（确切地说，是需要使用基于撤销数据的闪回功能时），最短时间发现错误，第一时间执行闪回操作，才能最大程度地保证闪回功能的成功。
闪回删除
在Oracle中，当一个表被drop掉，表会被放入recyclebin 即：回收站。于是我们这里的drop闪回就可以通过回收站做表的闪回。在闪回过程中 表上的索引、约束等同样会被恢复，同样这里也不支持sys/system用户表空间对象
而回收站功能的开启和关闭是可通过alter system set recyclebin=off; alter system set recyclebin=on scope=spfile;来完成（默认是开启的）示例如下：
SQL> alter database flashback off; Database altered. SQL> show recyclebin; # 看一眼回收站 ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST1 BIN$sC+Fb/6hJ+3gUAB/AQATZg==$0 TABLE 2020-09-26:10:52:17 SQL> purge recyclebin; # 清空回收站 不影响下面的操作 Recyclebin purged. SQL> show recyclebin; # 回收站为空 SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL> drop table test1; Table dropped. SQL> show recyclebin; # 表被放到回收站了 ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST1 BIN$sC+Fb/6iJ+3gUAB/AQATZg==$0 TABLE 2020-09-26:12:24:15 SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-26 12:24:45 SQL> flashback table TEST1 to before drop; Flashback complete. SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL>
如上我们使用purge recyclebin;来清理当前的回收站。这里我们注意一点：在最开始的时候 我可是把flashback关闭了的，但是只要开启了recyclebin，那么就可以闪回DROP表。
但如果连续覆盖，就需要指定恢复的表名，如果已经存在表，则需要恢复重命名。
SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL> drop table test1; Table dropped. SQL> create table test1 (id int,mytime timestamp); Table created. SQL> insert into test1 values (1,sysdate); 1 row created. SQL> drop table test1; Table dropped. SQL> show recyclebin; # 这个时候回收站里面有两个不一样的同名表 ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST1 BIN$sC+Fb/6kJ+3gUAB/AQATZg==$0 TABLE 2020-09-26:12:31:23 TEST1 BIN$sC+Fb/6jJ+3gUAB/AQATZg==$0 TABLE 2020-09-26:12:30:18 SQL> flashback table "BIN$sC+Fb/6kJ+3gUAB/AQATZg==$0" to before drop ; # 这样可以恢复指定那个 Flashback complete. SQL> select * from test1; ID MYTIME ---------- --------------------------------------------------------------------------- 1 26-SEP-20 12.31.17.000000 PM SQL> flashback table test1 to before drop rename to another_test1; # 恢复的时候 同时做了个重命名 Flashback complete. SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 26-SEP-20 10.53.20.000000 AM 2 orac 26-SEP-20 10.53.28.000000 AM 3 mysq 26-SEP-20 10.53.36.000000 AM 4 redi 26-SEP-20 10.53.45.000000 AM SQL>
闪回数据库(truncate/多表数据变更)
数据库闪回功能可以基于两种方式来完成：1、数据库闪回必须在mounted状态下进行 ； 2、基于快照的可以在open下进行闪回库 （要求数据库为归档模式）。
闪回数据库主要是将数据库还原到过去的某个时间点或SCN，用于数据库出现逻辑错误时，需要open database resetlogs。当然，闪回点之后的所有工作就丢失了，其实就相当于数据库的不完整恢复，所以只能以resetlogs模式打开数据库。闪回数据库会造成停机时间，当然相比于传统备份恢复机制，恢复过程会快很多。其工作原理为：闪回数据库不使用撤销数据（undo log），使用另外一种机制来保留回退所需要的恢复数据。当启用闪回数据库时，发生变化的数据块会不断从数据库缓冲区缓存中复制到闪回缓冲区，然后被称为恢复写入器（Recovery Writer）的后台进程会将这些数据刷新到磁盘中的闪回日志文件中。闪回的过程：是一个 提取闪回日志 到 将块映像复制回数据文件 的过程。
虽然在上面我们已经配置过闪回数据库的一些属性，但是这个很重要，下面再来详细看一下闪回数据库功能的配置：
主要操作步骤如下：
1、数据库处于归档模式 startup mount
2、开启归档日志 alter database archivelog;
3、设置合理的闪回区 指定闪回恢复区 指定恢复区大小 指定闪回日志保存时间
指定闪回恢复区，也就是存放闪回日志的位置，但闪回恢复区不仅仅是为了存放闪回日志。Oracle的很多备份恢复技术都用到这个区域，比如控制文件的自动备份等都会存放到此区域
alter system set db_recovery_file_dest='/home/oracle/app/flash_recovery_area' scope=both;
指定恢复区大小
alter system set db_recovery_file_dest_size=60G scope=both;
指定闪回日志保留时间为4320 / 60 = 72小时，即通过闪回操作，可以将数据库回退到前72小时内的任意时间点
alter system set db_flashback_retention_target=4320 scope=both;
4、开启flashback并检查：alter database flashback on/off;
下面是详细的SQL演示：
SQL> shutdown immediate # 因为当前用户是song 不是sys 没有权限 ORA-01031: insufficient privileges SQL> conn /as sysdba # 连接 用sys用户 Connected. SQL> shutdown immediate # 关闭数据库 Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount # 开启mounted状态 ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2213736 bytes Variable Size 1090521240 bytes Database Buffers 486539264 bytes Redo Buffers 7434240 bytes Database mounted. SQL> archive log list; # 查看归档日志参数 Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 58 Next log sequence to archive 60 Current log sequence 60 SQL> alter database archivelog; # 开启归档日志 Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 58 Next log sequence to archive 60 Current log sequence 60 SQL> select flashback_on from v$database; # 查看闪回是否开启 FLASHBACK_ON ------------------ NO SQL> # 下面是配置闪回属性 SQL> alter system set db_recovery_file_dest='/home/oracle/app/flash_recovery_area' scope=both; System altered. SQL> alter system set db_recovery_file_dest_size=60G scope=both; System altered. SQL> alter system set db_flashback_retention_target=4320 scope=both; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 58 Next log sequence to archive 60 Current log sequence 60 SQL> alter database flashback on; # 开启闪回 Database altered. SQL> select flashback_on from v$database; # 查看 是开启的 FLASHBACK_ON ------------------ YES SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; # 设置时间格式 Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-27 10:55:26 SQL> select * from test1; select * from test1 * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only SQL> alter database open; # 打开数据库 Database altered. SQL> select * from test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 2 orac 26-SEP-20 10.20.15.000000 AM 3 mysq 26-SEP-20 10.20.23.000000 AM 4 redi 26-SEP-20 10.20.32.000000 AM SQL> connect song/123456 # 使用自定义用户连接 Connected. SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM SQL>
如下是闪回数据库的实例：
SQL> connect /as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2213736 bytes Variable Size 1090521240 bytes Database Buffers 486539264 bytes Redo Buffers 7434240 bytes Database mounted. Database opened. SQL> connect song/123456 Connected. SQL> select table_name from dba_tables where owner = 'SONG'; TABLE_NAME ------------------------------ SHANHUI SYS_TEMP_FBT ANOTHER_TEST1 SQL> select * from ANOTHER_TEST1; ID NAME ---------- ---------------- CURTIME --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM ID NAME ---------- ---------------- CURTIME --------------------------------------------------------------------------- 4 redi 27-SEP-20 02.06.35.000000 PM SQL> set linesize 800 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------- 2020-09-27 14:29:26 SQL> truncate table ANOTHER_TEST1; Table truncated. SQL> select * from ANOTHER_TEST1; no rows selected SQL> commit; Commit complete. SQL> connect /as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2213736 bytes Variable Size 1090521240 bytes Database Buffers 486539264 bytes Redo Buffers 7434240 bytes Database mounted. SQL> flashback database to timestamp to_timestamp('2020-09-27 14:29:26','yyyy-mm-dd HH24:MI:SS'); Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> conn song/123456 Connected. SQL> select * from ANOTHER_TEST1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM SQL> show user USER is "SONG" SQL>
如上，在闪回完成之后，还需要注意的是：闪回数据库主要是将数据库还原值过去的某个时间点或SCN，用于数据库出现逻辑错误时，千万不能忘记open database resetlogs。
在上面我们也曾说过：闪回数据库的功能 也是 可以基于快照来实现。即：创建闪回快照点，然后恢复到指定的快照点。示例如下：
SQL> select * from ANOTHER_TEST1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM SQL> create restore point myfirstpoint guarantee flashback database; create restore point myfirstpoint guarantee flashback database * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn /as sysdba Connected. SQL> create restore point myfirstpoint guarantee flashback database; # 创建闪回快照点 Restore point created. SQL> conn song/123456 Connected. SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM SQL> truncate table another_test1; Table truncated. SQL> commit; Commit complete. SQL> conn /as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1586708480 bytes Fixed Size 2213736 bytes Variable Size 1090521240 bytes Database Buffers 486539264 bytes Redo Buffers 7434240 bytes Database mounted. SQL> flashback database to restore point myfirstpoint; # 基于快照点，开始闪回 Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> show user USER is "SYS" SQL> conn song/123456 Connected. SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> archive log list; ORA-01031: insufficient privileges SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SQL> SQL> select NAME,OPEN_MODE ,DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ON from v$database; NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FLASHBACK_ON --------- -------------------- ---------------- ----------- ------------------ ORCL READ WRITE PRIMARY 2364180 YES SQL> show user USER is "SYS" SQL> select to_char(systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt , dbms_flashback.get_system_change_number scn from dual; SYSDT SCN ------------------- ---------- 2020-09-27 15:52:26 2364190 SQL> select * from V$FLASHBACK_DATABASE_LOG; # 查看数据库可恢复的时间点 OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------- ---------------- -------------- ------------------------ 2328783 27-SEP-20 4320 31883264 388988928 SQL>
闪回数据归档
闪回数据归档：使得表具有回退到过去任何时间点的能力(前面提到的闪回查询、闪回表都会受限于撤销数据是否失效，如果撤销数据被覆盖重写了，闪回操作自然会失败；闪回删除则受限于表空间是否有足够可用空间) 而闪回数据归档，则没有这些限制。
该功能实现的步骤如下：
1、创建一个用户闪回数据归档的表空间
2、创建一个保留时间为一定时间的闪回归档
3、为某一个表启用闪回归档
示例如下：
SQL> show user USER is "SYS" SQL> conn song/123456 Connected. SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM # 创建一个用户闪回数据归档的表空间 SQL> create tablespace another_test1 datafile 'test1.dbf' size 20m; Tablespace created. # 创建一个保留时间为一年时间的闪回归档 SQL> create flashback archive test1_flahback_archive tablespace another_test1 retention 1 year; Flashback archive created. # 赋予用户归档的权限 SQL> grant flashback archive on test1_flahback_archive to song; Grant succeeded. # 为 another_test1 表启用闪回归档 SQL> alter table another_test1 flashback archive test1_flahback_archive; Table altered. SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM # 查哪些表已经启用了闪回数据归档 SQL> select table_name from dba_flashback_archive_tables; TABLE_NAME ------------------------------ ANOTHER_TEST1 SQL> set line 300 SQL> col FLASHBACK_ARCHIVE_NAME for a50 SQL> col tablespace_name for a50 SQL> col quota_in_mb for a50 # 查看有关闪回数据归档所使用的表空间的信息 SQL> select flashback_archive_name,tablespace_name,quota_in_mb from dba_flashback_archive_ts; FLASHBACK_ARCHIVE_NAME TABLESPACE_NAME QUOTA_IN_MB -------------------------------------------------- ------------------------------------------- TEST1_FLAHBACK_ARCHIVE ANOTHER_TEST1 SQL> # 查询数据库中所有的闪回数据归档 SQL> select flashback_archive_name,retention_in_days from dba_flashback_archive; FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS -------------------------------------------------- ----------------- TEST1_FLAHBACK_ARCHIVE 365 SQL>
取消对于数据表的闪回归档可以使用如下命令：
alter table table_name no flashback archive;下面来实际操作一下闪回数据归档 首先记录一下SCN，从数据库表中删除部分数据：
原表数据内容如下：
SQL> select * from another_test1; ID NAME CURTIME ---------- ---------------- --------------------------------------------------------------------------- 1 post 27-SEP-20 02.05.11.000000 PM 2 orac 27-SEP-20 02.06.02.000000 PM 3 mysq 27-SEP-20 02.06.02.000000 PM 4 redi 27-SEP-20 02.06.35.000000 PM SQL>
通过闪回查询如下：
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 2367563 SQL> delete from another_test1 where id <= 2; 2 rows deleted. SQL> commit; Commit complete. SQL> select count(*) from another_test1 as of scn 2367563; COUNT(*) ---------- 4 SQL>
其查询计划如下：
SQL> explain plan for select count(*) from another_test1 as of scn 2367563; Explained. SQL> select count(*) from another_test1 as of scn 2367563; COUNT(*) ---------- 4 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------- Plan hash value: 3878810653 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ANOTHER_TEST1 | 4 | 6 (0)| 00:00:01 | ---------------------------------------------------------------------------- 9 rows selected. SQL>
接下来执行一段代码，使UNDO数据老化并覆盖。之后再来看一下闪回数据归档发挥作用的闪回查询，通过执行计划能够看到和之前查询执行方式的不同：
SQL> begin delete from another_test1 where rownum < 2; commit; end; / 2 3 4 5 PL/SQL procedure successfully completed. SQL> select * from another_test1; ID NAME CURTIME ---------- -------------------------------------------------- -------------------------------------------- 4 redi 27-SEP-20 02.06.35.000000 PM SQL> select count(*) from another_test1 as of scn 2367563; COUNT(*) ---------- 4 SQL> explain plan for select count(*) from another_test1 as of scn 2367563; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- Plan hash value: 1720804579 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 13 (8)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | VIEW | | 3 | | 13 (8)| 00:00:01 | | | | 3 | UNION-ALL | | | | | | | | | 4 | PARTITION RANGE SINGLE| | 2 | 52 | 3 (0)| 00:00:01 | 1 | 1 | |* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_75607 | 2 | 52 | 3 (0)| 00:00:01 | 1 | 1 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- |* 6 | FILTER | | | | | | | | |* 7 | HASH JOIN OUTER | | 1 | 2040 | 10 (10)| 00:00:01 | | | |* 8 | TABLE ACCESS FULL | ANOTHER_TEST1 | 1 | 12 | 6 (0)| 00:00:01 | | | |* 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_75607 | 2 | 4056 | 3 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("ENDSCN">2367563 AND "ENDSCN"<=2367777 AND ("STARTSCN" IS NULL OR "STARTSCN"<=2367563)) 6 - filter("STARTSCN"<=2367563 OR "STARTSCN" IS NULL) PLAN_TABLE_OUTPUT ---------------------------------------------------------- 7 - access("T".ROWID=CHARTOROWID("RID"(+))) 8 - filter("T"."VERSIONS_STARTSCN" IS NULL) 9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>2367777) AND ("STARTSCN"(+) IS NULL OR "STARTSCN"(+)<2367777)) Note ----- - dynamic sampling used for this statement (level=2) 30 rows selected. SQL>
通过以上执行计划可以看到，查询闪回来自SYS_FBA_TCRV_75607系统表，该表隶属于闪回归档表空间，用于记录闪回数据：
SQL> set linesize 100 SQL> desc SYS_FBA_TCRV_75607 Name Null? Type ----------------------------------------------------- -------- ------------------------------------ RID VARCHAR2(4000) STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OP VARCHAR2(1) SQL> select count(*) from SYS_FBA_TCRV_75607; COUNT(*) ---------- 3 SQL> select * from SYS_FBA_TCRV_75607; RID STARTSCN ENDSCN XID O -------------------- ---------- ---------- ---------------- - AAASe9AAEAAAAI/AAA 2367600 06000C0004060000 AAASe9AAEAAAAI/AAB 2367600 06000C0004060000 AAASe9AAEAAAAI/AAC 2368015 08001F0001060000 SQL>
闪回功能生成的字典对象有多个，通过查询USER_TABLES / USER_OBJECTS视图可以获得这些对象的详细信息：
SQL> select table_name,tablespace_name from user_tables where table_name like '%FBA%'; TABLE_NAME TABLESPACE_NAME ------------------------------ -------------------------------------------------- SYS_FBA_DDL_COLMAP_75607 ANOTHER_TEST1 SYS_FBA_TCRV_75607 ANOTHER_TEST1 SYS_FBA_HIST_75607 SQL> select object_name,object_type from user_objects where object_name like '%FBA%'; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- SYS_FBA_TCRV_IDX_75607 INDEX SYS_FBA_TCRV_75607 TABLE SYS_FBA_HIST_75607 TABLE PARTITION SYS_FBA_HIST_75607 TABLE SYS_FBA_DDL_COLMAP_75607 TABLE SQL>
还可以通过数据字典视图来查看关于闪回归档表的记录：
SQL> select * from user_flashback_archive_tables; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS ------------------------------------------------------------ ---------------------------------------------- --------- ANOTHER_TEST1 SONG TEST1_FLAHBACK_ARCHIVE SYS_FBA_HIST_75607 ENABLED SQL> # 可以通过dict字典查询和闪回归档有关的数据字典表 SQL> select table_name from dict where table_name like '%FLASHBACK_ARCHIVE%'; TABLE_NAME ------------------------------ DBA_FLASHBACK_ARCHIVE DBA_FLASHBACK_ARCHIVE_TABLES DBA_FLASHBACK_ARCHIVE_TS USER_FLASHBACK_ARCHIVE USER_FLASHBACK_ARCHIVE_TABLES SQL>
本文小结：
闪回查询 包括基本闪回查询，闪回表等技术都依赖于撤销数据（还有一类闪回技术为闪回事务，可以对指定事务进行闪回操作，原理类似，借助于撤销数据来构建用于反转事务的SQL语句），依赖于撤销数据，则自然受限于撤销数据的保留时间，可能会由于撤销数据被覆写而导致闪回失败
闪回删除 则是由于10g版本后对表的删除仅表现为一个rename操作，引入回收站的概念，但此回收站仅是当前表空间的一块逻辑划分，所以会受限于当前表空间的可用空间的限制
闪回归档 可提供查询或回退到过去任意时间点的功能
闪回数据库 则是一中更极端的数据库恢复功能，相当于不完整恢复，依赖于闪回日志