经过沟通，初步了解到系统的信息是：这是一套运行在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点之间的归档：
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系统包，添加归档日志：
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开始挖掘日志：
SQL>exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
4 从v$logmnr_contents系统表中，查看是否包含FIN_CASH_DETAIL表的SQL语句：
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：
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之间。
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脚本交给项目组确认，数据是否正确？
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，已经修复。从这次的恢复数据过程中，我们说在生产系统上的程序也好，人为操作数据库也好，一定要谨慎。同样，数据库的备份也不容忽视！
.png)
.png)
