Upgrading Oracle 10g to Oracle 11g

Step by Step Upgrading Oracle 10g to Oracle 11g

Pre-Requisite:

You should have the Oracle database 10g, which you want to migerate.
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.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

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]$ cd $ORACLE_HOME
[oracle]$ cd rdbms/admin/
[oracle]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle]$ cp utlu111i.sql /tmp


Step 3) Executing the recommended steps


SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 2

SQL> SELECT CASE COUNT(DISTINCT(tzname))

VERSION
———-
2

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;

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

Backup $ORACLE_HOME/oracore/zoneinfo directory

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

Copy the .dat files

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

Bounce the database and check the TIMEZONE version again

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4

SQL> SELECT CASE COUNT(DISTINCT(tzname))

Gather Dictionary stats:

Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

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]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ 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

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.

SQL> STARTUP

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

Run utlrp.sql to recompile

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

COUNT(*)
———-
1576

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

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

COUNT(*)
———-
0

This completes the upgrade.