数据库恢复时遭遇ORA-01152

故障现象：
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+OCR/eisoo/datafile/system.256.901474327'

故障原因：
在一次全库恢复中，首先恢复控制文件，恢复成功，但是在恢复数据文件的时候发现其中有一个数据文件不存在，导致数据文件恢复失败

分析：由于此时控制文件是从备份中恢复，而数据库文件还是原来的文件，controlfile里所记录的scn与datafile里的scn不一致，数据库open是抛错

解决办法：
以datafile上的scn为基准，将controlfile恢复到与datafile scn一致（这样能够保证数据不丢失，但是前提是归档日志存在）

SQL> recover database using backup controlfile;
ORA-00279: change 2782940 generated at 05/20/2016 17:36:28 needed for thread 1
ORA-00289: suggestion : +DATA/logs/1_14_912180304.dbf
ORA-00280: change 2782940 for thread 1 is in sequence #14


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2782940 generated at 05/20/2016 17:36:28 needed for thread 2
ORA-00289: suggestion : +DATA/logs/2_9_912180304.dbf
ORA-00280: change 2782940 for thread 2 is in sequence #9


ORA-00279: change 2782956 generated at 05/20/2016 17:37:51 needed for thread 2
ORA-00289: suggestion : +DATA/logs/2_10_912180304.dbf
ORA-00280: change 2782956 for thread 2 is in sequence #10
ORA-00278: log file '+DATA/logs/2_9_912180304.dbf' no longer needed for this
recovery


ORA-00308: cannot open archived log '+DATA/logs/2_10_912180304.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/logs/2_10_912180304.dbf
ORA-15173: entry '2_10_912180304.dbf' does not exist in directory 'logs'

根据提示还缺少三个日志文件，但是查看归档如下：
2_8_911148576.dbf
2_8_911161131.dbf
2_8_912180304.dbf
2_9_902230546.dbf
2_9_911148576.dbf
2_9_911161131.dbf
2_9_912180304.dbf

归档中日志序列号只到9（只看912180304对应文件），说明所需数据在联机日志组中
SQL> recover database using backup controlfile;
ORA-00279: change 2782956 generated at 05/20/2016 17:37:51 needed for thread 2
ORA-00289: suggestion : +DATA/logs/2_10_912180304.dbf
ORA-00280: change 2782956 for thread 2 is in sequence #10


Specify log: {=suggested | filename | AUTO | CANCEL}
+ocr/eisoo/onlinelog/group_4.266.901474871
ORA-00279: change 2782956 generated at 05/20/2016 17:36:28 needed for thread 1
ORA-00289: suggestion : +DATA/logs/1_14_912180304.dbf
ORA-00280: change 2782956 for thread 1 is in sequence #14


Specify log: {=suggested | filename | AUTO | CANCEL}
+ocr/eisoo/onlinelog/group_2.262.901474597
ORA-00279: change 2782961 generated at 05/20/2016 17:37:53 needed for thread 1
ORA-00289: suggestion : +DATA/logs/1_15_912180304.dbf
ORA-00280: change 2782961 for thread 1 is in sequence #15
ORA-00278: log file '+ocr/eisoo/onlinelog/group_2.262.901474597' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
+ocr/eisoo/onlinelog/group_1.261.901474589
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file '+ocr/eisoo/onlinelog/group_1.261.901474589' no longer
needed for this recovery


Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.


成功！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2103947/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30373263/viewspace-2103947/