Upgrade Oracle Database 10205 to 11203 !!!

Home »  10g »  Upgrade Oracle Database 10205 to 11203 !!!

in this article we will see the steps to Upgrade Oracle Database 10205 to 11203 on Red Hat Enterprise Linux.



Follow the below steps to upgrade Oracle database 10205 to 11203

• Download and Install 11gR2 software

We cannot upgrade the existing 10g Oracle Home, since 11gR2 is not a patchset. We have to install 11gR2 binaries in a separate Oracle home.

my existing 10g Oracle Home is : /u01/app/oracle/products/10.2.0/db
my new 11g Oracle Home is : /u01/app/oracle/products/11.2.0/db

Change the environmental variables to reflect the new Oracle Home directory and start the Oracle Software installation of 11gR2. You can refer this link.

We have to install only software and not configure any database as of now. Also Oracle 11gR2 software installation does not requires the 10g services to be stopped, you can install it in parallel while the Oracle 10g database, listener, etc are up and running.



• Get details of current database

– Check database version

SQL> select * from v$version;


– Check version of timezone

SQL> select * from v$timezone_file;

As per Oracle Doc ID – 1358166.1, there’s no pre-patch required if the timezone version is 4 on 10gR2 but it’s advised to upgrade the timezone to 14 after the upgrade. Here, current timezone version is 4, so we will upgrade the DST later.
[Refer Oracle Doc ID: 553812.1 for detailed information]


– Check if no files are in recovery mode

SQL> select * from v$recover_file;


– Check registry components


– Check Invalid objects



• Backup database

Before proceeding with the upgrade, backup 10g Oracle Database. You can take RMAN or COLD backup of database, but cold backup is recommended.
Stop all Oracle components like Listener, DB, EM, etc. before proceeding with cold backup

$ lsnrctl stop LISTENER

$ sqlplus / as sysdba
SQL> shutdown immediate;

Copy all the data files, redo log files, control files to a backup location using OS copy command.



• Run the pre-upgrade utility

Before starting the database upgradation, we will need to run “utlu112i.sql” script which will do checks on database and give us details whether any changes are needed to be done or not for upgradation to be successful.

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

SQL> @/u01/app/oracle/products/11.2.0/db/rdbms/admin/utlu112i.sql

The above script 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.

After executing the recommended steps, run the pre-upgrade utility once again to make sure everything is proper.



• Copy required files from 10g to 11g Oracle Home

– Copy init.ora to 11g Oracle home

Create pfile using below command

SQL> create pfile from spfile;

Copy the init.ora to dbs folder of 11g Oracle Home. Comment/Remove the background_dump_dest and user_dump_dest parameters since both the parameters are deprecated in 11g and replaced by the parameter diagnostic_dest.

Add the new parameter in init.ora as shown below,

Note: Once you start the database, it will automatically create the directory structure like ‘diag/rdbms/…’ under the given path ‘/u01/app/oracle’.


– Copy the password file to 11g Oracle home


– Copy the listener.ora and tnsnames.ora to 11g Oracle home


– Shutdown the database

Shutdown the 10g database,

$ sqlplus / as sysdba
SQL> shutdown immediate;



• Upgrade the database

Now change the environmental variables to 11g Oracle Home and start the upgrade process.

$ export ORACLE_HOME=/u01/app/oracle/products/11.2.0/db
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=orcldb
$ export TNS_ADMIN=$ORACLE_HOME/network/admin

Start the database in Upgrade mode and run the catupgrd.sql script.

When the upgrade script finishes, it will shut down the database automatically.
Login again as sysdba and do a normal startup.



• Post Upgrade checks

– Run catuppst.sql script

Run this script 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 the utlrp.sql script to recompile any invalid objects

SQL> spool recompile.log
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off;


– Verify the registry components

Verify if all the components are upgraded to by running the below script. Run utlu112s.sql to display the results of the upgrade,

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

OR you can also query the dba_registry table for the components and its status


– Create spfile

Create spfile for the upgraded database and restart the database using spfile

SQL> create spfile from pfile;
SQL> shutdown immediate;

SQL> startup
SQL> show parameter spfile;


– Change the “compatible” parameter

The value of compatible parameter would be “”, we need to change it to “

SQL> alter system set compatible=’′ scope=spfile;

Restart the database to reflect the new value.


– Check files for any recovery

SQL> select * from v$recover_file;

no rows selected


– Make changes in /etc/oratab file

Change the path of Oracle Home from 10g Oracle Home to 11g Oracle Home and save the changes.


– Configure listener and start the listener


– Upgrade timezone to DSTv14

The timezone version will still be 4, you can refer the Oracle Doc ID – 1358166.1 to upgrade the timezone version in 11gR2 database.
Below is the upgraded timezone version.

SQL> select * from v$timezone_file;

So this completes the final step of database upgradation from 10205 to 11203.


Tags : ,, ,

Leave a Reply

Your email address will not be published. Required fields are marked *