| 订阅 | 在线投稿
分享
 
 
 

实例讲解误删除一部分重要数据的恢复方法

来源:互联网网民  宽屏版  评论
2008-06-13 06:49:13

如果你在工作中误删除了一部分重要的数据,并且已经提交,当你需要恢复数据的时候,你可以登陆到数据库上仔细查看,如果是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.

注释:至此,则可以满足需要,找回误删除的部分数据,闪回恢复成功。

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
如果你在工作中误删除了一部分重要的数据,并且已经提交,当你需要恢复数据的时候,你可以登陆到数据库上仔细查看,如果是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. 注释:至此,则可以满足需要,找回误删除的部分数据,闪回恢复成功。
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有