Upgrading to Oracle Database 11g

 

Upgrade Path

You can directly upgrade to oracle 11g, if your curent database is 9.2.0.4 or newer.

It supports direct upgrades to versions 9.2.0.4, 10.1 and 10.2

7.3.3 -> 7.3.4 -> 9.2.0.8 -> 11.1

8.0.5 -> 8.0.6 -> 9.2.0.8 -> 11.1

8.1.7 -> 8.1.7.4 -> 9.2.0.8 -> 11.1

9.0.1.3-> 9.0.1.4 -> 9.2.0.8 -> 11.1

9.2.0.3 (or lower) -> 9.2.0.8 -> 11.1

Oracle 11g client can access Oracle databases of versions 8i, 9i and 10g.

COMPATIBLE parameter

The default compatibilty value for Oracle 11g is 11.1 , You can , however upgrade to 11g with a minimum value of the COMPATIBLE parameter of 10.0.0 , but only a small number of features will be available if you keep the parameter to 10.0.0

Manual Upgrade Procedure

1.Invoke the Pre-Upgrade Information Tool in the database to upgrade.

This tool is simply the script $ORACLE_HOME/rdbms/admin/utlu111i.sql. So, you have to copy this script altogether with the scripts: utlu111s.sql and utlu111x.sql to a staging directory in the database to upgrade.

As with previous versions, this tool will examine the target database and display the warnings and recommendations that you should consider before you start the upgrade process such as removing obsolete initialization parameters, setting proper values to some parameters and adding space to key tablespaces.

Spool the output of running the script for later review.

SQL>spool upgrade11g.log

SQL>@utlu111i.sql

…

SQL>spool off

2.Backup the database.

3.Set the COMPATIBLE parameter to 11.1.0. You can do so by issuing the following command:

ALTER SYSTEM SET COMPATIBLE=’11.1.0′ SCOPE=SPFILE;

4.Modify the values of the initialization parameters and remove the obsolete parameters as recommended by the Pre-upgrade tool in the current initialization parameter file.

5.Copy the initialization parameter file to the new Oracle 11g home.

6.Shutdown cleanly the database.

7.If the database is installed in a Windows system, perform the following steps:

a)Stop the Oracle database service. Usually its name has the format OracleService.

b)Delete the service. You can use the oradim utility for this purpose.

oradim -delete –SID

c)Use oradim utility in the Oracle 11g home to create a new Oracle Database 11g release instance. Of course, it should use the same SID.

oradim -NEW –SID

8.If the database to upgrade is using a password file, move it to the new Oracle 11g home.

9.Change the environment variables ORACLE_HOME, PATH, and LD_LIBRARY_PATH so that they point to the new Oracle Database 11g directories.

10.In the Oracle 11g home, change to directory $ORACLE_HOME/rdbms/admin and then start the SQL*Plus

11.Start the database in upgrade mode and then run the upgrade script (it takes long time). When working in upgrade mode, Oracle allows only SYSDBA connections and sets some initialization parameters to specific values that are required to run the upgrade script.

SQL>login sys/password as sysdba

SQL>startup upgrade pfile=$ORACLE_HOME/dbs/initorcl.ora

SQL>spool upgrade11g.log

SQL>@ catupgrd.sql

…

SQL>spool off

12.After the upgrade script finishes, make sure no error occurred during the upgrade. Usually errors are raised

because of lack of shared memory or tablespace size. If there is any error, fix its cause and restart the upgrade script.

13.When the upgrade script successfully finishes, restart the database in OPEN mode.

14.Rum utlu111s.sql script (referred to as Post-Upgrade Status tool) to view the results of the upgrade. This tool will view the installed components and their status. If you see a component with invalid status, usually running the script in the next step will set it to valid.

15.Execute the script utlrp.sql to in parallel recompile any invalid PL/SQL program unit and Java code.

SQL>@utlrp.sql

SQL>SELECT COUNT(*) FROM DBA_INVALID_OBJECTS;

16.Because the upgraded database disables all tablespace alerts by setting the threshold to null, set the thresholds for tablespace alert to the values you desire.

If the upgrade process fails, restore the database from the backup.