生产数据库中,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