20090918_oralce 042 试题27 关于 Flashback Transaction query

27. Which two statements about Flashback Query are true? (Choose two.)

A) It is generated by using the redo log files.

B) It helps in row-level recovery from user errors.

C) It can be performed to recover ALTER TABLE statements

D) It fails when undo data pertaining to the transaction is overwritten.

E) The database has to be opened with the resetlogs option after performing Flashback Query.

例子如下:

从9i简单的flashback query到10G的flashback database、flashback drop、flashback version query 、flashback transaction query。oracle一直在完善flashback。尤其是flashback transaction query可以用来恢复一个事务。要想完成这个工作,必须通过两步实现:

    1、通过flashback version query 技术查看某张表过去某个时刻发生的事务的XID;

    2、根据事务ID,再在flashback_transaction_query视图中找过需要undo的sql,回滚事务;

 

必须注意:flashback query、flashback transaction query都依赖于undo,如果undo过期或者被覆盖,乃么这个恢复也将最终以失败告终.看操作

 

SQL> connect mayp/mayp
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
————————————————————
P
T
TEST
TEST1
TEST2
SQL> desc t    
 Name                                                                                                              Null?    Type
 —————————————————————————————————————– ——– —————————————————————————-
 NAME                                                                                                                       VARCHAR2(10)
 AGE                                                                                                                        NUMBER
 SEX                                                                                                                        NUMBER

SQL> select name,versions_xid,versions_starttime,versions_endtime,versions_operation from t versions between timestamp minvalue and maxvalue;

NAME                 VERSIONS_XID     VERSIONS_STARTTIME                                                          VERSIONS_ENDTIME                                                     VE
——————– —————- ————————————————————————— ————————————————————————— –
AAA
bbb
ccc

目前表t上没有发生的事务信息,下面做一个事务:

SQL> insert into t values(’4545′,4,5);

SQL> insert into t values(’4545′,4,5);

SQL> insert into t values(’4545′,4,5);

SQL> insert into t values(’4545′,4,5);

SQL> commit;

 

SQL> set feedback on
SQL> select name,versions_xid,versions_starttime,versions_endtime,versions_operation from t versions between timestamp minvalue and maxvalue;

NAME                 VERSIONS_XID     VERSIONS_STARTTIME                                                          VERSIONS_ENDTIME                                                     VE
——————– —————- ————————————————————————— ————————————————————————— –
4545                 06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
454554               06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
454554               06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
454554               06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
AAA
bbb
ccc

7 rows selected.

可以发现最近发生的事务.

 

SQL> insert into t values(’dfdd’,3,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select name,versions_xid,versions_starttime,versions_endtime,versions_operation from t versions between timestamp minvalue and maxvalue;

NAME                 VERSIONS_XID     VERSIONS_STARTTIME                                                          VERSIONS_ENDTIME                                                     VE
——————– —————- ————————————————————————— ————————————————————————— –
dfdd                 01001200E4EA0000 08-JUL-08 04.17.22 PM                                                                                                                            I
4545                 06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
454554               06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
454554               06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
454554               06000C00B3C10000 08-JUL-08 04.16.43 PM                                                                                                                            I
AAA
bbb
ccc

8 rows selected.

 

现在发现了两笔事务,是否是同一个事务,根据VERSIONS_XID  判断。

 

现在需要回滚XID=06000C00B3C10000 的事务;通过查询FLASHBACK_TRANSACTION_QUERY视图获取undo sql

 

SQL> desc flashback_transaction_query
 Name                                                                                                              Null?    Type
 —————————————————————————————————————– ——– —————————————————————————-
 XID                                                                                                                        RAW(8)
 START_SCN                                                                                                                  NUMBER
 START_TIMESTAMP                                                                                                            DATE
 COMMIT_SCN                                                                                                                 NUMBER
 COMMIT_TIMESTAMP                                                                                                           DATE
 LOGON_USER                                                                                                                 VARCHAR2(30)
 UNDO_CHANGE#                                                                                                               NUMBER
 OPERATION                                                                                                                  VARCHAR2(32)
 TABLE_NAME                                                                                                                 VARCHAR2(256)
 TABLE_OWNER                                                                                                                VARCHAR2(32)
 ROW_ID                                                                                                                     VARCHAR2(19)
 UNDO_SQL                                                                                                                   VARCHAR2(4000

 

SQL> select undo_sql from flashback_transaction_query where xid=hextoraw(’06000C00B3C10000′);

UNDO_SQL
—————————————————————————————————-
delete from "MAYP"."T" where ROWID = ‘AAAiqrAANAAAAUcAAG’;
delete from "MAYP"."T" where ROWID = ‘AAAiqrAANAAAAUcAAF’;
delete from "MAYP"."T" where ROWID = ‘AAAiqrAANAAAAUcAAE’;
delete from "MAYP"."T" where ROWID = ‘AAAiqrAANAAAAUcAAD’;

 

可以插入的4条insert可以用4条delete来undo.

 

flashback transaction query 演示到此结束.

 

其他参考： http://www.wangchao.net.cn/bbsdetail_1776485.html

          http://space.itpub.net/111631/viewspace-607506

          http://www.cnblogs.com/gkl0818/archive/2009/02/08/1386513.html