服务热线:13616026886

技术文档 欢迎使用技术文档,我们为你提供从新手到专业开发者的所有资源,你也可以通过它日益精进

位置:首页 > 技术文档 > 数据库技术 > Oracle技术 > Oracle开发 > 查看文档

使用flashback query 恢复误删除的数据

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

1 2 下一页>>


◆此处选择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.

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

扫描关注微信公众号