网上有很多关于数据回复的文章,这里整理一篇供大家参考,希望能帮助的大家!
推荐一家即时通讯云服务商:www.yun2win.com,功能包含im即时通讯、实时音视频、电子白板、屏幕共享的多种融合通讯云能力!
好了,言归正传!如果你在工作中误删除了一部分重要的数据,并且已经提交,如果你需要回复这些数据,你可以登陆到数据库上仔细查看,是Oracle9iR2的话,你可以首先尝试使用flashback query闪回数据。
◆第一步:确认数据库的SCN变化:
SQL> col fscn for 9999999999999999999SQL> col nscn for 9999999999999999999SQL> 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:00NAME 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:0714811 rows selected.
◆此时的SCN为:
SQL> select dbms_flashback.get_system_change_number fscn from dual; FSCN-------------------- 12930142214
◆此时使用应用用户尝试闪回
SQL> connect username/passwordConnected.
◆现有的数据
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: 12929941968old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12929941968 COUNT(*)---------- 861684SQL> /Enter value for scn: 12927633776old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12927633776select count(*) from hs_passport as of scn 12927633776 *ERROR at line 1:ORA-01466: unable to read data - table definition has changedSQL> /Enter value for scn: 12929928784old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12929928784 COUNT(*)---------- 825110SQL> /Enter value for scn: 12928000000old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12928000000select 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.
注释:至此,则可以满足需要,找回误删除的部分数据,闪回恢复成功。
转载于:https://www.cnblogs.com/Siege-lion/p/5780696.html
相关资源:JAVA上百实例源码以及开源项目