在9i之前，如果出現DML的誤操作，只能透過備份來完成基於時間點的恢復，9i給提供了一個新的特性Flashback Query，我們可以應用此特性，可以很方便的實現恢復。但是要注意的是，Flashback Query 僅僅是一個查詢的機制，不會真正的UNDO任何資料。

1. 什麼是Flashback Query？

利用Oracle 多版本讀一致的特性透過undo 來提供所需的前映象中的資料。透過這個功能，可以看到歷史資料，甚至用歷史資料來修復誤操作引起的錯誤。可以透過指定時間或者SCN 來檢索需要的資料。｛Uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. You can view and repair historical data, and you can perform. queries on the database as of a certain wall clock time or user-specified system commit number (SCN).｝

可以理解成，Oracle在之前的版本中就已經存在了Flashback Query了，之前我們把它稱為多版本的讀一致性。（多版本特性提供跨多個使用者會話的一致性檢視，Oracle不執行dirty read）

2. 準備工作

the best way：資料庫處於Automatic Undo Management 狀態（也有文章說必須，我查詢了oracle文件應用了這個詞Prerequisite，同時在AskTom上我也查詢到相應的解釋，指明最好是自動Undo管理，手動的Undo管理也是可以的，並且有例子）

最大可以閃回查詢的時間段由UNDO_RETENTION 初始化引數（單位為秒）指定，參看下面執行命令

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 600

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1

SQL>

這是一個可以動態的修改的引數，可以透過ALTER SYSTEM SET UNDO_RETENTION =;來修改引數值

比較顯然的是，你設定了相對大的UNDO_RETENTION，就必須設定足夠大的UNDO ROLLBACK SEGMENTS。

3. 如何使用Flashback Query？

我們可以透過兩種方式來使用Flashback Query：

用SQL

使用SELECT 語句的AS OF 來進行閃回查詢，語法如下：

as of scn ( timestamp) expr

透過關鍵詞 AS OF 可以對錶，檢視，物化檢視進行Flashback Query，可以制定SCN或者TIMESTAMP，其中TIMESTAMP是9i中出現的，可以有毫秒的時間單位，如

SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

29-12月-03 10.15.05.171000 下午 +08:00

下面，進行一個例子：

SQL> connect scott/tiger

已連線。

SQL> create table test (id number(1));

表已建立。

SQL> insert into test values (1);

已建立 1 行。

SQL> insert into test values (2);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

ID

----------

1

2

SQL> delete from test where id=1;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

ID

----------

2

SQL> select * from test as of timestamp (systimestamp -interval'10'second);

ID

----------

1

2

SQL> insert into test (select * from test as of timestamp (systimestamp -interva

l'10'second) where id =1);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

ID

----------

2

1

現在，利用了Flashback Query 我們恢復了test表中被誤刪除的記錄。當然我們可以透過一些其他的方法，類似於建立一箇中間表等，進行這種少量資料的DML誤操作的恢復.

9i新特性之Flashback Query的應用-------------針對DML誤操作的恢復(2)

用DBMS_FLASHBACK包

DBMS_FLASHBACK 包提供了以下幾個函式：

ENABLE_AT_TIME：設定當前SESSION 的閃回查詢時間

ENABLE_AT_SYSTEM_CHANGE_NUMBER：設定當前SESSION的閃回查詢SCN

GET_SYSTEM_CHANGE_NUMBER：取得當前資料庫的SCN

DISABLE：關閉當前SESSION 的閃回查詢

如：

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

8053651

當將一個SESSION 設定為閃回查詢模式之後，後續的查詢都會基於那個時間點或者SCN 的資料庫狀態，如果SESSION 結束，那麼即使沒有明確指定DISABLE，閃回查詢也會自動失效。在SESSION 執行在閃回查詢狀態時，是不允許進行任何DML 和DDL 操作。如果要用DML操作來進行資料恢復就必須使用PL/SQL 遊標（其實，這裡也就是給我們提供了一個資料恢復的方法）。即使SESSION 執行在閃回查詢模式，SYSDATE 函式也不會受到影響，仍然會返回當前正確的系統時間。

下面我們用一個例子說明如何使用DBMS_FLASHBACK 包來恢復資料。

假設由於誤操作刪除了SCOTT.EMP 表中的所有資料，現在我們要恢復。

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)

----------

0

然後執行下面的SQL 建立一個儲存過程用於恢復資料

CREATE OR REPLACE PROCEDURE prc_recoveremp IS

CURSOR c_emp IS

SELECT * FROM scott.emp;

v_row c_emp%ROWTYPE;

BEGIN

DBMS_FLASHBACK.ENABLE_AT_TIME(SYSTIMESTAMP - INTERVAL '1' DAY);

OPEN c_emp;

DBMS_FLASHBACK.DISABLE;

LOOP

FETCH c_emp

INTO v_row;

EXIT WHEN c_emp%NOTFOUND;

INSERT INTO scott.emp

VALUES

(v_row.EMPNO,

v_row.ENAME,

v_row.JOB,

v_row.MGR,

v_row.HIREDATE,

v_row.SAL,

v_row.COMM,

v_row.DEPTNO);

END LOOP;

CLOSE c_emp;

COMMIT;

END prc_recoveremp;

SQL> execute prc_recoveremp;

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)

----------

14

到此成功結束，檢查EMP 表可以看到所有的資料已經全部都恢復了。

備註：在儲存過程中我們建立了遊標之後就將執行了DBMS_FLASHBACK.DISABLE，只

有這樣我們才能在這個SESSION 中進行DML 操作。否則將產生ORA-08182 錯誤，In

Flashback mode, user cannot perform. DML or DDL operations。

上面我們已經介紹了關於如何的應用Flashback Query來恢復DML的誤操作，但都是基於時間點（timestamp）的，其實呢，儘管timestamp可以精確到毫秒，可是由於{oracle 每隔5分鐘會將產生的 SCN 對應一個 TIME 做記錄，也就是說通常只記錄了SCN,但是每5分鐘會記錄 SCN and TIME }(這段話需要深入的考究)，當採用 timestamp 來做flashback 的時候就有可能產生偏差，5分鐘的來由是在於表SYS.SMON_SCN_TIME，我們可以察看一下：

該表的記錄一共是1440行，那來幾行可以看看

THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS

---------- ---------- ----------- ---------- ----------

1 1072772527 2003-12-30 0 8052536

1 1072772834 2003-12-30 0 8053330

1 1072773142 2003-12-30 0 8054053

1 1072773446 2003-12-30 0 8054845

可以看到，每行的timestamp差上5分鐘左右，實際上，每5分鐘，SMON刪除最舊的資料並且插入當前的資訊，這也就可以推算出為什麼無論你的UNDO RETENTION 設定多大，Flashback Query 只能用5天(1440*5/24/60 )。所以基於SCN的Flashback Query是最準確的

舉個例子看看：

SQL> select * from lyb;

未選定行

SQL> insert into lyb values (1);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

8058302

SQL> delete from lyb;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

8058379

SQL> select * from lyb as of scn 8058302

2 ;

ID

----------

1

SQL> select * from lyb as of scn 8058379

2 ;

未選定行

SQL>

所以說，基於scn的恢復才是能夠做到精確！

當然，我們很明顯遇到的問題是，如果真正的誤操作，我那裡會記錄scn啊？這裡就設計到另外的一個oracle很好用的工具，logminer，下次介紹！

注：SYS 使用者不允許執行DBMS_FLASHBACK 包，將會產生ORA-08185 錯誤，

Flashback not supported for user SYS

參考：

otn.oracle.com

asktom.oracle.com
Seraphim（張樂奕）的《利用Flashback Query 恢復誤操作的資料》

Flashback Query的應用(轉帖)

相關文章