Solved

file 1 was not restored from a sufficiently old backup

Posted on 2013-01-29
14
1,354 Views
Last Modified: 2013-02-01
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?
0
Comment
Question by:YZlat
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38831804
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).

Check:
http://ora-01152.ora-code.com/
0
 
LVL 35

Author Comment

by:YZlat
ID: 38831909
any idea what can i do at this point?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 75 total points
ID: 38831946
>>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:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#i1011846

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

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#i1006192
0
 
LVL 35

Author Comment

by:YZlat
ID: 38832008
i tried to restore it to the time of last backup from yesterday but got the same error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38832042
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.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 150 total points
ID: 38832187
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

RMAN> RESTORE DATABASE UNTIL TIME 'sysdate - 3';

Then to do full recovery until your last archive log:

RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

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

RMAN> RESTORE DATABASE UNTIL TIME 'sysdate - 3';
RMAN> RECOVER DATABASE UNTIL TIME 'sysdate - 1';
RMAN> ALTER DATABASE OPEN RESETLOGS;


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

RMAN> RESTORE DATABASE FROM BACKUPSET ...;

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).
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 150 total points
ID: 38832205
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


and:

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

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Author Comment

by:YZlat
ID: 38832238
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38832456
YZLat,

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.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38832701
Still getting an error while opening database:(
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38832710
Is it possible that your backup was taken after your recovery until time?
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 75 total points
ID: 38833265
set nls_date_format=dd/mm/yyyy hh24:mi:ss

what time is your controlfile on in your restored database
query:
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';
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 38835240
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.
0
 
LVL 35

Author Comment

by:YZlat
ID: 38835573
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now