客户的一次疏忽，DBA的一次噩梦

今晚接到老大的电话，泰国的客户不小心删除了一些表的数据，现在非常着急，需要恢复数据。其实DBA做的数据库备份，很大程度是用于数据库crash掉的时候，恢复数据，而不是三天两头的因为客户误删了数据，而去做恢复。

看了客户的邮件，是有2个表的数据被误删除或者误插入或者误更新了。总之，操作过一大通，希望恢复到当天下午15：30的数据。上数据库去查了一下，用备份来恢复，似乎时间不够，尝试用户flashback query，发现已经回不去了：

SQL>SQL>SQL>SELECTcount(*)fromhr_ttm.TA_ABSDOCS 
2ASOFTIMESTAMPTO_TIMESTAMP('2011-06-0915:29:00','YYYY-MM-DDHH24:MI:SS'); 
SELECTcount(*)fromhr_ttm.TA_ABSDOCS 
* 
ERRORatline1: 
ORA-01555:snapshottooold:rollbacksegmentnumber1withname"_SYSSMU1$"
toosmall

其他也没有更快的方法了，于是当下决定用logmnr挖数据，

由于数据库原来就没有配置utl_file_dir，因此还需要重启数据库使得该参数生效。一路做下来，大致算顺利，不过也遇到了不少小插曲。下面就是恢复的步骤：

一、备份原表

createtablehr_ttm.TA_ABSDOCS_20110610_0010as
SELECT*fromhr_ttm.TA_ABSDOCS; 
 
createtablehr_ttm.TA_ABSDOC_20110610_0010as
SELECT*fromhr_ttm.TA_ABSDOC;

二，根据客户要求，建立新用户，将恢复的数据导入到这2个表中：

createuserhr_ttm2identifiedbyhr_ttm2defaulttablespaceMSG_DATA; 
grantconnect,resource,dbatohr_ttm2;

三、把原表数据备份到新用户下，用于做回滚

createtablehr_ttm2.TA_ABSDOCSas
SELECT*fromhr_ttm.TA_ABSDOCS 
 
createtablehr_ttm2.TA_ABSDOCas
SELECT*fromhr_ttm.TA_ABSDOC

四、修改参数，用于挖日志，重启数据库

altersystemsetutl_file_dir='/prodlog/logmnr'scope=spfile;

五、生成数据字典

execdbms_logmnr_d.build('dictionary.ora','/prodlog/logmnr');

做这一步之前注意需要修改LD_LIBRARY_PATH和LIBPATH，使得lib的变量在lib32前面。不然会有报错ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []

#p#

六、检查需要回滚的日志，客户要求回滚到6月9日15：30之前：

-rw-r-----1oracleoinstall48868352Jun0914:53ARC0000025854_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0915:08ARC0000025855_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0915:17ARC0000025856_0666465023.0001 
-rw-r-----1oracleoinstall48910848Jun0915:42ARC0000025857_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0916:04ARC0000025858_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0917:22ARC0000025859_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0917:27ARC0000025860_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0917:47ARC0000025861_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0920:52ARC0000025862_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0921:17ARC0000025863_0666465023.0001 
drwxr-xr-x2oracleoinstall256Jun0923:02logmnr 
-rw-r-----1oracleoinstall48863744Jun0923:07ARC0000025864_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:28ARC0000025865_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:29ARC0000025866_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:29ARC0000025867_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:29ARC0000025868_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:29ARC0000025869_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:30ARC0000025870_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:30ARC0000025871_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:30ARC0000025872_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:31ARC0000025873_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:31ARC0000025874_0666465023.0001 
-rw-r-----1oracleoinstall48868864Jun0923:31ARC0000025875_0666465023.0001

七、添加归档日志

execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025854_0666465023.0001',Options=>dbms_logmnr.new); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025855_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025856_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025857_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025858_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025859_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025860_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025861_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025862_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025863_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025864_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025865_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025866_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025867_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025868_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025869_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025870_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025871_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025872_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025873_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025874_0666465023.0001',Options=>dbms_logmnr.addfile); 
execdbms_logmnr.add_logfile(LogFileName=>'/prodlog/logmnr/ARC0000025875_0666465023.0001',Options=>dbms_logmnr.addfile);

八、开始挖日志

execdbms_logmnr.start_logmnr(dictfilename=>'/prodlog/logmnr/dictionary.ora');

九、将logmnr的数据暂时保存在一个表里面，免得再次查询时候不用再次添加归档日志

createtablehjm_logmnrnologgingasselect*fromv$logmnr_contentswhere1=2; 
insert/*+append*/intohjm_logmnrselect*fromv$logmnr_contents; 
/*------做这一步之前注意将nls_date_format改成'yyyy-mm-ddhh24:mi:ss'，不然泰文乱码，时间会变成问号。------*/

十、导出脚本，用脚本做回滚，注意SQL_UNDO中的delete语句末尾有rowid，不能直接用，需要用正则表达式替换掉。

spoolTA_ABSDOCS_undosql.txt 
selectregexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'andROWID.+;',';') 
fromhjm_logmnr 
WHERE
SEG_NAME='TA_ABSDOCS'AND
SEG_OWNER='HR_TTM' 
orderbyto_char(TIMESTAMP,'yyyy-mm-ddhh24:mi:ss')desc; 
spooloff
 
 
spoolTA_ABSDOC_undosql.txt 
selectregexp_replace(replace(SQL_UNDO,'"HR_TTM"','"HR_TTM2"'),'andROWID.+;',';') 
fromhjm_logmnr 
WHERE
SEG_NAME='TA_ABSDOC'AND
SEG_OWNER='HR_TTM' 
orderbyto_char(TIMESTAMP,'yyyy-mm-ddhh24:mi:ss')desc; 
spooloff

去掉脚本的头部的语句和末尾返回多少多少行的文字，在hr_ttm2下执行这2个脚本，实现数据回滚。

另外，我们来看一下：

SQL>selectto_char(max(TIMESTAMP),'yyyy-mm-ddhh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-ddhh24:mi:ss') 
fromhjm_logmnr 
WHERE
SEG_NAME='TA_ABSDOC'AND
SEG_OWNER='HR_TTM'2345 
6/ 
 
TO_CHAR(MAX(TIMESTATO_CHAR(MIN(TIMESTA 
-------------------------------------- 
2011-06-0921:20:262011-06-0915:31:54 
 
SQL> 
SQL> 
SQL> 
SQL> 
SQL>l 
1selectto_char(max(TIMESTAMP),'yyyy-mm-ddhh24:mi:ss'),to_char(min(TIMESTAMP),'yyyy-mm-ddhh24:mi:ss') 
2fromhjm_logmnr 
3WHERE
4SEG_NAME='TA_ABSDOC'AND
5*SEG_OWNER='HR_TTM'
SQL>l4 
4*SEG_NAME='TA_ABSDOC'AND
SQL>c/TA_ABSDOC/TA_ABSDOCS 
4*SEG_NAME='TA_ABSDOCS'AND
SQL> 
 
TO_CHAR(MAX(TIMESTATO_CHAR(MIN(TIMESTA 
-------------------------------------- 
2011-06-0921:20:262011-06-0915:37:39

归档日志是从14:53开始，而这2个表的变动，是在15：30之后才有的（min(TIMESTAMP是在15：30之后）。 也就是说从14：53分到15：30之前，这2个表一直是没动过的。因此，可以跟客户说，2个表的数据恢复到15：00的。

抬头一看，东方既白。唉，又熬了个通宵。

【小边碎语】以上是作者在月初的一次噩梦一般的经历，无论是否在你身上印证过，都可以来看看，作者真不愧是专业级人物，思路超清晰，值得学习和借鉴。