Restore data from an old rman backup - ORA-01152

Question

I have some trouble restoring some data from an old backup.

Original machine was lost and my mission is to restore data. This is not a critical machine, but some data is still useful.

• Oracle on original server: 11.2.0.2
• Oracle on destination server: 11.2.0.4

Server is redhat- 5.6 original. 6.x destination.

On the new machine, with oracle installed I did:

export ORACLE_SID=oldsid
rman TARGET /

And in rman i set the sid as the old databsae:

SET DBID 3348161342;

Then restored controlfile and spfile from the backup. Important note: these files are more recent than the backup.

RESTORE CONTROLFILE from '/mount/restorepoint/3348161342_41qtltn4_1_1_20160111.bck
restore spfile from '/mount/restorepoint/sp_OLDDB-3348161342_42qtltn6_1_1_20160111.bck';

Database in mount with:

ALTER DATABASE MOUNT;

And specified where the backup files are:

catalog start with '/mount/restorepoint/rmanfiles';

Because on the original db there is also TDE.

• Created a directory to put wallet
• Created sqlnet.ora files with indications on the wallet location

Altered system with:

alter system set encryption wallet open identified by "thetdepassword";

And then again in rman:

alter database mount;
RESTORE DATABSE;

So far, everything fine. But when I trai to OPEN database:

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

Fine, my bad. Then I use: alter database open RESETLOGS; and I have the error:

ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/ltm/data/oracle/dati/olddb/users01.dbf'

What am I missing?
In the original backup script options must be:

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE

How can I read again that data?

I come from sqlserver and postgresql world, so probably I miss something...

Answer 1

Lets see the information provided by oerr utility.

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

According to your post you have not recovered the database. You may try the following commands to recover your database.

SQL> recover database using backup controlfile until cancel;
SQL>alter database open resetlog;

Since you are new to Oracle, you may find the following link useful to restore old backup to new host.
Restoring a Database on a New Host

Answer 2

You have to recover the database. I assume you restored an online backup that you (or an inconsistent offline backup, but we will ignore this possiblility). The next step is to recover the database. So in your rman session execute a

recover database;

or a

recover database until ....;

where '....' is a point in time (or SCN or something similar). If you use a point in time it must be after the end time of the data file backup. Otherwise the data files will remain inconsistent.

The restore process will apply incremental level 1 backups (if they exists) and archive logs to your database.

After you have restored the database you maybe will run into the next problems because you restore a 11.2.0.2 database to a 11.2.0.4 environment. Therefore you must combine your restore with an upgrade to 11.2.0.4.

The detailed instruction for an upgrade to 11.2.0.4 can be found in the 11.2.0.4 software bundle. Here is a short blob article I googled (rkorclappsdbastuff). From this we see there are some preugrade steps (step 2 to 5) you have to do on the 11.2.0.2 database, so you have to skip them: you cannot run the preupgrade script utlu112i.sql and implement its recommendations, you also cannot calculate the dictionary statistics before the upgrade and you cannot run utlrp.sql to compile the invalid objects. You have to adapt the restored spfile to 11.2.0.4: create a pfile , change/add/remove parameters, if necessary an recreated an spfile again from the spfile. You nead no database to execute the create pfile and create spfile.

Now you can open the database but you must open it in upgrade mode because it is an 11.2.0.2 database that must be upgraded to 11.2.0.4 and you mast open it with reset logs, because it is a recovered database without logfiles. So the right command (see the Oracle SQL Language Reference) is

REM step 12 of rkorclappsdbastuff
startup mount
alter database open resetlogs upgrade ;
@catupgrd.sql

Now you can proceed with the remaining steps.

Answer 3

If you have more than one backup of your control file, try using the oldest backup. Essentially the message is telling you that the control file is newer than the rest of the backup. for example if you backup the control file, then backup the data base, then backup the control file. Doing a restore using the second backup of the control file will result in the error that you are getting. Doing a restore using the older or first backup of the control file should work.