Upgrading to Oracle Database 11g

Upgrading to Oracle Database 11g

Compatibility Matrix

Minimum Version of the database that can be directly upgraded to Oracle 11g Release 1


The following database version will require an indirect upgrade path.


This post is about upgrading the Oracle database from 10g to 11g.

This post is based on metalink note ID 429825.1

Below steps are the one which are tried and successfully implemented on linux RHEL 4.

For Oracle database 10g, we need REHL 3, but for 11g version the minimum OS level for linux is RHEL 4.

I have given the list of patches for RHEL 3.0 and RHEL 4.0

Red Hat Enterprise Linux 3.0 and Asianux 1.0:

Red Hat Enterprise Linux 4.0 and Asianux 2.0:

Packages is install.
linux@oracle]# rpm -q package_name

Once the OS is upto RHEL 4.0 level, you can start the upgrade process. Please follow the below steps for performing the upgrade.


You should have the Oracle database 10g, which you want to migerate.
OS level should be RHEL 4.0
Also here we are upgrading to Oracle Database 11g – Beta 6 (

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0

then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0

Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.

Start the installation using the below command

./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst.Loc


Screen 1 – Select Product Install
select “Oracle Database 11g”


Screen 2 – Select Installation Method
Choose “Advanced Installation”


Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.


Screen 4 – Select Installation Type
Choose “Enterprise Edition”


Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME


Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further

Checking for gcc-3.4.5; found gcc-3.4.6-3.1-i386. Passed
Checking for libaio-0.3.105; found libaio-0.3.105-2-i386. Passed
Checking for libaio-devel-0.3.105; found libaio-devel-0.3.105-2-i386. Passed
Checking for libstdc++-3.4.5; found libstdc++-3.4.6-3.1-i386. Passed
Checking for elfutils-libelf-devel-0.97; found elfutils-libelf-devel-0.97.1-3-i386. Passed
Checking for sysstat-5.0.5; found sysstat-5.0.5-11.rhel4-i386. Passed
Checking for libgcc-3.4.5; found libgcc-3.4.6-3.1-i386. Passed
Checking for libstdc++-devel-3.4.5; found libstdc++-devel-3.4.6-3.1-i386. Passed
Checking for unixODBC-2.2.11; Not found. Failed <<<<
Checking for unixODBC-devel-2.2.11; Not found. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<

Checking for rmem_default=4194304; rmem_default=262144. Failed <<<<
Checking for rmem_max=4194304; rmem_max=262144. Failed <<<<
Checking for wmem_default=262144; found wmem_default=262144. Passed
Checking for wmem_max=262144; found wmem_max=262144. Passed
Check complete. The overall result of this check is: Failed <<<<


Screen 7 – Upgrade an Existsing Database
Choose “No”


Screen 8 – Select Configuration Option
Choose “Install Software Only”


Screen 9 – Privileged system groups

Based on the group of oracle user, this value has to be set.


Screen 10 – Summary
Click on “Install”


At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

[oracle@ocvmrh2178 admin]$ cd $ORACLE_HOME
[oracle@ocvmrh2178 db_1]$ cd rdbms/admin/
[oracle@ocvmrh2178 admin]$ pwd
[oracle@ocvmrh2178 admin]$ cp utlu111i.sql /tmp

Then login to the 10g oracle database and run the above sql you copied.

Oracle Database 11.1 Pre-Upgrade Information Tool 09-07-2007 01:34:07
–> name: ORCL
–> version:
–> compatible:
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
Tablespaces: [make adjustments in the current environment]
–> SYSTEM tablespace is adequate for the upgrade.
WARNING: –> Database contains schemas with objects dependent on network
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

PL/SQL procedure successfully completed.

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select * from v$timezone_file;

———— ———-
timezlrg.dat 2

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
FROM v$timezone_names;


If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.

Check the database version

SQL> select banner from v$version;

Oracle Database 10g Enterprise Edition Release – Prod
PL/SQL Release – Production
CORE Production
TNS for Linux: Version – Production
NLSRTL Version – Production

For check the metalink note ID 413671.1. We have a table which defines the patch to be applied.

Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.

If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.

For example if you run and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for and this will be applicable to as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle@ocvmrh2178 patch]$ unzip p5632264_10202_LINUX.zip
[oracle@ocvmrh2178 5632264]$ ls
etc files README.txt
[oracle@ocvmrh2178 5632264]$ cd files/oracore/zoneinfo
[oracle@ocvmrh2178 zoneinfo]$ ls

readme.txt timezlrg.dat timezone.dat

Backup $ORACLE_HOME/oracore/zoneinfo directory

[oracle@ocvmrh2178 zoneinfo]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup

Copy the .dat files

[oracle@ocvmrh2178 zoneinfo]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

Bounce the database and check the TIMEZONE version again

SQL> select * from v$timezone_file;

———— ———-
timezlrg.dat 4

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
FROM v$timezone_names;


WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS

Gather Dictionary stats:

Connect as sys user and gather statistics


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Step 4) Run Pre-Upgrade Utility again

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

Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.

If every thing looks fine, Shut down the database from 10g Oracle Home

This time make sure you dont have the critical warnings like the one with TIMEZONE version.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle@ocvmrh2178 db_1]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle@ocvmrh2178 db_1]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ocvmrh2178 db_1]$ export ORACLE_SID=orcl
[oracle@ocvmrh2178 db_1]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

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

SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status


Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.


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

SQL> @?/rdbms/admin/utlu111s.sql
Oracle Database 11.1 Post-Upgrade Status Tool 09-07-2007 05:22:40
Component Status Version HH:MM:SS
Oracle Server
. VALID 00:19:02
JServer JAVA Virtual Machine
. VALID 00:02:55
Oracle Workspace Manager
. VALID 00:00:54
OLAP Analytic Workspace
. VALID 00:00:26
OLAP Catalog
. VALID 00:00:58
. VALID 00:00:25
Oracle Enterprise Manager
. VALID 00:11:00
Oracle XDK
. VALID 00:00:53
Oracle Text
. VALID 00:00:50
Oracle XML Database
. VALID 00:03:52
Oracle Database Java Packages
. VALID 00:00:21
Oracle Multimedia
. VALID 00:04:25
. VALID 00:05:18
Oracle Expression Filter
. VALID 00:00:13
Oracle Rules Manager
. VALID 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57

PL/SQL procedure successfully completed.

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

Run utlrp.sql to recompile

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


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

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


This completes the upgrade.


Metalink Note ID: 429825.1

Metalink Note ID: 396671.1

Metalink Note ID: 396387.1

32 thoughts on “Upgrading to Oracle Database 11g

  1. The document is too good. Straight and clearcut.
    We still got stuck at the following.
    “– PublishDataModel
    exec ord_dicom_admin.publishDatabModel”

    If you guys have faced this situation do let us know.
    At the above statement its stuck for more than 1 day.

  2. Excellent document. Just what I was looking for!!! I yet have to try these steps though and I’ll do that soon. Will post further comments if I experience any issues (and their resolution, if I could resolve them).

    Thank you for your effort and sharing this document!

  3. Hello there,
    This is one of the best upgrade doc that i have seen so far. I just followed the steps and did a smooth upgrade to from
    Hats off to the author.
    Also i would like to mention a step or two, before you start the database upgrade process, copy the initdb_name.ora from the 10g ora_home/dbs to the 11g ora_home/dbs location and make the following modifications as shown below.
    Remove the entries for bdump,cdump and udump and add the following to the init.ora file

    Also when you do a startup upgrade,You may find some oracle errors on the alert log pointing to lock issues on certain database files which may hinder the instance from being started up.
    If that’s the case, shutdown the database.
    do a kill -9 -1 or reboot the server and then proceed ahead.
    This should fix the issue.


  4. This article is really good. I could upgrade 10g to 11g but how do I upgrade the EM dbconsole from 10g to 11g?

  5. A lot simplified yet so integrated. Millions thanks, advait. It doesn’t ask me to backup my old database in case of failure though.😛😛

  6. Its Very Coooool and easy to understand a person who is not have the sufficient knowledge. GREATTTTTTTTTTTTTTT🙂

  7. its really wonderful awesome and mind blowing document.. I should have say u BRO U ROCK….keep u good things up..And keep posting all ur knowladge….Cheers….

    1. Hello,
      I got bit confuse..
      In setp 5 ..we are connecting to 11G home. But
      1. we have not created 11g database during installation.
      2. We fired startup upgrade , then catupgrd.sql on 11g Database..? How it will know the path of 10g database..what it is upgrading..?

      Can some one explain what is going on after setp 4

  8. I have not tried the document but there is a answer to my problem i.e. I have 10g( installed in my computer, what I understood with this document that I can upgrade to 11g but according to other documents I can’t.
    Let me try it than I will offer my comments. However, thanks in advance.

  9. Excellent..and pls do provide on windows also. i’ve to upgrade from 10g to 11g on windows. So it would be greatfull if u provide same document as above. Its very urgent for me.

  10. My spouse and I absolutely love your blog and find many of your
    post’s to be exactly what I’m looking for. Do you offer guest writers to write content for yourself?

    I wouldn’t mind producing a post or elaborating on some of the subjects you write concerning here. Again, awesome site!

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