目錄結構

---

注：提前言明 本文借鑑了以下博主、書籍或網站的內容，其列表如下：

1、Oracle閃回詳解，點選前往
2、Oracle閃回技術詳解，點選前往
3、參考書籍：《塗抹Oracle 三思筆記之一步一步學Oracle》
4、參考書籍：《Oracle Database 11g資料庫管理藝術》
5、Flashback Transaction Backout，點選前往
6、關於oracle閃回資料歸檔的總結，點選前往
7、參考書籍：《Oracle Database 9i10g11g程式設計藝術深入資料庫體系結構(第2版)》

---

---

---

文章快速說明索引

學習目標：

目的：因為接下來想在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操作，引入回收站的概念，但此回收站僅是當前表空間的一塊邏輯劃分，所以會受限於當前表空間的可用空間的限制

---

閃迴歸檔 可提供查詢或回退到過去任意時間點的功能

---

閃回資料庫 則是一中更極端的資料庫恢復功能，相當於不完整恢復，依賴於閃回紀錄檔