如何快速、及时、准确的闪回DML提交后的数据

生产环境中，由于表的数据量非常大，我们或许只错误的删除了其中一条数据。如果我们用flashback query+flashback table是非常得不偿失的，不光有可能丢失一部分后来的数据，而且也不一定能闪回成功。在这种细粒度的闪回上，我们更应该使用flashback version+flashback transaction的方式来实现。由于存储在undo中数据的时间受很多因素，当出现问题的时候，我们应该尽量让undo存储的数据时间更长些，已备我们有flashback的充足时间。

[oracle@rhel2 ~]$ sqlplus system/oracle

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Feb 7 00:09:03 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create table test as select * from dba_extents;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
      7134

SQL> delete from test where rowid='AAANBbAABAAAPOiAAG';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
      7133

SQL> alter tablespace UNDOTBS1 retention GUARANTEE;//确保自提交后undo_retention时间内undo里的数据不会被清除

Tablespace altered.

SQL> alter tablespace UNDOTBS2 retention GUARANTEE;

Tablespace altered.

SQL> show parameters undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2
SQL> show parameter retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
undo_retention                       integer     0
SQL> alter system set undo_retention=9000;//增大undo_retention的值，让在Undo里的数据保存更长时间

System altered.

SQL> create undo tablespace undotmp01 datafile '+DG1' size 10m autoextend on next 10m;

Tablespace created.

SQL> create undo tablespace undotmp02 datafile '+DG1' size 10m autoextend on next 10m;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTMP01' scope=memory sid='ractest2';//将创建好的临时undo tablespace设置为当前Undo，这也是为了确保以前的undo数据能够保存更长时间，而不会被覆盖。数据恢复完成后再切换回原始的undo表空间，当然临时undo也不必删除，可供以后出现类似情况更快速的完成切换。

System altered.

SQL> alter system set undo_tablespace='UNDOTMP02' scope=memory sid='ractest1';

System altered.

SQL> set linesize 200;
SQL> col versions_operation format a10;
SQL> col versions_xid format a20;

SQL> select versions_operation,
  2         versions_xid,
  3         rowid
  4    from test versions between timestamp minvalue and maxvalue
  5   where versions_operation = 'D';                            //flashback version

VERSIONS_O VERSIONS_XID         ROWID
---------- -------------------- ------------------
D          0E001F0058000000     AAANBbAABAAAPOiAAG

SQL> select undo_sql
  2    from flashback_transaction_query
  3   where peration = 'DELETE'
  4     and xid = hextoraw('0E001F0058000000');                  //flashback transaction

UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYSTEM"."TEST"("OWNER","SEGMENT_NAME","PARTITION_NAME","SEGMENT_TYPE","TABLESPACE_NAME","EXTENT_ID","FILE_ID","BLOCK_ID","BYTES","BLOCKS","RELATIVE_FNO") values ('SYS','SYS_LOB0000037868C
00005$$',NULL,'LOBSEGMENT','SYSTEM','0','1','44249','65536','8','1');

SQL> insert into "SYSTEM"."TEST"("OWNER","SEGMENT_NAME","PARTITION_NAME","SEGMENT_TYPE","TABLESPACE_NAME","EXTENT_ID","FILE_ID","BLOCK_ID","BYTES","BLOCKS","RELATIVE_FNO") values ('SYS','SYS_LOB0000037868C00005$$',NULL,'LOBSEGMENT','SYSTEM','0','1','44249','65536','8','1');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
      7134

SQL>

至此，由用户操作错误删除的一条数据就还原成功了，之后操作的数据也没有任何丢失。

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

转载于:http://blog.itpub.net/23135684/viewspace-625642/