数据库恢复后打开时遇到 ORA-01152的解决方法

数据库恢复后打开时遇到 ORA-01152

数据库恢复后打开时遇到：

RMAN> alter database open resetlogs;
......
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/u01/app/oracle/oradata/orcl/system01.dbf'

分析原因

oracle对于这个错误的解释是：

ORA-01152: file 1 was not restored from a sufficiently old backup  
Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.
Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.  

其实说得很清楚，就是说使用了一个旧的控制文件。备份里restore出的controlfile上的scn 小于 datafile上所记录的scn，连解决方法也说清楚了。一是以old controlfile的SCN为准，将datafile恢复到与其一致，这样就会导致数据的不一致。二是以datafile的SCN为准，将controlfile恢复到与其一致，此时就会报ORA-01152的错误，然后我们通过recover database，进而利用归档日志来完成controlfile和datafile的SCN一致，归档找不到再利用redolog来恢复。

恢复到超过controlfile的SCN状态

SQL> recover database using backup controlfile;
......
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.

注意：/u01/app/oracle/oradata/orcl/redo01.log是手工输入的。
此时恢复完成，应用完所有归档，并重新应用了redolog，启动数据库到open状态

SQL>alter database open resetlogs;
Database altered.

也可以进行时间点的数据恢复(大于之前所需archivelog中的最后一个archivelog即可)，例如： set until sequence 15 thread 1; 这个sequence 15 里面的scn是大于数据库里面的SCN的。这样就恢复出一致性的数据，然后用open resetlogs打开数据即可。

利用隐含参数"_allow_resetlogs_corruption"=true打开数据库

更改参数

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.

恢复数据库

SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;
SQL> startup force;

将内置参数改回来

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.

数据库启动到open状态后查询数据

文章下方是我的微信，欢迎加我。👇