Steps for RMAN Restore backup of lower version database to a higher version

Source DB Name       :   TESTDB

Source DB Version     :   11.2.0.1.0

Source DB Host Name   :   HOSTNAME_1

Target DB Name       :   TESTDB

Target DB Version     :   11.2.0.4

Target DB Host Name   :   HOSTNAME_2

 

Step-1

=====

Installing the Oracle binary version 11.2.0.4 on HOSTNAME_2.

 

Step – 2

======

Taking a full backup of Database TESTDB from Source server (HOSTNAME_1),

Below command can be run on the source database RMAN prompt.

While finishing the backup it will be copied to the target location

@ HOSTNAME_1 ———> This should be executed in the Source database.

rman target / nocatalog

 

run {

allocate channel disk_1 type disk format ‘\\HOSTNAME_2\Source_backup\%U’;

backup keep until time ‘SYSDATE+3’ as BACKUPSET tag ‘%TAG’ format ‘\\HOSTNAME_2\Source_backup\hot_%d_%t’ database;

backup as BACKUPSET tag ‘%TAG’ format ‘\\HOSTNAME_2\Source_backup\%d_log_%d_%t’ archivelog all not backed up 1 times;

delete noprompt force obsolete device type disk;

delete noprompt expired backup;

delete noprompt expired archivelog all;

delete force obsolete;

release channel oem_backup_disk1;

backup current controlfile format ‘\\HOSTNAME_2\Source_backup\clone_TESTDB.ctl’;

}

 

Step -3

======

 

D:\programs\product\11204\dbhome_1\BIN>oradim.exe -NEW -SID TESTDB -pfile D:\programs\product\11204\dbhome_1\dbs\initTESTDB.ora

Instance created.

 

Step-4

=====

 

Copy the initTESTDB to D:\programs\product\11204\dbhome_1\database

 

SQL> CONN / AS SYSDBA

Connected to an idle instance

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 1081520128 bytes

Fixed Size                 2288080 bytes

Variable Size             574621232 bytes

Database Buffers         499122176 bytes

Redo Buffers                5488640 bytes

SQL> exit

 

Note: In windows init.ora file should be in $ORACLE_HOME/database folder instead of $ORACLE_HOME/dbs folder.

 

Step -5

======

Restore the control file form the backup.

 

D:\programs\product\11204\dbhome_1\BIN>rman target /

 

Recovery Manager: Release 11.2.0.4.0 – Production on Sun Nov 15 09:32:50 2015

 

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

 

connected to target database: TESTDB (not mounted)

 

RMAN> restore controlfile from ‘D:\Source_backup\CLONE_TESTDB.CTL’;

 

Starting restore at 15-NOV-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=122 device type=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL01.CTL

output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL02.CTL

output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL03.CTL

Finished restore at 15-NOV-15

 

Step -6

======

 

Restore and recover the database.

 

RMAN> catalog start with ‘D:\Source_backup’;

 

RMAN> run

2> {

3> set newname for datafile 1 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\SYSTEM01.DBF’;

4> set newname for datafile 2 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\SYSAUX01.DBF’;

5> set newname for datafile 3 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\UNDOTBS01.DBF’;

6> set newname for datafile 4 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\USERS01.DBF’;

7> restore database;

8> switch datafile all;

9> recover database;

10> }

 

Step -7

=====

 

Rectifying log location mismatch error.

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00344: unable to re-create online log

‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1A.ORA’

ORA-27040: file create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 3) The system cannot find the path specified.

 

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG3A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG3A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG3B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG3B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG2A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG2A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG2B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG2B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG1A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG1B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG4A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG4A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG4B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG4B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG5A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG5A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG5B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG5B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG6A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG6A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG6B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG6B.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG7A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG7A.ORA’;

alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG7B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG7B.ORA’;

 

Step – 8

======

 

Now open the database with “ALTER DATABASE OPEN RESETLOGS UPGRADE” command.

 

SQL> alter database open resetlogs upgrade;

Database altered.

 

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

 

Step -9

======

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

 

SQL> spool catupgrade.log

SQL> @D:\programs\product\11204\dbhome_1\RDBMS\ADMIN\catupgrd.sqlplus SQL> spool catupgrade.log

 

Note: If any errors are encountered, fix them and re-run the script before proceeding further.

 

Step -10

=======

Now start the target database normally and look out for any INVALID objects. Compile them by running the UTLRP.SQL script

 

sqlplus / as sysdba

SQL> startup

SQL> @D:\programs\product\11204\dbhome_1\RDBMS\ADMIN\utlrp.sql

 

And check all the registry components are valid as per the Source.

 

SQL> SET PAUSE ON

SQL> SET PAUSE ‘Press Return to Continue’

SQL> SET PAGESIZE 60

SQL> SET LINESIZE 300

SQL> SET VERIFY OFF

SQL> COL comp_name FOR a44 HEA ‘Component’

SQL> COL version FOR a17 HEA ‘Version’

SQL> COL status FOR a17 HEA ‘Status’

SQL>

SQL> SELECT comp_name, version, status FROM dba_registry;

 

COMP_NAME                               VERSION             STATUS

—————————————- ——————– ———–

Oracle Text                             11.2.0.4.0           VALID

Oracle Application Express               3.2.1.00.10         INVALID

Oracle Multimedia                       11.2.0.4.0           VALID

Oracle XML Database                     11.2.0.4.0           VALID

Oracle Expression Filter                 11.2.0.4.0           VALID

Oracle Rules Manager                    11.2.0.4.0           VALID

Oracle Workspace Manager                 11.2.0.4.0           VALID

Oracle Database Catalog Views           11.2.0.4.0           VALID

Oracle Database Packages and Types       11.2.0.4.0           VALID

JServer JAVA Virtual Machine             11.2.0.4.0           VALID

Oracle XDK                               11.2.0.4.0           VALID

Oracle Database Java Packages           11.2.0.4.0           VALID

 

12 rows selected.

 

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s