Oracle資料庫的閃回查詢功能簡介

Flashback query(閃回查詢)原理
從Oracle 9i開始，Oracle提供閃回查詢功能，Oracle根據undo資訊，利用undo資料，類似一致性讀取方法，可以把表置於一個刪除前的時間點(或SCN)，從而將資料找回。


Flashback query(閃回查詢)前提：
SQL> show parameter undo
NAME                                TYPE       VALUE
------------------------------------ ----------- ------------------------------
undo_management                     string     AUTO
undo_retention                        integer    900
undo_tablespace                       string     UNDOTBS1
其中undo_management = auto，設定自動undo管理（AUM），該引數預設設定為：auto；
undo_retention = n(秒),設定決定undo最多的儲存時間，其值越大，就需要越多的undo表空間的支援，如果undo足夠大或者系統產生的undo足夠少，資料在undo裡面保留的時間會比undo_retention設定的時間長。
修改undo_retention的命令如下：
SQL> alter system set undo_retention = 3600;
System altered


閃回實現方式
1.獲取資料刪除前的一個時間點或scn，如下：
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
 
TIME                SCN
------------------- ----------------------------------------
2013-12-26 15:58:13 302044764


2.查詢該時間點（或scn）的資料，如下：
SQL> delete from t_user where isvalid=0;
 
11163 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select count(*) from t_user as of timestamp to_timestamp('2013-12-26 15:58:13', 'yyyy-mm-dd hh24:mi:ss');
 
  COUNT(*)
----------
     28614


SQL> select count(*) from t_user as of scn 302044764;
 
  COUNT(*)
----------
     28614




3.將查詢到的資料，新增到表中。也可用更直接的方法，如：
SQL> create table t_test as select * from t_user as of timestamp to_timestamp('2013-12-26 15:58:13', 'yyyy-mm-dd hh24:mi:ss') where isvalid=0;
 
Table created


SQL> insert into t_user select * from tab_test;
 
11163 rows inserted
 
SQL> commit;
 
Commit complete


SQL> select count(1) from t_user;
 
  COUNT(1)
----------
     28614
 
附：
查詢5分鐘前的資料
SQL> select count(*) from t_user as of timestamp(systimestamp - interval '5' minute);
 
  COUNT(*)
----------
     17451


將10分鐘前的t_user表與1小時前的t_user表以id為條件進行關聯（Join）：
select e.username, d.username
  from t_user as of timestamp(systimestamp - interval '10' minute) e,
       t_user as of timestamp(systimestamp - interval '1' hour) d
 where e.id = d.id;


將 admin 使用者的EMAIL欄位修改為15分鐘前的值：
update t_user
   set email =
       (select email
          from t_user as of timestamp(systimestamp - interval '15' minute)
         where username = 'admin')
 where username = 'admin';






注：推薦使用scn，由於oracle9i中，因為scn與時間點的同步需要5分鐘，如果最近5分鐘之內的資料需要Falshback query查詢，可能會查詢丟失，而scn則不存在這個問題。Oracle10g中這個問題已修正（scn與時間點的大致關係，可以透過logmnr分析歸檔日誌獲得）。
Falshback query查詢的侷限：
1． 不能Falshback到5天以前的資料。
2． 閃回查詢無法恢復到表結構改變之前，因為閃回查詢使用的是當前的資料字典。
3． 受到undo_retention引數的影響，對於undo_retention之前的資料，Flashback不保證能Flashback成功。
4． 對drop,truncate等不記錄回滾的操作，不能恢復。
5． 普通使用者使用dbms_flashback包，必須透過管理員授權。命令如下:
SQL>grant execute on dbms_flashback to scott;


閃回時間點查詢的另一種方法是首先用PL/SQL包dbms_flashback的enable_at_time或enable_at_scn儲存過程鎖定一個會話級別的閃回時間目標，即進入閃回模式，隨後的查詢命令就可以省略“as of”，但也能達到使用“as of”的閃回效果，直到呼叫dbms_flashback. disable儲存過程關閉閃回模式為止。


比如，將閃回模式會話定格在15分鐘前：


SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);


PL/SQL procedure successfully completed.


現在，雖然沒有“as of”子句，以下查詢命令的含義就是檢視15分鐘前的t_user表：


SQL> select count(*) from t_user;
 
  COUNT(*)
----------
     17451


需要注意此時若訪問SYSDATE、SYSTIMESTAMP等日期函式，它們的返回值還是正常的當前的值，而不是靜止在15分鐘以前。另外，處於閃回會話模式時，執行dml和ddl將報錯：


SQL> update t_user u set u.realname='test' where id=1;
 
update t_user u set u.realname='test' where id=1
 
ORA-08182: operation not supported while in Flashback mode
還有，SYS使用者不能呼叫enable_at_time和enable_at_scn：
SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute);
BEGIN dbms_flashback.enable_at_time(systimestamp - interval '15' minute); END;


*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS
ORA-06512: at "SYS.DBMS_FLASHBACK", line 3
ORA-06512: at line 1




在回到過去對所有感興趣的表查詢一番後，再呼叫disable儲存過程關閉閃回會話模式回到正常狀態：


SQL> exec dbms_flashback.disable;
 
PL/SQL procedure successfully completed