Flashback闪回技术

1、Flashback Query闪回查询数据

原理：闪回查询使用的是undo表空间里存放的前映像

构造环境

conn le01/le01

drop table t5 purge;

create table t5 (x int);

insert into t5 values(1);

insert into t5 values(2);

insert into t5 values(3);

commit;

select * from t5;

2.为构造后续的闪回查询查询当前的时间和scn号

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

查询当前系统时间

LE01@LEO1 > select sysdate from dual;

 

SYSDATE

-------------------

2014-02-09 09:15:59

 

查询当前系统SCN号

LE01@LEO1 > select dbms_flashback.get_system_change_number from dual;  

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  954191

3、删除数据

delete from t5 where x=1;

commit;

LE01@LEO1 > select * from t5;

 

         X

----------

         2

         3

4、两种方法创建视图构造闪回查询删除之前的数据

1）第一种方法：使用时间戳来构造闪回查询视图

create view v_t5_timestamp as select * from t5

as of timestamp to_timestamp('2014-02-09 09:15:59','yyyy-mm-dd hh24:mi:ss');

 

2）第二种方法：使用SCN构造闪回查询视图

create view v_t5_scn as select * from t5 as of scn 954191;

注：scn 比 timestamp 更精确

查询视图闪回内容

select * from v_t5_timestamp;  

select * from v_t5_scn;

到此，两种构造视图的方法都顺利的获得了闪回查询的数据

5、一张表被反复多次删除，要求恢复到指定的数据版本

原理：清空回收站

purge recyclebin;       

create table t6 (x int);

insert into t6 values (1);

commit;

select * from t6; 

drop table t6;

create table t6 (x int);

insert into t6 values (1);

insert into t6 values (2);

commit;

select * from t6;

drop table t6;

查询回收站数据字典

select object_name,original_name,type from recyclebin;

show recyclebin

LE01@LEO1 > select object_name,original_name,type from recyclebin;

 

OBJECT_NAME                              ORIGINAL_N TYPE

---------------------------------------- ---------- ---------------

BIN$8fARJMqANI7gQKjAZAEakA==$0           T6         TABLE

BIN$8fARJMqBNI7gQKjAZAEakA==$0           T6         TABLE

 

LE01@LEO1 > show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

T6               BIN$8fARJMqBNI7gQKjAZAEakA==$0 TABLE        2014-02-09:09:23:23

T6               BIN$8fARJMqANI7gQKjAZAEakA==$0 TABLE        2014-02-09:09:23:13

LE01@LEO1 >

获得t6表被drop的两个版本中哪个是我们需要恢复的对象，恢复有1条记录的t6表

LE01@LEO1 > select * from "BIN$8fARJMqANI7gQKjAZAEakA==$0";

 

         X

----------

         1

 

LE01@LEO1 > select * from "BIN$8fARJMqBNI7gQKjAZAEakA==$0";

 

         X

----------

         1

         2

闪回指定的版本->闪回同时重命名

flashback table "BIN$8fARJMqANI7gQKjAZAEakA==$0" to before drop rename to t6_new;

LE01@LEO1 > select * from t6_new;

 

         X

----------

         1

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

转载于:http://blog.itpub.net/29330852/viewspace-1078335/