前天一朋友打电话给我说他一客户的一张表的数据没了，叫我帮忙看看。
电话里简单了解了一下情况，大致是上午10:15左右开发人员发现一张基础表数据全没了，由于客户也不清楚数据库是归档还是非归档，于是吩咐他赶紧把应用停下来，因为一怕数据库是非归档，万一日志被覆盖就不好恢复了，二怕undo信息被覆盖，那样的话想用flashback都不行了。
下班后赶了过去，一检查果然数据库是非归档模式！晕！更晕的是数据库没备份！！！
检查alert log中的日志切换情况，发现当前日志是redo2，而状况发生时日志刚好处于redo2切换到redo3的时刻，由此可以断定，如果要通过logminer完全找回来是不可能了，因为redo2已经被覆盖！
1.先尝试用flashback table看能不能恢复：
SQL>alter table wj.ut_member ENABLE ROW MOVEMENT;
SQL>flashback table wj.ut_member TO TIMESTAMP TO_TIMESTAMP('2009-01-19 10:10:00','yyyy-mm-dd hh24:mi:ss');
SQL>flashback table wj.ut_member TO TIMESTAMP TO_TIMESTAMP('2009-01-19 10:10:00','yyyy-mm-dd hh24:mi:ss');
提示01555错误，说明undo已经被覆盖，那最后只能看看用logminer能恢复多少了。
2.用logminer进行恢复：
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('F:\oracle\product\10.2.0\oradata\wj\REDO03.LOG',DBMS_LOGMNR.NEW);
SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT+DBMS_LOGMNR.NO_SQL_DELIMITER);
SQL>set linesize 32767
SQL>set feedback off
SQL>set heading off
SQL>spool c:\ut_member1.sql
SQL>SELECT SQL_UNDO||';' FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER='WJ' AND OPERATION = 'DELETE' AND SEG_NAME = 'UT_MEMBER';
SQL>spool off
SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT+DBMS_LOGMNR.NO_SQL_DELIMITER);
SQL>set linesize 32767
SQL>set feedback off
SQL>set heading off
SQL>spool c:\ut_member1.sql
SQL>SELECT SQL_UNDO||';' FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER='WJ' AND OPERATION = 'DELETE' AND SEG_NAME = 'UT_MEMBER';
SQL>spool off
重复上面步骤，对redo1进行分析。
最后恢复回来3万多笔数据，还有1万多笔数据丢失。
No Comments
Be the first to comment on this entry.
Leave a comment
Fields in bold are required. Email addresses are never published or distributed.
Some HTML code is allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>URLs must be fully qualified (eg: http://www.dbifan.com),and all tags must be properly closed.
Line breaks and paragraphs are automatically converted.
Please keep comments relevant. Off-topic, offensive or inappropriate comments may be edited or removed.