Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Tuesday, February 26, 2013

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

Scenerio: After restring and recovering database got ORA-01152 while opening the database with resetlogs. Backup was taken without taking the archive logs. Perform the following:

 set until scn 1167586;
 restore database;

executing command: SET until clause

Starting restore at 26-FEB-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\INAM\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\INAM\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\INAM\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\INAM\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\INAM\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to C:\APP\INAM\ORADATA\ORCL\TS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to C:\APP\INAM\ORADATA\ORCL\TSNEW.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\INAM\PRODUCT\11.2.0\DBHOME_1\DATABASE\02O2U4DA_1_1
channel ORA_DISK_1: piece handle=C:\APP\INAM\PRODUCT\11.2.0\DBHOME_1\DATABASE\02O2U4DA_1_1 tag=TAG20130226T090442
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-FEB-13

RMAN> recover database;

Starting recover at 26-FEB-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-FEB-13

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/26/2013 09:26:17
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\APP\INAM\ORADATA\ORCL\SYSTEM01.DBF'

RMAN> exit

C:\Users\inam.HOME>sqlplus / as sysdba

SQL*Plus: Release Production on Tue Feb 26 09:36:54 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel
ORA-00279: change 1167586 generated at 02/26/2013 08:40:26 needed for thread 1
ORA-00289: suggestion :
ORA-00280: change 1167586 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.


No comments: