Upgrade Oracle Database from 10g to 11g on another host

In this post we will discuss about restoring 10g database into new host with 11g oracle software installed.
Source database is 10.2.0.1, O/S is Windows server 2003 64 bit
On destination server installed oracle database software 11.2.0.1, O/S is Windows server 2012 64 bit

Note : direct upgrade between these versions is not possible, see Oracle Documentation http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#UPGRD12358, hence we need to upgrade source database version from 10.2.0.1 to 10.2.0.2 or(higher)
after applying patchset 10.2.0.5, we can continue upgrading to 11g on another host.

The steps for 10g database:
1) Run the utlu112i.sql Pre-upgrade script. You can find this script under @%ORACLE_HOME%\rdbms\admin\. It must be copied from the 11g database software.

SQL> @%ORACLE_HOME%\rdbms\admin\utlu112i.sql

it performs following operation

SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
SQL> UPDATE registry$database set tz_version =4;
SQL> ALTER  PACKAGE “SYS”.”DBMS_REGISTRY”  COMPILE BODY;
SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE”  COMPILE;

2) Connect to 10g database and take RMAN full backup.

run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
backup tag test_full database format "E:\databasefiles_%d_%u_%s_%T";
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP archivelog all format "E:\archivelogs_%d_%u_%s_%T";
backup current controlfile format "E:\control01.cf";
RELEASE CHANNEL RMAN_BACK_CH01;
}

3) Copy 10g database backup files and archive files to 11g database server.

The steps for 11g database:
1) Create temporary pfile in $ORACLE_HOME$\dbs and change path of directories parameters as you have.

2) Open the database in NOMOUNT state.

rman target /

RMAN> startup nomount;

3) Restore controlfile from backup.

RMAN> restore controlfile from 'D:\backup\control01.cf';

4) Open the database in MOUNT state.

RMAN> alter database mount;

5) Make crosscheck backup and archive log, for delete expireds, Catalog RMAN backup files and archive log files.

RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete expired backup;
RMAN> delete expired archivelog all; 
RMAN> catalog start with 'D:\backup';

6) restore and recover 10g database backup

RMAN> run
{
allocate channel c1 device type disk FORMAT 'D:\backup';
SET NEWNAME FOR DATAFILE 1 TO ‘D:\oradata\sd01\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO ‘D:\oradata\sd01\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO ‘D:\oradata\sd01\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO ‘D:\oradata\sd01\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO ‘D:\oradata\sd01\USERS02.DBF';
SET NEWNAME FOR DATAFILE 6 TO ‘D:\oradata\sd01\USERS03.DBF';
SET NEWNAME FOR DATAFILE 7 TO ‘D:\oradata\sd01\USERS04.DBF';
restore database;
switch datafile all;
recover database;
}

7) open database with upgrade mode

SQL> shutdown immediate;
SQL> startup upgrade;

8) Run the upgrade script

SQL> SPOOL upgradelog.log
SQL> @%ORACLE_HOME%\rdbms\admin\catupgrd.sql
SQL> SPOOL off

9) If our 10g and 11g database os platforms are different then you must run utlmmig.sql script

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL upgradelog.log
SQL> @%ORACLE_HOME%\rdbms\admin\utlmmig.sql
SQL> SPOOL off

10) open the database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11) Run the Post-Upgrade script

SQL> @%ORACLE_HOME%\rdbms\admin\utlu112s.sql

12) Compile invalid objects

SQL> @%ORACLE_HOME%\rdbms\admin\utlrp.sql

that's all, have a good upgrade.