Upgrading to Oracle Database 11g

This article provides a brief overview of the areas involved in upgrading existing databases to Oracle 11g Release 1. The article provides the minumum information needed when preparing for the 11g Database Administation OCP upgrade exam, including:

The whole migration process is beyond the scope of this article so please refer to the document for further information.

Supported Upgrade Paths

Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

The preferred 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.

Alternatively you may which to perform a manual upgrade which involves the following steps:

• Backup the database.
• In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
• Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script, explained below.
• Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
• Recompile invalid objects.
• Restart the database.
• Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
• Troubleshoot any issues or abort the upgrade.

An alternative to these upgrade methods is to use the export and import utilities (exp/imp or expdp/impdp). This enables upgrades from a wider variety of Oracle versions and has the advantage of leaving the original database available as a fallback option. The down sides of this approach are it is much slower, and it requires enough disk space to hold two copies of the database and the export dump file.

Pre-Upgrade Validation Checks

The "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script 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 including:

• Database version.
• Tablespace sizes.
• Updated, renamed and deprecated initialization parameters.
• Database components.
• SYSAUX tablespace present (if missing).
• Miscellaneous information.

The issues indicated by this script should be resolved before a manual upgrade is attempted. The type of output you can expect is displayed below.

SQL> conn / as sysdba
Connected.
SQL> spool /tmp/upgrade.txt
SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool    08-11-2008 11:04:25
.
**********************************************************************
Database:
**********************************************************************
--> name:          DB10G
--> version:       10.2.0.1.0
--> compatible:    10.2.0.1.0
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 720 MB
.... AUTOEXTEND additional space required: 240 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 460 MB
.... AUTOEXTEND additional space required: 435 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 412 MB
.... AUTOEXTEND additional space required: 182 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by  "diagnostic_dest"
--> "user_dump_dest" replaced by  "diagnostic_dest"
--> "core_dump_dest" replaced by  "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using an old timezone file version.
.... Patch the 10.2.0.1.0 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.
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
....   SYSMAN
....   CTXSYS
....   XDB
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... 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.

SQL> spool off

Database Upgrade Assistant (DBUA)

The Database Upgrade Assistant (DBUA) is a GUI tool that guides the user through the whole upgrade process, including all the steps listed in the manual upgrade process. The assistant is started using the dbua command in UNIX and Linux environments or from the Start menu (Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Database Upgrade Assistant) in Windows environments.

Once the assistant has started it leads the user through the several steps including:

• Selecting the instance to upgrade.
• Analyzing the existing database to make sure it is suitable for upgrade.
• Creating the SYSAUX tablespace which is required for 11g.
• Deciding whether to recompile all invalid objects when the upgrade is complete.
• Selecting a backup option for the database.
• Deciding how the database should be managed (OEM Console or Grid Control) and defining the appropriate authentication.
• Defining the flash recovery area.
• Performing any necessary network configuration.
• Performing the upgrade process.
• Checking the upgrade results.
• Listing the changes in default behaviour between the old and new versionsof the database.
• Completing the upgrade procedure.

The DBUA can also be started in silent mode provided all the necessary parameters are provided.

For more information see:

Hope this helps. Regards Tim...