Oracle的闪回技术–闪回错误的DML操作

166次阅读

共计 2784 个字符，预计需要花费 7 分钟才能阅读完成。

提交 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;

系统已更改。

实例测试：

目标：将 emp_bak 表中的员工的工资统一更新为 9999，提交后使用闪回技术回滚回初始值。

– 更新 emp_bak

SQL> update emp_bak set sal = 9999;

已更新 10 行。

SQL> commit;

提交完成。

– 查出更新操作对应的事务号

SQL> select  versions_xid,ename,sal from emp_bak
  2         versions between scn minvalue and maxvalue
  3         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 之前的一个状态。

附录：

除了根据 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';