一个表的数据被删掉,不清楚具体时间,得恢复到几天前的数据,准备恢复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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22531473/viewspace-749506/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22531473/viewspace-749506/