Validba'S Home

从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 演示到此结束.

 

 

 

 

 

 

CopyRight Validba'S Home 版权所有.本站为个人学习之用,评论时请遵守本国法律