oracle提供的闪回事务功能,可以闪回单个事务,也可以闪回相关联的事务。闪回事务由dbms_flashback包完成。
一、闪回查询
1、查询当前的SCN。
SQL> select dbms_flashback.get_system_change_number from dual;
2、查询过去的状态。
首先是,利用dbms_flashback.enable_at_system_change_number或dbms_flashback.enable_at_time来使得状态回退到指定状态下的值。
然后,所查询的数据都是该状态之下的值,也就是过去状态下的值。此时,不能进行任何的DML或DDL操作。
利用dbms_flashback.disable恢复到当前状态。
SQL> select dbms_flashback.get_system_change_number from dual;---查询更新前的状态:SCN为2693789
GET_SYSTEM_CHANGE_NUMBER
------------------------
2693789
SQL> select * from t where id =1;--查询更新前的状态:此时ID为1的NUM为100;
ID NUM
---------- ----------
1 100
SQL> update t set num=300 where id =1;--更新ID为1的NUM为300;
已更新 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_flashback.enable_at_system_change_number(2693789);---把当前的环境回退到SCN为2693789的时间上,
---在这个状态下,你所查询的都是这个时间点上的数据;
---同时需要注意的是,在没有执行DISABLE之前,不能进行DML和DDL操作。
PL/SQL 过程已成功完成。 ---查询的时候,不需要AS OF 或者VERSIONS BETWEEN之类的语句,直接查询即可
SQL> select * from t where id =1;--查询当下状态的ID为1时的NUM值为100,也就是更新前状态的值。
ID NUM
---------- ----------
1 100
SQL> exec dbms_flashback.disable;---让环境的状态恢复到现在的状态。
PL/SQL 过程已成功完成。
SQL> select * from t where id=1;---查看现在的值,显然是更新后状态的值。
ID NUM
---------- ----------
1 300
二、闪回事务
闪回事务需要使用dbms_flashback.flashback_backout()这个存储过程来实现。这个存储过程的原理是:自动分析重做日志,挖掘出变更前的值用以构建撤销SQL(Undo SQL),然后执行撤销SQL最后达到撤销事务的目的。为了该功能可以正常使用,至少需要事先启用主键补充日志。另外,为了能够跟踪外键依赖还需要启用外键补充日志。执行了该存储过程之后,并没有把相应的事务马上恢复,还需要commit才能提交,然后才会事务闪回。期间,跟事务相关的行和表都会以适当的方式加上锁,其他事务不能对其进行修改,知道提交了commit或roll back操作。
闪回事务的一般逻辑为:
(1)利用flashback_transaction_query来确定需要闪回的事务;
(2)将需要撤销的事务的事务号或事务名载入对应的VARRAY集合变量。
(3)以NOCASCADE方式调用BACKOUT_TRANSACTION。如果报错,再从另外3种方式中选择一个调用BACKOUT_TRANSACTION。
(4)查看闪回事务操作的报告。
(5)最后决定提交或回滚。
另外,在这里需要的是事务的依赖性。比如,两个事务TX1和TX2,若符合以下3个条件的任意一个就可以认为TX2依赖TX1:
(1)WAW依赖(Write After Write),即在TX1修改了表的某行之后,TX2又修改了同一行。
(2)主键依赖,即在一张拥有主键的表中TX1首先删除了一行,之后TX2又插入了具有相同主键值的另一行。
(3)外建依赖,即由于TX1的修改(insert或update)而产生了新的可被外键参考的字段值,之后TX2修改(insert或update)外键字段时利用了TX1所产生的字段值。
对于事务依赖,oracle 提供了cascade、nocascade、nocascade_force、nocascade_only四种策略。假设被撤销的事务是TX1,若其具有依赖事务,则称为TX2:
(1)NOCASCADE,TX1不可以被任何其他事务依赖(即TX2不存在),否则撤销操作报错。
(2)CASCADE,将TX1连同TX2一起撤销。
(3)NOCASCADE_FORCE,忽略TX2,直接执行TX1的撤销SQL将TX1撤销,如果没有约束上的冲突,操作将成功,否则约束报错导致撤销操作失败。
(4)NONCONFILICT_ONLY,在不影响TX2的前提下,撤销TX1的修改。与NOCASCADE_FORCE的不同点在于会首先过滤一下TX1的撤销SQL,确保它们不会作用在TX2修改的行上。
下面以WAW来解析一下上面的四种策略的区别。
SQL> select * from t;----更新前数据
ID NUM
---------- ----------
1 100
2 200
3 300
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER----更新前SCN
------------------------
2698173
SQL> update t set num =4000;---事务一:T1
已更新3行。
SQL> commit;
提交完成。
SQL> begin
2 update t set num=6000 where id>1;----事务二:T2
3 commit;
4 end;
5 /
PL/SQL 过程已成功完成。
这个时候,如果采用NOCASCADE策略的话,将会报错,因为WAW依赖。
SQL> declare
2 xids sys.xid_array;
3 begin
4 xids :=sys.xid_array('080013004B060000');
5 dbms_flashback.transaction_backout(1,xids, dbms_flashback.nocascade);
6 end;
7 /
declare
*
第 1 行出现错误:
ORA-55504: NOCASCADE 模式下的事务处理冲突
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 37
ORA-06512: 在 "SYS.DBMS_FLASHBACK", line 70
ORA-06512: 在 line 5
而其实,使用CASCADE的话,两个事务都会被回滚。
SQL> declare
2 xids sys.xid_array;
3 begin
4 xids :=sys.xid_array('080013004B060000');
5 dbms_flashback.transaction_backout(1,xids, dbms_flashback.cascade);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select * from t;
ID NUM
---------- ----------
1 100
2 200
3 300
接下来,需要比较的就是nocascade_force和 两个的区别。看下下面的例子,可以发现两者的结果是一样的。不过,其实是不一样的。nocascade_force执行了所有的undo语句,而nonconfilict_only已经过滤掉了事务中有交叉的数据行,为此只会执行不同的那个undo语句。
SQL> declare
2 xids sys.xid_array;
3 begin
4 xids :=sys.xid_array('09001E006F060000');
5 dbms_flashback.transaction_backout(1,xids, dbms_flashback.nocascade_force);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select * from t2;
ID NUM
---------- ----------
1 100
2 6000
3 6000
SQL> declare
2 xids sys.xid_array;
3 begin
4 xids :=sys.xid_array('09001E006F060000');
5 dbms_flashback.transaction_backout(1,xids, dbms_flashback.nonconfilict_only);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select * from t2;
ID NUM
---------- ----------
1 100
2 6000
3 6000
最后,演示一下整个流程:
首先,在更新前表T3的ID为2的NUM为6000,然后先后更新了两次,依次为10000,20000.现在需要恢复到原来所有更新前的状态。
SQL> select * from t3;
ID NUM
---------- ----------
2 6000
3 6000
SQL> begin
2 update t3 set num=10000 where id=2;
3 commit;
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> update t3 set num=20000 where id =2;
已更新 1 行。
SQL> commit;
提交完成。
然后,第二步我们查看flashback_transaction_query来确定需要闪回的事务ID,显然是0300150069060000,应为其对应的undo_sql实现之后的结果刚好是6000,也就是更新前的状态。
SQL> select xid,start_scn,commit_scn,operation,undo_sql from flashback_transaction_query where table_name ='T3' order by commit_scn;
XID START_SCN COMMIT_SCN OPERATION UNDO_SQL
-------------------- ---------- ---------- -------------------------------- ----------------------------------------
0300150069060000 2728804 2728805 UPDATE update "SH"."T3" set "NUM" = '6000' wher
e ROWID = 'AAASbqAAEAAAAJjAAA';
06001D0094060000 2728816 2728818 UPDATE update "SH"."T3" set "NUM" = '10000' whe
re ROWID = 'AAASbqAAEAAAAJjAAA';
第三步,闪回该事务0300150069060000。
SQL> declare
2 xids sys.xid_array;
3 begin
4 xids :=sys.xid_array('0300150069060000');
5 dbms_flashback.transaction_backout(1,xids,dbms_flashback.cascade);
6 end;
7 /
PL/SQL 过程已成功完成。
第四步,查看dba_flashback_txn_state或dba_flashback_txn_report来确定是否真的闪回了该事务。下面我们查看下dba_flashback_txn_state。可以发现,该事务 0300150069060000已经被闪回,同时还闪回了有关联的事务06001D0094060000,也就是第二次update操作。
SQL> select xid,dependent_xid,backout_mode from dba_flashback_txn_state;
XID DEPENDENT_XID BACKOUT_MODE
-------------------- ---------------- ----------------
06001D0094060000 CASCADE
0300150069060000 06001D0094060000 CASCADE
当然现在也可以查看表当前数据的状态来确认,是否闪回成功了。从下面的查询来看,id为2的num的确为6000闪回成功。
SQL> select * from t3;
ID NUM
---------- ----------
2 6000
3 6000
第五步,提交commit;
SQL>commit;
最后结果为ID为2的NUM为6000;
SQL> select * from t3;
ID NUM
---------- ----------
2 6000
3 6000