eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

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

具备了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

历史上的今天...
    >> 2017-03-30文章:
    >> 2012-03-30文章:
    >> 2011-03-30文章:
    >> 2009-03-30文章:
           关于ocssd进程的三言两语
    >> 2008-03-30文章:
           resize datafile 与 checkpoint
    >> 2006-03-30文章:
           广告: 招聘SQL SERVER DBA

无觅

By eygle on 2005-03-30 10:55 | Comments (0) | Backup&Recovery | 231 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com