file 1 was not restored from a sufficiently old backup

I was running database recovery when I got that error.

here is the script I ran:

run { 
  set until time  "TO_DATE('29-JAN-2013 10:51:00','DD-MON-YYYY HH24:MI:SS')";
  restore database;
  recover database;
  alter database open resetlogs;

Open in new window

and here is the error I got:

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u02/oradata/DBName/redo02.log
archived log for thread 1 with sequence 5 is already on disk as file /u03/oradata/DBName/redo01.log
RMAN-08187: WARNING: media recovery until SCN 805970 complete
Finished recover at 29-JAN-13

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 01/29/2013 11:02:25
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oradata/DBName/system01.dbf'

Open in new window

can anyone help?
LVL 35
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
File 1, likely the SYSTEM datafile is newer than your recovery until time.

You need a version older than your recovery time.  You cannot use RMAN to roll back a database to a prior point in time (at least I don't think you can... never tried).

YZlatAuthor Commented:
any idea what can i do at this point?
slightwv (䄆 Netminder) Commented:
>>any idea what can i do at this point?

That depends on what you are trying to do.

If you want to recover to a point in time, restore the database to a point in time prior.

The docs have what you need for this:

Performing Database Point-in-Time Recovery
RMAN DBPITR restores the database from backups before the target time for recovery, then uses incremental backups and redo to roll the database forward to the target time.

If the date is outside the current incarnation, you need to follow these steps (I think I provided this link before):
Recovering the Database to an Ancestor Incarnation

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

YZlatAuthor Commented:
i tried to restore it to the time of last backup from yesterday but got the same error
slightwv (䄆 Netminder) Commented:
I'm afraid I don't know RMAN down to the nuts and bolts.  I typically go document diving myself.

I can open go by what the error shows:  The datafile is newer than the recovery time.  You need a datafile restored that is before the time you are recovering to.
Your question doesn't specify what you are trying to accomplish, just that you were "running database recovery". That could mean all sorts of things.

Your command shows that you are trying to do Point In Time Recovery (PITR). That means incomplete recovery. Is that your intention? Usually it is not, and instead you want full recovery. Remember there are 2 operations:

1) Restore (replaces existing data files with the backup copies)
2) Recover (applies redo from archive logs to roll forward)

When you specify an UNTIL TIME in a block, both commands are subject to that (as far as I remember, I dont use that form, instead I prefer the explicit syntax of providing to each command)

In your case, you need to RESTORE UNTIL TIME from a time old enough to force RMAN to restore the last backup. Then RECOVER DATABASE which will roll forward by applying all archive logs.

You weren't clear whether you wanted a full recovery, or to recover up to a point in time (assuming some data got corrupted, and you want to get th snapshot before that time, you need an incomplete recovery).

Here is an example I use a lot. This example is a 3 day old backup, and then variations on how far you want to recover.

If you just want to go back at least 3 days, use this with RMAN

startup mount


Then to do full recovery until your last archive log:


Or perhaps you want to recover until 1 day ago (after restoring from the 3 day old backup):


Or you can find the specific backupset by listing backups, then explicitly restore from it:


For the recover command, make sure to specificy how far forward to recover, or just plain recover without an argument will recover fully (except when you have set an UNTIL TIME in a block statement as you have in your first script).
In case I wasn't clear above, the key here is the difference between:

run {
  set until time ...;   -- until time applies to both restore and recover commands within the block
  restore database; -- restore last backup before time
  recover database;  -- recover until time (PITR - wont roll forward all the way)

Open in new window


restore database until time ...;  -- restore last backup before time to allow roll forward
recover database;  -- Full recovery

Open in new window

YZlatAuthor Commented:
what happened was I was testing recovering from a loss of a datafile. I removed the physical file and then ran the restore. File got copied back and everything, but then, when I attempted to open the dataabse, I got this error
slightwv (䄆 Netminder) Commented:

I am still monitoring this question but mrjoltcola is our resident Expert on RMAN.  Unless you need something from me, I will leave you in his very capable hands.
YZlatAuthor Commented:
Still getting an error while opening database:(
slightwv (䄆 Netminder) Commented:
Is it possible that your backup was taken after your recovery until time?
Geert GOracle dbaCommented:
set nls_date_format=dd/mm/yyyy hh24:mi:ss

what time is your controlfile on in your restored database
select controlfile_time from v$database;

check v$recover_file
select r.file#, r.time, d.filename from v$recover_file r, v$datafile d
where r.file# = d.file#
order by r.time desc;

all the files in v$recover_file have to be newer (or equal) to the controlfile_time
to be able to open the database, otherwise not enough recovery has been done

applying more redo logs could solve the problem.
recover database until time 'sysdate';
To the OP. Please try "recover database" without any set until time commands. You want a full recovery. Then let us know.

Also read my original post and let me know if you still have questions.
YZlatAuthor Commented:
Full recovery didn't work either, same issue.

I have been stuck unable to move forward so i wa sforced to just drop that tablespace in order to be able to open the database
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.