Flashback Query 針對DML誤操作的恢復

Flashback Query的應用-------------針對DML誤操作的恢復 用DBMS_FLASHBACK包

摘自:

[@more@]　　
　　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