分类: 服务器与存储
2008-06-14 13:58:01
◆第一步:确认数据库的SCN变化:
SQL> col fscn for 9999999999999999999 SQL> col nscn for 9999999999999999999 SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from |
◆此时的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. |
注释:至此,则可以满足需要,找回误删除的部分数据,闪回恢复成功。