oracle flashback 闪回功能，可以闪回删除的表数据，闪回drop的table以及闪回数据库到某个时间点。
除了Flashback Drop之外的所有逻辑闪回特性都是利用undo data完成的，而Flashback Drop是利用 recycle bin 完成，把drop的表存放在回收站，flashback时直接从回收站恢复。

Flashback Table

先决条件：
1.需要有FLASHBACK ANY TABLE的权限或者FLASHBACK指定表的权限；
2.需要有 SELECT, INSERT, DELETE and ALTER 的权限；
3.需要有SELECT ANY DICTIONARY or FLASHBACK ANY TABLE系统权限 或者SELECT_CATALOG_ROLE 角色。

需要闪回的表也需要满足以下条件：

不能是下面提到的表或者视图

• The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

表结构不能发生变化，如以下DDL操作不允许执行

• The structure of the table must not have been changed between the current time and the target flash back time.
  The following DDL operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition).

需要开启表 row movement 功能

• Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs.
  This restriction exists because if rowids before the flashback were stored by the application, then there is no guarantee that the rowids correspond to the same rows after the flashback. If your application depends on rowids, then you cannot use Flashback Table.

undo表空间必须有足够的空间来满足闪回需求

• The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.
  The point to which you can perform Flashback Table is determined by the undo retention period, which is the minimal time for which undo data is kept before being recycled, and tablespace characteristics. The undo data contains information about data blocks before they were changed. The flashback operation uses undo to re-create the original data.
  To ensure that the undo information is retained for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.

闪回案例：

SELECT CURRENT_SCN FROM V$DATABASE;
SELECT NAME, SCN, TIME FROM V$RESTORE_POINT; --如果有创建还原点，查到它
SELECT NAME, VALUE / 60 MINUTES_RETAINED FROM V$PARAMETER WHERE NAME = 'undo_retention'; -- 查看undo的保存时间
ALTER TABLE table_name ENABLE|DISABLE ROW MOVEMENT; --打开表 ROW MOVEMENT，完毕后DISABLE

--确保表是否存在依赖关系
SELECT OTHER.OWNER, OTHER.TABLE_NAME
FROM SYS.ALL_CONSTRAINTS THIS, SYS.ALL_CONSTRAINTS OTHER
WHERE THIS.OWNER = SCHEMA_NAME
AND THIS.TABLE_NAME = '表名'
AND THIS.R_OWNER = OTHER.OWNER
AND THIS.R_CONSTRAINT_NAME = OTHER.CONSTRAINT_NAME
AND THIS.CONSTRAINT_TYPE = 'R';

--开始闪回
FLASHBACK TABLE test_tb TO RESTORE POINT restore_test_tb;
FLASHBACK TABLE test_tb TO SCN 87077447518; -- 可以先闪回到最早，发现数据不对，继续往后闪回
FLASHBACK TABLE test_tb TO TIMESTAMP TO_TIMESTAMP('2018-8-24 15:30:00', 'YYYY-MM-DD HH:MI:SS');

-- 默认情况下，闪回过程中不开启触发器，可以通过下面参数打开
FLASHBACK TABLE test_tb TO TIMESTAMP TO_TIMESTAMP('2018-8-24 15:30:00', 'YYYY-MM-DD HH:MI:SS') ENABLE TRIGGERS;

Flashback Drop

用户需要 SELECT 和 FLASHBACK 权限
需要闪回的表也需要满足以下条件：

• The recycle bin is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.
• Tables that have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The table must not have been purged, either by a user or by Oracle Database because of a space reclamation operation.

闪回案例：

SELECT OBJECT_NAME AS RECYCLE_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN; --查看回收站表
SELECT * FROM "BIN$dCo334kmkAXgUwoDyAorRQ==$0";    -- 可以查询回收站的表

--开始闪回
FLASHBACK TABLE "BIN$dCo334kmkAXgUwoDyAorRQ==$0" TO BEFORE DROP;
FLASHBACK TABLE test_tb TO BEFORE DROP;
FLASHBACK TABLE "BIN$dCo334kmkAXgUwoDyAorRQ==$0" TO BEFORE DROP RENAME TO test_tb_tmp;      --闪回到其他表名
SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'TEST_TB';      -- 查看是否有索引
ALTER INDEX "BIN$JKS983293M1dsab4gsz/I249==$0" RENAME TO I_EMP_DEMO;   -- 如果有索引，则需要将索引重命名，方便后续查看使用

-- 如果一张表被多次创建及删除，那么被flashback的时候默认闪回最新记录的表

1.jpg

FLASHBACK TABLE test_tb TO BEFORE DROP ; 

2.jpg

可以通过指定回收站的对象名来恢复指定的表：
FLASHBACK TABLE "BIN$dCo334kmkAXgUwoDyAorRQ==$0" TO BEFORE DROP;
15：20 的表被恢复，另外一张仍在回收站

3.jpg

Flashback Database

步骤：

-- 查询可以闪回的scn
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME V$FLASHBACK_DATABASE_LOG;      --查看之前的闪回记录
SELECT CURRENT_SCN FROM V$DATABASE;       -- 查看当前的SCN号
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE  GUARANTEE_FLASHBACK_DATABASE='YES';    -- 查看创建的仍旧可用的RESTORE POINT

-- mount数据库
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- 再次查看第一步，确保scn没有被挤出 fast recovery area

-- 通过 rman 连接到数据库
rman target /

-- 确保channel参数配置正确，执行  show all
   因为执行flashback的时候，rman会通过备份restore archive log

-- 通过rman执行flashback
FLASHBACK DATABASE TO SCN 87077447518;
FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
FLASHBACK DATABASE TO TIME "TO_DATE('2018-8-24 9:00:00','yyyy-mm-dd hh24:mi:ss')";
When the FLASHBACK DATABASE command completes, the database is left mounted and recovered to the specified target time.

-- 以只读模式打开数据库，确保数据ok
ALTER DATABASE OPEN READ ONLY;

-- 如果数据没问题，则打开数据库，如果数据不符合要求，则执行  <后面的步骤>
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

对之前执行误操作的数据进行逻辑备份，然后恢复数据库到最新scn
RECOVER DATABASE;


-- <后面的步骤>
FLASHBACK DATABASE TO SCN 42963;      -- 如果闪回的不够老，继续往前闪回
RECOVER DATABASE UNTIL SCN 56963;     -- 如果闪回的太老，则往后恢复
RECOVER DATABASE;                     -- 如果不想闪回了，则执行recover即可


-- 监控闪回
select sofar, totalwork, units from v$session_longops where opname = 'Flashback Database';
SOFAR  TOTALWORK   UNITS
-----        ----------             --------------------------------
   17          60                  Megabytes

sofar           -- 目前已经读了多少M数据
totalwork       -- 总共需要读取多少M数据
units           -- 单位