【備份恢復】 閃回技術之閃回事務處理查詢

閃回事務處理查詢

 

1)  11g 閃回事務處理查詢必須要啟用最小補充日誌

SYS@ORA11GR2>alter database add supplemental log data;

 

Database altered.

 

SYS@ORA11GR2>select supplemental_log_data_min from v$database;

 

SUPPLEME

--------

YES

===========================================================================================

注 預設情況下最小補充日誌是未開啟的，關閉命令如下：
SYS@ORA11GR2>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES
SYS@ORA11GR2>alter database drop supplemental log data;
Database altered.
SYS@ORA11GR2>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
=======================================================

 

 

2) 建立測試表，並對其進行 DML 操作

SYS@ORA11GR2>create table t(id number(2),name varchar2(10)) tablespace users;

 

Table created.

 

SYS@ORA11GR2>insert into t values (1,'zhangsan');

 

1 row created.

 

SYS@ORA11GR2>update t set name='sss' where id=1;

 

1 row updated.

 

SYS@ORA11GR2>commit;

 

Commit complete.

 

3) 執行閃回版本查詢，得到事務 ID

SYS@ORA11GR2>select versions_xid,versions_startscn,id,name from t versions between timestamp minvalue and maxvalue;

 

VERSIONS_XID     VERSIONS_STARTSCN         ID NAME

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

0B001700E1000000           1761763          1 sss

 

 

4) 透過事務 ID 得到相關 SQL
事務'0B001700E1000000'做了兩個操作：插入資料、更新資料，閃回事務會逆向的返回這個事務
的操作，透過這組 SQL 可修改會事務修改前的樣子：

SYS@ORA11GR2>select undo_sql from flashback_transaction_query where xid='0B001700E1000000';

 

UNDO_SQL

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

update "SYS"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAV73AAEAAAAJEAAA';

delete from "SYS"."T" where ROWID = 'AAAV73AAEAAAAJEAAA';

透過上述 SQL 可修改會事務修改前的樣子，操作如下：
SQL> update "SCOTT"."T" set "NAME" = 'zhangsan' where ROWID = 'AAAWD/AAEAAAArUAAA';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID NAME
---------- ----------
         1 zhangsan

完成！！！！！！！！！！！！！！！！！！！