使用Oracle9i的新特性Flashback Query恢复误删除数据

« 黄山云海观日出 | Blog首页 | Oracle数据库诊断案例-redo log日志组处于高激活状态 »

使用Oracle9i的新特性Flashback Query恢复误删除数据

作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/06/eoaoracle9iaeai.html

站内相关文章|Related Articles

修改默认的undo_retention参数设置

关于HDS的高端存储设备USPV

成功恢复案例:打开断电崩溃数据库

恢复8.1.6的数据库勿忘svrmgrl

用Rman的Plus archvielog选项简化数据库备份

下午接到研发工程师的电话，说误删除了部分重要数据，并且已经提交，需要恢复。

登陆到数据库上查看，由于是Oracle9iR2，首先尝试使用flashback query闪回数据。

首先确认数据库的SCN变化:

SQL> col fscn for 9999999999999999999
SQL> col nscn for 9999999999999999999
SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;

...................

NAME                                           FSCN                 NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
/mwarch/oracle/1_52413.dbf              12929941968          12929942881 2005-06-22 14:38:28
/mwarch/oracle/1_52414.dbf              12929942881          12929943706 2005-06-22 14:38:32
/mwarch/oracle/1_52415.dbf              12929943706          12929944623 2005-06-22 14:38:35
/mwarch/oracle/1_52416.dbf              12929944623          12929945392 2005-06-22 14:38:38
/mwarch/oracle/1_52417.dbf              12929945392          12929945888 2005-06-22 14:38:41
/mwarch/oracle/1_52418.dbf              12929945888          12929945965 2005-06-22 14:38:44
/mwarch/oracle/1_52419.dbf              12929945965          12929948945 2005-06-22 14:38:45
/mwarch/oracle/1_52420.dbf              12929948945          12929949904 2005-06-22 14:46:05
/mwarch/oracle/1_52421.dbf              12929949904          12929950854 2005-06-22 14:46:08
/mwarch/oracle/1_52422.dbf              12929950854          12929951751 2005-06-22 14:46:11
/mwarch/oracle/1_52423.dbf              12929951751          12929952587 2005-06-22 14:46:14
...................

/mwarch/oracle/1_52498.dbf              12930138975          12930139212 2005-06-22 15:55:57
/mwarch/oracle/1_52499.dbf              12930139212          12930139446 2005-06-22 15:55:59
/mwarch/oracle/1_52500.dbf              12930139446          12930139682 2005-06-22 15:56:00

NAME                                           FSCN                 NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
/mwarch/oracle/1_52501.dbf              12930139682          12930139915 2005-06-22 15:56:02
/mwarch/oracle/1_52502.dbf              12930139915          12930140149 2005-06-22 15:56:03
/mwarch/oracle/1_52503.dbf              12930140149          12930140379 2005-06-22 15:56:05
/mwarch/oracle/1_52504.dbf              12930140379          12930140610 2005-06-22 15:56:05
/mwarch/oracle/1_52505.dbf              12930140610          12930140845 2005-06-22 15:56:07

14811 rows selected.

当前的SCN为:

SQL> select dbms_flashback.get_system_change_number fscn from dual;

                FSCN
--------------------
         12930142214

使用应用用户尝试闪回

SQL> connect username/password
Connected.

现有数据:

SQL> select count(*) from hs_passport;

  COUNT(*)
----------
    851998

创建恢复表

SQL> create table hs_passport_recov as select * from hs_passport where 1=0;

Table created.

选择SCN向前恢复

SQL> select count(*) from hs_passport as of scn 12929970422;

  COUNT(*)
----------
    861686

尝试多个SCN，获取最佳值（如果能得知具体时间，那么可以获得准确的数据闪回）

SQL> select count(*) from hs_passport as of scn &scn;
Enter value for scn: 12929941968
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12929941968

  COUNT(*)
----------
    861684

SQL> /
Enter value for scn: 12927633776
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12927633776
select count(*) from hs_passport as of scn 12927633776
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL> /
Enter value for scn: 12929928784
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12929928784

  COUNT(*)
----------
    825110

SQL> /
Enter value for scn: 12928000000
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12928000000
select count(*) from hs_passport as of scn 12928000000
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

最后选择恢复到SCN为12929941968的时间点

SQL> insert into hs_passport_recov select * from hs_passport as of scn 12929941968;

861684 rows created.

SQL> commit;

Commit complete.

研发人员确认，已经可以满足需要，找回误删除部分数据，至此闪回恢复成功完成。
-----
这篇【使用Oracle9i的新特性Flashback Query恢复误删除数据】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

By eygle on 2005-06-22 16:33 | Comments (23) | Posted to Backup&Recovery | Edit |Pageviews:

相关文章	随机文章
修改默认的undo_retention参数设置关于HDS的高端存储设备USPV 成功恢复案例:打开断电崩溃数据库恢复8.1.6的数据库勿忘svrmgrl 用Rman的Plus archvielog选项简化数据库备份	字符集问题的初步探讨(二)-数据库的字符集 Oracle10g New Feature:CRS(Cluster Ready Services) 西塘游记之三 AIX不完整的补丁安装以及Oracle的Bug 10g New Feature:MAXTRANS参数已经废弃

网上相关主题:

Listed below are links to weblogs that reference 使用Oracle9i的新特性Flashback Query恢复误删除数据:

» what is SMON_SCN_TIME ? from Friends Life and Oracle
SMON_SCN_TIME是Oracle数据库的系统表，用以进行辅助恢复等功能。在Oracle9iR2中,SMON_SCN_TIME每5分钟被更新一次。 [Read More]

Tracked on September 7, 2006 1:55 PM

留言 (23)

睡觉前再看一遍，让你网站排名往上升，收到一张又一张广告支票，再给我发钱！哈哈

Posted by: 柔 at June 23, 2005 12:46 AM

select name,FIRST_CHANGE# fscn,NEXT_CHANGE#,nscn,FIRST_TIME from v$archived_log;

多了个逗号，应该是：

select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;

Posted by: zhpsam at September 7, 2005 3:42 PM

为什么我这里的v$archived_log没有纪录啊;

Posted by: wkdhanxu at September 12, 2005 8:26 PM

你的是归档模式么?

Posted by: eygle at September 12, 2005 8:31 PM

谢谢zhpsam，修正了。

Posted by: eygle at September 12, 2005 8:35 PM

flashback应该与undo_retention的设置有关系吧,只能在undo_retention内恢复数据

Posted by: we_are at November 30, 2005 4:52 PM

是与undo_retention有关,所以flashback并非万能...

Posted by: eygle at November 30, 2005 4:56 PM

请教:如果误删除一个表,flashback可以恢复吗?
实际上,因为失误drop一个表比delete数据还出现的要多.如果用recover database until time/scn,会把其他的表也恢复过去,而逻辑备份不可能每天都做.

Posted by: we_are at November 30, 2005 5:31 PM

那就要用到10g的FlashBack Drop功能,参考:
http://www.eygle.com/archives/2005/03/eoaoracle10giaa.html

Posted by: eygle at November 30, 2005 5:36 PM

可是是9i的环境啊,看来没办法了,呵呵

Posted by: we_are at November 30, 2005 6:01 PM

请问eygle flashback可以在不归档情况下应用吗？

Posted by: 侯雪峰 at January 13, 2006 11:02 AM

Flashback Query和UNDO有关,和归档模式无关的.

Posted by: eygle at January 13, 2006 11:53 AM

我执行
select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;
怎么提示：未选定行

Posted by: flmn at February 21, 2006 9:10 PM

你的是归档模式么?

Posted by: eygle at February 21, 2006 9:33 PM

eygle
请教一下我执行下面的语句怎么出错了啊
SQL> /
select count(*) from jj as of scn 344034054721
ERROR 在行 1:
ORA-00933: SQL 命令的结束有问题問題

Posted by: jiangxuyong at March 18, 2006 1:22 PM

你什么版本啊?

Posted by: eygle at March 18, 2006 1:36 PM

Oracle9i Enterprise Edition Release 9.0.1.1.1
OS: Win2003

Posted by: jiangxuyong at March 18, 2006 2:23 PM

9iR1不能这么作,还有几个步骤.

你可以看看文档.9iR1很不稳定,建议不要采用.

Posted by: eygle at March 18, 2006 2:38 PM

谢谢,我再9204中试了下,可以的

Posted by: jiangxuyong at March 18, 2006 2:57 PM

选择SCN向前恢复
SQL> select count(*) from hs_passport as of scn 12929970422;

您的这个scn 12929970422 是如何得知的，只靠推测吗？
我自己试验时，就是不行，我也试过用时间。

Posted by: ghbspecial at December 19, 2006 4:51 PM

前面有写啊，我是根据v$archived_log中的SCN及时间来推断的。
你要找到你自己的ＳＣＮ。

Posted by: eygle at December 19, 2006 11:12 PM

eygle大师谢谢您的回复，我也看到您是通过这里v$archived_log中的SCN及时间来推断的，可是我始终没有实验成功，恳请您在百忙之中看一下小弟在itpub发的这个贴子，看其它文章用oracle9i的闪回查询，没这么复杂呀，可是自己却总是实验失败，请指点一下。。。

Posted by: ghbspecial at December 20, 2006 10:26 AM

http://www.itpub.net/showthread.php?s=&threadid=685357&perpage=10&pagenumber=1

这个是我发的贴子，请您看一下，谢谢了！！！

Posted by: ghbspecial at December 20, 2006 2:11 PM

www.eygle.com

使用Oracle9i的新特性Flashback Query恢复误删除数据

留言 (23)

发表留言: