ORACLE 11G FLASHBACK FEATURE

Author: Rainny Zhong

Date: 2009-4-28

Oracle的閃回功能非常強大，如果瞭解它並真正掌握它，你會發現比介質恢復要省事不少。有這麼一個既省事又強大的功能，我們為何不用呢？所以，花點心思，來學習一下它的使用。能用FLASHBACK解決問題的時候，絕不要使用介質恢復。

請參看：Oracle® Database Advanced Application Developer's Guide11g Release 1 (11.1)B28424-03第373頁：第13章：Using Oracle Flashback Technology

1.ORACLE閃回技術概述

ORACLE閃回技術利用自動的回退管理(Automatic Undo Management),它們依賴於UNDO資料。

下面是為應用開發而準備的閃回技術：

n Oracle Flashback Query

n Oracle Flashback Version Query

n Flashback Transaction Query

n DBMS_FLASHBACK Package

n Flashback Transaction

n Flashback Data Archive (Oracle Total Recall)

下面是為資料庫管理而準備的閃回技術：

n Oracle Flashback Table

n Oracle Flashback Drop

n Oracle Flashback Database

2.為ORACLE閃回技術配置資料庫

要利用ORACLE的閃回技術功能，你必須對資料庫進行一些相關的設定。

2.1.配置資料庫自動回退管理

n 建立一個回退表空間

n 設定下面的例項引數以啟用自動的回退管理(AUM）

– UNDO_MANAGEMENT=’AUTO’

– UNDO_TABLESPACE=’UNDO_TBS’

– UNDO_RETENTION=integer

你可以查詢V$UNDOSTAT.TUNED_UNDORETENTION以獲得當前回退表空間的回退保持時間

設定UNDO_RETENTION並不能保證沒有到期的UNDO資料不被丟棄或覆蓋.

n 在回退表空間指定RETENTION GUARANTEE子句以保證未到期的UNDO資料不被丟棄或覆蓋

2.2.為Flashback Transaction Query配置資料庫

n 資料庫必須是10G或10G以上

n 啟用追加日誌

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2.3.為Flashback Transaction配置資料庫

■ With the database mounted but not open, enable ARCHIVELOG:

ALTER DATABASE ARCHIVELOG;

■ Open at least one archive log:

ALTER SYSTEM ARCHIVE LOG CURRENT;

■ If not done already, enable supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2.4.為指定的LOB欄位啟用閃回操作功能

To enable flashback operations on specific LOB columns of a table, use the ALTER TABLE statement with the RETENTION option.

因為LOB欄位會產生很大的UNDO資料，所以你必須選擇性地對確實需要的LOB欄位才啟用閃回操作功能。

2.5.授予必要的許可權

對於Oracle Flashback Query 和Oracle Flashback Version Query，必須擁有下面的許可權：

■ 對資料庫物件有FLASHBACK和SELECT許可權

■ 為了查詢所有的表，你必須有FLASHBACK ANY TABLE許可權

對於Oracle Flashback Transaction Query，必須擁有下面的許可權：

■ 有SELECT ANY TRANSACTION許可權

■ 為了執行從Oracle Flashback Transaction Query獲取的回退SQL語句，你必須對錶有SELECT，UPDATE，DELETE，INSERT許可權

對於DBMS_FLASHBACK包，必須擁有下面的許可權：

■ 對DBMS_FLASHBACK包有EXECUTE許可權

對於Flashback Data Archive (Oracle Total Recall)，必須擁有下面的許可權：

■ 有FLASHBACK ARCHIVE許可權

■ 你要將FLASHBACK ARCHIVE許可權授予使用者，你必須以SYSDBA登入或擁有FLASHBACK ARCHIVE ADMINISTER系統許可權

■ 為了執行下面的語句，你還必面擁有FLASHBACK ARCHIVE ADMINISTER系統許可權：

CREATE FLASHBACK ARCHIVE；

ALTER FLASHBACK ARCHIVE；

DROP FLASHBACK ARCHIVE；

■ 為了將FLASHBACK ARCHIVE ADMINISTER授予使用者，你必須以SYSDBA登入

為了建立一個預設的Flashback Data Archive，請以SYSDBA登入，併發布CREATE FLASHBACK ARCHIVE 或ALTER FLASHBACK ARCHIVE語句。

3.Oracle Flashback Query(SELECT AS OF)

Example 13–1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:

Example 13–1 Retrieving a Lost Row with Oracle Flashback Query

SELECT * FROM employees

AS OF TIMESTAMP

TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE last_name = 'Chung';

Example 13–2 restores Chung's information to the employees table:

Example 13–2 Restoring a Lost Row After Oracle Flashback Query

INSERT INTO employees

(SELECT * FROM employees

AS OF TIMESTAMP

TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE last_name = 'Chung');

閃回查詢可以用到DDL語句中：

CREATE VIEW hour_ago AS

SELECT * FROM employees

AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

閃回查詢也可以用到集合運算子中：

INSERT INTO employees
((SELECT *
FROM employees AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '60' MINUTE)) MINUS
SELECT * FROM employees);

4.Oracle Flashback Version Query

ORACLE閃回版本查詢是用於獲取行在一段時間間隔裡的不同版本。當每釋出一次COMMIT，就產生一個新的行版本。語法：

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

下面是閃回版本查詢的偽列：

VERSIONS_STARTSCN：行版本建立時的開始SCN,如果為NULL，表示行版本是在START之前建立的。

VERSIONS_STARTTIME：行版本建立時的開始時間戳,如果為NULL，表示行版本是在START之前建立的。

VERSIONS_ENDSCN：行版本期滿SCN, 如果為NULL，表示行版本是在查詢時是當前的或對應一個DELETE操作

VERSIONS_ENDTIME：行版本期滿時間戳,如果為NULL，表示行版本是在查詢時是當前的或對應一個DELETE操作

VERSIONS_XID：建立行版本的事務ID

VERSIONS_OPERATION：事務所做的操作：I代表INSERT，D代表DELETE，U代表UPDATE，行版本是INSERT操作之後產生的，在DELETE之前產生的，或行版本是受UPDATE操作的影響而產生的。如果使用者更新一個索引鍵，Oracle Flashback Version Query會把UPDATE看成是兩個操作：DELETE加INSERT，在VERSIONS_OPERATION中會有兩行，一個D和一個I。

一個行版本在ERSIONS_START*至VERSIONS_END*(但不包括VERSIONS_END*本身)期間是有效的。也就是說在時間t有效：VERSIONS_START* <= t < VERSIONS_END*

舉個例子：

VERSIONS_START_TIME VERSIONS_END_TIME SALARY

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

09-SEP-2003 25-NOV-2003 10243

行版本SALARY=10243在09-SEP-2003到25-NOV-2003但不包括25-NOV-2003本身（也就是25-NOV-2003 00：00：00之前）期間有效。

Oracle Flashback Version Query的例子：

SELECT versions_startscn,
versions_starttime,
versions_endscn,
versions_endtime,
versions_xid,
versions_operation,
name,
salary
FROM employees VERSIONS BETWEEN

TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'JOE';

5.Oracle Flashback Transaction Query

ORACLE閃回事務查詢透過查詢FLASHBACK_TRANSACTION_QUERY檢視來實現。

下面是一個例子：SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');

欄位解釋：

XID：事務ID

OPERATION：事務所做的操作

START_SCN:操作開始時的SCN

COMMIT_SCN:操作結束時的SCN

LOGON_USER:執行操作的USER

UNDO_SQL:如果要回退這個事務所需的SQL程式碼

ORACLE閃回事務查詢要和ORACLE閃回版本查詢結合起來：

SELECT xid, logon_user
FROM flashback_transaction_query
WHERE xid IN
(SELECT versions_xid
FROM employees VERSIONS BETWEEN

TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));

6.Flashback Transaction Query 和Flashback Version Query的結合

ORACLE閃回事務查詢一般是和ORACLE閃回版本查詢結合起來使用。

我們來建立測試表和測試資料：

CREATE TABLE emp(empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER);INSERT INTO emp VALUES (111, 'Mike', 555);COMMIT;CREATE TABLE dept(deptno NUMBER,
deptname VARCHAR2(32));INSERT INTO dept VALUES (10, 'Accounting');COMMIT;

現在EMP和DEPT表都只有一行。假設有一個事務錯誤地刪除了員工編號為111的記錄：

UPDATE emp SET salary = salary + 100 WHERE empno = 111;INSERT INTO dept VALUES (20, 'Finance');DELETE FROM emp WHERE empno = 111;COMMIT;

接下來，另外一個事務重新插入員工編號為111的記錄，但用不同的姓名：

INSERT INTO emp VALUES (111, 'Tom', 777);UPDATE emp SET salary = salary + 100 WHERE empno = 111;UPDATE emp SET salary = salary + 50 WHERE empno = 111;COMMIT

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/684234/viewspace-1025009/，如需轉載，請註明出處，否則將追究法律責任。