利用flashback query 恢復誤操作的數據
2024-07-21 02:32:20
供稿：網友
利用Flashback Query 恢復誤操作的數據
Author：Kamus Seraphim（張樂奕）
Date：2003-10
Mail：kamus@itpub.net
轉載請注明出處及作者
Oracle9i 中新增的閃回查詢（Flashback Query）功能對于誤刪除或者誤更新并且已經commit
了的情況，提供了簡便快捷的恢復方法，而在Oracle 提供閃回查詢之前，碰到這種情況只
能通過備份來進行基于時間點的恢復，無疑這比閃回查詢要麻煩而且費時。
什么是Flashback Query
利用Oracle 多版本讀一致的特性，在需要的時候通過undo 來提供所需的前鏡像中的數據。
利用這個功能，可以看到歷史數據（呵呵，就像時光倒流。月光寶盒？），甚至用歷史數據
來修復誤操作引起的錯誤?？梢酝ㄟ^指定時間或者SCN 來檢索需要的數據。
前提條件
數據庫必須處于Automatic Undo Management 狀態。
最大可以閃回查詢的時間段由UNDO_RETENTION 初始化參數（單位為秒）指定
可以通過ALTER SYSTEM SET UNDO_RETENTION = <seconds>;來修改參數值
如何使用Flashback Query
通過SQL
使用SELECT 語句的AS OF 來進行閃回查詢，語法如下：
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
使用AS OF 關鍵字來對表，視圖，或者物化視圖進行Flashback Query，如果指定了SCN，
那么exPR 部分必須是一個數字，如果指定了TIMESTAMP，那么expr 必須是一個timestamp
類型的值。查詢結果將返回在指定的SCN 或者時間點上的數據。
下面我們使用scott 方案來作一個實驗。
[zhangleyi@linux9 bin]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 23:44:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect scott/tiger
Connected.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> update emp set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select sal from emp where empno=7369;
SAL
----------
4000
備注：TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)指查詢距當前時間一天以
前的時間點的數據，如果我們要查詢一小時以前的，那么需要將DAY 替換成HOUR 即可，
查詢10 分鐘以前的將'1' DAY 替換'10' MINUTE。
以上演示了對于誤更新的字段進行恢復的方法，但是如果想在update 的子查詢部分使用AS
OF 那么該查詢只能返回一條記錄，否則將會報錯。如下：
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
SQL> select sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
3 where empno=7369;
SAL
----------
800
SQL> update emp set sal=
2 (select sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where empno=7369)
5 where empno=7369;
1 row updated.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> commit;
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp set sal=4000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 4000
7499 4000
7521 4000
7566 4000
7654 4000
7698 4000
7782 4000
7788 4000
7839 4000
7844 4000
7876 4000
EMPNO SAL
---------- ----------
7900 4000
7902 4000
7934 4000
14 rows selected.
SQL> select empno,sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp a set sal =
2 (select sal from emp b
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where a.empno=b.empno);
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
*
ERROR at line 3:
ORA-00907: missing right parenthesis
其實上面的語法是沒有問題的，但是可能是因為閃回查詢的特殊性導致上面的SQL 報錯，
而這種錯誤update 了大量數據時候的恢復才真正是閃回查詢的方便所在，對于這種情況我
們可以有兩種處理方法，一種是使用DBMS_FLASHBACK 包，將在后面介紹，另外一種方
法仍然是直接使用SQL，但是添加一個臨時表作為中轉，如下：
SQL> create table empsal_temp as
2 select empno,sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
Table created.
SQL> update emp a set sal =
2 (select sal from empsal_temp b
3 where a.empno=b.empno);
14 rows updated.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> commit;
Commit complete.
SQL> drop table empsal_temp;
Table dropped.
這樣我們就完成了錯誤數據的恢復。COOL!! RIGHT? :D
介紹DBMS_FLASHBACK 包
DBMS_FLASHBACK 包提供了以下幾個函數：
ENABLE_AT_TIME：設置當前session 的閃回查詢時間
ENABLE_AT_SYSTEM_CHANGE_NUMBER：設置當前SESSION 的閃回查詢SCN
GET_SYSTEM_CHANGE_NUMBER：取得當前數據庫的SCN
比如：select dbms_flashback.get_system_change_number from dual；
DISABLE：關閉當前SESSION 的閃回查詢
當將一個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。
以上例子中的所有恢復都是基于時間點的，下面介紹基于SCN 的閃回查詢。
既然已經有基于時間點的恢復，為什么還需要基于SCN 呢，我們先來看一個例子。
[zhangleyi@linux9 oralinux]$ sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 11 02:26:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> set time on;
02:26:50 SQL> insert into dept values(60,'FLASH','BEIJING');
1 row created.
02:27:53 SQL> commit;
Commit complete.
02:27:57 SQL> delete from dept where deptno=60;
1 row deleted.
02:28:19 SQL> commit;
Commit complete.
02:28:21 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '1' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:29:49 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '2' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:11 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '3' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:19 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '4' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:30 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '5' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:39 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '6' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可以發現我們閃回查詢了1 分鐘到6 分鐘之前的所有前鏡像都沒有找到新插入的那條
DEPTNO＝60 的記錄，雖然TIMESTAMP 可以精確到毫秒，但是很顯然我們很難準確地
定位到毫秒級的時間點。至于為什么如此，biti 在論壇上的某個帖子中有所探討，這里暫時
先不追究產生這種結果的原因。在這種情況下，使用基于SCN 的閃回查詢是解決問題的最
好辦法。
以往的一些測試例子，都是在insert 數據之后立刻使用DBMS_FLASHBACK 包中的
GET_SYSTEM_CHANGE_NUMBER 函數來返回當時的SCN，然后再利用AS OF SCN 來進
行閃回查詢，但是實際應用中這是不可能的，因為在誤操作之前不會運行這個函數。所以我
們要使用LOGMINER 來對redolog 進行分析，得到當時錯誤地update 或者delete 數據時的
SCN。
對于LOGMINER 的安裝和使用方法本文不進行詳細的敘述，請自行查閱文檔。
下面是結合LOGMINER 進行閃回查詢的例子，為了描述簡便，假設從刪除數據到目前
ONLINE REDO LOG 沒有進行LOG SWITCH，也就是我們只需要分析當前ACTIVE 的
ONLINE REDO LOG 就可以了。
SQL> connect / as sysdba
Connected.
SQL> select b.MEMBER,a.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
MEMBER STATUS
-------------------------------------------- ----------------
/oracle/oradata/oralinux/redo01.log INACTIVE
/oracle/oradata/oralinux/redo02.log INACTIVE
/oracle/oradata/oralinux/redo03.log CURRENT
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName =>
'/oracle/oradata/oralinux/redo03.log',Options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>
'/oracle/admin/oralinux/orcldict.ora');
PL/SQL procedure successfully completed.
SQL> select scn,sql_redo from (select * from v$logmnr_contents where
sql_redo like 'delete%' order by scn desc) where rownum<2;
SCN SQL_REDO
---------- -------------------------------------------------------------------
543523 delete from "SCOTT"."DEPT" where "DEPTNO" = '60' and "D
現在我們已經找到了刪除那條記錄時候的SCN 是543523。
SQL> select * from scott.dept as of scn 543523;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 FLASH BEIJING
搞定！我們利用SCN 進行閃回查詢找到了刪除前的那條數據，此時利用上文描述過的SQL
方法或者DBMS_FLASHBACK 方法都可以進行數據恢復了。
幾個注意點：
1． Flashback Query 對于DDL 操作（比如DROP）無效，只能適用于DML 的誤操作
（UPDATE，DELETE）
2． SYS 用戶不允許執行DBMS_FLASHBACK 包，將會產生ORA-08185 錯誤，
Flashback not supported for user SYS
3．可能需要給其它用戶授權才能允許其它用戶執行DBMS_FLASHBACK 包，需要執
行：GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
4．閃回查詢的功能不止是適用于數據恢復，同樣適用于DSS 和OLAP，比如需要查詢
在前一個小時內生成的新訂單，那么就可以利用AS OF 來取得兩個時間點的查詢結
果的差集。
5．如果結合使用LOGON TRIGGERS，那么可以實現不更改代碼就支持各個時間點的
報表查詢功能。
6． Flashback Query 的查詢速度依賴于需要執行多少UNDO，也就是想查詢多長時間以
前的數據庫快照，回溯的時間越久可能執行的速度就越慢。
7． Flashback Query 不會真正的UNDO 任何數據，僅僅是一個查詢的機制而已。
8． Flashback Query 不會告訴你到底數據發生了哪些變化，這是LOGMINER 的功能。
9．如果需要恢復的表中有巨大的數據量，那么閃回查詢會是一個極為昂貴的操作，此
時可能作基于時間點的恢復反而更有效。
