Oracle 闪回特性(Flashback Query、Flashback Table)

来源：互联网发布：mac怎么给文件夹改名编辑：程序博客网时间：2024/06/05 01:11

--==================================================

-- Oracle 闪回特性(Flashback Query、Flashback Table)

--==================================================

Oracle 闪回查询是指针对特定的表来查询特定的时间段内的数据变化情况来确定是否将表闪回到某一个特定的时刻以保证数据无讹误存在。

这个特性大大的减少了采用时点恢复所需的工作量以及数据库脱机的时间。闪回查询通常分为Flashback Query(闪回查询)，

Flashback Table Query(闪回表查询)，Flashback Version Query(闪回版本查询)，Flashback Transaction Query(闪回事务查询)。本文主要讲

述Flashback Query(闪回查询)，Flashback Table Query(闪回表查询)。其余闪回请参考后续文章。

一、Flashback Query(闪回查询)

通常用于检索一条记录的所有版本，倒退单独的事务或者倒退从指定时间以来对特定表的所有变化

Flashback Query的所有形式取决于UNDO表表空间，关于UDNO表空间请参考：Oracle 回滚(ROLLBACK)和撤销(UNDO)

1.闪回查询(Flashback Query)语法

SELECT <column_name_list>

FROM <table_name>

AS OF <SCN> --使用as of scn

[WHERE <filter_conditions>]

[GROUP BY <unaggregated columns>]

[HAVING <group_filter>]

[ORDER BY <column_positions_or_name>]

SELECT <column_name_list>

FROM <table_name>

AS OF <TIMESTAMP> --使用as of timestamp

[WHERE <filter_conditions>]

[GROUP BY <unaggregated columns>]

[HAVING <group_filter>]

[ORDER BY <column_positions_or_name>]

2.演示闪回查询

a.演示使用as of timestamp来进行闪回查询

flasher@ORCL11G> create table tb1 as

2 select empno,ename,job,deptno from scott.emp where 1=0;

flasher@ORCL11G> insert into tb1

2 select empno,ename,job,deptno

3 from scott.emp where empno in(7369,7499,7521,7566);

flasher@ORCL11G> commit;

flasher@ORCL11G> select * from tb1;

EMPNO ENAME JOB DEPTNO

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

7369 SMITH CLERK 20

7499 ALLEN SALESMAN 30

7521 WARD SALESMAN 30

7566 JONES MANAGER 20

flasher@ORCL11G> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY'

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

2010-10-25 17:26:08

flasher@ORCL11G> delete from tb1 where job='SALESMAN';

flasher@ORCL11G> commit;

flasher@ORCL11G> select * from tb1;

EMPNO ENAME JOB DEPTNO

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

7369 SMITH CLERK 20

7566 JONES MANAGER 20

flasher@ORCL11G> select * from tb1 as of timestamp

2 to_timestamp('2010-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss');

EMPNO ENAME JOB DEPTNO

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

7369 SMITH CLERK 20

7499 ALLEN SALESMAN 30

7521 WARD SALESMAN 30

7566 JONES MANAGER 20

flasher@ORCL11G> select * from tb1 as of timestamp

2 to_timestamp('2010-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss')

3 minus select * from tb1;

EMPNO ENAME JOB DEPTNO

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

7499 ALLEN SALESMAN 30

7521 WARD SALESMAN 30

b.演示使用as of scn来进行闪回查询

flasher@ORCL11G> select current_scn from v$database;

CURRENT_SCN

-----------

2032782

flasher@ORCL11G> select * from tb1;

EMPNO ENAME JOB DEPTNO

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

7369 Henry CLERK 20

7566 JONES MANAGER 20

flasher@ORCL11G> delete from tb1 where empno=7369;

flasher@ORCL11G> commit;

flasher@ORCL11G> select * from tb1 as of scn 2032782;

EMPNO ENAME JOB DEPTNO

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

7369 Henry CLERK 20

7566 JONES MANAGER 20

由以上可知，通过闪回查询获得所需的记录信息，然后来构造新的DML语句并实施其操作来保证数据的完整性。

二、Flashback Table Query(闪回表查询)

通过查询UNDO段来抽取所有已变化的记录细节，在此基础之上再构造和执行能够倒退这些变化的语句

表闪回通过执行倒退变化的语句并且该执行是一个事务，所有常用规则在该事务上起作用。

表闪回时，表上的触发器缺省被禁用，即该表上的DML触发器将暂时失效，可以在闪回时指定触发器是否失效。

表闪回需要启用表上的记录转移选项

1.下面给出表闪回的种方式

FLASHBACK TABLE <schema_name.table_name>

TO SCN <scn_number> --基于SCN的表闪回

[<ENABLE | DISABLE> TRIGGERS]

FLASHBACK TABLE <schema_name.table_name>

TO TIMESTAMP <timestamp> --基于TIMESTAMP的表闪回

[<ENABLE | DISABLE> TRIGGERS]

FLASHBACK TABLE <schema_name.table_name>

TO RESTORE POINT <restore_point> --基于RESTORE POINT的表闪回

[<ENABLE | DISABLE> TRIGGERS]

2.演示基于SCN的表闪回

下面的演示首先创建表tb_tables，并对表分几次插入数据，在完成插入前记录其SCN号用于后续对其进行闪回

create table tb_emp as --创建演示表tb_emp

select empno,ename,job,deptno from scott.emp where 1=0;

select table_name,row_movement from user_tables; --查看表的row movement行为，缺省为disable

TABLE_NAME ROW_MOVE

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

TB_EMP DISABLED

select current_scn,systimestamp from v$database; --获取系统当前的SCN

CURRENT_SCN SYSTIMESTAMP

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

661490 01-JAN-11 10.56.28.733000 PM +08:00

insert into tb_emp --插入deptno为10的员工

select empno,ename,job,deptno from scott.emp where deptno=10;

commit;

select current_scn,systimestamp from v$database; --获取系统当前的SCN

CURRENT_SCN SYSTIMESTAMP

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

661510 01-JAN-11 10.56.56.546000 PM +08:00

insert into tb_emp --插入deptno为20的员工

select empno,ename,job,deptno from scott.emp where deptno=20;

commit;

select current_scn,systimestamp from v$database; --获取系统当前的SCN

CURRENT_SCN SYSTIMESTAMP

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

661521 01-JAN-11 10.57.17.358000 PM +08:00

insert into tb_emp --插入deptno为30的员工

select empno,ename,job,deptno from scott.emp where deptno=30;

commit;

select current_scn,systimestamp from v$database; --获取系统当前的SCN

CURRENT_SCN SYSTIMESTAMP

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

661539 01-JAN-11 10.57.37.843000 PM +08:00

select deptno,count(*) from tb_emp group by deptno order by 1;

DEPTNO COUNT(*)

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

10 3

20 5

30 6

flashback table tb_emp to scn 661521; --将表闪回到scn为，即插入部门号为的记录之前

flashback table tb_emp to scn 661521 --闪回失败，收到错误提示，没有开启row movement

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

alter table tb_emp enable row movement; --开启表tb_emp表的row movement 功能

flashback table tb_emp to scn 661521; --再次实施闪回，闪回成功

select deptno,count(*) from tb_emp group by deptno order by 1; --记录中没有部门为30的记录

DEPTNO COUNT(*)

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

10 3

20 5

flashback table tb_emp to scn 661510; --将表闪回到scn为，即插入部门号为20的记录之前

select deptno,count(*) from tb_emp group by deptno order by 1; --记录中没有部门为20的记录

DEPTNO COUNT(*)

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

10 3

3.演示基于TIMESTAMP的表闪回

使用to timestamp进行表闪回,继续使用上面创建的表来进行闪回

--使用timestamp将表闪回到插入部门号10为之前

flashback table tb_emp to timestamp to_timestamp('01-JAN-11 10.56.28.733000');

flashback table tb_emp to timestamp to_timestamp('01-JAN-11 10.56.28.733000') --收到错误提示

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed --表结构发生改变

flasher@ORCL11G> flashback table tb_emp to scn 661539; --可以将表闪回到插入部门号为30的记录之后

Flashback complete.

此处演示中收到了错误提示，注意对于表闪回，可以多次使用同一类型的闪回方式，可以往前闪回，一旦往前闪回之后，也可以往后进行闪回。

但交叉闪回则提示表定义发生了变化。闪回失败。我们可以再次创建一张类似的新表进行基于timestamp进行闪回，与闪回SCN说不同的

是，此处使用了timestamp,此演示在此省略。

4.演示基于RESTORE POINT的表闪回

基于RESTORE POINT的表闪回首先要创建适当的闪回点，创建闪回点的方式为

CREATE RESTORE POINT point_name;

对于闪回成功之后，无用的闪回点可以及时删除掉，删除闪回点的方式为

DROP RESTORE POINT point_name

下面对基于RESTORE POINT 闪回进行演示

drop table tb_emp purge; --删除先前创建的表tb_emp

create table tb_emp --创建演示表tb_emp

enable row movement

as select empno,ename,job,deptno from scott.emp where 1=0;

create restore point zero; --创建闪回点zero

insert into tb_emp --插入deptno为10的员工

select empno,ename,job,deptno from scott.emp where deptno=10;

commit;

create restore point one; --创建闪回点one

insert into tb_emp --插入deptno为20的员工

select empno,ename,job,deptno from scott.emp where deptno=20;

commit;

create restore point two; --创建闪回点two

insert into tb_emp --插入deptno为30的员工

select empno,ename,job,deptno from scott.emp where deptno=30;

commit;

select deptno,count(*) from tb_emp group by deptno order by 1;

DEPTNO COUNT(*)

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

10 3

20 5

30 6

flashback table tb_emp to restore point two; --闪回到闪回点two之前

select deptno,count(*) from tb_emp group by deptno order by 1;

flashback table tb_emp to restore point one; --闪回到闪回点one之前

select deptno,count(*) from tb_emp group by deptno order by 1;

DEPTNO COUNT(*)

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

10 3

drop restore point two; --删除创建的闪回点

drop restore point one;

drop restore point zero;

5.存在参照关系的表闪回

帐户flasher中表tb1与表tb2存在外键关系，表tb1的deptno 参照了表tb2中的deptno列

帐户flasher中表tb1与scott.emp具有相同的表结构，表tb2与表scott.dept具有相同的表结构

下面为表tb2新增一个deptno，且为表tb1新增一条记录

create table tb1 --基于表scott.emp来创建表tb1

enable row movement

as select * from scott.emp ;

create table tb2 --基于表scott.dept来创建表tb2

enable row movement

as select * from scott.dept;

alter table tb1 --为表tb1添加主键约束

add constraint tb1_empno_pk primary key(empno);

alter table tb2 --为表tb2添加主键约束

add constraint tb2_deptno_pk primary key(deptno);

alter table tb1 --为表tb1添加外键约束

add constraint tb1_tb2_deptno_fk foreign key(deptno)

references tb2(deptno);

insert into tb2 --为表tb2插入一个新部门

select 50,'Customer','Landon' from dual;

insert into tb1(empno,ename,job,deptno) --为表tb1插入一个新的雇员

select 8000,'Robinson','Clerk',50 from dual;

commit;

select current_scn from v$database; --获得当前的scn

--- 687444

delete from tb1 where empno=8000; --删除先前新增的部门

delete from tb2 where deptno=50; --删除先前新增的雇员

commit;

flashback table tb1 to scn 687444; --闪回先前删除的雇员

ERROR at line 1:

ORA-02091: transaction rolled back --提示事务被回滚，外键没有找到

ORA-02291: integrity constraint (FLASHER.TB1_TB2_DEPTNO_FK) violated - parent key not found */

flashback table tb1,tb2 to scn 687444; --将两个表同时闪回

select empno,ename,deptno,dname --此时新增的雇员被闪回，部门也被闪回

from tb1

inner join tb2

using(deptno)

where deptno=50;

EMPNO ENAME DEPTNO DNAME

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

8000 Robinson 50 Customer

6.表闪回的几个常见问题

a.当闪回删除操作之前，如果某个键值如主键被重用，将导致违反主键约束，闪回失败。

b.若闪回所需要的UNDO信息不存在，将引发ORA-08180：no snapshot found based on specified time(未找到基于指定时间的快照)错误

c.如果受闪回影响的记录被其它用户锁定，将引发ORA-00054：resource busy and acquire with NOWAIT specified (资源忙碌)错误

d.表定义在闪回期间不能发生变化，否则导致ORA-01466：unable to read data - table definition has changed(表定义已变化)错误

e.闪回前未启用row movement，将收到ORA-08189: cannot flashback the table because row movement is not enabled 错误

f.对于存在参照关系的情况，建议将主表等一起实施闪回，否则，将收到ORA-02091: transaction rolled back，ORA-02291错误

g.SYS 模式中的表不能使用表闪回技术

flashback与ora-01466 ：

在Oracle 9iR2中，表属性修改与flashback时间应该相隔5分钟，否则就会报ORA-01466。但测试时发现，如果使用as of scn来flashback时，就算相隔5分钟，仍然报ORA-01466：

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> col scn for 999999999999999999SQL> drop table test; Table dropped. SQL> create table test (d date); Table created. SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual;                 SCN SYSDATE------------------- -------------------     10751229983414 2009-05-14 18:53:08 SQL> insert into test values(sysdate); 1 row created. SQL> commit; Commit complete. SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual;                 SCN SYSDATE------------------- -------------------     10751229983423 2009-05-14 18:53:33 SQL> exec dbms_lock.sleep(300); PL/SQL procedure successfully completed. SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual;                 SCN SYSDATE------------------- -------------------     10751231051348 2009-05-14 19:00:08 SQL> insert into test values(sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from test as of scn 10751231051348;                          select * from test as of scn 10751231051348              *ERROR at line 1:ORA-01466: unable to read data - table definition has changed

这里查询5分钟后的SCN：10751231051348仍会报ORA-01466，后尝试等待更久的时间，仍不行
百思不得其解，最后开了个SR，得到回复是个BUG：5525497

PROBLEM:
——–
Flashback query using SELECT AS OF SCN fails with ORA-1466 when
SMON_SCN_TIME as more than 1 distinct value for the column SCN_WRP.

也就是说，当使用：“AS OF SCN”进行flashback时，当SMON_SCN_TIME表中存在多个不同的SCN_WRP值时，就有可能会报ORA-01466。

SQL> select scn_wrp, min(time_dp), max(time_dp)  2  from sys.smon_scn_time  3  group by scn_wrp;    SCN_WRP MIN(TIME_DP)        MAX(TIME_DP)---------- ------------------- -------------------      2502 2009-05-08 04:40:21 2009-05-11 11:30:58      2503 2009-05-11 11:36:10 2009-05-14 19:01:01

不过为什么会存在多个SCN_WRP值？SR里说是BUG，我也只找到这么点资料：

Whenever the SCN is incremented, the BASE component is incremented first
unil it reaches it maximum. Once the BASE reaches the maximum value
allowed, it is initialized to zero again after incrementing the WRAP by 1.

继续回到之前的ORA-01466问题，这里我把包含时间较老的一个SCN_WRP值delete掉了，再重复实验，不再报ORA-01466错误。当然，Oracle并不推荐这么做。

SQL> select scn_wrp, min(time_dp), max(time_dp)  2  from sys.smon_scn_time  3  group by scn_wrp;    SCN_WRP MIN(TIME_DP)        MAX(TIME_DP)---------- ------------------- -------------------      2502 2009-05-08 04:40:21 2009-05-11 11:30:58      2503 2009-05-11 11:36:10 2009-05-14 19:01:01 SQL> delete from sys.smon_scn_time  2  where scn_wrp=2502; 704 rows deleted. SQL> commit; Commit complete.

— The End —