Oracle Flashback

一 閃回資料(DELETE)
二 閃回表(Flashback Drop)
三 閃回資料庫(Flashback Database)
四 閃回查詢(Flashback Query)
五 閃回版本查詢(Flashback Version Query)
六 閃回事務查詢(Flashback Transaction)
七 閃迴歸檔查詢
八 Other
===================
一 閃回資料(DELETE)
===================
一：原理
UNDO

二：資料庫基本資訊
SQL> select name,log_mode,flashback_on from v$database;

NAME               LOG_MODE                 FLASHBACK_ON
------------------ ------------------------ ------------------------------------
CHEN               NOARCHIVELOG             NO

三 方法
方法一：基於SCN
1 準備資料
SQL> create user chen identified by chen;
User created.

SQL> grant resource,connect to chen;
Grant succeeded.

SQL> create table chen.t1 as select level as id from dual connect by level<=10;
Table created.

2 檢視當前SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1726953


3 刪除資料
SQL> conn chen/chen
Connected.
SQL> delete t1;
10 rows deleted.

SQL> commit;
Commit complete.


4 基於SCN閃回刪除
SQL> flashback table t1 to scn 1726953;
flashback table t1 to scn 1726953
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table t1 enable row movement;
Table altered.

/*被flashback回來rowid發生了變化，這也是為什麼flashback table 需要enable row movement的原因，正常情況表中 資料的rowid是不可以改變的;*/

SQL> flashback table t1 to scn 1726953;
Flashback complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
        10

方法二：基於時間

1 檢視當前時間
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as t_time from dual;

T_TIME
--------------------------------------
2015-08-06 15:17:48

2 刪除資料
SQL> delete t1; 
10 rows deleted.

SQL> commit;
Commit complete.

3 基於時間閃回刪除
SQL> alter table t1 enable row movement;
Table altered.

SQL> flashback table t1 to timestamp to_timestamp('2015-08-06 15:17:22','yyyy-mm-dd hh24:mi:ss');
Flashback complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
        10

方法三：基於快閃記憶體查詢

1 檢視當前SCN或當前時間
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1727759

2 刪除資料
SQL> conn chen/chen
Connected.
SQL> delete t1;
10 rows deleted.

SQL> commit;
Commit complete.

3 透過閃回查詢建立表t2
SQL> create table t2 as select * from t1 as of scn 1727759;
Table created.
/*
或者
SQL> create table t2 as select * from t1 as of timestamp to_timestamp('2015-08-06 15:25:56','yyyy-mm-dd hh24:mi:ss');
Table created.
*/

4 重新命名
SQL> rename t1 to t1_old;
Table renamed.

SQL> rename t2 to t1;
Table renamed.

SQL> select count(*) from t1;

  COUNT(*)
----------
        10

=========================
二 閃回表(Flashback Drop)
=========================

一：原理
回收站
閃回丟棄是將被丟棄的資料庫物件及其相依物件的複製儲存在回收站中，以便在必要時能夠及時恢復這些物件。在回收站被清空以前，被丟棄的物件並沒有從資料庫中刪除。
這就使資料庫能夠恢復被意外或者誤操作而刪除的表。
/*FLASHBACK語句會還原最後放入回收站的表，而PURGE語句會清除最早進入回收站的表*/

二：資料庫基本資訊
SQL> select name,log_mode,flashback_on from v$database;

NAME                 LOG_MODE                 FLASHBACK_ON
-------------------- ------------------------ ------------------------------------
CHEN                 NOARCHIVELOG             NO

SQL> col name for a20
SQL> col type for a10
SQL> col value for a8
SQL> show parameter recyclebin

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
recyclebin                           string     on

三：方法
不清空回收站的恢復

1 刪除表
SQL> drop table t1;
Table dropped.

2 檢視回收站
SQL> show recyclebi
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0 TABLE        2015-08-06:15:39:34

SQL> select object_name,ORIGINAL_NAME from user_recyclebin;

OBJECT_NAME                    ORIGI
------------------------------ -----
BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0 T1

SQL> select original_name,operation,droptime from recyclebin;

ORIGI OPERATION          DROPTIME
----- ------------------ --------------------------------------
T1    DROP               2015-08-06:15:39:34

SQL> select tname from tab;
TNAME
------------------------------------------------------------
BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0
SYS_TEMP_FBT
T1_OLD
T2

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from "BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0";

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

3 恢復
恢復方法一：
SQL> create table t1 as select * from "BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0";
Table created.

恢復方法二：
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$HKC57EgIDWXgU8UDqMA0zA==$0 TABLE        2015-08-06:15:47:08

SQL> flashback table t1 to before drop;
或者
SQL> flashback table "BIN$HKC57EgJDWXgU8UDqMA0zA==$0" to before drop;
或者(閃回表並且重新命名錶)
SQL> flashback table t1 to before drop rename to t1_1;

2 清空回收站的恢復(PURE)
drop table t1 purge;
清空回收站後不能按照以上方法進行恢復，可以透過閃回資料庫進行恢復。

=================================
三 閃回資料庫(Flashback Database)
=================================

一：原理

閃回日誌

Flashback Database整個架構包括一個程式Recover Writer(RVWR)後臺程式，Flashback Database Log日誌和Flash Recovery Area。
一旦資料庫啟用了Flashback Database,則RVWR程式會啟動，該程式會向Flash Recovery Area中寫入Flashback Database Log,這些日誌包括的是資料塊的前映象(before image),這也是Flashback Database技術不完全恢復塊的原因。

/*該功能不基於撤銷資料（undodata），而是基於閃回日誌*/

/*使用閃回資料庫恢復，恢復時間是由恢復過程中需要備份的變化的數量決定的，而不是資料檔案和歸檔日誌的大小*/

/*閃回資料庫的結構是由恢復寫入器（RVWR）後臺程式和閃回資料庫日誌組成的*/

配置閃回資料庫
配置閃回恢復區以後，要啟用閃回資料庫功能，還需要進行進一步的配置，需要注意如下幾點。
l 配置閃回恢復區。
2 資料庫需要執行在歸檔模式下（Archivelog）。
3 透過資料庫引數DB_FLASHBACK_RETENTION_TARGET，來指定可以在多長時間內閃回資料庫。
該值以分鐘為單位，預設值為1440（1天），更大的值對應更大的閃回恢復空間，類似於閃回資料庫的基線。
4 需要在MOUNT狀態下使用ALTER DATABASE FLASHBACK ON 命令啟動閃回資料庫功能。

二 檢視資料庫基本資訊
1 檢視是否啟動閃回資料庫功能
SQL> select name,log_mode,flashback_on from v$database;

NAME                 LOG_MODE                 FLASHBACK_ON
-------------------- ------------------------ ------------------------------------
CHEN                 NOARCHIVELOG             NO

2 檢視是否使用spfile檔案
SQL> show parameter spfile

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
spfile                               string     /u01/app/oracle/product/11.2.4
                                                /dbs/spfilechen.ora

3 檢視閃回區大小及路徑
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_recovery_file_dest                string     /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size          big integer 3882M

4 檢視保留時間
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_flashback_retention_target        integer    1440

5 檢視閃回日誌檔案
[oracle@chen flashback]$ pwd
/u01/app/oracle/flash_recovery_area/CHEN/flashback

[oracle@chen flashback]$ ls
o1_mf_bw69sdgk_.flb  o1_mf_bw69sg6d_.flb

6 檢視歸檔目錄，規定的格式
SQL> set linesize 100
SQL> show parameter log_archive_format

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_format                   string                 %t_%s_%r.dbf

SQL> show parameter log_archive_dest_
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_2                   string
...

①將閃回恢復區的大小設定為4GB。
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=4g SCOPE=BOTH;
②要停用閃回恢復區，只需將引數db_recovery_file_dest置空就可以了。
SQL>ALTER SYSTEM SET db_recovery_file_dest=’’;

當閃回恢復區中的空間使用率超過85%的時候，資料庫將會向alert檔案中寫入警告資訊。而當超過97%的時候將會寫入嚴重告警資訊。
當閃回恢復區空間全部耗盡的時候，Oracle將報告如下類似的錯誤：
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 1258291200 limit
此時查詢檢視dba_outstanding_alerts，將會給出錯誤的原因及操作建議

三 啟動歸檔和閃回功能

SQL> ho mkdir {chen_archive1,chen_archive2} -p

SQL> alter system set log_archive_dest_1='location=/home/oracle/chen_archive1' scope=both;
System altered.

SQL> alter system set log_archive_dest_2='location=/home/oracle/chen_archive2' scope=both;
System altered.

SQL> shutdown immediate

SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open;
Database altered.

3
SQL> select name,log_mode,flashback_on from v$database;

NAME               LOG_MODE                 FLASHBACK_ON
------------------ ------------------------ ------------------------------------
CHEN               ARCHIVELOG               YES

SQL> ho ps -ef|grep ora_
......
oracle    9059     1  0 17:21 ?        00:00:00 ora_rvwr_chen  
oracle    9100     1  0 17:26 ?        00:00:00 ora_arc0_chen  
oracle    9102     1  0 17:26 ?        00:00:00 ora_arc1_chen  
oracle    9104     1  0 17:26 ?        00:00:00 ora_arc2_chen  
oracle    9106     1  0 17:26 ?        00:00:00 ora_arc3_chen  
......

四：閃回資料庫示例

1 建立表，檢視當前SCN和時間
SQL> conn chen/chen
Connected.

SQL> create table t1 as select level as id from dual connect by level<=10;
Table created.

SQL> conn / as sysdba
Connected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1733751

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as t_time from dual;

T_TIME
--------------------------------------
2015-08-06 17:40:57

2 刪除表同時清空回收站
SQL> conn chen/chen  
Connected.

SQL> drop table t1 purge;
Table dropped.

3 閃回資料庫
SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to scn 1733751;
Flashback complete.

或者
/*
SQL> flashback database to timestamp to_timestamp('2014-06-25 10:57:48','yyyy-mm-dd hh24:mi:ss');
*/

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

4
SQL> select count(*) from chen.t1;

  COUNT(*)
----------
        10

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/chen_archive2
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

============
四 閃回查詢
============

一：原理
UNDO

二：檢視

1 查詢t1表2分鐘之前的資料
SQL> select * from t1 as of timestamp (systimestamp - interval '2' minute);   

SQL> select * from t1 as of timestamp (systimestamp - interval '2' minute) where ...;  

2 查詢t1表120秒之前的資料
SQL> select * from t1 as of timestamp (systimestamp - interval '120' second); 

3 查詢dept表12小時之前的資料
SQL> select * from dept as of timestamp (systimestamp - interval '12' hour);  

4 查詢dept表12天之前的資料
SQL> select * from dept as of timestamp (systimestamp - interval '12' day);   

5 查詢cardacct表2天之前的資料
SQL> select * from cardacct as of timestamp sysdate-2;                              

SQL> select * from t2 as of timestamp to_timestamp('2014-08-24 00:13:59', 'yyyy-mm-dd hh24:mi:ss');

6 時間和scn之間的轉換
SQL> select timestamp_to_scn(to_timestamp('2014-08-24 05:15:22','yyyy-mm-dd hh24:mi:ss')) scn from dual;       
       SCN
----------
   1081597

SQL> select scn_to_timestamp(1081597) scn from dual;
SCN
------------------------------------
24-AUG-14 05.15.21.000000000 AM

=========================================
五 閃回版本查詢(Flashback Version Query)
=========================================

一：原理
UNDO

在 Oracle9i Database 中，我們看到它推出了以閃回查詢形式表示的“時間機器”。
該特性允許 DBA 看到特定時間的列值，只要在還原段中提供該資料塊此前映象的複製即可。
但是，閃回查詢只提供某時刻資料的固定快照，而不是在兩個時間點之間被更改資料的執行狀態表示。
某些應用程式，如涉及到外幣管理的應用程式，可能需要了解一段時期內數值資料的變化，而不僅僅是兩個時間點的數值。
由於閃回版本查詢特性，Oracle Database 10g 能夠更方便高效地執行該任務。

二：準備資料
SQL> create table t1 as select level as id from dual connect by level<=10;
Table created.

三：更改資料，進行幾個事務
SQL> alter table t1 add(a number);
Table altered.

SQL> update t1 set a=id;
10 rows updated.

SQL> commit;
Commit complete.

SQL> delete t1 where id=7;
1 row deleted.

SQL> commit;
Commit complete.

SQL> insert into t1 values(7,7);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;

        ID          A
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         8          8
         9          9
        10         10
         7          7

10 rows selected.

四：閃回檢視版本

其中：
U 代表UPDATE
I 代表INSERT
D 代表DELETE

1
SQL> select versions_xid,versions_startscn,versions_endscn,versions_operation from t1 versions between scn minvalue and maxvalue order by 2;

VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN VE
---------------- ----------------- --------------- --
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
08001800A3040000           1739604         1739615 U
08001800A3040000           1739604                 U
08001800A3040000           1739604                 U
09000D00A3040000           1739615                 D
07000500C3030000           1739655                 I
                                           1739604
                                           1739604
                                           1739604
                                           1739604
                                           1739604
                                           1739604
                                           1739604
                                           1739604
                                           1739604
                                           1739604


22 rows selected.

2
SQL> col versions_endtime for a25
SQL>  col versions_starttime for a25
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation from t1 versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     VE
------------------------- ------------------------- ---------------- --
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM     07-AUG-15 11.16.03 AM     08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.15.42 AM                               08001800A3040000 U
07-AUG-15 11.16.03 AM                               09000D00A3040000 D
07-AUG-15 11.16.24 AM                               07000500C3030000 I
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM
                          07-AUG-15 11.15.42 AM


22 rows selected.

3
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation from t1 versions between scn 1743474 and 1743668 order by 1;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     VE
------------------------- ------------------------- ---------------- --
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
07-AUG-15 01.00.13 PM                               02000100F1040000 U
                          07-AUG-15 01.00.13 PM


VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     VE
------------------------- ------------------------- ---------------- --
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM
                          07-AUG-15 01.00.13 PM


20 rows selected.

4 
SQL> select timestamp_to_scn(to_timestamp('2015-08-07 11:15:42','yyyy-mm-dd hh24:mi:ss')) scn from dual;  
/*或者select timestamp_to_scn(to_date('2015-08-07 11:15:42','yyyy-mm-dd hh24:mi:ss')) scn from dual;*/

       SCN
----------
   1739603


SQL> select timestamp_to_scn(to_timestamp('2015-08-07 11:16:24','yyyy-mm-dd hh24:mi:ss')) scn from dual; 

       SCN
----------
   1739654

SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation from t1 versions between scn 1739603 and 1739654;
select versions_starttime, versions_endtime, versions_xid,versions_operation from t1 versions between scn 1739603 and 1739654
                                                                                  *
ERROR at line 1:
ORA-30052: invalid lower limit snapshot expression


SQL> show parameter undo

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 UNDOTBS1

注意，此處顯示了對該行所作的所有更改，甚至包括該行被刪除和重新插入的情況。VERSION_OPERATION 列顯示對該行執行了什麼操作 (Insert/Update/Delete)。所做的這些工作不需要歷史表或額外的列。
在上述查詢中，列 versions_starttime、versions_endtime、versions_xid、versions_operation 是偽列，與 ROWNUM、LEVEL 等其他熟悉的偽列相類似。其他偽列 — 如 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN — 顯示了該時刻的系統更改號。列 versions_xid 顯示了更改該行的事務識別符號。有關該事務的更多詳細資訊可在檢視FLASHBACK_TRANSACTION_QUERY 中找到，其中列 XID 顯示事務 id。例如，使用上述的 VERSIONS_XID 值 000A000D00000029，UNDO_SQL 值顯示了實際的語句。
注： versions_starttime : 這個資料開始生效的時間
VERSIONS_ENDTIME ：這個資料失效的時間--一般就是下面一條記錄開始的時間
versions_xid : 顯示了更改該行的事務識別符號。
VERSION_OPERATION ：這條記錄執行的操作(Insert/Update/Delete)

======================================
六 閃回事務查詢(Flashback Transaction)
======================================

一：原理
REDO LOG

閃回事務又稱撤銷事務(Backout Transation)，能夠撤銷一個或多個事務的修改，其功能由一個名為DBMS_FLASHBACK.TRANSACTION_BACKOUT的儲存過程實現。
該儲存過程的工作原理是自動分析重做日誌，挖掘出變更前的值用以構建撤銷SQL(Undo SQL),然後執行撤銷SQL最後達到撤銷的目的。
為了該功能可以正常使用，至少需要事先啟用主鍵補充日誌。另外，為了能夠跟蹤外來鍵依賴還需要啟用外來鍵補充日誌。

閃回事務查詢有別於閃回查詢的特點有以下3個：
（1）其正常工作不但需要利用撤銷資料，還需要事先啟用最小補充日誌。
（2）返回的結果不是以前的“舊”資料，而是能夠將當前資料修改為以前的樣子的撤銷SQL（Undo SQL）語句。
（3）集中地在名為flashback_transaction_query表上查詢，而不是在各個表上透過“as of”或“versions between”子句查詢。

二：不啟用補充閃回事務查詢
SQL> insert into t1 values(10000,10000);
1 row created.

SQL> commit;

SQL> select versions_xid,versions_startscn from t1 versions between timestamp minvalue and maxvalue order by 2;

VERSIONS_XID     VERSIONS_STARTSCN
---------------- -----------------
07001F00D5030000           1752034

SQL> conn /as sysdba
Connected.
SQL> select undo_sql from flashback_transaction_query where xid='07001F00D5030000';

UNDO_SQL
--------------------------------------------------------------------------------


SQL> select supplemental_log_data_fk,supplemental_log_data_all,supplemental_log_data_min from v$database;

SUPPLE SUPPLE SUPPLEMENTAL_LOG
------ ------ ----------------
NO     NO     NO

三 啟用補充閃回事務查詢
SQL> alter database add supplemental log data;
Database altered.

/*SQL> alter database drop supplemental log data;*/

SQL>  select supplemental_log_data_fk,supplemental_log_data_all,supplemental_log_data_min from v$database;

SUPPLE SUPPLE SUPPLEMENTAL_LOG
------ ------ ----------------
NO     NO     YES

SQL> delete t1 where id=10000;
1 row deleted.

SQL> commit;
Commit complete.

SQL> select versions_xid,versions_startscn from t1 versions between timestamp minvalue and maxvalue order by 2;

VERSIONS_XID     VERSIONS_STARTSCN
---------------- -----------------
08000300B3040000           1784373

SQL> conn /as sysdba
Connected.
SQL> select undo_sql from flashback_transaction_query where xid='08000300B3040000';

UNDO_SQL
--------------------------------------------------------------------------------
insert into "CHEN"."T1"("ID","A") values ('10000','10000');

======================================
七 閃迴歸檔查詢
======================================

一：原理
UNDO歸檔

從Oracle Database 11g開始，Oracle 提供了一個這樣的功能：閃回資料歸檔（Flashback Data Archive）。
透過這一功能Oracle資料庫可以將UNDO資料進行歸檔，從而提供全面的歷史資料查詢，也因此Oracle引入一個新的概念Oracle Total Recall，也即Oracle全面回憶功能。
閃回資料歸檔可以和我們一直熟悉的日誌歸檔類比，日誌歸檔記錄的是Redo的歷史狀態，用於保證恢復的連續性；
而閃迴歸檔記錄的是UNDO的歷史狀態，可以用於對資料進行閃回追溯查詢；
後臺程式LGWR用於將Redo資訊寫出到日誌檔案，ARCH程式負責進行日誌歸檔；
在Oracle 11g中，新增的後臺程式FBDA（Flashback Data Archiver Process）則用於對閃回資料進行歸檔寫出： 
SQL> ho ps -ef|grep fb  
oracle    9173     1  0 16:38 ?        00:00:00 ora_fbda_chen
閃迴歸檔資料甚至可以以年為單位進行儲存，Oracle可以透過內部分割槽和壓縮演算法減少空間耗用，這一特性對於需要審計以及歷史資料分割槽的環境尤其有用，但是注意，對於繁忙的資料庫環境，閃回資料儲存顯然要耗用更多的儲存空間。
當然，使用者可以根據需要，對部分表進行閃回資料歸檔，從而滿足特定的業務需求。

二：建立UNDO歸檔表空間
SQL> create tablespace fbda datafile '/u01/app/oracle/oradata/chen/fbda01.dbf' size 50M autoextend on;
Tablespace created.

三：設定閃迴歸檔資料儲存一個月
SQL> create flashback archive fdba tablespace fbda retention 1 month;
Flashback archive created.

四：切換較小的回退段表空間
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/chen/undotbs2_01.dbf' size 10M;
Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;
System altered.

SQL> set linesize 200
SQL> show parameter undo

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 UNDOTBS2

五：對錶T1開啟閃迴歸檔
SQL> conn /as sysdba
Connected.
SQL> grant flashback archive administer to chen;  
Grant succeeded.

SQL> conn chen/chen
Connected.
SQL> alter table t1 flashback archive fdba;
Table altered.

/*取消對於資料表的閃迴歸檔可以使用如下命令： alter table table_name no flashback archive*/

六：插入大量資料
SQL> conn chen/chen
Connected.
SQL> select count(*) from t1;

  COUNT(*)
----------
        10

SQL> insert into t1 select level as id,level as a from dual connect by level<=10000;
10000 rows created.

SQL> commit;
Commit complete.

七：開啟TRACE跟蹤
SQL> conn /as sysdba
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> grant plustrace to chen;
Grant succeeded.

八：執行一次較近的閃回查詢
SQL> conn /as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1821700

SQL> conn chen/chen
Connected.

SQL> set autotrace on
SQL> select count(*) from t1 as of scn 1821700;
  COUNT(*)
----------
     10010

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   654 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
九：執行一次較久遠的閃回查詢(透過UDNO歸檔查詢)
 
SQL> select count(*) from t1 as of scn 1820792;

  COUNT(*)
----------
        10

Execution Plan
----------------------------------------------------------
Plan hash value: 1563784122


------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     1 |       |       |  4250   (1)| 00:00:52 |       |       |
|   1 |  SORT AGGREGATE           |                    |     1 |       |       |    |          |       |       |
|   2 |   VIEW                    |                    |    34 |       |       |  4250   (1)| 00:00:52 |       |       |
|   3 |    UNION-ALL              |                    |       |       |       |    |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                    |     1 |    28 |       |     2   (0)| 00:00:01 |     1 |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_75719 |     1 |    28 |       |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     FILTER                |                    |       |       |       |    |          |       |       |
|   7 |      MERGE JOIN OUTER     |                    |    33 | 67320 |       |  4248   (1)| 00:00:51 |       |       |
|   8 |       SORT JOIN           |                    |    33 |   396 |       |    10  (10)| 00:00:01 |       |       |
|*  9 |        TABLE ACCESS FULL  | T1                 |    33 |   396 |       |     9   (0)| 00:00:01 |       |       |
|* 10 |       SORT JOIN           |                    |  9963 |    19M|    38M|  4238   (1)| 00:00:51 |       |       |
|* 11 |        TABLE ACCESS FULL  | SYS_FBA_TCRV_75719 |  9963 |    19M|       |    19   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


   5 - filter("ENDSCN">1820792 AND "ENDSCN"<=1821594 AND ("STARTSCN" IS NULL OR "STARTSCN"<=1820792) AND
              ("OPERATION" IS NULL OR "OPERATION"<>'D'))
   6 - filter("STARTSCN"<=1820792 OR "STARTSCN" IS NULL)
   9 - filter("T"."VERSIONS_STARTSCN" IS NULL)
  10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))
       filter("RID"(+)=ROWIDTOCHAR("T".ROWID))
  11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1821594) AND ("STARTSCN"(+) IS NULL OR "STARTSCN"(+)<1821594))


Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
        187  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

透過以上執行計劃可以看到，查詢閃回來自SYS_FBA_TCRV_75719系統表，該表隸屬於閃迴歸檔表空間，用於記錄閃回資料。

=========
八 Other
=========
1 當資料庫存在閃回回退儲存點時不能更改資料庫歸檔狀態
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
CHEN_ROLLBACK


SQL> drop restore point chen_rollback;
Restore point dropped.

SQL> alter database noarchivelog;
Database altered.

2 資料庫歸檔自定義路徑必須有location引數，否則報ORA-16179錯誤
SQL> alter system set log_archive_dest_1='/home/oracle/chen_archive1' scope=both;
alter system set log_archive_dest_1='/home/oracle/chen_archive1' scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

SQL> alter system set log_archive_dest_1='location=/home/oracle/chen_archive1' scope=both;
System altered.


3 開啟資料庫閃回功能之前，必須開啟資料庫歸檔
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

4 無許可權
SQL> select undo_sql from flashback_transaction_query where xid='07001F00D5030000';
select undo_sql from flashback_transaction_query where xid='07001F00D5030000'
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant select on flashback_transaction_query to chen;
Grant succeeded.

SQL> conn chen/chen
Connected.
SQL> select undo_sql from flashback_transaction_query where xid='07001F00D5030000';
select undo_sql from flashback_transaction_query where xid='07001F00D5030000'
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！