使用Log Miner恢复数据的案例一则

上周五（9月21日上午11点左右），收到项目组的一封紧急邮件：

生产数据库中，FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的数据9月1号到9月19号的数据都被删除了。

烦请提供下技术支持，恢复这两张表的数据。待回复。谢谢！   

经过沟通，初步了解到系统的信息是：这是一套运行在IBM P750的小机上的64位的11gR2的单实例数据库。其实，这套环境也是之前的一篇文章里[记录一次在IBM P750小机上给Oracle动态扩展存储]提到的系统。

进一步了解，确认数据库中FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的数据在9月20号下午3点左右被误删除了，且这两张表是主子表的关系。

我首先想到的方法是，尝试使用事务的闪回查询，看看能否找回数据？结果很不幸，由于是生产数据库，事务繁忙，且误操作离当前时间较长（差不错相差20个小时），UNDO表空间中的回滚数据被覆盖了，遇到了ORA-01555回滚过旧的错误。显然，这条路是走不通了。

接下来，看看系统中是否有之前的有效的逻辑备份？如果有的话，可以用逻辑恢复的方式来尝试找回数据，再次不幸，该系统中采用的RMAN备份，并无逻辑备份。显然，该方法同样不凑效。

那么，我能想到的方法就是对全库做基于时间点的不完全恢复或者使用Oracle 自带的Log Miner工具来挖掘数据了。而该生产库的数据量很大，如果使用基于时间的不完全恢复的话，又有种种弊端和风险。比如，肯定得在一套独立的测试库上执行基于时间的不完全恢复，还有就是rman备份文件很大，这个显然是下下策了。

最后，选择Log Miner工具来尝试找回数据了。下面，记录一下这次的主要过程：

1  首先，找出系统中涵盖误操作时间段的归档日志，这里找出9月20日15点到17点之间的归档：

1 2 3 4 5 6 7 8 9 10 11 12

select name,FIRST_TIME from v $archived_log where first_time between to_date( '2012/09/20 14:50:00' , 'yyyy/mm/dd hh24:mi:ss' ) and to_date( '2012/09/20 17:00:00' , 'yyyy/mm/dd hh24:mi:ss' ); NAME                                                                   FIRST_TIME ---------------------------------------------------------------------- ------------------- /oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123956_85ohgybj_ .arc    2012 /09/20 14:52:41 /oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123957_85ohn6qh_ .arc    2012 /09/20 14:55:26 ... ... /oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123984_85olkh0t_ .arc    2012 /09/20 15:45:49 /oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123985_85olo3vb_ .arc    2012 /09/20 15:47:59 /oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123986_85olqg4r_ .arc    2012 /09/20 15:49:55 ... /oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123992_85om9wo7_ .arc    2012 /09/20 15:59:07

2  调用dbms_logmnr系统包，添加归档日志：

1	SQL> exec dbms_logmnr.add_logfile(logfilename=> '/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123956_85ohgybj_.arc' ,options=>dbms_logmnr.new);

3  调用dbms_logmnr系统包，启动Log Miner开始挖掘日志：

1	SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

4  从v$logmnr_contents系统表中，查看是否包含FIN_CASH_DETAIL表的SQL语句：

1	SQL> select timestamp,table_name,sql_redo,sql_undo,operation from v $logmnr_contents where table_name= 'FIN_CASH_DETAIL' ;

5  调用dbms_logmnr系统包，停止Log Miner：

1	exec dbms_logmnr.end_logmnr;

6  重复上述2~5步骤的动作，只是每次需要添加的归档日志不同而已。终于，在/oraflash/SITCDB/archivelog/2012_09_20/o1_mf_1_123985_85olo3vb_.arc这份归档日志中，均找到有FIN_CASH_MOVEMENT和FIN_CASH_DETAIL这两张表的操作。也就是误操作的时间应该是在2012/09/20 15:47:59到2012/09/20 15:49:55之间。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

select sql_redo from v $logmnr_contents where table_name= 'FIN_CASH_MOVEMENT' and operation= 'DELETE' ; SQL_REDO --------------------------------------------------------------------------------------------------- delete from "SITCLINE" . "FIN_CASH_MOVEMENT" where "CASH_MOVEMENT_ID" = '2c2881d63987424d01398b77fa2f6345' and "RP_ID" = 'R' and "OFFICE_ID" = 'SITTP' and "STATE_IND" = '0' and "MOVEMENT_TYPE" = 'CHECK' and "MOVEMENT_NO" = 'CR201209030138' and "BANK_MOVEMENT_NO" = '010060936' and "MOVEMENT_DATE" = TO_DATE( '03-9月 -12' , 'DD-MON-RR' ) and "LEDGER_PARTNER_CODE" = '80273312' and "LEDGER_PARTNER_NAME" IS NULL and "EXTERNAL_BANK_NAME" IS NULL and "EXTERNAL_BANK_ACCOUNT" = '056637' and "EXTERAL_BANK_ACCOUN T_NAME " IS NULL and " INTERNAL_BANK_ID " = '2c2881d63978294201397a864fe30c1e' and " INTERNAL_BANK_NAME " = '花旗台灣' and " INTERNAL_BACNT_ID " = '2c2881d63978294201397a8c72be0c6c' and " INTERNAL_BANK_ACCOUNT_CODE" = '5049328003' and "INTERNAL_BANK_ACCOUNT_NAME" = 'SITC STEAMSHIPS CO LTD TAIWAN BRANCH' and "PRIME_CURRENCY_CODE" = 'NTD' and "PRIME_CURRENCY_VALUE" = '10799' and "BASE_CURRENCY_CODE" IS NULL and "BASE_CURRENCY_VALUE" IS NULL and "REMARK" IS NULL and "REALRP" = '1' and "REALRP_DATE" IS NULL and   "REALRP_PERSON" IS NULL and "REALRP_PERSON_NAME" IS NULL and "DISCOUNT_VALUE" IS NULL and "DISCOUNT_REMARK" IS NULL and "RATE_BASE" IS NULL and "ALLOCATION_EVENT_ID" IS NULL and "DEPOSIT_DATE" = TO_DATE( '03-9月 -12' , 'DD-MON-RR' ) and "INVOICE_INFO" IS NULL and "CREATED_BY_USER" = 'FIN_TWPEI05' and "CREATED_OFFICE" = 'TP_FIN_DP' and "CREATED_DTM_LOC" = TO_DATE( '03-9月 -12' , 'DD-MON-RR' ) and "CREATED_TIME_ZONE" IS NULL and "UPDATED_BY_USER" = 'FIN_TWPEI05' and "UPDATED_OFFICE" = 'TP_FIN_DP' and "UP DATED_DTM_LOC " = TO_DATE('03-9月 -12', 'DD-MON-RR') and " UPDATED_TIME_ZONE " IS NULL and " RECORD_VERSION " = '0' and " PRINCIPAL_GROUP_CODE" = 'SIT' and "CHECK_NO" = '6822983' and "PRINTED_PERSON" IS NULL and "IS_PRINTED" = '0' and "PRINTED_PERSON_NAME" IS NULL and "PRINTED_DATE" IS NULL and "BANK_EXCHANGE_NO" IS NULL and "INVOICE_AMOUNT" IS NULL and "SHORT_OVER_AMOUNT" = '0' and "SAP_STATUS" = '0' and "ARP_ID" IS NULL and ROWID = 'AAATyIAAUAAAMQ7AAH' ;   ...

7  发现对于主、子表FIN_CASH_MOVEMENT、FIN_CASH_DETAIL的误操作分别删除了1390和1911条数据。生成下述的反向SQL，并把SQL脚本交给项目组确认，数据是否正确？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

select sql_undo from v $logmnr_contents where table_name= 'FIN_CASH_MOVEMENT' and operation= 'DELETE' ; SQL_UNDO --------------------------------------------------------------------------------------------------- insert into "SITCLINE" . "FIN_CASH_MOVEMENT" ( "CASH_MOVEMENT_ID" , "RP_ID" , "OFFICE_ID" ," STATE_IND "," MOVEMENT_TYPE "," MOVEMENT_NO "," BANK_MOVEMENT_NO "," MOVEMENT_DATE "," LEDGER_PARTNER_CODE "," LEDGER_PARTNER_NAME "," EXTERNAL_BANK_NAME "," EXTERNAL_BANK_ACCOUNT "," EXTERAL_BANK_ACCOUNT_NAME "," INTERNAL_BANK_ID "," INTERNAL_BANK_NAME "," INTERNAL_BACNT_ID "," INTERNAL_BANK_ACCOUNT_CODE "," INTERNAL_BANK_ACCOUNT_NAME "," PRIME_CURRENCY_CODE "," PRIME_CURRENCY_VALUE "," BASE_CURRENCY_CODE "," BASE_CURRENCY_VALUE "," REMARK "," REALRP "," REALRP_DATE "," REALRP_PERSON "," REALRP_PERSON_NAME "," DISCOUNT_VALUE "," DISCOUNT_REMARK "," RATE_BASE "," A LLOCATION_EVENT_ID "," DEPOSIT_DATE "," INVOICE_INFO "," CREATED_BY_USER "," CREATED_OFFICE "," CREATED_DTM_LOC "," CREATED_TIME_ZONE "," UPDATED_BY_USER "," UPDATED_OFFICE "," UPDATED_DTM_LOC "," UPDATED_TIME_ZONE "," RECORD_VERSION "," PRINCIPAL_GROUP_CODE "," CHECK_NO "," PRINTED_PERSON "," IS_PRINTED "," PRINTED_PERSON_NAME "," PRINTED_DATE "," BANK_EXCHANGE_NO "," INVOICE_AMOUNT "," SHORT_OVER_AMOUNT "," SAP_STATUS "," ARP_ID") values ( '2c2881d63987424d01398b77fa2f6345' , 'R' , 'SITTP' , '0' , 'CHECK' , 'CR201209030138' , '010060936' ,TO_DATE( '03-9月 -12' , 'DD-MON-RR' ), '80273312' ,NULL,NULL, '056637' ,NULL, '2c2881d63978294201397a864fe30c1e' , '花旗台灣' , '2c2881d63978294201397a8c72be0c6c' , '5049328003' , 'SITC STEAMSHIPS CO LTD TAIWAN BRANCH' , 'NTD' , '10799' ,NULL,NULL,NULL, '1' , NULL,NULL,NULL,NULL,NULL,NULL,NULL,TO_DATE( '03-9月 -12' , 'DD-MON-RR' ),NULL, 'FIN_TWPEI05' , 'TP_FIN_DP' ,TO_DATE( '03-9月 -12' , 'DD-MON-RR' ),NULL, 'FIN_TWPEI05' , 'TP_FIN_DP' ,TO_DATE( '03-9月 -12' , 'DD-MON-RR' ),NULL, '0' , 'SIT' , '6822983' , NULL, '0' ,NULL,NULL,NULL,NULL, '0' , '0' ,NULL);   ... select sql_undo from v $logmnr_contents where table_name= 'FIN_CASH_DETAIL' and operation= 'DELETE' ; SQL_UNDO --------------------------------------------------------------------------------------------------- insert into "SITCLINE" . "FIN_CASH_DETAIL" ( "CASH_DETAIL_ID" , "CASH_MOVEMENT_ID" , "INVOICE_NO" ," VESSEL_CODE "," VOYAGE_NO "," VOYAGE_LEG "," BL_NO "," AMOUNT "," CURRENCY "," RATE "," INVOICE_DOC_ID "," FREIGHT_ITEM_ID "," CREATED_BY_USER "," CREATED_OFFICE "," CREATED_DTM_LOC "," CREATED_TIME_ZONE "," UPDATED_BY_USER "," UPDATED_OFFICE "," UPDATED_DTM_LOC "," UPDATED_TIME_ZONE "," RECORD_VERSION "," PRINCIPAL_GROUP_CODE") values ( '2c2881d63987424d01398b77fa2f6346' , '2c2881d63987424d01398b77fa2f6345' , 'EZ03404580' , 'STKE' , '1236' , 'N' , 'SITGKESH002049' ,' 10799 ',NULL,NULL,' 2c2881d63987424d01398a8999e22caa ',NULL,' FIN_TWPEI05 ',' TP_FIN_DP',TO_D ATE( '03-9月 -12' , 'DD-MON-RR' ),NULL, 'FIN_TWPEI05' , 'TP_FIN_DP' ,TO_DATE( '03-9月 -12' , 'DD-MON-RR' ),NULL, '0' , 'SIT' ); ...

8  最后，项目组确认之后，重新执行反向的SQL脚本，并发邮件过来，确认数据全部找回。

后记：项目组发布出来，确认引起该错误的原因是程序bug，已经修复。从这次的恢复数据过程中，我们说在生产系统上的程序也好，人为操作数据库也好，一定要谨慎。同样，数据库的备份也不容忽视！

如无特别说明，本站文章皆为原创，若要转载，务必请注明以下原文信息:
日志标题:《使用Log Miner恢复数据的案例一则》
日志链接:http://www.oracleonlinux.cn/2012/09/log-miner-recover-data/
博客名称:OracleOnLinux