Restore in between Upgrades

Requirement

Had to restore a database to a point-in-time from a hotbackup. Followed Steps:

• Copied datafiles to target
• Created a New Controlfile
• Recovered using syntax: RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI' USING BACKUP CONTROLFILE;
• Ran ALTER DATABASE OPEN RESETLOGS;

... and that's when the wheels fell off, with the database instance terminated and errors in the alert log advising startup upgrade needs to be done.

The reason for this is that the target database was upgraded sometime after the point in time recovery date.

In the past, when faced with this situation, I was able to point the database and the /etc/oratab entries to the old Oracle Home and recover that way, but in this instance the old home had been removed and well a more elegant solution needed to be found.

Resolution:

• Copied datafiles to target
• Created a New Controlfile
• Recovered using syntax: RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI' USING BACKUP CONTROLFILE;
• Ran ALTER DATABASE OPEN RESETLOGS UPGRADE;

The next step was to apply the upgrade scripts and breathe a sigh of relief!

Update September 2012 (Highly Experimental - follow at your own risk)

Back when I documented the above, I was working on a version 9 database. Today on an 11g database using ASM I had to refresh an 11.2.0.3.0 database from an 11.2.0.2.0 rman hotbackup. I chose to use a standard duplicate command.

As anticipated, the tailend of the duplicate recovery process (most likely the alter database open resetlogs part) failed and the database instance was terminated. Expecting I would have to perform some more recovery before trying to run ALTER DATABASE OPEN RESETLOGS UPGRADE, I mounted the database and ran the command RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL. This returned the error:

ORA-16433: The database has not been opened in read-write mode

I figured that my recovered controlfile probably has the database mode written to it and recreating a controlfile using ALTER DATABASE BACKUP CONTROLFILE TO TRACE; may do the trick. However, with the database being in the state whereby an ORA-16433 had been triggered, this was not possible.

So, option two in my mind was to simply get a backup controlfile script from my source database. The only problem with this approach was that with ASM system generated filenames, the datafiles listed in my backup controlfile did not equate to the same system generated filenames of my target database. So, I decided to cheat and simply did a listing of my target datafiles through asmcmd> ls -l and copied these listed files into my backup controlfile script.

I then removed my current controlfiles from the target, started up the database in nomount mount, created the controlfile using my backup controlfile script and this allowed me to mount the database in read/write mode and commence recovery.

At this point, the last issue encountered is that the command RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; indicated that the next archivelog required by the database to complete media recovery was the first archivelog generated by the new incarnation of the database. This actually hadn't been written out, so all I did was point my recovery to the online redolog:

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'+REDO1/PCEDEV/ONLINELOG/group_1.275.793285995'

Log applied.
Media recovery complete.

This led me to the final stage:

ALTER DATABASE OPEN RESETLOGS UPGRADE;

followed by the running of the upgrade script:

SQL>@?rdbms/admin/catupgrd.sql
catupgrd.sql

and a recompile of invalid objects

SQL>@?rdbms/admin/utlrp.sql