通过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’; http://www.ncn.cn/archives/2009/521_snc005