oracle資料庫不完全恢復

一個表的資料被刪掉，不清楚具體時間，得恢復到幾天前的資料，準備恢復26號的全備。

1、用spfile啟動資料庫到nomount狀態。

2、恢復一個全備之後不久的控制檔案出來，啟動到mount狀態

3、restore database

4、recover database

5、alter database open resetlogs;

恢復指令碼1：

RUN {

ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';

set  until time  "to_date('2012-10-26 15:31:00','YYYY-MM-DD HH24:MI:SS')";

set newname for datafile   1 to '+ASMDATADG/test/DATAFILE/testsystem';

set newname for datafile   2 to '+ASMDATADG/test/DATAFILE/testundo1_01';

set newname for tempfile 1 to '+ASMDATADG/test/DATAFILE/temp1';

set newname for tempfile 2 to '+ASMDATADG/test/DATAFILE/temp2';

restore datafile 1,3,10,12,17,19,243,193,206,207,208,209,204,214,259,230,110,88,118,150,158,171,192,153,183,237,240,241,254,252,270,275,305,121,267,276,263,311,352,378,379,132,392,403,406,408,409,410,414,418,421,428,431;

switch datafile all;

switch tempfile all;

 release channel t1;

 release channel t2;

}

或者：restore database skip forever tablespace DATA_TISMONI,DAT_CRMDEP_WH,LOB_CRM_DEP_N1_HIST,USERS,DAT_CRM_IVPN,IDX_CRM_IVPN_HOT,LOB_CRM_DEP_N1,LOB_CRM_DEP_N2,DAT_CRM_LOG,DAT_CRM_DEP_N2_HOT,IDX_CRM_DEP_N4_HOT,LOB_CRM_DEP_N4,DAT_CRM_CONF,IDX_CRM_DEP,DAT_CRM_DEP_N3_HOT,DAT_CRM_DEP_N4_HOT,LOB_CRM_IVPN,DAT_CRM_DEP,IDX_CRM_DEP_N1_HIST,DAT_CRM_BAP,DAT_CRM_CONF_HIST,IDX_CRM_CONF,DAT_CRM_IVPN_HOT,IDX_CRM_IVPN,DAT_CRM_DEP_N1_HOT,LOB_CRM_DEP_N3;

修改不需要恢復的資料檔案為offline：

select 'alter database datafile '''||file_id||''' offline;'  from dba_data_files where TABLESPACE_NAME not in('SYSTEM','UNDOTBS1','SYSAUX','UNDOTBS2','DAT_CRM_IVPN')；

recover指令碼：

RUN {

ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';

set  until time  "to_date('2012-10-26 15:31:00','YYYY-MM-DD HH24:MI:SS')";

recover database skip forever tablespace DATA_TISMONI,DAT_CRMDEP_WH,LOB_CRM_DEP_N1_HIST,USERS,DAT_CRM_IVPN,IDX_CRM_IVPN_HOT,LOB_CRM_DEP_N1,LOB_CRM_DEP_N2,DAT_CRM_LOG,DAT_CRM_DEP_N2_HOT,IDX_CRM_DEP_N4_HOT,LOB_CRM_DEP_N4,DAT_CRM_CONF,IDX_CRM_DEP,DAT_CRM_DEP_N3_HOT,DAT_CRM_DEP_N4_HOT,LOB_CRM_IVPN,DAT_CRM_DEP,IDX_CRM_DEP_N1_HIST,DAT_CRM_BAP,DAT_CRM_CONF_HIST,IDX_CRM_CONF,DAT_CRM_IVPN_HOT,IDX_CRM_IVPN,DAT_CRM_DEP_N1_HOT,LOB_CRM_DEP_N3;

release channel t1;

release channel t2;

}

修改redologfile到新路徑

alter database rename file '/dev/rcrmdepredo02a' to '+ASMDATADG/crmdep/onlinelog/group_02a';

alter database open resetlogs;之後發現需要的表空間資料檔案全部offline的，open之前記得的時候檢查一下需要的資料檔案狀態

SQL> alter database datafile 431 online;

alter database datafile 431 online

*

ERROR at line 1:

ORA-01190: control file or data file 431 is from before the last RESETLOGS

ORA-01110: data file 431: '+ASMDATADG/test/datafile/testd0412'

設定引數

_allow_resetlogs_corruption=TRUE

調整scn號，啟動到mount狀態下執行

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ADJUST_SCN LEVEL 1';

再恢復一下

RECOVER UNTIL CANCEL;

auto

把資料庫檔案聯機：

alter database datafile 392 online;

.

.

然後再

alter database open resetlogs;成功

查詢資料，已經出來

SQL> select /*+FULL(a)*/ count(*) from crm_ivpn_inst.tb_cm_vpn_member_num a;

  COUNT(*)

----------

   7685202

附：

1、關於_allow_resetlogs_corruption

2、關於ADJUST_SCN