Oracle的闪回技术--闪回错误的DML操作
提交DML操作后,该操作使用的还原段就可以被其它对象使用了,为了保证闪回操作时这些数据仍然被保存在还原段中,可能需要重新设置undo_retention参数,表示一个事务提交后,该事务的数据必须保存在还原段中的时间;但是这也并不能完全保证指定的时间的数据一定能够被恢复,还原表空间没有足够的时间时,仍会覆盖要求保留的磁盘空间。
查看undo_retention的当前值:
SQL> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900
默认是900s
设置undo_retention的值为2个小时
SQL> alter system set undo_retention = 7200; 系统已更改。
实例测试1,根据SCN号闪回:
目标:将emp_bak表中的员工的工资统一更新为9999,提交后使用闪回技术回滚回初始值。
--更新emp_bak
SQL> update emp_bak set sal = 9999; 已更新10行。
SQL> commit; 提交完成。
--查出更新操作对应的事务号
SQL> select versions_xid,ename,sal from emp_bak versions between scn minvalue and maxvalue where empno = 7900; VERSIONS_XID ENAME SAL ---------------- ---------- --------- 08001D0018060000 JAMES 9999.00 JAMES 950.00
versions_xid为事务号,minvalue为最小值,maxvalue为最大值。
--从数据字典flashback_transaction_query中查询出事务号所对应的SCN号
select start_scn from flashback_transaction_query 2 where xid = hextoraw('08001D0018060000'); START_SCN ---------- 2206522 2206522 2206522 2206522 2206522 2206522 2206522 2206522 已选择8行。
--启用行移动功能, 否则不能闪回表
SQL> alter table scott.emp_bak enable row movement; 表已更改。
--闪回表
SQL> flashback table scott.emp_bak to scn 2206522; 闪回完成。 SQL> select sal from scott.emp_bak where empno=7900; SAL ---------- 950
闪回已经完成了。
注意闪回是闪回到该SCN之前的一个状态。
如果查询flashback_transaction_query视图中的列operation全是unknown 而且undo_sql 也为空,要开启对DML更改引用的列值和主键值的日志记录
SQL> alter database add supplemental log data (primary key) columns;
再查询就可以见到了
SQL> select start_scn,operation,undo_sql from flashback_transaction_query 2 where xid = hextoraw('05000C00DE060000'); START_SCN OPERATION UNDO_SQL ---------- -------------------------------- -------------------------------------------------------------------------------- 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAT'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAS'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAR'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAQ'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAP'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAO'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAN'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAM'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAL'; 2210297 UPDATE update "SCOTT"."EMP_BAK" set "JOB" = NULL where ROWID = 'AAASNGAAEAAAADrAAK';
实例测试2,根据时间闪回:
1. 利用Flashback Table特性
需要开启行移动
alter table test enable row movement;
flashback table test to timestamp to_timestamp('20171018234845','yyyymmddHH24miss');
2. 利用Flasback Query特性,这种方法最通用且实用
不需要开启行移动,直接查询出不同时间的数据,并保存在临时表中
create table test_tmp as select * from test as of timestamp to_timestamp('20171018234845','yyyymmddHH24miss');