oracle ora 01152,ORA-01152: file 1 was not restored from a sufficiently old backup

所有控制文件和数据文件丢失，使用rman从备份集中恢复：

SQL> startup;

ORACLE instance started.

Total System Global Area  218103808 bytes

Fixed Size                  1218604 bytes

Variable Size              62916564 bytes

Database Buffers          150994944 bytes

Redo Buffers                2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  218103808 bytes

Fixed Size                  1218604 bytes

Variable Size              62916564 bytes

Database Buffers          150994944 bytes

Redo Buffers                2973696 bytes

[oracle@centos ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 9 06:01:06 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: orcl (not mounted)

RMAN>set dbid 1337758187-- 设置DBID

executing command: SET DBID

RMAN>restore controlfile from'/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2013_04_08/o1_mf_s_812177402_8p3pzv19_.bkp';-- 恢复控制文件

Starting restore at 09-APR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/u01/app/oracle/oradata/orcl/control01.ctl

output filename=/u01/app/oracle/oradata/orcl/control02.ctl

output filename=/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at 09-APR-13

RMAN>sql 'alter database mount';-- mount 数据库

sql statement: alter database mount

released channel: ORA_DISK_1

RMAN>restore database;-- restore数据文件

Starting restore at 09-APR-13

Starting implicit crosscheck backup at 09-APR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

Crosschecked 7 objects

Finished implicit crosscheck backup at 09-APR-13

Starting implicit crosscheck copy at 09-APR-13

using channel ORA_DISK_1

Finished implicit crosscheck copy at 09-APR-13

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2013_04_08/o1_mf_s_812177402_8p3pzv                                           19_.bkp

File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_04_09/o1_mf_1_13_8p6h3zwk_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/orcl/test01.dbf

restoring datafile 00006 to /u01/app/oracle/oradata/orcl/test02.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2      013_04_08/o1_mf_nnnd0_TAG20130408T044814_8p3pwhd1_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_04_08/o1_mf_nnnd0_TAG20130408T044814_8p3pwhd1_.bkp tag=TAG20130408T044814

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 09-APR-13

RMAN>sql 'alter database open';

sql statement: alter database open

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of sql command on default channel at 04/09/2013 06:03:15

RMAN-11003: failure during parse/execution of SQL statement: alter database open

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of sql command on default channel at 04/09/2013 06:03:23

RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

-- 因为前面恢复的控制文件太老，太旧了。所以其scn比数据库文件头部中的scn要小，所以报错：ORA-01152说数据文件不是从足够老的备份中恢复的。

有两种思路：

1、以old controlfile为准的，datafile上的scn是新的，这样存在着数据的不一致，要继续恢复下去，

将datafile上的scn也要restore到与controlfile一致的情况，但这样会丢失datafile上的数据。

2、就是以datafile上的scn为基准，将controlfile恢复到与datafile scn一致：

RMAN> recover database; ---找出同步controlfile scn和datafile scn所需的archivelog，

---如果归档目录缺少所列出log就从备份里(比如说是在磁带里)取出来并放回归档目录

Starting recover at 09-APR-13

using channel ORA_DISK_1

starting media recovery

archive logthread 1 sequence 14is already on disk as file /u01/app/oracle/oradata/orcl/redo02.log

archive log filename=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=14

media recovery complete, elapsed time: 00:00:04

Finished recover at 09-APR-13

-- 然后进行时间点的数据恢复(大于之前所需archivelog中的最后一个archivelog即可)

这样就恢复出一致性的数据，然后用open resetlogs打开数据即可，但记得resetlogs后应该全备一次当前数据库。

当然，如果不愿意用resetlogs后，重建controlfile后，用noresetlogs也是ok的。此方法同样适合于RAC数据库的恢复测试时遇到的ORA-01152错误。

RMAN> run{

allocate channel d1 type disk;

allocate channel d2 type disk;

set untilsequence 15 thread 1;

recover database;

release channel d1;

release channel d2;

}

released channel: ORA_DISK_1

allocated channel: d1

channel d1: sid=155 devtype=DISK

allocated channel: d2

channel d2: sid=154 devtype=DISK

executing command: SET until clause

Starting recover at 09-APR-13

starting media recovery

archive log thread 1 sequence 14 is already on disk as file /u01/app/oracle/oradata/orcl/redo02.log

archive log filename=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=14

media recovery complete, elapsed time: 00:00:03

Finished recover at 09-APR-13

released channel: d1

released channel: d2

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS

------------

OPEN

其实，上面sql 'alter database open resetlogs';表错：ORA-01152: file 1 was not restored from a sufficiently old backup 的原因是，忘记了 在 restore database之后，还要执行 recover database. ,所以才会报错：ora-01152