Upgrading to Oracle Database 11g

If there are no issues to resolve prior to the upgrade oracle 11g , Shut down the database and perform an offline backup before continuing with the upgrade process. Once you have a backup you can restore if needed, you are ready to proceed with the upgrade process. The process is detailed and script-based, so you should consult with the Oracle installation and upgrade documentation for your environment and version. You can choose one from following two upgrading methods.

Database Upgrade Assistant (DBUA)

Its recommended upgrade method is to use the Database Upgrade Assistant (DBUA), a GUI tool that performs all necessary prerequisite checks and operations before upgrading the specified instances. The DBUA can be started directly from the Oracle Universal Installer (OUI) or separately after the software installation is complete. In Microsoft Windows environments chose Database Upgrade Assistand from program list of typ "dbua" on comant prompt, installer welcome window will appear. Alternatively you may which to perform a manual upgrade which involves the following steps:

Manual Oracle 11g Database Upgrade

Execute script "$ORACLE_HOME/rdbms/admin/utlu111i.sql, it performs pre-update validation checks on an existing instance. The script checks a number of areas to make sure the instance is suitable for upgrade suchas Database version, initialization parameters, SYSAUX etc.

SQL> connect / as sysdba
SQL> spool /oradisk/11gdatabase_upgrade_checklist.txt

SQL> @utlu111i.sql

SQL> spool off

Resolved all issues indicated bebefore a manual upgrade is attempted. See 11gdatabase_upgrade_checklist.txt

1. Copy configuration files (init.ora, spfile.ora, password file) from their old location to the new Oracle software home directory.

2. Remove obsolete and deprecated initialization parameter from the configuration files identified in the Pre-Upgrade Information Tool. Update any initialization parameters to at least the minimum values specified in the Pre-Upgrade Information Tool report. Use full pathnames in the parameter files.

3. If you are upgrading a cluster database, set the CLUSTER_DATABASE initialization parameter to FALSE. After the upgrade, you must set this initialization parameter back to TRUE.

4. Shut down the instance.

5. If you are using Windows, stop the service associated with the instance and delete the
Oracle service at the command prompt. For Oracle 8.0, use the command

NET STOP OracleServiceName
ORADIM –DELETE –SID instance_name

Next, create the new Oracle Database 11g service using the ORADIM command, as shown here. The variables for this command are shown in the following table.

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

6. If your operating system is Unix or Linux, make sure the environment variables ORACLE_HOME and PATH point to the new release 11.1 directories, ORACLE_SID is set to the existing database’s SID, and the file /etc/oratab points to the new Oracle Database 11g home directory. In addition, any server or client-side scripts that set ORACLE_HOME must be changed to point to the new Oracle software home directory.

7. Log into the system as the owner of the Oracle Database 11g software.

8. Change your directory to the $ORACLE_HOME/rdbms/admin subdirectory under the Oracle software home directory.

9. Connect to SQL*Plus as a user with SYSDBA privileges.
10. Issue the startup upgrade command.
11. Use the spool command to log the results of the following steps.

12. Create a SYSAUX tablespace via the create tablespace command if you are upgrading from a release prior to 10.1. You should allocate SYSAUX between 500MB and 5GB of disk space, depending on the number of user objects. SYSAUX must be created with the following clauses: online, permanent, read write, extent management local, and segment space management auto. All those clauses except segment space management auto are the defaults. See the output from the Pre-Upgrade Information Tool for suggested sizing of the SYSAUX tablespace. Here’s an example:

create tablespace SYSAUX
datafile '/u01/oradata/db1/sysaux01.dbf' size 500m reuse
extent management local
segment space management auto online;

13. Run the script catupgrd.sql in the 11g environment. This script automatically determines which upgrade scripts must be run, runs them, and then shuts down the database.

14. Stop spooling (via spool off) and review the spool file for errors. Resolve any problems identified there. Restart the database at the SQL*Plus prompt using the startup command.

15. Run the utlu111s.sql file to upgrade Oracle components such as Oracle Text, Oracle Ultra Search, Oracle Application Express, and the Oracle Server itself. You run it as follows:
SQL> @utlu101s.sql

16. Oracle will then display the upgrade status of each component. The upgrade elements should all be listed with a status of “VALID.”

17. Run the catuppst.sql script, located in $ORACLE_HOME/rdbms/admin to perform upgrade steps that do not require the database to be in UPGRADE mode:
SQL> @rdbms/admin/catuppst.sql

18. Run the utlrp.sql script to recompile invalid packages:
SQL> @utlrp.sql

19. You can then verify that all packages and classes are valid by using the following SQL:

select count(*) from dba_invalid_objects;
select distinct object_name from dba_invalid_objects;

20. Shut down the database and perform an offline backup of the database; then restart the database. The upgrade is complete.