flashback transaction query

flashback transaction query也是使用UNDO信息来实现。利用这个功能可以查看某个事务执行的所有变化，它需要访问flashback_transaction_query 视图，这个视图的XID列代表事务ID，利用这个ID可以区分特定事务发生的所有数据变化。

 

1.环境准备

sys@ORCL>create table zhong2(zhong int);

Table created.

sys@ORCL>insert into zhong2 values(100);

1 row created.

sys@ORCL>insert into zhong2 values(200);

1 row created.

sys@ORCL>commit;

Commit complete.

 

sys@ORCL>update zhong2 set zhong=2000 where zhong=200;

1 row updated.

sys@ORCL>commit ;

Commit complete.

 

sys@ORCL>insert into zhong2 values(3);

1 row created.

sys@ORCL>commit;

Commit complete.

sys@ORCL>select * from zhong2;

     ZHONG

----------

       100

      2000

         3

2.查看事务

----flashback version query

sys@ORCL>select versions_starttime, versions_endtime, versions_xid, versions_operation,zhong from zhong2 versions between scn minvalue and maxvalue  order by versions_starttime;

 

VERSIONS_STARTTIME     VERSIONS_ENDTIME         VERSIONS_XID     V      ZHONG

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

02-MAY-16 07.14.05 AM    02-MAY-16 07.14.50 AM      0F000100B4000000 I        200

02-MAY-16 07.14.05 AM                              0F000100B4000000 I        100

02-MAY-16 07.14.50 AM                              0F001B00B4000000 U       2000

02-MAY-16 07.15.08 AM                              10001400B6000000 I          3

----有时候可以根据需要查询获取想XID，然后在去查询对应的undp_sql。

 

----flashback transaction query

Select xid,operation,commit_scn,undo_sql from flashback_transaction_query

  where xid in (Select versions_xid from zhong2

                     versions between scn minvalue and maxvalue);

 

sys@ORCL>set linesize 200

sys@ORCL>/

 

XID              OPERATION                        COMMIT_SCN

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

UNDO_SQL

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

10001400B6000000 UNKNOWN                             2691613

10001400B6000000 BEGIN                               2691613

0F001B00B4000000 UNKNOWN                             2691605

0F001B00B4000000 BEGIN                               2691605

0F000100B4000000 UNKNOWN                             2691087

0F000100B4000000 UNKNOWN                             2691087

0F000100B4000000 BEGIN                               2691087

7 rows selected.

3.开启追加日志

------发现上面查询结果中没有undo_sql，并且operation列也是unknown，原因是没有开启追加日志（跟踪日志）

sys@ORCL>alter database add supplemental log data;

Database altered.

 

sys@ORCL>select log_mode,flashback_on,open_mode,supplemental_log_data_min from v$database;

 

LOG_MODE     FLASHBACK_ON       OPEN_MODE            SUPPLEME

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

NOARCHIVELOG NO                 READ WRITE           YES

---- 关追加日志 alter database drop supplemental log data;

4.再次插入/修改数据

sys@ORCL>insert into zhong2 values (1000);

1 row created.

sys@ORCL>insert into zhong2 values (2000);

1 row created.

sys@ORCL>commit;

Commit complete.

 

sys@ORCL>update zhong2 set zhong=999 where zhong=100;

1 row updated.

sys@ORCL>commit;

Commit complete.

sys@ORCL>select * from zhong2;

     ZHONG

----------

       999

      2000

         3

      1000

      2000

 

5.再次查看事务操作

----开启追加日志后，undo_sql，operation有记录

sys@ORCL>Select xid,operation,commit_scn,undo_sql from flashback_transaction_query where xid in (Select versions_xid from zhong2 versions between scn minvalue and maxvalue);

 

XID              OPERATION                        COMMIT_SCN

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

UNDO_SQL

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

10001400B6000000 UNKNOWN                             2691613

 

10001400B6000000 BEGIN                               2691613

 

0C000700B8000000 UPDATE                              2694272

update "SYS"."ZHONG2" set "ZHONG" = '100' where ROWID = 'AAAWG3AABAAAXcRAAA';

0C000700B8000000 BEGIN                               2694272

 

0F001B00B4000000 UNKNOWN                             2691605

 

0F001B00B4000000 BEGIN                               2691605

 

0F000100B4000000 UNKNOWN                             2691087

 

0F000100B4000000 UNKNOWN                             2691087

 

0F000100B4000000 BEGIN                               2691087

 

15000E00BF000000 INSERT                              2694252

delete from "SYS"."ZHONG2" where ROWID = 'AAAWG3AABAAAXcRAAE';

15000E00BF000000 INSERT                              2694252

delete from "SYS"."ZHONG2" where ROWID = 'AAAWG3AABAAAXcRAAD';

15000E00BF000000 BEGIN                               2694252

12 rows selected.

 

6.利用flashback transaction query找回误操作

6.1 当追加日志开启的情况下恢复误操作

----有时候可以根据需要查询获取想XID，然后在去查询对应的undp_sql。

----（1）先查找XID值

sys@ORCL>select versions_starttime, versions_endtime, versions_xid, versions_operation,zhong from zhong2 versions between scn minvalue and maxvalue  order by versions_starttime;

 

VERSIONS_STARTTIME    VERSIONS_ENDTIME          VERSIONS_XID     V      ZHONG

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

02-MAY-16 07.14.05 AM   02-MAY-16 07.14.50 AM      0F000100B4000000 I        200

02-MAY-16 07.14.05 AM   02-MAY-16 08.19.04 AM      0F000100B4000000 I        100

02-MAY-16 07.14.50 AM                             0F001B00B4000000 U       2000

02-MAY-16 07.15.08 AM                             10001400B6000000 I          3

02-MAY-16 08.18.25 AM                             15000E00BF000000 I       2000

02-MAY-16 08.18.25 AM                             15000E00BF000000 I       1000

02-MAY-16 08.19.04 AM                             0C000700B8000000 U        999

7 rows selected.

 

----（2）假如误操作为update更新为999的那句，进行恢复（前面没有开启追加日志的所以看不到undo_sql）

----查询比较慢，耐心等待

sys@ORCL>select operation,undo_sql from flashback_transaction_query where xid='0C000700B8000000';

OPERATION

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

UNDO_SQL

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

UPDATE

update "SYS"."ZHONG2" set "ZHONG" = '100' where ROWID = 'AAAWG3AABAAAXcRAAA';

BEGIN

 

----（3）执行修正语句

sys@ORCL>update "SYS"."ZHONG2" set "ZHONG" = '100' where ROWID = 'AAAWG3AABAAAXcRAAA';

1 row updated.

 

sys@ORCL>select * from zhong2;

 

     ZHONG

----------

       100

      2000

         3

      1000

      2000

 

sys@ORCL>commit;

Commit complete.

 

6.2 追加日志未开启，恢复误操作

----假如，之前没有开启追加日志，然后又做了误操作，怎么去恢复数据呢？

----先找到误操作时间点，然后创建一个恢复表，将数据恢复到该表中

 

----例如，之前版本查询中，一条修改为2000的语句：

----flashback version query

sys@ORCL>select versions_starttime, versions_endtime, versions_xid, versions_operation,zhong from zhong2 versions between scn minvalue and maxvalue  order by versions_starttime;

 

VERSIONS_STARTTIME     VERSIONS_ENDTIME         VERSIONS_XID     V      ZHONG

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

02-MAY-16 07.14.05 AM    02-MAY-16 07.14.50 AM      0F000100B4000000 I        200

02-MAY-16 07.14.05 AM                              0F000100B4000000 I        100

02-MAY-16 07.14.50 AM                            0F001B00B4000000 U       2000

02-MAY-16 07.15.08 AM                              10001400B6000000 I          3

----flashback transaction query

sys@ORCL>select operation,undo_sql from flashback_transaction_query where xid='0C000700B8000000';

OPERATION

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

UNDO_SQL

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

UNKNOWN

BEGIN

 

从flashback transaction query中看不到undo_sql，operation也未unknown

 

这时要恢复误操作可以利用flashback query方式：

----从flashback version query中确定误操作时间，然后闪回查询，确实是否是我们需要更改的数据

sys@ORCL>select * from zhong2 as of timestamp to_timestamp('2016-05-02 07:14:50','yyyy-mm-dd hh24:mi:ss');

     ZHONG

----------

       100

       200

 

----确定之后，创建一个用于存放数据的表。不用用原表，原表还有该时间点之后操作的事务

sys@ORCL>create table zhong_res as  select * from zhong2 as of timestamp to_timestamp('2016-05-02 07:14:50','yyyy-mm-dd hh24:mi:ss');

Table created.

 

----查看恢复的数据内容

sys@ORCL>select * from zhong_res;

      ZHONG

----------

       100

       200

----现在数据找回了，可以根据情况，导入原表

sys@ORCL>select * from zhong2;

     ZHONG

----------

       100

      2000

         3

      1000

      2000

 

 

flashback transaction query 查询慢 处理：

reference                http://www.killdb.com/2012/11/20/flashback_transaction_query-%e6%9f%a5%e8%af%a2%e6%85%a2%e7%9a%84%e9%97%ae%e9%a2%98.html