通过Oracle10g的flashback transaction query新特性进行事务撤销

« Oracle10g的Flashback version Query | Blog首页 | 如何从自动备份中恢复控制文件和SPFILE文件 »

具备了flashback version query查询的基础，我们就可以进行基于flashback version query的恢复.
这就是flashback transaction query。flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY中获得指定事务的历史信息以及Undo_SQL，通过这个UNDO_SQL，我们就可以恢复特定的事务。
Flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图，我们先看一下视图



SQL> desc FLASHBACK_TRANSACTION_QUERY;
Name             Type           Nullable Default Comments                
---------------- -------------- -------- ------- -------------------------- 
XID              RAW(8)          Y       Transaction identifier   
START_SCN        NUMBER          Y       Transaction start SCN    
START_TIMESTAMP  DATE            Y       Transaction start timestamp   
COMMIT_SCN       NUMBER          Y       Transaction commit SCN  
COMMIT_TIMESTAMP DATE            Y       Transaction commit timestamp  
LOGON_USER       VARCHAR2(30)    Y       Logon user for transaction   
UNDO_CHANGE#     NUMBER          Y       1-based undo change number  
OPERATION        VARCHAR2(32)    Y       forward operation for this undo  
TABLE_NAME       VARCHAR2(256)   Y       table name to which this undo applies     
TABLE_OWNER      VARCHAR2(32)    Y       owner of table to which this undo applies
ROW_ID           VARCHAR2(19)    Y       rowid to which this undo applies          
UNDO_SQL         VARCHAR2(4000)  Y       SQL corresponding to this undo

该视图的定义为：



select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry

参考前面的文章，我们通过FLASHBACK_TRANSACTION_QUERY来恢复事务。
flashback version query参考:Oracle10g的Flashback version Query
1.通过flashback version query获得XID



EYGLE  on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid,
  2          versions_operation, username,user_id
  3          from t versions between timestamp minvalue and maxvalue
  4  /

VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID     V USERNAME      USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D DBSNMP             22
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D WMSYS              23
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D OPERATOR           31
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D TRANS              27
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D DIP                19
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D SCOTT              29
30-MAR-05 09.34.49 AM                                         000A000B000000F1 D TEST               25
30-MAR-05 09.34.15 AM                                         0001001900000F0F U EYGLE               1
30-MAR-05 09.33.51 AM                                         00080016000000EF D TEST1              28
30-MAR-05 09.33.23 AM                                         0004000A000005EF D OUTLN              11
                                                                                 SYSTEM              5

VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID     V USERNAME      USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
                                                                                 SYS                 0
                               30-MAR-05 09.34.49 AM                             TEST               25
                               30-MAR-05 09.34.15 AM                             EYGLE              26
                               30-MAR-05 09.34.49 AM                             SCOTT              29
                               30-MAR-05 09.34.49 AM                             DIP                19
                               30-MAR-05 09.34.49 AM                             TRANS              27
                               30-MAR-05 09.33.51 AM                             TEST1              28
                               30-MAR-05 09.34.49 AM                             OPERATOR           31
                               30-MAR-05 09.34.49 AM                             WMSYS              23
                               30-MAR-05 09.34.49 AM                             DBSNMP             22
                               30-MAR-05 09.33.23 AM                             OUTLN              11

VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID     V USERNAME      USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.49.24 AM                                         00080006000000EF I PENNY               2

23 rows selected.

2.恢复XID=000A000B000000F1的事务
注意:观察到x$ktuqqry的查询非常耗时，所以请注意评估你的恢复成本。
由于x$ktuqqry表的xid字段上不存在索引(很奇怪，而且我们不能自己添加，估计后续版本中会有改进)，查询该表会导致犬表扫描。
以下测试中，x$ktuqqry中存在大约19万记录，查询一次需要近6分钟。



SYS AS SYSDBA on 30-MAR-05 >select count(addr) from x$ktuqqry;  

COUNT(ADDR)
-----------
     196015

SYS AS SYSDBA on 30-MAR-05 >set autotrace on
SYS AS SYSDBA on 30-MAR-05 >SELECT /*+ rule */ UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
  2  WHERE XID = '000A000B000000F1';

UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');


8 rows selected.

Elapsed: 00:05:53.44

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED))




Statistics
----------------------------------------------------------
     393399  recursive calls
          0  db block gets
    1559016  consistent gets
       4388  physical reads
          0  redo size
       1069  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
      23164  sorts (memory)
          0  sorts (disk)
          8  rows processed

SYS AS SYSDBA on 30-MAR-05 >SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
  2  WHERE XID = '000A000B000000F1';

UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');


8 rows selected.

Elapsed: 00:05:55.30

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes=2008)
   1    0   FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED)) (Cost=25 Card=1 Bytes=2008)


Statistics
----------------------------------------------------------
     393454  recursive calls
          0  db block gets
    1562425  consistent gets
       4644  physical reads
          0  redo size
       1069  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
      23166  sorts (memory)
          0  sorts (disk)
          8  rows processed

SYS AS SYSDBA on 30-MAR-05 >

3.通过UNDO语句我们可以撤销该事物。
4.注意，在Oracle Database 11g中，需要启用supplemental log data才能够得到UNDO SQL。以下测试过程供参考：

SQL> connect / as sysdba
Connected.
SQL> alter database add supplemental log data;

Database altered.

SQL> connect eygle/eygle
Connected.

SQL> create table t as select username from dba_users where rownum < 6;

Table created.

SQL> select * from t;

USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DIP
ORACLE_OCM

5 rows selected.

SQL> delete from t where username='DIP';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select versions_xid,versions_operation,username from t versions between timestamp minvalue and maxvalue;

VERSIONS_XID	 V USERNAME
---------------- - ------------------------------
06001400D60C0000 D DIP
		   SYS
		   SYSTEM
		   OUTLN
		   DIP
		   ORACLE_OCM

6 rows selected.

SQL> select xid,operation,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('06001400D60C0000');

XID		 OPERATION			  UNDO_SQL
---------------- -------------------------------- ------------------------------------------------------------
06001400D60C0000 DELETE 			  insert into "EYGLE"."T"("USERNAME") values ('DIP');
06001400D60C0000 BEGIN

2 rows selected.

此处的UNDO_SQL显示为两行，其中一条为BEGIN，这是作为事务开始的标记。Oracle隐式的开始一个事务，在Reverse转换时，BEGIN被显示出来，而且在INSERT之后：

SQL> select xid,operation,undo_sql,undo_change# from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('06001400D60C0000');

XID		 OPERATION  UNDO_SQL							 UNDO_CHANGE#
---------------- ---------- ------------------------------------------------------------ ------------
06001400D60C0000 DELETE     insert into "EYGLE"."T"("USERNAME") values ('DIP'); 		    1
06001400D60C0000 BEGIN										    2

历史上的今天...
>> 2019-03-30文章:

2015 数据库Gartner市场份额-Oracle 45.6% 占据领先优势

>> 2017-03-30文章:

Oracle 11g 密码延迟认证与 library cache lock 等待

>> 2012-03-30文章:

Oracle Security Alert for CVE-2011-5035 OC4J

数据恢复:ORA-600 kccpb_sanity_check_2解决

>> 2011-03-30文章:

恩墨科技2011性能优化培训成功结束

>> 2009-03-30文章:

关于ocssd进程的三言两语

>> 2008-03-30文章: