目錄結構

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

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操作，引入回收站的概念，但此回收站僅是當前表空間的一塊邏輯劃分，所以會受限于當前表空間的可用空間的限制

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

閃回資料庫則是一中更極端的資料庫恢復功能，相當于不完整恢復，依賴于閃回日志

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/141052.html

標籤：java

上一篇：postgresql安裝，環境配置，密碼設定，c++呼叫libpqxx鏈接資料庫，決議jsonb格式

下一篇：MySQL 共享鎖和排他鎖

Oracle的學習心得和知識總結（一）|Oracle資料庫閃回技術詳解