9i新特性之Flashback Query的應用---針對DML誤操作的恢復
在9i之前,如果出現DML的誤操作,只能透過備份來完成基於時間點的恢復,9i給提供了一個新的特性Flashback Query,我們可以應用此特性,可以很方便的實現恢復。但是要注意的是,Flashback Query 僅僅是一個查詢的機制,不會真正的UNDO任何資料。
[@more@]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誤操作的恢復.
用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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9099175/viewspace-906380/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Flashback Query 針對DML誤操作的恢復
- 9i新特性之Flashback Query的應用(1)
- 9i新特性之Flashback Query的應用(2)
- 對錶誤操作的閃回恢復--flashback_transaction_query檢視
- 使用Oracle9i的新特性Flashback Query恢復誤刪除資料(轉eygle)Oracle
- Oracle10g中FLASHBACK TABLE語句恢復DML誤操作Oracle
- Flashback Query恢復誤刪除資料(轉)
- flashback query和logmnr用於恢復資料的誤刪除(轉)
- 基於LOGMINER 的表DML誤操作恢復
- oracle資料庫災難挽救應急方案之DML誤操作恢復Oracle資料庫
- 閃回之 Flashback Query (dml表、過程、函式、包等)、Flashback version Query函式
- oracle flashback特性(2.2)--Flashback Table之從UNDO中恢復Oracle
- Flashback Query的應用(轉帖)
- 【徵文】應用oracle flashback(2.2)--Flashback Table之從UNDO中恢復Oracle
- flashback version query和 flashback transaction query簡單應用
- 【Flashback】使用閃回功能快速恢復使用者的誤操作
- 利用undo的閃回特性恢復錯誤操作的表
- 全面學習oracle flashback特性(2.2)--Flashback Table之從UNDO中恢復Oracle
- flashback技術之---flashback query
- flashback database 恢復誤刪除的表空間。Database
- 【MySQL】恢復誤操作的方法MySql
- Oracle10g的Flashback之Flashback Transaction QueryOracle
- unusable index對DML/QUERY的影響Index
- 【NetApp資料恢復案例】針對NetApp誤刪除資料的恢復APP資料恢復
- oracle flashback特性(1.4)--閃回查詢之Transaction queryOracle
- flashback技術之---flashback Transaction Query
- flashback技術之---flashback version query
- Oracle 備份恢復之 FlashbackOracle
- 10G新特性筆記之備份恢復新特性筆記
- Oracle 12C 新特性之 恢復表Oracle
- 【閃回特性之閃回事務查詢】Flashback Transaction Query
- [閃回特性之閃回版本查詢]Flashback Version Query
- flashback總結四之Flashback_Query
- 表資料被誤操作的恢復
- Oracle恢復誤操作刪除掉的表Oracle
- 用flashback恢復儲存過程儲存過程
- 閃回 錯誤的DML 操作
- Mysql update誤操作恢復MySql