Oracle9i 中新增的闪回查询(Flashback Query)功能对于误删除或者误更新并且已经commit了的情况,提供了简便快捷的恢复方法,所谓的Flashback Query就是利用Oracle 多版本读一致的特性,在需要的时候通过undo 来提供所需的前镜像中的数数据。利用这个功能,可以看到历史数据,甚至用历史数数据来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。前提条件是数据库必须处于Automatic Undo Management 状态。最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数(单位为秒)指定。可以通过ALTER SYSTEM SET UNDO_RETENTION = ;来修改参数值。如何使用Flashback Query呢?使用SELECT 语句的AS OF 来进行闪回查询。使用AS OF 关键字来对表,视图,或者物化视图进行Flashback Query,如果指定了SCN,那么expr 部分必须是一个数字,如果指定了TIMESTAMP,那么expr 必须是一个timestamp类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。
下面提供一个恢复的例子:
ctoc@ORCL> show parameter undo_retention
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 10800 ctoc@ORCL> show parameter undo_management
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO ctoc@ORCL> select count(*) from esms;
COUNT(*) ---------- 207
ctoc@ORCL> delete esms;
207 rows deleted.
ctoc@ORCL> commit 2 ;
Commit complete.
ctoc@ORCL> select count(*) from esms; COUNT(*) ---------- 0
ctoc@ORCL> col fscn for 99999999999 ctoc@ORCL> col nscn for 99999999999 ctoc@ORCL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;
NAME ---------------------------------------------------------------------------------------------------- FSCN NSCN FIRST_TIM ------------ ------------ --------- /oracle/oradata/orcl/archivelog/1_99.dbf 10741133432 10741615345 16-APR-08
/oracle/oradata/orcl/archivelog/1_100.dbf 10741615345 10741649195 14-MAY-08
/oracle/oradata/orcl/archivelog/1_101.dbf 10741649195 10741754762 15-MAY-08
/oracle/oradata/orcl/archivelog/1_102.dbf 10741754762 10741972765 19-MAY-08
/oracle/oradata/orcl/archivelog/1_103.dbf 10741972765 10742805005 26-MAY-08
ctoc@ORCL> select dbms_flashback.get_system_change_number fscn from dual;
FSCN ------------ 10743479803
ctoc@ORCL> create table esms_recov as select * from esms where 1=0;
Table created.
ctoc@ORCL> select count(*) from esms as of scn 10743472211;
COUNT(*) ---------- 207 ctoc@ORCL> insert into esms_recov select * from esms as of scn 10743472211;
207 rows created.
ctoc@ORCL> commit;
Commit complete.
ctoc@ORCL> select count(*) from esms;
COUNT(*) ---------- 0
ctoc@ORCL> select count(*) from esms_recov;
COUNT(*) ---------- 207
ctoc@ORCL> rename esms to esms_old;
Table renamed.
ctoc@ORCL> rename esms_recov to esms;
Table renamed.
ctoc@ORCL> select count(*) from esms;
COUNT(*) ---------- 207 |
至些删除的数据数据全部恢复完成.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12045182/viewspace-432981/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12045182/viewspace-432981/