2

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...

2
  • 1
    @user0: The backup was online or offline? If the backup was online, what in /mount/restorepoint/rmanfiles? Only datafile backup or archivedlog backup too? Seems that your datafiles have been restored but not recovered. I think that, at least at this stage, this is not a controlfile problem. Let me know
    – Giova
    Feb 15, 2016 at 16:48
  • I'm gathering infomations and studing answer. I will reply as soon as possibly.
    – user_0
    Feb 16, 2016 at 15:00

3 Answers 3

3

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

1
  • Well, I gave up. To much effort for a non critical db. Anyway I did the new set up for backup, including controlfile, and tested the restore on another host as the document you linked. Some more trouble for the TDE and vault, but at the end, everything fine. I discovered the previous dba never tested the restore :-O
    – user_0
    Feb 24, 2016 at 10:25
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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.