Oracle10g的Flashback之Flashback Transaction Query

/**
*環境：WindowsXP+Oracle10.2.0.1.0（32位）
*日誌模式：歸檔模式
*作者：蒙昭良
*標題：Oracle10g的Flashback之Flashback Transaction Query
**/

       從Oracle9i開始，Oracle將閃回（flashback）引入資料庫中,閃（flashback）可以簡化使用者的資料恢復，不用從邏輯備份和物理備份中恢復過來。
      在Oracle10g中，Oracle對閃回特性進行了加強，flashback能夠恢復所有的資料庫錯誤（除了檔案損壞、介質錯誤等）。

      在Oracle10g中開始，Flashback支援的恢復級別包括：
1、事務閃回：對錶進行了insert，update，delete操作，已經commit了，發現弄錯了，需要恢復到某個commit操作之前的資料，則需要撤銷剛才的某個操作。
2、行級閃回：對錶進行了insert，update，delete操作，需要恢復到某個時間點。
3、表級閃回：錯誤的drop table的操作，需要表和表中的資料。如刪除表的某列等。
4、資料庫級閃回：錯誤對整個資料庫匯入很多表資料，將整個資料庫恢復到過去的時間點。

這裡主要說明事務閃回，先介紹什麼事務？
        事務（Transaction）就是對資料執行的一組操作，這些操作鈄資料從一種狀態更改為另一種狀態，他們必須為一個單元執行。事務最主要的功能就是確保多個連續的操作必須全部執行成功，否則回覆到未執行任何資料操作的最初狀態。比如對錶做了insert操作，然後進行commit成功之後，就算完成了一個事務。

        Oracle Flashback Transaction Query特性確保檢查資料庫的任何改變在一個事務級別，可以利用此功能進行診斷問題、效能分析和審計事務。它其實是Flashback Version Query查詢的一個擴充，Flashback Version Query說明了可以審計一段時間內表的所有改變，但是也僅僅是能發現問題，對於錯誤的事務，沒有好的處理辦法。而Flashback Transaction Query提供了從FLASHBACK_TRANSACTION_QUERY檢視中獲得事務的歷史以及Undo_sql（回滾事務對應的sql語句），也就是說審計一個事務到底做了什麼，甚至可以回滾一個已經提交的事務。

     某天，有個開發人員很慌張的跑過來來對我說，對dept表執行了insert，update，delete的操作，都已經提交commit。現在想把剛才的update、delete操作撤銷，恢復到update、delete之前的資料。
     1、檢視是哪個版本的資料庫，用擁有資料庫dba角色許可權的使用者檢視資料庫版本（預設最高管理員sys、system都具有）
 

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

   從Oracle10開始可以使用 Oracle Flashback Transaction Query特性

    2、檢視該使用者是否具有flashback的許可權

SQL> conn scott/mzl 已連線。

SQL> select * from session_privs where privilege like 'FLASHBACK%'; PRIVILEGE ----------------------------------------  FLASHBACK ANY TABLE

   3、在這先備份下dept表的資料，以防丟失現在的表資料（小心駛得萬年船）

SQL> create table dept_backup20081129 2 as 3 select * from dept;

        Oracle10g可以透過Flashback Version Query允許對不同時間段內資料表的不同版本進行查詢，查詢可以反映不同時間段內資料表的變更。
        Oracle Flashback Version Query特性，利用儲存的回滾資訊，可以看到特定的表在時間段內的任何修改，如電影的回放一樣，可以瞭解表在該期間的任何變化。Flashback version query一樣依賴於AUM，提供了一個檢視行改變的功能，能找到所有已經提交了的行的記錄，分析出過去時間都執行了什麼操作。Flashback version query採用VERSIONS BETWEEN語句來進行查詢，常用的方法：
·VERSIONS_SCN – 系統改變號
·VERSIONS_TIMESTAMP – 時間

   4、查詢什麼時間段做哪些操作

SQL> select versions_starttime, versions_endtime, versions_xid, 2 versions_operation, deptno,dname,loc 3 from dept versions  between timestamp minvalue and maxvalue  VERSIONS_STARTTIME  VERSIONS_ENDTIME VERSIONS_XID  VERSIONS_OPERATION DEPTNO DNAME LOC 1   29-11月-08 10.32.12 下午  0A001B00B3040000   U      10 ACCOUNTING ShanXi 2   29-11月-08 10.32.12 下午                                                      10 ACCOUNTING BeiJing 3                                                                                                         20 RESEARCH 4                                                                                                         30 SALES 5                                                                                                         40 OPERATIONS 6   29-11月-08 10.25.32 下午 07001700BB040000   D  70 WenYuan GuangXi 7   29-11月-08 10.25.32 下午 70 WenYuan GuangXi 8   29-11月-08 10.34.33 下午 07002F00BB040000    I   90 WenYuan ShanDong

VERSIONS_STARTSCN，VERSIONS_STARTTIME：該記錄操作時的scn或時間，如果為空，表示該行記錄是在查詢範圍外建立的。
 
VERSIONS_ENDSCN，VERSIONS_ENDTIME：該記錄失效時的scn或時間，如果為空，說明記錄當前時間在當前表記憶體在，或者已經被刪除了，可以配合著VERSIONS_OPERATION列來看，如果VERSIONS_OPERATION列值為D，說明該列已被刪除，如果該列為空，則說明記錄在這段時間無操作。
 
VERSIONS_XID：該操作的事務ID
 
VERSIONS_OPERATION：對該行執行的操作：I表示insert，D表示delete，U表示update。

提示：對於索引鍵的update操作，版本查詢可能會將其識別成兩個操作：DELETE和INSERT。

   4、查出具體什麼操作。

      由上面可見剛才事務ID：0A001B00B3040000對dept表進行update操作，事務ID：07001700BB040000進行了delete的操作。事務ID：07002F00BB040000進行了insert操作。

SQL>set time on;                                  (在sqlplus中設定開啟時間)

SQL>select xid,start_timestamp,operation,table_name,undo_sql from flashback_transaction_query where xid in ('0A001B00B3040000','07001700BB040000','07002F00BB040000') and  table_name='DEPT'; XID START_TIMESTAMP OPERATION TABLE_NAME UNDO_SQL 1  07002F00BB040000  2008-11-29 22:34:33   INSERT  DEPT   delete from "SCOTT"."DEPT" where ROWID = 'AAANZrAAEAAAATAAAA'; 2  07001700BB040000  2008-11-29 22:25:32  DELETE  DEPT  insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('70','WenYuan','GuangXi'); 3  0A001B00B3040000  2008-11-29 22:32:09  UPDATE   DEPT update "SCOTT"."DEPT" set "LOC" = 'BeiJing' where ROWID = 'AAANZrAAEAAAAS8AAB';

如果需要撤銷剛才刪除的操作，執行insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('70','WenYuan','GuangXi');就可以了
撤銷其他的操作，則執行與之相對應的redo_sql中的sql語句則可以了。
 
   5、需要撤銷剛才的刪除操作，執行

SQL>insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('70','WenYuan','GuangXi');

  
     需要撤銷剛才的更新操作，執行

SQL>update "SCOTT"."DEPT" set "LOC" = 'BeiJing'  where ROWID = 'AAANZrAAEAAAAS8AAB';

   6、檢視dept表的資料是否符合要求，符合要求後刪除剛才備份的表

22:53:08 SQL> drop  table  dept_backup20081129;

 

從上面看出2008-11-29 22:34:33  插入了哪些資料呢，執行select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAATAAAA';

22:49:49 SQL> select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAATAAAA'

   

DEPTNO DNAME LOC  ROWID ---------- ---------- ---------- ------------------ 90 WenYuan ShanDong AAANZrAAEAAAATAAAA

在2008-11-29 22:25:32又刪除了哪些資料呢，可以看出是'70','WenYuan','GuangXi'

在2008-11-29 22:32:09更新的資料是哪些，可以執行select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAAS8AAB';

22:53:07 SQL>select t.*,rowid from "SCOTT"."DEPT" t where ROWID = 'AAANZrAAEAAAAS8AAB';

  

DEPTNO DNAME LOC ROWID ---------- ---------- ---------- ------------------ 10 ACCOUNTING ShanXi AAANZrAAEAAAAS8AAB

 由此對比資料可知：該資料的loc欄位由“BeiJing”更新為“ShanXi”。