Oracle Upgrade from Oracle10g to Oracle11g

Below are the steps to Upgrade the Oracle database from 10.2.0.3.0 to 11.2.0.1.0:

Step 1) Installing Oracle 11g Home:

Install 11g database as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is: D:\oracle10g\product\10.2.0\db_1, then my 11g Oracle Home is: E:\oracle\product\11.2.0\ db_1

Step 2) Pre-Upgrade Utility:

In 11g Home you installed, go to $ORACLE_HOME\rdbms\admin and copy the file 'utlu112i.sql' to some temp location (C:\temp).

Then login to the 10g oracle database AS SYSDBA and run 'utlu112i.sql' you copied to temp folder.

--This script provides information about databases to be upgraded to 11.2.

Step 3) Finding the Version of existing time zone files:

SQL> select * from v$timezone_file;

SQL> SELECT CASE COUNT (DISTINCT(tzname))

WHEN 183 then 1

WHEN 355 then 1

WHEN 347 then 1

WHEN 377 then 2

WHEN 186 then case COUNT (tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end

WHEN 185 then 3

WHEN 386 then 3

WHEN 387 then case COUNT (tzname) WHEN 1438 then 3 ELSE 0 end

WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end

WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end

WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end

WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end

ELSE 0 end VERSION

FROM v$timezone_names;

If the output is 4, then it is well and good and it requires no additional steps.

If the output of this is < 4 then prior to upgrade we need to apply the Timezone patch.

SQL> select banner from v$version;

It gives DB version and accordingly you have to find the patch at Metalink. Download the zipped patch and unzip it in temp folder (C:\temp).

Step 4) Applying Timezone patch:

Copy the files from location: C:\temp\patch_file\files\oracore\zoneinfo and paste it to existing Oracle 10g location “D:\oracle10g\oracore\zoneinfo”. Make sure to keep backup of existing folder so that you could revert if any problem occurs.

Bounce the database and check the TIMEZONE version again. Again run the 'utlu112i.sql' and by now, the timezone file version should be upgraded.

Step 5) Gather Dictionary stats:

You may have found some warning messages in the output of 'utlu112i.sql' execution. Please do execute the below scripts to avoid those warnings.

Connect as SYS user,

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYSMAN’);  (if OEM in use)

After executing the above recommended steps, run the pre-upgrade utility 'utlu112i.sql' once again to make sure, you don’t get any critical warnings.

Step 6) Starting Upgrade:

Copy the PFILE from 10g home to 11g home. In the copied PFILE, change the COMAPATIBLE parameter from '10.2.0.3' to '11.2.0.1'.

Delete the old listener service (10g) & create a new one using ORADIM utility.

Set the environment variables as in the below example.

SET ORACLE_HOME=C:\oracle11g\product\11.2.0\dbhome_1

SET PATH=C:\oracle11g\product\11.2.0\dbhome_1\bin:$PATH

SET TNS_ADMIN=C:\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN

SET ORACLE_SID=FIN89COP

Connect as SYS user,

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade;

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Then run the Catalog Upgrade ‘catupgrd.sql’ located in $ORACLE_HOME\RDBMS\admin.

SQL> @catupgrd.sql

--This script is to be used for upgrading a 9.2, 10.1 or 10.2 database to the new release.  This script provides a direct upgrade path from these releases to the new Oracle release.

Once the upgrades finishes, it will shut down the database automatically.

Step 7) Post-Upgrade Steps:

Login again as SYS and start the database in normal mode. 

SQL> STARTUP;

Check the dba_registry for the components and its status.

SQL> select comp_name,version, status from dba_registry;

Run utlu111s.sql to display the results of the upgrade:

SQL> @?/RDBMS/admin/utlu111s.sql

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @?/RDBMS/admin/catuppst.sql

Check if there are any invalid objects in the upgraded database.

SQL> select count (*) from dba_objects where status = ‘INVALID’;

If the invalid objects exist, run utlrp.sql to recompile.

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

SQL> select count (*) from dba_objects where status = ‘INVALID’;

This completes the upgrade.

Now the Pfile would be in 10g format. Shutdown the database, change the Pfile structure to 11g and bring it up.