RMAN Restore backup of lower version database to a higher version

In this article, I’m demonstrating on how to restore a backup of 11.2.0.2 database on 11.2.0.3 version. Basically, this post demonstrates on how to restore a database backup of lower version on a higher version.

 

Source DB Name        :   TESTDB
Source DB Version     :   11.2.0.2
Source DB Host Name   :   ora1-1

Target DB Name        :   TESTDB
Target DB Version     :   11.2.0.3
Target DB Host Name   :   ora1-2

 

The steps involved is quite simple and is jus the traditional restore and recovery operation. The only additional step in this would be to not open the database with RESETLOGS after recovery, but instead open the database with RESETLOGS UPGRADE clause after the recovery operation.

Opening the database with just RESETLOGS would terminate the instance and would write the message that the database needs to be opened in upgrade mode in the alert log. Once the database is opened with RESETLOGS UPGRADE option, follow the usual process of manual upgrade of the database.

Let me demonstrate this with an example.

I create a simple PFILE with just “DB_NAME=testdb” entry in the target host and start the instance in NOMOUNT.

[oracle@ora1-2 ~]$ export PATH=/usr/lib64/qt-3.3/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.3/db1/bin
[oracle@ora1-2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1
[oracle@ora1-2 ~]$ export ORACLE_SID=testdb
[oracle@ora1-2 ~]$ cd $ORACLE_HOME/dbs
[oracle@ora1-2 dbs]$ cat inittestdb.ora
*.db_name='testdb'
[oracle@ora1-2 dbs]$

[oracle@ora1-2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 30 18:26:17 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes

 

Now let me begin with the restore activity. First with the restore of SPFILE from the backup and then the controlfile.

 

[oracle@ora1-2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 30 18:31:38 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTDB (not mounted)

RMAN> restore spfile from '/u03/bkp/o1_mf_ncsn0_TAG20150830T201857_by661lbz_.bkp';

Starting restore at 30-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/bkp/o1_mf_ncsn0_TAG20150830T201857_by661lbz_.bkp;
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 30-AUG-15

RMAN> restore controlfile from '/u03/bkp/o1_mf_ncsn0_TAG20150830T201857_by661lbz_.bkp';

Starting restore at 30-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u03/oradata/testdb/control01.ctl
output file name=/u03/oradata/testdb/control02.ctl
Finished restore at 30-AUG-15

 

Once the controlfile is restored, mount the instance and catalog the backup pieces (if the backup pieces are stored on a different location in the target server than the location it was originally taken on the source host).

 

RMAN> catalog start with '/u03/bkp/';

searching for all files that match the pattern /u03/bkp/

List of Files Unknown to the Database
=====================================
File Name: /u03/bkp/o1_mf_annnn_TAG20150830T202029_by661p20_.bkp
File Name: /u03/bkp/o1_mf_nnnd0_TAG20150830T201857_by65ysrh_.bkp
File Name: /u03/bkp/TESTDB_inc0_0qqfu28b_1_1.bak
File Name: /u03/bkp/o1_mf_ncsn0_TAG20150830T201857_by661lbz_.bkp
File Name: /u03/bkp/ctl.bkp
File Name: /u03/bkp/o1_mf_ncnnf_TAG20150830T202034_by661vv4_.bkp
File Name: /u03/bkp/TESTDB_inc0_0nqfu25d_1_1.bak

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/bkp/o1_mf_annnn_TAG20150830T202029_by661p20_.bkp
File Name: /u03/bkp/o1_mf_nnnd0_TAG20150830T201857_by65ysrh_.bkp
File Name: /u03/bkp/TESTDB_inc0_0qqfu28b_1_1.bak
File Name: /u03/bkp/o1_mf_ncsn0_TAG20150830T201857_by661lbz_.bkp
File Name: /u03/bkp/ctl.bkp
File Name: /u03/bkp/o1_mf_ncnnf_TAG20150830T202034_by661vv4_.bkp
File Name: /u03/bkp/TESTDB_inc0_0nqfu25d_1_1.bak

 

Start with the restore and recovery operations of the database.

 

RMAN> run
{
set newname for datafile 1 to '/u03/oradata/testdb/system01.dbf';
set newname for datafile 2 to '/u03/oradata/testdb/sysaux01.dbf';
set newname for datafile 3 to '/u03/oradata/testdb/undotbs01.dbf';
set newname for datafile 4 to '/u03/oradata/testdb/users01.dbf';
restore database;
switch datafile all;
recover database until sequence 28;
}

... output trimmed ...

archived log file name=/u03/oradata/fra/TESTDB/archivelog/2015_08_30/o1_mf_1_25_by67km3s_.arc RECID=21 STAMP=889130763
archived log file name=/u03/oradata/fra/TESTDB/archivelog/2015_08_30/o1_mf_1_26_by67km44_.arc thread=1 sequence=26
channel default: deleting archived log(s)
archived log file name=/u03/oradata/fra/TESTDB/archivelog/2015_08_30/o1_mf_1_26_by67km44_.arc RECID=23 STAMP=889130763
archived log file name=/u03/oradata/fra/TESTDB/archivelog/2015_08_30/o1_mf_1_27_by67km3w_.arc thread=1 sequence=27
channel default: deleting archived log(s)
archived log file name=/u03/oradata/fra/TESTDB/archivelog/2015_08_30/o1_mf_1_27_by67km3w_.arc RECID=22 STAMP=889130763
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-AUG-15

 

Now open the database with “ALTER DATABASE OPEN RESETLOGS UPGRADE” command.
If you would try opening with just “ALTER DATABASE OPEN RESETLOGS”, then it might fail with the below error.

 

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6773
Session ID: 19 Serial number: 25

 

Once opened, run the CATUPGRD.SQL script on the target database to upgrade the database.

 

SQL> alter database open resetlogs upgrade;

Database altered.

SQL> spool catupgrade.log
SQL> @?/rdbms/admin/catupgrd.sqlplus

 

If any errors are encountered, fix them and re-run the script before proceeding further.
Now start the target database normally and look out for any INVALID objects. Compile them by running the UTLRP.SQL script.

 

[oracle@ora1-2 testdb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 30 21:41:10 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 943669248 bytes
Fixed Size 2234000 bytes
Variable Size 335546736 bytes
Database Buffers 599785472 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.

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

 

Add tempfiles to the database based on the requirement.

 

Conclusion:

 

It’s possible to restore backup of lower version database on a higher version provided the minimum version matrix matches (Refer MOS for the version matrix).

RMAN duplicate from lower version to higher version does not allow, because it automatically tries to open the database with resetlogs after recovery. So until 11gR2, this can be done through the traditional RMAN restore and recovery operation. Please note that, the database needs to be opened with RESETLOGS UPGRADE option after the recovery.

RMAN duplicate in 12c has an option which allows to not to open the database automatically after the recovery. Let’s discuss on this in coming posts.

 

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.