RMAN Restore backup of lower version database to a higher version

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.

 

1 2 3 4 5 6 7

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.

1 2 3 4 5 6 7

[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]$

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[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

1 2 3 4 5 6 7 8 9 10

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

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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.

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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.

 

1 2 3 4 5 6 7 8 9

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.

 

1 2 3 4 5 6

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.

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

[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