ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 闪回技术——闪回错误的DML操作——基于undo段

闪回技术——闪回错误的DML操作——基于undo段

原创 Linux操作系统 作者:oracleclub 时间:2011-11-30 13:25:19 0 删除 编辑
闪回错误的DML操作——基于undo段

undo段中undo_retention参数,单位秒,表示提交后在undo段中保留时间

SQL> conn / as sysdba
Connected.
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

SQL> alter system set undo_retention = 7200;——两个小时

System altered.

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     7200

保留两小时,其实不能保证两个小时内的提交的DML一定能恢复,因为undo tablespace中没空间时会占用undo_retention要求保留的磁盘空间,这保留的空间会被刷掉。
这参数是针对整个数据库的,设太大会占用磁盘空间(undo表空间)
提示:安全和效率是矛盾的,要折衷。
例子:
===================================

SQL> conn scott/tiger
Connected.

SQL> update dept set loc='bj';

4 rows updated.

查看刚刚的DML的相关信息:
SQL> select versions_xid,deptno,dname,loc
  2  from dept
  3  versions between scn  minvalue and maxvalue
  4  where deptno=10;

VERSIONS_XID         DEPTNO DNAME          LOC
---------------- ---------- -------------- -------------
                         10 ACCOUNTING     NEW YORK

versions_xid为空,因为刚刚的DML没提交

SQL> commit;

Commit complete.

SQL> select versions_xid,deptno,dname,loc
  2  from dept
  3  versions between scn  minvalue and maxvalue
  4  where deptno=10;

VERSIONS_XID         DEPTNO DNAME          LOC
---------------- ---------- -------------- -------------
0A000300D0000000         10 ACCOUNTING     bj
                         10 ACCOUNTING     NEW YORK
提交后,VERSIONS_XID有值了
===================================

SQL> conn / as sysdba
Connected.
SQL> col operation format a10
SQL> col undo_sql format 99999;
SQL> set pagesize 100;


用flashback_transaction_query查看undo_sql和commit的DML操作
SQL> select undo_sql,operation
  2  from flashback_transaction_query
  3  where xid=hextoraw('0A000300D0000000');——DML操作的versions_xid

UNDO_SQL
--------------------------------------------------------------------------------
OPERATION
----------
update "SCOTT"."DEPT" set "LOC" = 'BOSTON' where ROWID = 'AAAMfKAAEAAAAAQAAD';
UPDATE

update "SCOTT"."DEPT" set "LOC" = 'CHICAGO' where ROWID = 'AAAMfKAAEAAAAAQAAC';
UPDATE

update "SCOTT"."DEPT" set "LOC" = 'DALLAS' where ROWID = 'AAAMfKAAEAAAAAQAAB';
UPDATE

update "SCOTT"."DEPT" set "LOC" = 'NEW YORK' where ROWID = 'AAAMfKAAEAAAAAQAAA';
UPDATE
===================================



利用SCN号进行闪回:

SQL> select START_SCN,operation ——利用刚刚的versions_xid查对应的SCN
  2  from flashback_transaction_query
  3  where xid=hextoraw('0A000300D0000000');——DML操作的versions_xid

 START_SCN OPERATION
---------- ----------
    509098 UPDATE
    509098 UPDATE
    509098 UPDATE
    509098 UPDATE


SQL> flashback table scott.dept to SCN 509098;
flashback table scott.dept to SCN 509098
                      *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

报错是正常的!要先打开行移动功能(Oracle默认是关闭的)
SQL> alter table scott.dept enable row movement;

Table altered.

SQL> flashback table scott.dept to SCN 509098;

Flashback complete.

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
===================================


利用时间进行闪回,原理:把SCN号做个转换而已,改一下语句语法
SQL> conn scott/tiger
Connected.
SQL> ho date
Wed Nov 30 12:49:11 CST 2011——记下修改前的时间,方便恢复

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update dept set loc = 'shenzhen';

4 rows updated.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     shenzhen
        20 RESEARCH       shenzhen
        30 SALES          shenzhen
        40 OPERATIONS     shenzhen

SQL> flashback table scott.dept
  2  to timestamp  to_timestamp('2011-11-30 12:49:11','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select * from dept;——成功回到那个修改前的时间点

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

===================================


实际:进行大量的DML操作,很难知道误操作的DML时间,所以多退些时间。但是会丢失不少数据,所以用SCN定位个更保险。要丢数据,都丢少一点比较好。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26465805/viewspace-712421/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-22

  • 博文量
    61
  • 访问量
    82153