利用undo的閃回特性恢復錯誤操作的表

網廳資料庫一張表被開發人員一條sql誤操作

update policy.wt_gift_instance set cust_id='171392222723',code_number='15306563211',code_type='18',
def_id='216481660',instance_id='305886471',
used_date=sysdate,gift_cust_id='171392222723',
give_code_number='15306563211',
give_code_type='18',modify_date=sysdate,
create_date=sysdate,status='已使用',
occupy_seq='309541654',sts='Y' where activity_def_id='4441521';

表policy.wt_gift_instance被錯誤的更改了，客戶要求（我覺得他們應該雪地裸體跪求才對，嘿嘿）恢復被修改的資料。
資料庫沒有閃回區不能利用閃回區恢復，客戶說資料今天更改量很小可以恢復到昨天備份的時候，但是rman備份沒法恢復一張表到一個時間點。
考慮用logminer獲得redo的sql但是太複雜，群裡一吆喝有個哥們提出個方法，使用撤銷段的閃回特性從undo資料裡提取。

沒有設定Retention Guarantee強制保留undo資料。
SQL> select tablespace_name,retention from dba_tablespaces where tablespace_name like '%UNDO%';

TABLESPACE_NAME            RETENTION
------------------------------ -----------
UNDOTBS1               NOGUARANTEE
UNDOTBS2               NOGUARANTEE

SQL> show parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     7200
undo_tablespace              string     UNDOTBS1

時間只有7200即2小時，切並不強制保留undo的資料。
先確定下是否有資料，這條sql的執行時間是15點13分，我們就查詢這個時間點之前的資料。

執行操作如下：
SQL> select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 15:13:00','YYYY-MM-DD HH24:MI:SS') ;

  COUNT(*)
----------
    128720

ok 這部分資料還在，檢查下現在表的資料記錄
SQL> select count(*) from policy.wt_gift_instance;

  COUNT(*)
----------
    128736
記錄數就差16條，因為開發人員說這個表今天新資料插入很少，剛才的誤操作也只是update所以資料量應該沒有問題。
趁熱打鐵，趕緊把資料匯出來，建立新表policy.wt_gift_instance_restore將15點13分之前的資料從undo裡匯出來到新表中。
SQL>  create table policy.wt_gift_instance_restore tablespace TS_POLICY_DATA
as select * from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 15:13:00','YYYY-MM-DD HH24:MI:SS') ;

Table created.

檢查新表記錄
SQL> select count(*) from policy.wt_gift_instance_restore;

  COUNT(*)
----------
    128720

沒問題

保險起見，有把15點10分的資料匯出一份出來。
SQL>  create table policy.wt_gift_instance_restore_1 tablespace TS_POLICY_DATA
as select * from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 15:10:00','YYYY-MM-DD HH24:MI:SS') ;

Table created.

SQL> select count(*) from policy.wt_gift_instance_restore_1;

  COUNT(*)
----------
    128718

保險起見，有把15點整的資料匯出一份出來。
SQL> create table policy.wt_gift_instance_restore_2 tablespace TS_POLICY_DATA
 as select * from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 15:00:00','YYYY-MM-DD HH24:MI:SS') ;

Table created.


SQL> select count(*) from policy.wt_gift_instance_restore_2;

  COUNT(*)
----------
    128709

把三個表告知開發人員，他們確定資料可以使用，剩下的事情是他們自己去整合新舊資料了。

隨後又測試了下，看看能從中獲得多少時間前的資料
SQL> select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 00:00:00','YYYY-MM-DD HH24:MI:SS') ;
select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 00:00:00','YYYY-MM-DD HH24:MI:SS')
                            *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 74 with name "_SYSSMU74$"
too small


SQL> select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 09:00:00','YYYY-MM-DD HH24:MI:SS') ;
select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 09:00:00','YYYY-MM-DD HH24:MI:SS')
                            *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 74 with name "_SYSSMU74$"
too small


SQL> select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 12:00:00','YYYY-MM-DD HH24:MI:SS') ;
select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 12:00:00','YYYY-MM-DD HH24:MI:SS')
                            *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 74 with name "_SYSSMU74$"
too small


SQL> select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 14:00:00','YYYY-MM-DD HH24:MI:SS') ;
select count(*) from policy.wt_gift_instance AS OF timestamp to_timestamp ('2012-3-2 14:00:00','YYYY-MM-DD HH24:MI:SS')
                            *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 89 with name "_SYSSMU89$"
too small

快照太舊，回滾段太小，都失敗了，也就1小時左右前的資料保留在undo空間裡了。


總結，利用undo的閃回特性查詢資料表在某個時間點的資料可恢復意外的錯誤操作，但這個跟undo_retention設定的大小和Retention Guarantee引數（強制保留undo資料）有關。