合 Oracle闪回（flashback）功能详解
Tags： Oracle闪回闪回数据库闪回查询闪回事务查询闪回删除闪回数据归档闪回版本查询闪回表
👉 本文共约5013个字，系统预计阅读时间或需19分钟。
目录
什么是闪回？闪回有哪些分类？
Oracle闪回技术从根本上改变了数据恢复策略，利用闪回技术，可以使更正错误的时间大大缩短，而且它简单易用，使用一条短命令便可恢复整个数据库，而不必执行复杂的程序。闪回技术是Oracle数据库独有的特性，支持各级恢复，包括行、事务、表、表空间和数据库范围。采用闪回技术，可以针对行级和事务级发生过变化的数据进行恢复，减少了数据恢复的时间，而且操作简单，通过SQL语句就可以实现数据的恢复，大大提高了数据库恢复的效率。
Oracle中闪回技术分类图如下所示：
闪回技术分类：
（1）闪回查询（Flashback Query）：查询过去某个时间点或某个SCN值对应的表中的数据信息，即从回滚段中读取一定时间内对表进行操作的数据，恢复错误的DML操作。
（2）闪回版本查询（Flashback Version Query）：查询过去某个时间段或某个SCN段内表中数据的变化情况。
（3）闪回事务查询（Flashback Transaction Query）：查看某个事务或所有事务在过去一段时间对数据进行的修改。
（4）闪回表（Flashback TABLE）：将表恢复到过去的某个时间点或某个SCN值时的状态。
（5）闪回删除（Flashback DROP）：将已经删除的表及表上的索引恢复到删除前的状态，但索引名不会恢复到删除前的状态。
（6）闪回数据库（Flashback Database）：将数据库恢复到过去某个时间点或某个SCN值时的状态。
有关闪回需要注意以下几点：
（1）闪回查询、闪回版本查询、闪回事务查询以及闪回表主要是基于回滚（Undo）表空间中的回滚信息实现的。
（2）闪回删除是基于Oracle中的回收站（Recycle Bin）特性实现的。
（3）闪回数据库是基于闪回恢复区（Flash Recovery Area）中的闪回日志来实现的。
（4）闪回数据归档是基于闪回归档区中的数据来实现的。
闪回查询（Flashback Query）
闪回查询分为基于AS OF TIMESTAMP和基于AS OF SCN的闪回查询，如下所示：
1 2 3 4 | SELECT * FROM SCOTT.EMP AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '6' MINUTE) WHERE ENAME = 'LHR'; SELECT ENAME, SAL FROM SCOTT.EMP AS OF TIMESTAMP(SYSDATE - 6 / 1440) WHERE ENAME = 'LHR'; SELECT * FROM SCOTT.EMP AS OF TIMESTAMP TO_TIMESTAMP('2017-11-14 11:41:22','YYYY-MM-DD HH24:MI:SS') WHERE WHERE ENAME = 'LHR'; INSERT INTO SCOTT.EMP select * from t AS OF SCN 67830969; |
闪回查询示例：
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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | SQL> set timing on; SQL> set serveroutput on; SQL> set sqlblanklines on; SQL> set linesize 800 SQL> set time on; 11:40:54 SQL> select * from old_t; ID NAME ---------- -------------------- 1 li 已用时间: 00: 00: 00.02 11:41:05 SQL> update old_t set name='lih' where id=1; 已更新 1 行。 已用时间: 00: 00: 00.01 11:41:16 SQL> commit; 提交完成。 已用时间: 00: 00: 00.03 11:41:20 SQL> update old_t set name='lihu' where id=1; 已更新 1 行。 已用时间: 00: 00: 00.01 11:41:31 SQL> commit; 提交完成。 已用时间: 00: 00: 00.01 11:41:34 SQL> update old_t set name='lihua' where id=1; 已更新 1 行。 已用时间: 00: 00: 00.01 11:41:39 SQL> commit; 提交完成。 已用时间: 00: 00: 00.01 11:41:43 SQL> update old_t set name='lihuar' where id=1; 已更新 1 行。 已用时间: 00: 00: 00.02 11:42:24 SQL> commit; 提交完成。 已用时间: 00: 00: 00.01 11:42:26 SQL> select * from old_t; ID NAME ---------- -------------------- 1 lihuar 已用时间: 00: 00: 00.02 ------查询5分钟之前的数据 11:45:53 SQL> select * from old_t as of timestamp sysdate-5/24/60 where id=1; ID NAME ---------- -------------------- 1 li 已用时间: 00: 00: 00.02 ------查询第一个事务提交，第二个事务还没有提交时的数据 11:55:13 SQL> select * from old_t as of timestamp to_timestamp('2012-11-14 11:41:22','YYYY-MM-DD HH2 4:MI:SS') where id=1; ID NAME ---------- -------------------- 1 lih 已用时间: 00: 00: 00.02 ------查询第二个事务提交，第三个事务还没有提交时的数据 12:23:33 SQL> select * from old_t as of timestamp to_timestamp('2012-11-14 11:41:38','YYYY-MM-DD HH2 4:MI:SS') where id=1; ID NAME ---------- -------------------- 1 lihu 已用时间: 00: 00: 00.01 12:24:01 SQL> |
闪回版本查询（Flashback Version Query）
查询语句一般为：
1 2 3 | SELECT TO_CHAR(VERSIONS_STARTTIME,'YYYY-MM-DD HH24:MI:SS') VERSIONS_STARTTIME,VERSIONS_STARTSCN,TO_CHAR(VERSIONS_ENDTIME,'YYYY-MM-DD HH24:MI:SS') VERSIONS_ENDTIME ,VERSIONS_ENDSCN,VERSIONS_XID,VERSIONS_OPERATION,EMPNO FROM T_FVQ_20170617_LHR VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE ORDER BY VERSIONS_STARTTIME; |
其中，VERSIONS BETWEEN用于指定闪回版本查询时查询的时间段或SCN段；AS OF用于指定闪回查询时查询的时间点或SCN。在闪回版本查询的目标列中，可以使用下列几个伪列返回版本信息：
VERSIONS_STARTTIME：基于时间的版本有效范围的下界；
VERSIONS_STARTSCN：基于SCN的版本有效范围的下界；
VERSIONS_ENDTIME：基于时间的版本有效范围的上界；
VERSIONS_ENDSCN：基于SCN的版本有效范围的上界；
VERSIONS_XID：操作的事务ID，唯一的标识行；
VERSIONS_OPERATION：执行操作的类型，I 表示INSERT，D 表示DELETE，U 表示UPDATE。
闪回版本查询注意事项：
① VERSIONS子句不能用于查询的表包括外部表、临时表和固定表。
② 不能使用VERSIONS子句查询视图。但是，在视图定义中可使用VERSIONS子句。
③ SELECT语句中的VERSIONS子句不能跨多个DDL语句（这些语句会更改相应表的结构）。
闪回版本查询示例：
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 57 58 59 60 61 62 63 | LHR@orclasm > SHOW PARAMETER UNDO NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _undo_autotune boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 LHR@orclasm > CREATE TABLE T_FVQ_20170617_LHR AS SELECT * FROM SCOTT.EMP WHERE 1=2;--创建表T_FVQ_20170617_LHR Table created. LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;--查询时间作为TIMESTAMP开始时间 TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE) ------------------- ------------------------- 2017-06-17 14:49:58 67842991 LHR@orclasm > INSERT INTO T_FVQ_20170617_LHR SELECT * FROM SCOTT.EMP WHERE EMPNO=7902;--插入EMPNO=7902 1 row created. LHR@orclasm > COMMIT; --插入一行提交作为一个版本 Commit complete. LHR@orclasm > INSERT INTO T_FVQ_20170617_LHR SELECT * FROM SCOTT.EMP WHERE EMPNO=7788; 1 row created. LHR@orclasm > INSERT INTO T_FVQ_20170617_LHR SELECT * FROM SCOTT.EMP WHERE EMPNO=7698; 1 row created. LHR@orclasm > COMMIT; --插入两行提交作为一个版本 Commit complete. LHR@orclasm > UPDATE T_FVQ_20170617_LHR SET SAL=8888 WHERE EMPNO=7788; 1 row updated. LHR@orclasm > COMMIT; --再次更改EMPNO=7788的行提交，使这行有旧版本 Commit complete. LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;--查询时间作为TIMESTAMP结束时间 TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE) ------------------- ------------------------- 2017-06-17 14:51:46 67843218 LHR@orclasm > SELECT TO_CHAR(VERSIONS_STARTTIME,'YYYY-MM-DD HH24:MI:SS') VERSIONS_STARTTIME,VERSIONS_STARTSCN,TO_CHAR(VERSIONS_ENDTIME,'YYYY-MM-DD HH24:MI:SS') VERSIONS_ENDTIME ,VERSIONS_ENDSCN,VERSIONS_XID,VERSIONS_OPERATION,EMPNO 2 FROM T_FVQ_20170617_LHR VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE 3 ORDER BY VERSIONS_STARTTIME; VERSIONS_STARTTIME VERSIONS_STARTSCN VERSIONS_ENDTIME VERSIONS_ENDSCN VERSIONS_XID V EMPNO ------------------- ----------------- ------------------- --------------- ---------------- - ---------- 2017-06-17 14:50:04 67843038 050007009F9F0000 I 7902 2017-06-17 14:50:57 67843139 07000D0018830000 I 7698 2017-06-17 14:50:57 67843139 2017-06-17 14:51:34 67843209 07000D0018830000 I 7788 2017-06-17 14:51:34 67843209 07001F0019830000 U 7788 |
闪回事务查询（Flashback Transaction Query）
闪回事务查询提供了一种查看事务级数据库变化的方法。它是SQL的扩展，能够看到事务带来的所有变化。此外，返回补充SQL语句，并用于撤消由事务引起的各行变化。使用闪回事务查询的权限：
1 2 | GRANT SELECT ANY TRANSACTION TO LHR; GRANT EXECUTE ON DBMS_FLASHBACK TO LHR; |
开启补全日志：
1 2 | ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY,UNIQUE INDEX) COLUMNS; |
闪回事务查询实际上是查询的数据字典FLASHBACK_TRANSACTION_QUERY。可以根据该视图的UNDO_SQL列值返回数据以前版本。
闪回事务查询示例说明：
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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | SQL> conn / as sysdba SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered. SQL> grant select any transaction to lhr; Grant succeeded. SQL> conn lhr/lhr LHR@orclasm > SELECT TO_DATE(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL; TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE) ------------------- ------------------------- 2017-06-17 16:27:29 67854536 LHR@orclasm > CREATE TABLE T_FTQ_20170617_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO=7902; Table created. LHR@orclasm > SELECT * FROM T_FTQ_20170617_LHR; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 LHR@orclasm > UPDATE T_FTQ_20170617_LHR SET SAL=9999 WHERE EMPNO=7902; 1 row updated. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL; TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE) ------------------- ------------------------- 2017-06-17 16:28:25 67854637 LHR@orclasm > UPDATE T_FTQ_20170617_LHR SET SAL=99999 WHERE EMPNO=7902; 1 row updated. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL; TO_DATE(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE) ------------------- ------------------------- 2017-06-17 16:28:41 67854673 LHR@orclasm > col UNDO_SQL format a100 LHR@orclasm > col TABLE_NAME format a30 LHR@orclasm > SELECT START_SCN, COMMIT_SCN, LOGON_USER, OPERATION, TABLE_NAME, UNDO_SQL 2 FROM FLASHBACK_TRANSACTION_QUERY V 3 WHERE XID IN ('08000000AFA50000', '020014002C9F0000') 4 ORDER BY V.START_SCN; START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME UNDO_SQL ---------- ---------- ------------ ----------- -------------------- ---------------------------------------------------------------------------------------------------- 67854610 67854626 LHR UPDATE T_FTQ_20170617_LHR update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '3000' where ROWID = 'AAAnyVAAEAAAACDAAA'; 67854610 67854626 LHR BEGIN 67854645 67854656 LHR UPDATE T_FTQ_20170617_LHR update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '9999' where ROWID = 'AAAnyVAAEAAAACDAAA'; 67854645 67854656 LHR BEGIN LHR@orclasm > update "LHR"."T_FTQ_20170617_LHR" set "SAL" = '9999' where ROWID = 'AAAnyVAAEAAAACDAAA'; 1 row updated. LHR@orclasm > SELECT * FROM T_FTQ_20170617_LHR; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 1981-12-03 00:00:00 9999 20 |
闪回表（Flashback TABLE）
1 2 3 | alter table TRUNTAB enable row movement; flashback table TRUNTAB to timestamp to_date('2014/12/31 17:33:00','YYYY/MM/DD HH24:MI/SS'); |
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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | LHR@orclasm > CREATE TABLE T_FT_20170617_LHR (A NUMBER,B NUMBER); Table created. LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(1,1); 1 row created. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 67902120 LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(2,2); 1 row created. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > SELECT * FROM T_FT_20170617_LHR; A B ---------- ---------- 1 1 2 2 LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 67902215 LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902120;----闪回表的时候，需要对表执行ROW MOVMENT FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902120 * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled LHR@orclasm > ALTER TABLE T_FT_20170617_LHR ENABLE ROW MOVEMENT; Table altered. LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902120; Flashback complete. LHR@orclasm > SELECT * FROM T_FT_20170617_LHR; A B ---------- ---------- 1 1 LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902215;---闪回到最后的位置 Flashback complete. LHR@orclasm > SELECT * FROM T_FT_20170617_LHR; A B ---------- ---------- 1 1 2 2 |
下面是验证索引在FLASHBACK TABLE TO SCN中的情况：
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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | LHR@orclasm > DROP TABLE T_FT_20170617_LHR; Table dropped. LHR@orclasm > CREATE TABLE T_FT_20170617_LHR (A NUMBER,B NUMBER); Table created. LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(1,1); 1 row created. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 67902871 LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(2,2); 1 row created. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > CREATE INDEX IDX_T_LHR ON T_FT_20170617_LHR(A); Index created. LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;---该SCN表中存在索引 GET_SYSTEM_CHANGE_NUMBER ------------------------ 67902969 LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(3,3); 1 row created. LHR@orclasm > COMMIT; Commit complete. LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 67903002 LHR@orclasm > SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T_FT_20170617_LHR'; INDEX_NAME STATUS ------------------------------ -------- IDX_T_LHR VALID LHR@orclasm > ALTER TABLE T_FT_20170617_LHR ENABLE ROW MOVEMENT; Table altered. LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902871; Flashback complete. LHR@orclasm > SELECT * FROM T_FT_20170617_LHR;--恢复到没有创建索引之前的SCN A B ---------- ---------- 1 1 LHR@orclasm > SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T_FT_20170617_LHR';--此时索引仍然存在 INDEX_NAME STATUS ------------------------------ -------- IDX_T_LHR VALID LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67903002;--闪回到最后的SCN Flashback complete. LHR@orclasm > SELECT * FROM T_FT_20170617_LHR; A B ---------- ---------- 1 1 2 2 3 3 LHR@orclasm > DROP INDEX IDX_T_LHR; Index dropped. LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902969;--闪回到创建索引的SCN ，但是经过闪回后索引已经不再存在了 Flashback complete. LHR@orclasm > SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T_FT_20170617_LHR'; no rows selected LHR@orclasm > |
闪回删除（Flashback DROP）
从Oracle 10g开始，为了支持闪回删除（Flashback Drop）功能，Oracle引入了回收站（Recycle Bin）的概念。它的全称叫Tablespace Recycle Bin。回收站实际是一个逻辑容器（逻辑区域），原理有点类似于Windows系统的回收站。它以表空间中现有已经分配的空间为基础，而不是从表空间上物理划出一个固定区域用作回收站。这意味着回收站和表空间中的对象共用存储区域、系统没有给回收站预留空间。
从原理上来说，回收站就是一个数据字典表，放置用户已删除的的数据库对象信息。用户进行DROP操作的对象并没有被数据库删除，仍然会占用空间，除非是由用户手工进行PURGE或者因为存储空间不够而被数据库清除掉。在回收站功能被打开的情况下，当某个表被删除时，它就被移动到了回收站中。该对象一直保存在回收站中，直到清除回收站为止。因此，如果使用类似“DROP TABLE T_LHR;”的语句，那么T_T_LHR表就被移动到了回收站中。如果希望彻底删除T_LHR表，而不是保存在回收站中，那么可以在DROP TABLE命令中使用PURGE参数，命令为“DROP TABLE T_LHR PURGE;”。
闪回删除需要使用到回收站，打开回收站的命令如下所示：
1 2 | 系统级别：ALTER SYSTEM SET RECYCLEBIN = ON SCOPE=SPFILE; 会话级别：ALTER SESSION SET RECYCLEBIN = ON; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
