Oracle Team @SNC

通过logmnr工具实现数据恢复

最近接到的case.接到客户报障，xxx数据库的一个关键表数据被误删除了，需要做紧急数据恢复。接到客户报障后，随后联系了开发商的工程师。
一.问题分析,经过与局方和开发商的讨论，以及根据系统情况，了解到如下信息。
1.误删除的表名为EMP用户下的EPM_PRO_WORK开头的表名
2.误删除的数据是该表的部分记录。
3.通过delete方式删除，误删除时间大概上午在9:00至11:00分左右，

综合以上信息，因为误删的EPM_PRO_WORKPROCESS表的其中一部分记录，并且是通过delete方式。这种方式适用于通过日志挖掘的方式做数据恢复，所以采用oracle自身的工具logminer方式做数据恢复。
二 定位被删除的表名，表名为EPM .EPM_PRO_WORKPROCESS
SQL> select owner,table_name from dba_tables where table_name like ‘%EPM_PRO_WORK%’;
OWNER TABLE_NAME
—————————— ——————————
EPM EPM_PRO_WORKPROCESS
EPM EPM_PRO_WORKPROCESS_TEMP
EPM_TEST EPM_PRO_WORKPROCESS
EPM_TEST EPM_PRO_WORKPROCESS_TEMP
EPM2 EPM_PRO_WORKPROCESS_TEMP
EPM2 EPM_PRO_WORKPROCESS
EPM3 EPM_PRO_WORKPROCESS
EPM3 EPM_PRO_WORKPROCESS_TEMP

三 定位关键数据被误删除时间段所产生的归档日志
SQL> desc V$ARCHIVED_LOG Name
Null? Type
—————————————– ——– —————————-
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
BLOCKS NUMBER
BLOCK_SIZE NUMBER
CREATOR VARCHAR2(7)
REGISTRAR VARCHAR2(7)
STANDBY_DEST VARCHAR2(3)
ARCHIVED VARCHAR2(3)
APPLIED VARCHAR2(3)
DELETED VARCHAR2(3)
STATUS VARCHAR2(1)
COMPLETION_TIME DATE
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
END_OF_REDO VARCHAR2(3)
BACKUP_COUNT NUMBER
ARCHIVAL_THREAD# NUMBER
ACTIVATION# NUMBER

SQL>alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
SQL>select name,first_time from V$ARCHIVED_LOG where FIRST_TIME > sysdate -1;
NAME FIRST_TIME——————————————— ——————-/data1/oracle/archivelog/epmdb/1_4447.dbf 2008-06-01 21:57:34
/data1/oracle/archivelog/epmdb/1_4448.dbf 2008-06-02 01:57:49
/data1/oracle/archivelog/epmdb/1_4449.dbf 2008-06-02 07:00:59
/data1/oracle/archivelog/epmdb/1_4450.dbf 2008-06-02 11:57:57
进一步定位为误删时段产生的归档日志为：/data1/oracle/archivelog/epmdb/1_4450.dbf
四通过logminer工具进行日志分析

1.安装LogMiner工具，以下两个脚本以SYSDBA身份运行
@$ORACLE_HOME/rdbms/admin/dbmslm.sql;
@ $ORACLE_HOME/rdbms/admin/dbmslmd.sql;
2.检查和设置utl_file_dir参数
alter system set utl_file_dir=’/data1’ scope=spfile;
shutdown immediate;
startup
3.创建数据字典文件（data-dictionary）
sqlplus /nolog
SQL> connect /as sysdba
SQL> execute dbms_logmnr_d.build(dictionary_filename => ‘dict.ora’,dictionary_location =’/data1′);
4.装载日志分析列表
SQL>execute dbms_logmnr.add_logfile(LogFileName => ‘/data1/oracle/archivelog/epmdb/1_4449.dbf’,Options =dbms_logmnr.new);
SQL>execute dbms_logmnr.add_logfile(LogFileName => ‘/data1/oracle/archivelog/epmdb/1_4450.dbf’,Options =dbms_logmnr.addfile);
5.开始分析日志
SQL> execute dbms_logmnr.start_logmnr(startTime => to_date(’20080602090000′,’yyyy-mm-dd hh24:mi:ss’),endTime =to_date(’20080602120000′,’yyyy-mm-dd hh24:mi:ss’),DictFileName =’/data1/dict.ora’);
6.获取分析结果
create table t3 as select * from v$logmnr_contents;
set line 250;
col seg_name format a25
col session_info format a10
col OPERATION format a8
col sql_redo format a80
col sql_undo format a80
alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;select timestamp,seg_name,operation,sql_redo,sql_undo
from t3
where username=’EPM’ and seg_name=’EPM_PRO_WORKPROCESS’
and OPERATION=’DELETE’;

至此，通过logminer完成了被误删数据表的恢复。恢复过程30分钟左右。

以下为恢复的部分记录:

SQL> /

TIMESTAMP SEG_NAME OPERATIO SQL_REDO SQL_UNDO
——————- ————————- ——– ——————————————————————————– ——————————————————————————–
2008-06-02 09:43:24 EPM_PRO_WORKPROCESS DELETE delete from “EPM”.”EPM_PRO_WORKPROCESS” where “PRO_CODE” = ‘C0838K21′ and “PRO_S insert into “EPM”.”EPM_PRO_WORKPROCESS”(”PRO_CODE”,”PRO_SUB_ID”,”REPORTCLASS”,”R
UB_ID” IS NULL and “REPORTCLASS” = ‘ 分公司/网络维护中心’ and “REPORTER” = ‘ EPORTER”,”STARTTIME”,”ENDTIME”,”FINISHINVESTION”,”TOTALINVESTIONTHISYEAR”,”TOTAL
‘xxx’ and “STARTTIME” IS NULL and “ENDTIME” IS NULL and “FINISHINVESTION” = ‘0 INVESTION”,”FINISHINVESTIONBYFUND”,”TOTALINVESTIONTHISYEARBYFUND”,”TOTALINVESTIO
‘ and “TOTALINVESTIONTHISYEAR” = ‘0′ and “TOTALINVESTION” = ‘0′ and “FINISHINVES NBYFUND”,”TOTALCONTRACTTHISYEAR”,”TOTALCONTRACT”,”FINISHWORKTHISMONTH”,”TEMPOINF
TIONBYFUND” = ‘0′ and “TOTALINVESTIONTHISYEARBYFUND” = ‘0′ and “TOTALINVESTIONBY O”,”REMARKS”,”FINISHINVESTIONBYCONTRACT”,”AUDITSTATUS”,”PROG_ID”,”P_MONTH”,”P_YE
FUND” = ‘0′ and “TOTALCONTRACTTHISYEAR” = ‘0′ and “TOTALCONTRACT” = ‘0′ and “FIN AR”,”SEQ_PROG”,”LEVEL_PROG”,”PARENT_PROGID”,”PRO_ID”,”PROCESSINSTID”,”STATUS”) v
ISHWORKTHISMONTH” = ‘完成采购请示。经咨询公司，软 alues (’C0838K21′,NULL,’ 分公司/网络维护中心’,’ ,NULL,NULL,’0′,’0′,’0′,
件部分用统谈分签方法谈判，我方只需等待确认价格后直接与开发商签订合同。’ and “TEM ‘0′,’0′,’0′,’0′,’0′,’完成采购请示 要求6月30日前到货。经咨询公司，软
POINFO” IS NULL and “REMARKS” IS NULL and “FINISHINVESTIONBYCONTRACT” = ‘0′ and 件部分用统谈分签方法谈判，我方只需等待确认价格后直接与开发商签订合同。’,NULL,NUL
“AUDITSTATUS” = ‘3′ and “PROG_ID” = ‘5813′ and “P_MONTH” = ‘5′ and “P_YEAR” = ‘2 L,’0′,’3′,’5813′,’5′,’2008′,’0.5813′,’2′,’0′,’1843′,’10793′,’1′);
008′ and “SEQ_PROG” = ‘0.5813′ and “LEVEL_PROG” = ‘2′ and “PARENT_PROGID” = ‘0′
and “PRO_ID” = ‘1843′ and “PROCESSINSTID” = ‘10793′ and “STATUS” = ‘1′ and ROWID
= ‘AAAK0OAAMAAADiYAAE’;

2008-06-02 09:43:24 EPM_PRO_WORKPROCESS DELETE delete from “EPM”.”EPM_PRO_WORKPROCESS” where “PRO_CODE” = ‘C0838K23′ and “PRO_S insert into “EPM”.”EPM_PRO_WORKPROCESS”(”PRO_CODE”,”PRO_SUB_ID”,”REPORTCLASS”,”R
UB_ID” IS NULL and “REPORTCLASS” = ‘分公司/网络维护中心’ and “REPORTER” = ‘ EPORTER”,”STARTTIME”,”ENDTIME”,”FINISHINVESTION”,”TOTALINVESTIONTHISYEAR”,”TOTAL
and “STARTTIME” IS NULL and “ENDTIME” IS NULL and “FINISHINVESTION” = ‘0 INVESTION”,”FINISHINVESTIONBYFUND”,”TOTALINVESTIONTHISYEARBYFUND”,”TOTALINVESTIO
‘ and “TOTALINVESTIONTHISYEAR” = ‘1.42′ and “TOTALINVESTION” = ‘1.42′ and “FINIS NBYFUND”,”TOTALCONTRACTTHISYEAR”,”TOTALCONTRACT”,”FINISHWORKTHISMONTH”,”TEMPOINF
HINVESTIONBYFUND” = ‘0′ and “TOTALINVESTIONTHISYEARBYFUND” = ‘0′ and “TOTALINVES O”,”REMARKS”,”FINISHINVESTIONBYCONTRACT”,”AUDITSTATUS”,”PROG_ID”,”P_MONTH”,”P_YE
TIONBYFUND” = ‘0′ and “TOTALCONTRACTTHISYEAR” = ‘0′ and “TOTALCONTRACT” = ‘0′ an AR”,”SEQ_PROG”,”LEVEL_PROG”,”PARENT_PROGID”,”PRO_ID”,”PROCESSINSTID”,”STATUS”) v
d “FINISHWORKTHISMONTH” = ‘跟进 公司方面直放站软件升级情况’ and “TEMPOINFO” IS alues (’C0838K23′,NULL,’分公司/网络维护中心’, ,NULL,NULL,’0′,’1.42′,’
NULL and “REMARKS” IS NULL and “FINISHINVESTIONBYCONTRACT” = ‘0′ and “AUDITSTATU 1.42′,’0′,’0′,’0′,’0′,’0′,’跟进公司方面直放站软件升级情况’,NULL,NULL,’0′,’3′,’
S” = ‘3′ and “PROG_ID” = ‘5812′ and “P_MONTH” = ‘5′ and “P_YEAR” = ‘2008′ and “S 5812′,’5′,’2008′,’0.5812′,’2′,’0′,’1844′,’10792′,’1′);
EQ_PROG” = ‘0.5812′ and “LEVEL_PROG” = ‘2′ and “PARENT_PROGID” = ‘0′ and “PRO_ID
” = ‘1844′ and “PROCESSINSTID” = ‘10792′ and “STATUS” = ‘1′ and ROWID = ‘AAAK0OA
AMAAADiYAAD’;

--EOF--

Posted by xiaosp 2008-06-04 15:59 Wednesday | Oracle技术