June 22, 2005
使用Oracle9i的新特性Flashback Query恢复误删除数据
作者:eygle
出处:http://blog.eygle.com
下午接到研发工程师的电话，说误删除了部分重要数据，并且已经提交，需要恢复。登陆到数据库上查看，由于是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. |
研发人员确认，已经可以满足需要，找回误删除部分数据，至此闪回恢复成功完成。
Posted by eygle at 4:33 PM | Comments (25)
黄山云海观日出
作者:eygle
出处:http://blog.eygle.com
去黄山的时候，据导游说，我们是运气很好的一群，以前很多游客去了几次黄山都看不到日出，而我们，准时地看到了日出。
我们3点钟就起床，幸好旅馆离金顶的观日台也很近，我们赶到的时候，还没有什么人，占到了第一排的位置，等人越来越多的时候，我想，后面的人，很难看到日出的奇观了吧。于是有人开拓了别的观看地，爬上了旁边的山顶，然后有很多人跟随；天还是黑的，那攀爬还是很危险的。
没有支点，手工拍下了日出的系列照片，无法保持同一取景视图，只是日出的过程宛然可见，做成一个Flash和大家分享(Flash很大，浏览时可能会较慢，请大家耐心等待,或者可以点击下载.)
Posted by eygle at 1:45 PM | Comments (12)
增加了一个"数据库管理员站点导航"页面
作者:eygle
出处:http://blog.eygle.com
今天做了一个导航页面，推荐一些好的技术站点，同时也算是给自己做一个书签来用：）
希望能对大家也有所帮助。
Posted by eygle at 11:12 AM | Comments (0)