Skip Headers
Oracle® Database Upgrade Guide
11g Release 1 (11.1)

B28300-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Upgrading to the New Release

This chapter guides you through the process of upgrading a database to Oracle Database 11g Release 1 (11.1). This chapter covers the following topics:

WARNING:

If you retain the old Oracle software, then never start the upgraded database with the old Oracle software. Only start the database with the executables in the new Oracle Database installation.

Also, before you remove the old Oracle environment, make sure you relocate any data files in that environment to the new Oracle Database environment. If you upgrade with Database Upgrade Assistant (DBUA), then you can do this automatically by selecting its Move Database Files option during the upgrade. See "Upgrade a Database Using Database Upgrade Assistant" for more information. It you perform a manual upgrade, then see Oracle Database Administrator's Guide for information about relocating data files.

See Also:

Some aspects of upgrading are operating system-specific. See your operating system-specific Oracle documentation for additional instructions about upgrading on your operating system.

System Considerations and Requirements

The following sections discuss system considerations and requirements:

Upgrading Oracle Clusterware and Automatic Storage Management (ASM) Databases

Prior to Oracle Database 11g Release 1 (11.1), either all Oracle software installations were owned by the Oracle user, typically oracle, or Oracle Database software was owned by oracle, and Oracle Clusterware software was owned by a separate user, typically crsuser. Starting with Oracle Database 11g Release 1 (11.1), the same user that owned the Oracle Clusterware 10g software must perform the Oracle Clusterware 11g release 1 (11.1) upgrade. Also, the Oracle Clusterware 11g release 1 (11.1) software must be upgraded in the same directory where the Oracle Clusterware 10g software was installed. Oracle Clusterware was installed in a separate home directory, but Oracle Database installations and ASM installations could be installed in the same Oracle home.

With Oracle Database 11g Release 1 (11.1), to improve data and software security, Oracle Database, ASM, and Oracle Clusterware may each be installed in separate home directories, and access to each of these directories may be controlled by creating separate operating system (OS) users.

If you are upgrading your database from any previous version of the database to Oracle Database 11g and the configuration includes ASM, then you must upgrade the cluster synchronization services daemon (CSS) to Oracle Database 11g. Otherwise, the database upgrade procedure will fail with the following error:

ERROR at line 1:
ORA-03113: end-of-file on communication channel
ERROR:

If your configuration does not include ASM, then you should shut down the CSS daemon and delete the CSS service from the system by running the localconfig batch file with the delete option. For example:

SYSTEM_DRIVE :\oracle\product\11.1.0\db_1\bin\localconfig delete

If you do not know whether your configuration includes ASM or not, then run the following command:

select count(*) from v$asm_client where status = 'CONNECTED';

If this returns one or more rows, then the database is actively using an ASM disk group.

Upgrading an Oracle Real Application Clusters (Oracle RAC) Database

If you are upgrading an Oracle RAC database with DBUA, then all necessary configuration is done for you automatically. If you are manually upgrading an Oracle RAC database, then most of the actions described in this chapter should be performed on only one node of the system. Actions that must be performed on more than one node are indicated in that particular step.

Note:

A new prerequisite check has been added to ensure that Oracle Clusterware release 10.2.0.x is at release 10.2.0.3 (or higher), before you attempt to upgrade it to Oracle Clusterware 11g release 1 (11.1). If this check fails, then you are instructed to apply Oracle Clusterware patch set release 10.2.0.3.0 or later to your existing release 10.2.0.1 or 10.2.0.2 before it can be upgraded. All other upgrade paths and fresh install cycles are unaffected by this prerequisite check.

Upgrading Automatic Storage Management (ASM) Instances

Starting with Oracle Database 11g Release 1 (11.1), you should use the SYSASM privilege to separate database management and storage management responsibilities.

Also starting with Oracle Database 11g Release 1 (11.1), you have the option to create separate operating system credentials for ASM and each database. This separation allows for an even greater division of database management and storage management responsibilities. For instance, if there are n databases using ASM on a given node, then you can configure n + 1 sets of operating system credentials groups whose members have SYS privileges: one OSDBA group for each database with SYSDBA privileges, and one OSASM group for the ASM instance with SYSASM privileges.

Before upgrading an ASM instance to Oracle Database 11g Release 1 (11.1), you must add a user and password combination to the password file that is local to a node's ASM instance using the SQL*Plus CREATE USER statement:

CREATE USER user_name IDENTIFIED BY password

Note:

This step is necessary only when upgrading the ASM instance. It is not necessary when upgrading a database to Oracle Database 11g Release 1 (11.1) without upgrading ASM.

If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

Each node's ASM instance uses this SQL*Plus statement to update its own password file. The password file itself has to be created by the ORAPWD utility. Note that new users have no privileges until they are granted access using the GRANT system_privilege TO user statement. This enables operating system authentication control to allow any user authenticated with SYSDBA privileges on the database instance to have limited access to certain ASM operations, without automatically granting that user full SYSDBA privileges on the ASM instance. Then, after the upgrade, users are able to connect to the ASM instance using either of the following connection types. In all of the following examples, replace password with a password that is secure:

  • Connect as SYSASM using operating system authentication to have full access to ASM. For example:

    • Connect to SQL*Plus as user SYS (when you create the ASM instance with DBCA, DBCA grants SYSASM to user SYS):

      sqlplus sys as sysasm
      Enter password: password
      Connected.
      
    • Connect as SYSASM:

      SQL> connect sys as sysasm;
      Enter password: password
      Connected.
      
  • Connect as SYSDBA for limited privileges. Every time a user who is authenticated as SYSDBA executes a command that only a user authenticated as SYSASM is able to do, a warning is written to the alert log files to warn the user to connect as SYSASM. For example:

    • Connect to SQL*Plus using the AS SYSDBA privilege:

      sqlplus sys as sysdba
      Enter password: password
      Connected.
      
    • Connect as SYSDBA:

      SQL> connect sys as sysdba;
      Enter password: password
      Connected.
      

See Also:

Upgrading with Read-Only and Offline Tablespaces

Oracle Database 11g Release 1 (11.1) can read file headers created in previous releases, so you are not required to do anything to them during the upgrade. The only exception to this is if you want to transport tablespaces created prior to Oracle Database 11g Release 1 (11.1) to another platform. In this case, the file headers must be made read/write at some point before the transport. However, there are no special actions required on them during the upgrade.

The file headers of offline data files are updated later when they are brought online, and the file headers of read-only tablespaces are updated if and when they are made read/write sometime after the upgrade. You are never required to make read-only tablespaces read/write in any other circumstance.

See Also:

Oracle Database Administrator's Guide for more information about read-only tablespaces and transporting tablespaces between databases

Upgrading Standby Databases

To upgrade the Oracle Database software when physical or logical standby databases are present in an Oracle Data Guard configuration, see the Oracle Data Guard Concepts and Administration.

To upgrade or downgrade Oracle Database and Oracle Enterprise Manager software in an Oracle Data Guard broker configuration, see Oracle Data Guard Broker.

Starting with Oracle Database 10g Release 1 (10.1.0.3), you can use SQL Apply on a logical standby database to perform a rolling upgrade to Oracle Database 11g Release 1 (11.1). During a rolling upgrade, you can run different releases of Oracle Database software on the primary and logical standby databases while you upgrade them, one at a time, incurring minimal downtime on the primary database. See the Oracle Data Guard Concepts and Administration documentation for complete information.

Upgrading Your Operating System

If required, upgrade the operating system before upgrading Oracle Database.

See Also:

  • The Oracle Database Installation Guide for your platform to obtain a list of supported operating systems

  • Your operating system-specific documentation for information on how to perform an operating system upgrade

Migrating Data to a Different Operating System

When using DBUA or when performing a manual upgrade, you cannot migrate data in a database on one operating system to a database on another operating system. For example, you cannot migrate data in an Oracle9i database on Solaris to an Oracle Database 11g Release 1 (11.1) database on Windows 2000 using DBUA.

If you must migrate Oracle Database software to a different operating system, then the best practice is to follow these steps:

  1. Upgrade to Oracle Database 11g Release 1 (11.1) on your current operating system platform following the instructions in this guide.

  2. Test the upgraded database on your current operating system platform.

  3. Use Oracle Data Guard and physical standby databases to migrate between operating systems.

    See Also:

    Note 413484.1 on OracleMetalink (https://metalink.oracle.com/) for more information on Data Guard support for heterogeneous primary and standby systems in the same Data Guard configuration
  4. If cross-platform physical standby database is not available for the platform combination to be migrated, then you can use the Oracle Database 11g Release 1 (11.1) cross-platform transportable tablespace feature or the Oracle Data Pump Export and Import utilities to migrate the upgraded database to the different operating system.

    Note:

    Transportable tablespaces do not support migrating SYSTEM or YSAUX tablespaces. All non-segment user data, such as roles, triggers, views, and procedures, must be moved to the new operating system with scripts or export/import.
  5. If the two operating systems are in the same endianess group, then you can use the Oracle Database 11g Release 1 (11.1) cross-platform transportable database feature to migrate the entire database.

  6. You can also use Oracle Streams to migrate data between operating systems. Oracle Streams has data type limitations and restrictions, such as for advanced queues and object types, and it requires additional administrative overhead.

See Also:

Upgrading Databases That Use Oracle Streams Downstream Capture

In an Oracle Streams replication environment, downstream captures means that a capture process runs on a database other than the source database. When you upgrade the databases in such an environment, upgrade the database with the downstream capture process before you upgrade the source database. Upgrading the databases in this order ensures that the downstream capture database can continue to function after the source database is upgraded.

See Also:

Oracle Streams Concepts and Administration for more information about downstream capture.

Install the New Oracle Database Software

Installation of the release 11.1 Oracle software involves upgrading Clusterware if necessary, and installing Oracle Database 11g Release 1 (11.1).

Note:

Oracle Clusterware was called Cluster Ready Services in earlier releases.
  1. If you are upgrading an Oracle Real Application Clusters (Oracle RAC) database, then you must perform the following steps in the order shown:

    1. Mount the Oracle Clusterware 11g release 1 (11.1) installation media.

    2. Complete operating system prerequisite checks on each of the nodes that you intend to upgrade, to ensure that they meet the system prerequisites for Oracle Clusterware 11g release 1 (11.1).

    3. If necessary, perform patch upgrades of the previous release Oracle Clusterware or Oracle Cluster Ready Services software to the most recent patch version.

      See Also:

      Your platform-specific Oracle Clusterware Installation Guide for patch upgrade instructions
    4. Run the preupdate.sh script, located in the clusterware/upgrade directory on the Oracle Clusterware 11g release 1 (11.1) installation media.

      This script shuts down the Oracle Clusterware software stack and unlocks the Oracle Clusterware home (CRS home) for upgrading.

      If you do not have a shared CRS home, then you must run this script on each node in the cluster that you want to upgrade.

      Note:

      You only need to run this script if upgrading from Oracle Database 10g. You do not need to run this script if upgrading from Oracle9i.
    5. Ensure that you are logged in as the user that you want to own the Oracle Clusterware installation, and run the Oracle Clusterware installation. For example, if the owner you want to use for Oracle Clusterware is crs, and the mountpoint for Oracle Clusterware 11g release 1 (11.1) is /shiphome/clusterware, then you could use the following commands:

      > whoami
        crs
      > /shiphome/clusterware/runInstaller
      

      Provide information as prompted by the installer.

    6. When prompted, open a separate terminal session, log in as root, and run the rootupgrade script.

      Note:

      If you are upgrading from Oracle Database 10g to Oracle Database 11g, you must install Oracle Clusterware into the existing CRS home on the local and remote node subset. When you run the root script, it starts the Oracle Clusterware 11g release 1 (11.1) stack on the subset cluster nodes. For every node except the last, the version change does not take effect. The version change only takes effect on the last node.

      For information on how to perform rolling upgrades, see the Oracle Clusterware Installation Guide for your operating system.

  2. After upgrading Oracle Clusterware (assuming it was necessary), follow the instructions in your Oracle operating system-specific documentation to prepare for installation of Oracle Database software and start the Oracle Universal Installer.

    When installation is complete, one or more assistants might be started. If you chose to run DBUA during installation, then you are ready to proceed with the upgrade when DBUA is started. However, Oracle recommends that you run the Pre-Upgrade Information Tool before you upgrade using DBUA, so that you can preview the types of items DBUA checks. (See "Run the Pre-Upgrade Information Tool".) You can then run DBUA independently after the installation is complete.

    Note also that you must run the Oracle Net Configuration Assistant before running DBUA.

    When installation of Oracle Database software has completed successfully, click Exit to close the Oracle Universal Installer.

    Note:

    If you use Oracle Label Security, Database Vault, or both, then you must perform a custom Oracle Database 11g Release 1 (11.1) installation, because these features are not included in the standard Oracle Database installation. See Oracle Label Security Administrator's Guide or your platform-specific Oracle Database Vault Installation Guide for more information.

Install the Latest Available Patchset Release and Any Required Patches

After you have installed Oracle Database 11g Release 1 (11.1), check to see if there is a patchset release or critical patch update to be installed:

The latest patchset release and critical patch update for Oracle Database 11g Release 1 (11.1) should be installed prior to upgrading your databases. Refer to the specific patch release and critical patch update documentation for installation information.

Run the Pre-Upgrade Information Tool

After you have installed Oracle Database 11g Release 1 (11.1) and any required patches, you should analyze your database before upgrading it to the new release. This is done by running the Pre-Upgrade Information Tool. This is a necessary step if you are upgrading manually. It is also recommended if you are upgrading with DBUA, so that you can preview the items that DBUA checks.

The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 11g Release 1 (11.1), and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool:

  1. Log in to the system as the owner of the Oracle Database 11g Release 1 (11.1) Oracle home directory.

  2. Copy the Pre-Upgrade Information Tool (utlu111i.sql) from the Oracle Database 11g Release 1 (11.1) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

    Make a note of the new location of this file.

  3. Log in to the system as the owner of the Oracle home directory of the database to be upgraded.

  4. Change to the directory that you copied utlu111i.sql to in Step 2.

  5. Start SQL*Plus.

  6. Connect to the database instance as a user with SYSDBA privileges.

  7. Set the system to spool results to a log file for later analysis:

    SQL> SPOOL upgrade_info.log
    
  8. Run the Pre-Upgrade Information Tool:

    SQL> @utlu111i.sql
    
  9. Turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

    Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 11.1 Pre-Upgrade Information Tool    06-15-2007 09:14:02
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       10.2.0.1.0
--> compatible:    10.2.0.1
--> blocksize:     8192
--> platform:      Linux IA (32-bit)
--> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
.... currently allocated size: 560 MB
.... minimum required size: 910 MB
.... increase current size by: 350 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 457 MB
.... AUTOEXTEND additional space required: 352 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 617 MB
.... AUTOEXTEND additional space required: 287 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 388 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "plsql_compiler_flags" old value was "INTERPRETED";
new name is "plsql_code_type" new value is "INTERPRETED"
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "max_enabled_roles"
--> "remote_os_authent"
--> "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
--> Messaging Gateway            [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle Label Security        [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
--> Oracle Ultra Search          [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]
--> 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
....   WMSYS
....   CTXSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 7 INVALID objects.
.... USER FLOWS_010600 has 1 INVALID objects.
.... USER SYS has 1 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 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.
.
WARNING: --> There are materialized view refreshes in progress.
.... Ensure all materialized view refreshes are complete prior to upgrade.
WARNING: --> There are files which need media recovery.
.... Ensure no files need media recovery prior to upgrade.
WARNING: --> There are files in backup mode.
.... Ensure no files are in backup mode prior to upgrade.
WARNING:--> There are outstanding unresolved distributed transactions.
.... Resolve outstanding distributed transactions prior to upgrade.
WARNING:--> A standby database exists.
.... Sync standby database prior to upgrade.

Note:

Oracle interMedia became Oracle Multimedia in Oracle Database 11g Release 1 (11.1).

The following sections describe the output of the Pre-Upgrade Information Tool.

Database

This section displays global database information about the current database, such as the database name, release number, and compatibility level. A warning is displayed if you must adjust the COMPATIBLE initialization parameter before the database is upgraded.

Logfiles

This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number, and recommended size is displayed.

In a manual upgrade using SQL scripts and utilities, new files of at least 4 MB (preferably 10 MB) must be created in the current database, and any redo log files less than 4 MB must be dropped before the database is upgraded. These tasks are performed automatically by the Database Upgrade Assistant

Tablespaces

This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade.

In a manual upgrade using SQL scripts and utilities, space must be added to tablespaces that do not have enough free space in the current database. These tablespace adjustments must be made before the database is upgraded. This task is performed automatically by the Database Upgrade Assistant.

Update Parameters

This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments must be made to the parameter file after it is copied to Oracle Database 11g Release 1 (11.1).

See Also:

Appendix A, "Behavior Changes" for more information about changes to initialization parameters in Oracle Database 11g Release 1 (11.1)

Deprecated Parameters

This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in Oracle Database 11g Release 1 (11.1).

See Also:

Appendix A, "Behavior Changes" for a list of initialization parameters that are deprecated in Oracle Database 11g Release 1 (11.1)

Obsolete Parameters

This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in Oracle Database 11g Release 1 (11.1). Obsolete initialization parameters must be removed from the parameter file before the database is upgraded.

See Also:

Appendix A, "Behavior Changes" for a list of initialization parameters that are obsolete in Oracle Database 11g Release 1 (11.1)

Components

This section displays a list of database components in Oracle Database 11g Release 1 (11.1) that are upgraded or installed when the current database is upgraded.

Miscellaneous Warnings

This section provides warnings about specific situations that might require attention before or after the upgrade.

SYSAUX Tablespace

This section displays the minimum required size for the SYSAUX tablespace, which is required in Oracle Database 11g Release 1 (11.1). The SYSAUX tablespace must be created after the new release is started and before the upgrade scripts are invoked.

Prepare the Database to Be Upgraded

If the Pre-Upgrade Information Tool displays a warning about any of the following issues, then further analysis of the database is recommended prior to upgrading it to Oracle Database 11g Release 1 (11.1):

Deprecated CONNECT Role

After upgrading to Oracle Database 11g Release 1 (11.1) from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs
     WHERE granted_role = 'CONNECT' and
                     grantee NOT IN (
                                'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 
                                'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
                                'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY', 
                                'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                                'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                                 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrading. The upgrade scripts adjust the privileges for the Oracle-supplied users.

Access Control to Network Utility Packages

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, then you must install Oracle XML DB if it is not already installed.

Use the following instructions to assess the dependencies and provide access by adding the appropriate access control lists (ACLs):

  1. Run the pre-upgrade information tool as described in "Run the Pre-Upgrade Information Tool".

  2. Check the output from the pre-upgrade information tool (upgrade_info.log) for messages such as the following:

    WARNING: --> Database contains schemas with objects dependent on network packages.
    .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
    .... USER WKSYS has dependent objects.
    .... USER SYSMAN has dependent objects.
    .... USER FLOWS_010600 has dependent objects.
    .
    
  3. Query the DBA_DEPENDENCIES view to obtain more information about the dependencies. For example:

    SELECT * FROM DBA_DEPENDENCIES
    WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
      AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
    
  4. Prepare post-upgrade scripts now so the scripts will be available for use in the test environment. This ensures the new access controls are part of your upgrade testing.

    To configure network access control lists (ACLs) in the database so that these packages can work as they did in prior releases, see the example script provided in "Configure Fine-Grained Access to External Network Services". This script shows how to use the DBMS_NETWORK_ACL_ADMIN package to create, assign, and add privileges to the access control list.

  5. After the upgrade, you will need to grant the specific required privileges. Access will be based on the usage in the original database.

Database Links with Passwords

During the upgrade to Oracle Database 11g Release 1 (11.1) from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.

TIMESTAMP WITH TIME ZONE Data Type

The time zone files that are supplied with Oracle Database 11g Release 1 (11.1) have been updated from version 2 to version 4 to reflect changes in transition rules for some time zone regions. The changes might affect existing data of TIMESTAMP WITH TIME ZONE data type. If you have not already updated your existing databases to time zone file version 4 (patch 5632264 - TZ V4 file), you must do so prior to upgrading to Oracle Database 11g Release 1 (11.1).

If your existing databases are already using a time zone file version greater than 4, then after installing Oracle Database 11g Release 1 (11.1), you must patch the ORACLE_HOME with the appropriate patch for the time zone file version in use with the databases you will be upgrading.

If the time zone file version used by the Oracle Database 11g Release 1 (11.1) server does not match the time zone file version that was used with the existing database being upgraded, then the upgrade script will terminate without upgrading the database. The TIMESTAMP WITH TIME ZONE data stored in the database can be corrupted during the upgrade if there is a time zone file version mismatch.

See Also:

Oracle Database Globalization Support Guide for a detailed description of time zone upgrade, and the following Metalink notes:
  • Note 359145.1 "Impact of 2007 USA daylight saving changes on the Oracle database"

  • Note 414590.1 "Time Zone IDs for 7 Time Zones Changed in Time Zone Files >=V3"

Optimizer Statistics

When upgrading to Oracle Database 11g Release 1 (11.1), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

If you are using Oracle9i Release 2 (9.2), then you should use the DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics. To do this, you can run the scripts provided in Appendix B.

Table 3-1 lists the system components and schemas that are checked for statistics collection during the upgrade.

Table 3-1 Statistics Collection for System Components and Schemas

Component Name Schema

JServer JAVA Virtual Machine

SYS

OLAP Analytic Workspace

SYS

Oracle Database Catalog Views

SYS

Oracle Database JAVA Packages

SYS

Oracle Database Packages and Types

SYS, DBSNMP, OUTLN, SYSTEM, DIP

Oracle Database Vault

DVSYS

Oracle Enterprise Manager

SYSMAN

Oracle Expression Filter

EXFSYS

Oracle Multimedia

ORDSYS, ORDPLUGINS, SI_INFORMTN_SCHEMA

Oracle Label Security

LBACSYS

Oracle OLAP API

SYS

Oracle Spatial

MDSYS, MDDATA

Oracle Text

CTXSYS

Oracle Ultra Search

WKSYS, WKPROXY,WK_TEST

Oracle Workspace Manager

WMSYS

Oracle XDK

SYS

Oracle XML Database

XDB


Disable Oracle Database Vault

If you have enabled Oracle Database Vault, then you must disable it before upgrading the database, and enable it again when the upgrade is finished.

See Also:

Oracle Database Vault Administrator's Guide for instructions on disabling and enabling Oracle Database Vault

Save Oracle Enterprise Manager Database Control Data

In order to downgrade Oracle Enterprise Manager Database Control after upgrading to Oracle Database 11g Release 1 (11.1), you must save your Database Control files and data before upgrading your database. This section explains how to use the emdwgrd utility before upgrading your database to keep a copy of your Database Control files and data.

The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 11g Release 1 (11.1) home. The emdwgrd utility consists of emdwgrd and emdwgrd.pl for Linux and UNIX, and emdwgrd.bat and emdwgrd.pl for Windows. Before running the utility you must install Oracle Database 11g Release 1 (11.1) and invoke the script from the Oracle Database 11g Release 1 (11.1) home. The emdwgrd utility, however, requires that you set ORACLE_HOME to the old Oracle home.

If you are performing an inplace patchset upgrade from release 11.1.0.6 and want to save the Oracle Enterprise Manager Database Control files and data before applying the patchset to the release 11.1.0.6 home, then you must patch the emdwgrd utility with a one-off patch of Bug 7198496. For this inplace patchset upgrade, the emdwgrd utility requires that you set ORACLE_HOME to the current Oracle home.

The following procedure is for Linux and UNIX. To run it on Windows, simply substitute emdwgrd.bat for emdwgrd.

Follow these steps to save your Database Control files and data:

  1. Install Oracle Database 11g Release 1 (11.1).

    This step is not required for an inplace patchset upgrade.

  2. Set ORACLE_HOME to your old Oracle home.

    This step is not required for an inplace patchset upgrade.

  3. Set ORACLE_SID to the SID of the database being upgraded.

  4. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.

  5. Go to the Oracle Database 11g Release 1 (11.1) home.

  6. Execute one of the following:

    • For a single-instance database, run the following command, where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data:

      emdwgrd -save -sid old_SID -path save_directory
      
    • If the database is an Oracle RAC database, remote copy is required across the cluster nodes. Define an environment variable to indicate which remote copy is configured. For example:

      setenv EM_REMCP /usr/bin/scp
      

      Then, execute the following save command:

      emdwgrd -save -cluster -sid old_SID -path save_directory
      

      If the release 10g Oracle home is on a shared device, add -shared to the previous command line.

  7. Enter the SYS password for the database to be upgraded.

    On a single-instance database, the emdwgrd utility produces output similar to the following:

    Sat Apr 28 08:49:45 2007 - Verify EM DB Control files ... pass
    Sat Apr 28 08:49:45 2007 - Validating DB Connection to DB102 ... pass
    Sat Apr 28 08:49:51 2007 - creating directory ... created
    Sat Apr 28 08:49:51 2007 - Stopping DB Control ... stopped
    Sat Apr 28 08:50:01 2007 - Saving DB Control files ... saved
    Sat Apr 28 08:50:14 2007 - recompiling invalid objects ... recompiled
    Sat Apr 28 08:50:18 2007 - Exporting sysman schema for DB102 ... exported
    Sat Apr 28 08:51:36 2007 - Starting DB Control ... started
    Sat Apr 28 08:53:21 2007 - DB Control was saved successfully.
    

    On an Oracle RAC database, the emdwgrd utility produces output similar to the following:

    $ /scratch/oracle/product/11.1.0/db_1/bin/emdwgrd -srcOracleHome $ORACLE_HOME -sid DB102 -path /scratch/rpattabh/ravi/tmp/dbcdir5 –save  -cluster
     
    Enter sys password for database DB102?
    *****
     
    Database Unique Name : DB102
    Sat Apr 28 08:49:45 2007 - Verify EM DB Control files ... pass
    Sat Apr 28 08:49:45 2007 - Validating DB Connection to DB102 ... pass
    Sat Apr 28 08:49:51 2007 - creating directory ... created
    Sat Apr 28 08:49:51 2007 - Stopping DB Control on all Nodes
     
    Please Execute '/tmp/racdwgrd_dbctl.sh' on Node1, Node2. 
     
    Press yes to continue when the operations are successful. 
    Continue (yes/no) ?
    y 
     
    ... stopped
    Sat Apr 28 08:50:01 2007 - Saving DB Control files 
    Executing save directories from node Node1
    Executing save directories from node Node2
     
    ... saved
    Sat Apr 28 08:50:14 2007 - Recompiling invalid objects ... recompiled
    Sat Apr 28 08:50:18 2007 - Exporting sysman schema for DB102 ... exported
    Sat Apr 28 08:53:21 2007 - DB Control was saved successfully.
    Sat Apr 28 08:51:36 2007 - Starting DB Control on all nodes
     
    Please Execute '/tmp/racdwgrd_dbctl.sh' on Node1, Node2. 
     
    Press yes to continue when the operations are successful. 
    Continue (yes/no) ?
    y
     
    ... started
    Sat Apr 28 08:57:26 2007 - Dump directory was dropped successfully.
    

Note:

The DBUA backup and restore process also allows you to revert to your previous Oracle Enterprise Manager Database Control environment after upgrading your database. But you will lose all user data accumulated between the upgrade and restore operations. Saving your Database Control files and data enables you to downgrade both your database and Database Control. You will lose all Database Control data accumulated between the upgrade and downgrade, but you will retain all user data.

Complete Materialized View Refreshes

For a list of materialized view refreshes that need to be completed, issue the following statement:

SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;

Ensure No Files Need Media Recovery

For a list of files that require media recovery, issue the following statement:

SELECT * FROM v$recover_file;

Ensure No Files Are in Backup Mode

For a list of files in backup mode, issue the following statement:

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Resolve Outstanding Distributed Transactions

Resolve outstanding distributed transactions prior to the upgrade.

SELECT * FROM dba_2pc_pending;

If this query returns rows, then issue the following statements:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Sync Standby Database with the Primary Database

To check if a standby database exists, issue the following query:

SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

If this query returns a row, then sync the standby database with the Primary database.

  1. Make sure all the logs are transported to the standby server after a final log switch in the primary.

  2. Start the recovery of the standby database with the NODELAY option.

Run the Oracle Net Configuration Assistant

If you are upgrading from Oracle9i and a listener was not configured in the Oracle9i repository, then you must run Oracle Net Configuration Assistant to configure the listening protocol address and service information for the Oracle Database 11g Release 1 (11.1) database, including a listener.ora file, before running DBUA. A new version of the listener is required for an Oracle Database 11g Release 1 (11.1) database. Previous versions of the listener are not supported for use with an Oracle Database 11g Release 1 (11.1) database. However, it is possible to use the new version of the listener with previous versions of Oracle Database.

If you are upgrading an Oracle Real Applications Cluster (Oracle RAC) database, then you have two options:

Upgrade a Database Using Database Upgrade Assistant

The following sections guide you through the process of upgrading a database using Database Upgrade Assistant (DBUA). Please note the following:

DBUA provides a graphical user interface (GUI) to guide you through the upgrade of a database, or you can invoke it in silent mode, which does not present a user interface:

Using the DBUA Graphical User Interface

If you installed Oracle Database 11g Release 1 (11.1) and specified that you are upgrading an existing database, then DBUA is started automatically. However, if you did not specify that you are upgrading an existing database, then you can start DBUA independently after installation is complete.

DBUA performs the following checks before the upgrade:

  • Invalid user accounts or roles

  • Invalid data types or invalid objects

  • Desupported character sets

  • Adequate resources, including rollback segments, tablespaces, and free disk space

  • Missing SQL scripts needed for the upgrade

  • Listener running (if Oracle Enterprise Manager Database Control upgrade or configuration is requested)

  • Oracle Database software linked with Database Vault option. If Database Vault is enabled, then DBUA will return an error asking you to disable Database Vault prior to upgrade. See "Disable Oracle Database Vault"

DBUA does not begin the upgrade until all of these pre-upgrade steps are completed.

During the upgrade, DBUA automatically modifies or creates new required tablespaces and invokes the appropriate upgrade scripts. Optionally, DBUA backs up all necessary files.

While the upgrade is running, DBUA shows the upgrade progress for each component. DBUA writes detailed trace and log files and produces a complete HTML report for later reference. To enhance security, DBUA automatically locks new user accounts in the upgraded database. DBUA then proceeds to create new configuration files (parameter and listener files) in the new Oracle home.

Complete the following steps to upgrade a database using the DBUA graphical user interface:

  1. Do one of the following to start DBUA:

    • On Linux or UNIX platforms, enter the following command at a system prompt in the Oracle Database 11g Release 1 (11.1) environment:

      dbua
      

      Note:

      The dbua executable is usually located in the ORACLE_HOME/bin directory.
    • On Windows operating systems, select Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Database Upgrade Assistant.

    The DBUA Welcome screen appears.

    Description of dbua01.jpg follows
    Description of the illustration dbua01.jpg

  2. If you need help at any screen or want to consult more documentation about DBUA, then click Help to open the online help.

    Click Next.

    The Upgrade Operations page appears with options to upgrade a database or an ASM instance.

    Description of dbua02.jpg follows
    Description of the illustration dbua02.jpg

  3. At the Upgrade Operations page, select Upgrade a Database. This operation upgrades a database to Oracle Database 11g Release 1 (11.1).

    The other option, Upgrade Automatic Storage Management Instance, is discussed in "Upgrade an ASM Instance with DBUA".

    Oracle recommends that you upgrade the database and ASM in separate DBUA sessions, in separate Oracle homes.

    Click Next.

    The Databases screen appears.

    Description of dbua03.jpg follows
    Description of the illustration dbua03.jpg

  4. Select the database you want to upgrade from the Available Databases table.

    You can select only one database at a time. If you do not see the database that you want, then make sure an entry with the database name exists in the oratab file in the etc directory.

    If you are running DBUA from a user account that does not have SYSDBA privileges, then you must enter the user name and password credentials to enable SYSDBA privileges for the selected database.

    Click Next.

    DBUA analyzes the database, performing pre-upgrade checks and displaying warnings as necessary. Examples of DBUA database checks include:

    • Redo log files whose size is less than 4 MB. If such files are found, then DBUA gives the option to drop/create new redo log files.

    • Obsolete or deprecated initialization parameters.

    When DBUA finishes its checks, the Diagnostic Destination screen appears.

    Description of dbua04.jpg follows
    Description of the illustration dbua04.jpg

  5. Do one of the following:

    • Accept the default location for your diagnostic destination

    • Enter the full path to a different diagnostic destination in the Diagnostic Destination field

    • Click Browse to select a diagnostic destination

    Diagnostic destination is the default location to store Oracle trace and diagnostic files. It replaces the initialization parameter settings for background dump destination, user dump destination, and core dump destination from earlier releases.

    Click Next.

    If you are upgrading a single-instance database or Oracle Express Edition (XE), then the Move Database Files screen appears. If you are upgrading an Oracle Real Application Clusters database, then the Move Database Files screen does not appear.

    Description of dbua05.jpg follows
    Description of the illustration dbua05.jpg

  6. Select one of the following options:

    • Do Not Move Database Files as Part of Upgrade

    • Move Database Files during Upgrade

    If you choose to move database files, then you must also select one of the following:

    • File System

      Your database files are moved to the host file system.

    • Automatic Storage Management (ASM)

      Your database files are moved to ASM storage, which must already exist on your system. If you do not have an ASM instance, you can create one using DBCA and then restart DBUA.

      See Also:

      Your platform-specific Oracle Clusterware Installation Guide for more information on installing and configuring ASM

      Click Next.

      The Database File Locations screen appears.

      Description of dbua06.jpg follows
      Description of the illustration dbua06.jpg

  7. Select one of the following options:

    • Use Common Location for All Database Files

      If you choose to have all of your database files in one location, then you must also do one of the following:

      • Accept the default location for your database files

      • Enter the full path to a different location in the Database Files Location field

      • Click Browse and select a different location for your database files

    • Use Oracle-Managed Files

      If you choose to use Oracle-Managed Files for your database files, then you must also do one of the following:

      • Accept the default database area

      • Enter the full path to a different database area in the Database Area field

      • Click Browse and select a different database area

    • Use a Mapping File to Specify Location of Database Files

      This option enables you to specify different locations for your database files. A sample mapping file is available in the logging location. You can edit the property values of the mapping file to specify a different location for each database file.

    Click Next.

    The Recovery Configuration screen appears, where you can designate a Flash Recovery Area for your database.

    Description of dbua07.jpg follows
    Description of the illustration dbua07.jpg

    Flash Recovery Area is an Oracle managed disk location used for storing backup and recovery related files. Oracle strongly recommends configuring a Flash Recovery Area, because it significantly enhances speed, reliability, and manageability of the database recovery process. This location is also used by Enterprise Manager if you enable local management and daily backups on the Management Options screen.

    If you selected the Move Database Files during Upgrade option in step 6, or if an Oracle Express Edition database is being upgraded to Oracle Enterprise Edition, then a Flash recovery Area must be configured. If a Flash Recovery Area is already configured, then current settings are retained but the screen will come up to allow you to override these values.

  8. Do one of the following:

    • Accept the default Flash Recovery Area location

    • Enter the full path to a different location in the Flash Recovery Area field

    • Click Browse and select a different Flash Recovery Area location

    Do one of the following:

    • Accept the default size for the Flash Recovery Area

    • Enter a different value in the Flash Recovery Area Size field

    • Click the up/down arrows to adjust the Flash Recovery Area size

    See Also:

    Oracle Database 2 Day DBA for more information on configuring the Flash Recovery Area

    Click Next.

    If no other database is already being monitored with Enterprise Manager, then the Management Options screen appears.

    Description of dbua17.jpg follows
    Description of the illustration dbua17.jpg

    At the Management Options screen, you have the option of setting up your database so it can be managed with Enterprise Manager. Enterprise Manager provides Web-based management tools for managing individual database instances, as well as central management tools for managing your entire Oracle environment, including multiple databases, hosts, application servers, and other components of your network.

    Before you can register the database with Oracle Enterprise Manager Grid Control, an Oracle Enterprise Manager Agent must be configured on the host computer.

  9. To set up your database to be managed with Enterprise Manager, select Configure the Database with Enterprise Manager and then select one of the following options:

    • Use Grid Control for Database Management

      If you select this option, then you must also select an Oracle Management Service from the Management Service drop-down list. When you run DBUA, it checks to see if the Oracle Management Agent has been installed on the host computer. If no Oracle Management Agent is found, then the Grid Control option is not available.

      When you finish installing the Oracle Database software, the database is automatically available as a managed target within the Oracle Enterprise Manager Grid Control.

    • Use Database Control for Database Management

      If you are not centrally managing your Oracle environment, then you can still use Enterprise Manager to manage your database. When you install Oracle Database, you automatically install Oracle Enterprise Manager Database Control, which provides Web-based features for monitoring and administering the single-instance or cluster database you are installing.

      Do the following steps if you want the SYSMAN user (the default Super Administrator and owner of the Management Repository schema) to receive E-mail notification when a metric for a specified condition reaches a critical or warning threshold:

      • Select Enable Email Notifications

      • Enter the name of the e-mail server you want to use for notifications in the Outgoing Mail (SMTP) Server field

      • Enter the e-mail address of the SYSMAN user in the Email Address field

      For example, Enterprise Manager can send an e-mail when a target goes down or when there are database space usage problems. E-mail notifications are enabled immediately upon installation.

      Do the following steps to use the Oracle-suggested backup strategy to back up your entire database with a minimum amount of configuration:

      • Select Enable Daily Backup

      • Enter a start time in the Backup Start Time field

      • Enter host credentials in the OS Username and Password fields

      If you select this option, then Enterprise Manager is configured to back up your database immediately after you finish upgrading Oracle Database. Enterprise Manager backs up the database to the Flash Recovery Area. Later, you can use Enterprise Manager to customize your backup strategy further.

      On Windows the user whose credentials you enter for the backup must be granted the Logon as a batch job privilege in the Local Security Policies of Windows. If the chosen user does not have this privilege, then the backup job fails.

    Click Next.

    The Database Credentials screen appears.

    Description of dbua18.jpg follows
    Description of the illustration dbua18.jpg

  10. Select one of the following options:

    • Use Different Passwords

      If you choose to use different passwords, then you must enter a password in the Password and Confirm Password columns for each account in the table.

    • Use the Same Password for All Accounts

      If you choose to use the same password, then you must enter that password in the Password and Confirm Password fields.

      Note:

      If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then new passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

    Click Next.

    If DBUA detects multiple listeners are configured in the Oracle Database 11g Release 1 (11.1) home, then the Network Configuration for the Database screen appears.

    Description of dbua08.jpg follows
    Description of the illustration dbua08.jpg

  11. The Network Configuration screen has two tabs. The Listeners tab is displayed if you have more than one listener in the Oracle Database 11g Release 1 (11.1) Oracle home. The Directory Service tab shows up if you have directory services configured in the Oracle Database 11g Release 1 (11.1) Oracle home.

    On the Listeners tab, select one of the following options:

    • Register this database with all the listeners

    • Register this database with selected listeners only

    If you choose to register selected listeners only, then you must select the listeners you want in the Available Listeners list and use the arrow buttons to move them to the Selected Listeners list.

    If you want to register your database with a directory service, then click the Directory Service tab.

    Description of dbua09.jpg follows
    Description of the illustration dbua09.jpg

    On the Directory Service tab, select one of the following options:

    • Yes, register the database

      Selecting this option enables client computers to connect to this database without a local name file (tnsnames.ora) and also enables them to use the Oracle Enterprise User Security feature.

    • No, don9t register the database

    If you choose to register the database, then you must also provide a user distinguished name (DN) in the User DN field and a password for that user in the Password field.

    Note:

    If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

    An Oracle wallet is created as part of database registration. It contains credentials suitable for password authentication between this database and the directory service. Enter a password in the Wallet Password and Confirm Password fields.

    Click Next.

    The Recompile Invalid Objects screen appears.

    Description of dbua10.jpg follows
    Description of the illustration dbua10.jpg

  12. Select Recompile invalid objects at the end of upgrade if you want DBUA to recompile all invalid PL/SQL modules after the upgrade is complete.

    When you upgrade a database to Oracle Database 11g Release 1 (11.1), many of the PL/SQL modules in your database become invalid. As a result, all existing PL/SQL modules in an INVALID state must be recompiled, such as packages, procedures, types, and so on.

    By default, Oracle Database recompiles invalid PL/SQL modules as they are used. The time it takes to recompile the module can result in poor performance when you begin to use your newly upgraded database.

    To eliminate these performance issues, select Recompile invalid objects at the end of upgrade. When you select this option, DBUA recompiles all the invalid PL/SQL modules immediately after the upgrade is performed. This ensures that you do not experience any performance issues later, as you begin using your newly upgraded database.

    Note:

    Selecting Recompile invalid objects at the end of upgrade is equivalent to running the utlrp.sql script, located in the ORACLE_HOME/rdbms/admin directory, which is used to recompile stored PL/SQL and Java code.

    The task of recompiling all the invalid PL/SQL modules in your database can take a significant amount of time and increase the time it takes to complete your database upgrade. If you have multiple CPUs, then you can reduce the time it takes to perform this task by taking advantage of parallel processing on your available CPUs. If you have multiple CPUs available, then DBUA automatically adds an additional section to the Recompile Invalid Objects screen and automatically determines the number of CPUs you have available.

    DBUA also provides a recommended degree of parallelism, which determines how many parallel processes are used to recompile your invalid PL/SQL modules. Specifically, DBUA sets the degree of parallelism to one less than the number of CPUs you have available. For example, if you have three CPUs available for processing, then DBUA selects 2 from the Degree of Parallelism menu. You can adjust this default value by selecting a new value from the Degree of Parallelism menu.

    Select Turn off Archiving and Flashback logging for the duration of upgrade to reduce the time required to complete the upgrade.

    If the database is in ARCHIVELOG or flashback logging mode, then DBUA gives you the choice of turning them off for the duration of the upgrade. If you choose this option, Oracle recommends that you perform an offline backup immediately after the upgrade.

    Click Next.

    The Backup screen appears.

    Description of dbua11.jpg follows
    Description of the illustration dbua11.jpg

  13. Select Backup database if you want DBUA to back up your database for you.

    Oracle strongly recommends that you back up your database before starting the upgrade. If errors occur during the upgrade, you might be required to restore the database from the backup.

    If you use DBUA to back up your database, then it makes a copy of all your database files in the directory you specify in the Backup Directory field. DBUA performs this cold backup automatically after it shuts down the database and before it begins performing the upgrade procedure. The cold backup does not compress your database files, and the backup directory must be a valid file system path. You cannot specify a raw device for the cold backup files.

    In addition, DBUA creates a batch file in the specified directory. You can use this batch file to restore the database files:

    • On Windows operating systems, the file is called db_name_restore.bat.

    • On Linux or UNIX platforms, the file is called db_name_restore.sh.

    If you choose not to use DBUA for your backup, then Oracle assumes you have already backed up your database using your own backup procedures.

    Note:

    DBUA does not back up ASM databases. You must manually back them up on your own.

    Click Next.

    The Summary screen appears.

    Description of dbua13.jpg follows
    Description of the illustration dbua13.jpg

  14. The Summary screen shows the following information about the upgrade before it starts:

    • Name, version, and Oracle home of the old and new databases

    • Database backup location, available space, and space required

    • Warnings ignored

    • Database components to be upgraded

    • Initialization parameters changes

    • Database files location

    • Listener registration

    See Also:

    "Setting the COMPATIBLE Initialization Parameter" for information about setting the COMPATIBLE initialization parameter after the upgrade

    Check all of the specifications. Then do one of the following:

    • Click Back if anything is incorrect until you reach the screen where you can correct it.

    • Click Finish if everything is correct.

    The Progress screen appears, and DBUA begins the upgrade.

    Description of dbua15.jpg follows
    Description of the illustration dbua15.jpg

  15. You might encounter error messages with Ignore and Abort choices. If other errors appear, then you must address them accordingly. If an error is severe and cannot be handled during the upgrade, then you have the following choices:

    • Click Ignore to ignore the error and proceed with the upgrade.

      This causes DBUA to display the step as skipped and move on to the next step in the upgrade, ignoring this and any dependent steps. The errors ignored are logged and shown later in the summary. After the upgrade is complete, you can fix the problem, restart DBUA, and complete the skipped steps.

    • Click Abort to terminate the upgrade process.

      If Ignore is not presented as a choice in the message, then this is your only option.

      If a database backup was taken by DBUA, then it asks if you want to restore the database. After the database has been restored, you must correct the cause of the error and restart DBUA to perform the upgrade again.

      If you do not want to restore the database, then DBUA leaves the database in its present state so that you can proceed with a manual upgrade.

  16. After the upgrade has completed, the following message is displayed on the Progress screen:

    Upgrade is complete. Click "OK" to see the results of the upgrade.
    

    Click OK.

    The Upgrade Results screen appears.

    Description of dbua16.jpg follows
    Description of the illustration dbua16.jpg

  17. The Upgrade Results screen displays a description of the original and upgraded databases and changes made to the initialization parameters. The screen also shows the directory where various log files are stored after the upgrade. You can examine these log files to obtain more details about the upgrade process.

    Note:

    An HTML version of the Upgrade Results is also saved in the log files directory.
    1. Click Configure Database Passwords to display the Password Management dialog box.

      The Password Management dialog box enables you to change the default password for a user after you upgrade the database. For security reasons, all users are locked except for the following users:

      • SYS

      • SYSTEM

      If you have enabled Local Management with Enterprise Manager, then the SYSMAN and DBSNMP accounts are also unlocked. These accounts provide Enterprise Manager with access to the database so it can gather monitoring data and so you can perform administration tasks with Enterprise Manager.

      If you have enabled Central Management with Enterprise Manager, then the DBSNMP account is unlocked, as well as the SYS and SYSTEM user accounts.

      Note:

      To prevent unauthorized use of the database, Oracle recommends that you change all user passwords immediately after you upgrade your database.

      If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

    2. Click Restore Database if you are not satisfied with the upgrade results.

      Depending on the method you used to back up your database, the Restore operation performs one of two tasks:

      • If you used DBUA to back up your database, then clicking Restore restores the original database and the original database settings from the backup.

      • If you used your own backup procedure to back up the database, then clicking Restore restores only the original database settings. To restore the database itself, you must restore the backup you created with your own backup utilities.

    If you are satisfied with the upgrade results, then click Exit to quit DBUA and use your newly upgraded database. DBUA removes the entry of the upgraded database from the old listener.ora file and reloads the listener of the old database.

Using DBUA in Silent Mode

When invoked with the -silent command line option, DBUA operates in silent mode. In silent mode, DBUA does not present a user interface. It also writes any messages (including information, errors, and warnings) to a log file in ORACLE_HOME/cfgtoollogs/dbua/SID/upgraden, where n is the number of upgrades that DBUA has performed as of this upgrade.

For example, the following command upgrades a database named ORCL in silent mode:

dbua -silent -sid ORCL &

DBUA Command Line Options

DBUA supports several command line options. You can specify all valid options from the command line using the following syntax:

dbua [ -silent ] [ -sid SID ] [-oracleHome home_name] [-oracleBase base_name]
[-diagnosticDest diagnostic_destination]
[-sysDBAUserName SYSDBA_user] [-sysDBAPassword SYSDBA_pwd]
[-upgradeASM] [-autoextendFiles] [-newGlobalDbName db_name] [-newSid new_SID]
[-generateMapFile] [-useASM] [-commonFileLocation common_files]
[-omfLocation omf_area] [-databaseMapFile map_file_name]
[-newRecoveryArea recover_area] [-newRecoveryAreaSize recover_size]
[-apexAdminPassword apex_pwd] [-disableUpgradeScriptLogging ] 
[-backupLocation directory]
[-sysauxTablespace -datafileName name -datafileSize size 
  -datafileSizeNext size -datafileSizeMax size]
[-postUpgradeScripts script [, script ] ... ]
[-initParam parameter=value [, parameter=value ] ... ]
[-disableArchiveLogMode] [-recompile_invalid_objects true | false]
[-degree_of_parallelism cpu_number]
[-emConfiguration {CENTRAL|LOCAL|ALL|NOBACKUP|NOEMAIL|NONE}
  -dbsnmpPassword password -sysmanPassword password -asmPassword password 
  -hostUserName hostname -hostUserPassword password -backupSchedule hh:mm
  [-smtpServer server_name -emailAddress address]
  [-centralAgent location] [-localRacSid SID]]
[-recoveryAreaDestination directory] [-h|-help]

Table 3-2 describes the various options and their parameters that are supported by DBUA.

Table 3-2 DBUA Command Line options

Option Description

-silent

Specifies that DBUA should operate in silent mode.

-sid SID

Specifies the system identifier (SID) of the database to upgrade

-oracleHome home_name

Specifies the Oracle Database home directory of the database to upgrade

-oracleBase base_name

Specifies the Oracle Database base directory of the database to upgrade

-diagnosticDest diagnostic_destination

Specifies the default location to store Oracle trace and diagnostic files. It replaces the initialization parameter settings for background dump destination, user dump destination, and core dump destination from earlier releases.

-sysDBAUserName SYSDBA_user

Specifies a user with SYSDBA privileges.

-sysDBAPassword SYSDBA_pwd

Specifies the password for SYSDBA_user.

-upgradeASM

This option enables the upgrade of an Automatic Storage Management (ASM) instance.

-autoextendFiles

This option autoextends database files during the upgrade. Data files revert to their original autoextend settings after the upgrade.

-newGlobalDbName db_name

Specifies a new global database name. This option applies only if you are moving data files or upgrading an Oracle XE database.

-newSid new_SID

Specifies a new system identifier (SID) of the database to upgrade. This option applies only if you are moving data files or upgrading an Oracle XE database.

-generateMapFile

If you specify this option, then DBUA only generates a database map file in the log location and then exits. This option applies only if you are moving data files or upgrading an Oracle XE database.

-useASM

If the database to upgrade has an ASM instance, then this option tells DBUA to use it for the upgrade. This option applies only if you are moving data files or upgrading an Oracle XE database.

-commonFileLocation common_files

Specifies a common location to store database files. This option applies only if you are moving data files or upgrading an Oracle XE database.

-omfLocation omf_area

Specifies a database area for Oracle-Managed Files. This option applies only if you are moving data files or upgrading an Oracle XE database.

-databaseMapFile map_file_name

Specifies the full name of the map file to map database files. This option applies only if you are moving data files or upgrading an Oracle XE database.

-newRecoveryArea recover_area

Specifies the recovery area for a database that is moved during upgrade. This option applies only if you are moving data files or upgrading an Oracle XE database.

-newRecoveryAreaSize recover_size

Specifies the recovery area size (MB) for a database that is moved during upgrade. This option applies only if you are moving data files or upgrading an Oracle XE database.

-apexAdminPassword apex_pwd

Specifies the password for the Application Express Administrator.

-disableUpgradeScriptLogging

This option disables the detailed log generation for running SQL scripts during the upgrade process. This is enabled by default. To enable log generation, do not specify this option.

-backupLocation directory

Specifies a directory to back up your database before the upgrade starts

-sysauxTablespace

This option creates a new SYSAUX tablespace

-datafileName name

Specifies the complete path of the data file for creating the new SYSAUX tablespace

-datafileSize size

Specifies the size of the data file for creating the new SYSAUX tablespace {500M, 10G}

-datafileSizeNext size

Specifies the next extent for the data file

-datafileSizeMax size

Specifies the maximum size of the data file

-postUpgradeScripts script [, script ] ...

Specifies a comma-delimited list of SQL scripts. Specify complete path names. The scripts are executed at the end of the upgrade.

-initParam parameter=value [, parameter=value ] ...

Specifies a comma-delimited list of initialization parameter values of the form name=value

-disableArchiveLogMode

This option turns off archiving and flashback logging for the duration of the upgrade.

-recompile_invalid_objects true|false

When you specify true for this option, DBUA recompiles all invalid PL/SQL modules immediately after the upgrade is performed.

-degree_of_parallelism cpu_number

Specifies the number of CPUs to be used for parallel recompilation.


-emConfiguration
{CENTRAL|LOCAL|ALL|NOBACKUP|
NOEMAIL|NONE}

Specifies Oracle Enterprise Manager management options:

  • CENTRAL - Database is centrally managed by Oracle Enterprise Manager.

  • LOCAL - Database is locally managed by Oracle Enterprise Manager.

  • ALL - This option configures Oracle Enterprise Manager Database Control, enables daily backups, and enables e-mail notifications

  • NOBACKUP - Automatic daily backups of the database are not enabled.

  • NOEMAIL - E-mail notifications are not enabled.

  • NONE - Database is not managed by Enterprise Manager.

-dbsnmpPassword password

Specifies the DBSNMP user password. This option applies only if you are configuring Oracle Enterprise Manager.

-sysmanPassword password

Specifies the SYSMAN user password. This option applies only if you are configuring Oracle Enterprise Manager.

-asmPassword password

Specifies the SYS password for ASM instance. This option applies only if you are configuring Oracle Enterprise Manager.

-hostUserName hostname

Specifies the host user name for the Oracle Enterprise Manager backup job. This option applies only if you are configuring Oracle Enterprise Manager.

-hostUserPassword password

Specifies the host user password for the Oracle Enterprise Manager backup job. This option applies only if you are configuring Oracle Enterprise Manager.

-backupSchedule hh:mm

Specifies the daily backup schedule in the form hh:mm (hours and minutes). This option applies only if you are configuring Oracle Enterprise Manager.

-smtpServer server_name

Specifies the outgoing mail (SMTP) server for E-mail notifications. This option applies only if you are configuring Oracle Enterprise Manager.

-emailAddress address

Specifies the E-mail address for E-mail notifications. This option applies only if you are configuring Oracle Enterprise Manager.

-centralAgent location

Specifies the Oracle Enterprise Manager central agent location. This option applies only if you are configuring Oracle Enterprise Manager.

-recoveryAreaDestination directory

Specifies the destination directory for all recovery files. This option applies only if you are moving data files, upgrading an Oracle XE database, or configuring Oracle Enterprise Manager.

[-localRacSid SID

Specifies the local SID of the cluster database if the cluster database is not registered in the Oracle Cluster Registry

-h | -help

Displays help for DBUA


Note:

If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

Upgrade a Database Manually

The following sections guide you through the process of performing a manual upgrade. They assume that you have already run the Pre-Upgrade Information Tool as described in "Run the Pre-Upgrade Information Tool".

Back Up the Database

After running the Pre-Upgrade Information Tool and cleanly shutting down the instance, you should perform a full backup of the database. Complete the following steps:

  1. Sign on to RMAN:

    rman "target / nocatalog"
    
  2. Issue the following RMAN commands:

    RUN
    {
        ALLOCATE CHANNEL chan_name TYPE DISK;
        BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
        BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
    }
    

Caution:

If you encounter problems with the upgrade and wish to abandon the upgrade completely, then you must restore the database from this backup. Therefore, make sure you back up your database now as a precaution.

See Also:

Oracle Database Backup and Recovery User's Guide for more information about backing up a database

Prepare the New Oracle Home

After analyzing the database to be upgraded, complete the following steps to prepare the new Oracle home:

  1. Copy configuration files from the Oracle home of the database being upgraded to the Oracle Database 11g Release 1 (11.1) Oracle home:

    1. If your parameter file resides within the old environment's Oracle home, then copy it to the new Oracle home. By default, Oracle looks for the parameter file in the ORACLE_HOME/dbs directory on Linux or UNIX platforms and in the ORACLE_HOME\database directory on Windows operating systems. The parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you upgrade to Oracle Database 11g Release 1 (11.1).

      Note:

      It might be necessary to create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) so that you can edit the initialization parameters.
    2. If your parameter file resides within an Automatic Storage Management (ASM) instance, then backup the parameter file using the following command:

      CREATE pfile FROM spfile;
      

      If you must downgrade the database and your SPFILE lived in ASM, then the parameter file must be restored prior to the downgrade.

    3. If your parameter file is a text-based initialization parameter file with either an IFILE (include file) or a SPFILE (server parameter file) entry, and the file specified in the IFILE or SPFILE entry resides within the old environment's Oracle home, then copy the file specified by the IFILE or SPFILE entry to the new Oracle home. The file specified in the IFILE or SPFILE entry contains additional initialization parameters.

    4. If you have a password file that resides within the old environment's Oracle home, then move or copy the password file to the Oracle Database 11g Release 1 (11.1) Oracle home.

      The name and location of the password file are operating system-specific. On Linux or UNIX platforms, the default password file is orapwsid, located in the ORACLE_HOME/dbs directory. On Windows operating systems, the default password file is pwdsid.ora, located in the ORACLE_HOME\database directory. In both cases, sid is your Oracle instance ID.

    5. If you are upgrading a cluster database and your initdb_name.ora file resides within the old environment's Oracle home, then move or copy the initdb_name.ora file to the new Oracle home.

    Note:

    If you are upgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.
  2. Adjust your parameter file in Oracle Database 11g Release 1 (11.1) by completing the following steps:

    1. Remove obsolete initialization parameters and adjust deprecated initialization parameters. Certain parameters are obsolete in Oracle Database 11g Release 1 (11.1), while other parameters have become deprecated. Remove all obsolete parameters from any parameter file that starts an Oracle Database 11g Release 1 (11.1) instance. Obsolete parameters might cause errors in Oracle Database 11g Release 1 (11.1). Also, alter any parameter whose syntax has changed in the new release.

      The Pre-Upgrade Information Tool displays any deprecated parameters and obsolete parameters it finds in the Deprecated Parameters and Obsolete Parameters sections, respectively.

      See Also:

      Appendix A, "Behavior Changes" for a list of initialization parameters that have been deprecated or have become obsolete, and for information on initialization parameters that have changed in ways that raise compatibility issues between different releases of Oracle Database software
    2. Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database 11g Release 1 (11.1). The Pre-Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set.

    3. Adjust the values of the initialization parameters to at least the minimum value indicated the Pre-Upgrade Information Tool.

    4. Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.

    5. If the parameter file contains an IFILE entry, then change the IFILE entry in the parameter file to point to the new location of the include file that you specified in Step 1c. Then, edit the file specified in the IFILE entry in the same way that you edited the parameter file in Step ` through Step d.

    6. If you are upgrading a cluster database, then modify the initdb_name.ora file in the same way that you modified the parameter file.

    Make sure you save all of the files you modified after making these adjustments.

    Note:

    If you are upgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.
  3. If you are upgrading a cluster database, then set the CLUSTER_DATABASE initialization parameter to false. After the upgrade, you must set this initialization parameter back to true.

Upgrade the Database

After preparing the new Oracle home, you are ready to proceed with the manual upgrade. Complete the following steps to upgrade the database:

  1. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE
    
  2. If your operating system is Windows, then complete the following steps:

    1. Stop the OracleServiceSID Oracle service of the database you are upgrading, where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt:

      C:\> NET STOP OracleServiceORCL
      
    2. Delete the Oracle service at a command prompt using ORADIM.

      If your SID is ORCL, then enter the following command:

      C:\> ORADIM -DELETE -SID ORCL
      
    3. Create the Oracle Database 11g Release 1 (11.1) service at a command prompt using the ORADIM command of the new Oracle Database release:

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

      This syntax includes the following variables:

      Variable Description
      SID The same SID name as the SID of the database you are upgrading.
      PASSWORD The password for the new Oracle Database 11g Release 1 (11.1) database instance. This is the password for the user connected with SYSDBA privileges. The -SYSPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.

      If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

      USERS The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
      ORACLE_HOME The Oracle Database 11g Release 1 (11.1) Oracle home directory. Ensure that you specify the full path name with the -PFILE option, including the drive letter of the Oracle home directory.

      For example, if your SID is ORCL, your password (SYSPWD) is TWxy5791, the maximum number of users (MAXUSERS) is 10, and the ORACLE_HOME directory is C:\ORACLE\PRODUCT\11.1.0\DB, then enter the following command:

      C:\> ORADIM -NEW -SID ORCL -SYSPWD TWxy5791 -MAXUSERS 10
      -STARTMODE AUTO -PFILE C:\ORACLE\PRODUCT\11.1.0\DB\DATABASE\INITORCL.ORA
      

      ORADIM writes a log file to the ORACLE_HOME\database directory.

  3. If your operating system is Linux or UNIX, then make the following checks:

    1. Your ORACLE_SID is set correctly

    2. The oratab file points to your Oracle Database 11g Release 1 (11.1) Oracle home

    3. The following environment variables point to the Oracle Database 11g Release 1 (11.1) directories:

      • ORACLE_HOME

      • PATH

    4. Any scripts that clients use to set the ORACLE_HOME value must point to the new Oracle home.

    Note:

    If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

    See Also:

    Your operating system-specific Oracle Database installation documents for information about setting other important environment variables on your operating system.
  4. Log in to the system as the owner of the Oracle Database 11g Release 1 (11.1) Oracle home directory.

  5. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

  6. Start SQL*Plus.

  7. Connect to the database instance as a user with SYSDBA privileges.

  8. Start the instance by issuing the following commands:

    SQL> STARTUP UPGRADE
    

    Note:

    The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.

    You might be required to use the PFILE option to specify the location of your initialization parameter file.

    Once the database is started in upgrade mode, only queries on fixed views will execute without error until after the catupgrd.sql script is run. Prior to running catupgrd.sql, queries on any other view or the use of PL/SQL will return an error.

    The following are common errors that might occur when attempting to start the new Oracle Database 11g Release 1 (11.1) database. Some of these errors are written to the alert log and not to your session. If you receive any of these errors, then issue the SHUTDOWN ABORT command to shut down the database and correct the problem.

    • ORA-00401: the value for parameter compatible is not supported by this release

      The COMPATIBLE initialization parameter is set to a value less than "10.0.0".

    • ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

      The CLUSTER_DATABASE initialization parameter is set to TRUE instead of FALSE.

    • ORA-39700: database must be opened with UPGRADE option

      The STARTUP command was issued without the UPGRADE keyword.

    • ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks

      A redo log file size is less than 4 MB:

    If errors appear listing obsolete initialization parameters, then make a note of the obsolete initialization parameters and continue with the upgrade. Remove the obsolete initialization parameters the next time you shut down the database.

  9. Do this step only if you are upgrading from Oracle9i Release 2 (9.2).

    Create a SYSAUX tablespace. In Oracle Database 11g Release 1 (11.1), the SYSAUX tablespace is used to consolidate data from a number of tablespaces that were separate in previous releases.

    The SYSAUX tablespace must be created with the following mandatory attributes:

    • ONLINE

    • PERMANENT

    • READ WRITE

    • EXTENT MANAGEMENT LOCAL

    • SEGMENT SPACE MANAGEMENT AUTO

    The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section. Table 3-3 can be used to determine an optimal size for the SYSAUX tablespace.

    Table 3-3 Guidelines for Sizing the SYSAUX Tablespace

    Factor Small Medium Large

    Number of CPUs

    2

    8

    32

    Number of concurrently active sessions

    5

    20

    100

    Number of user objects (tables and indexes)

    500

    5,000

    50,000

    Estimated SYSAUX size at steady state with default configuration

    500 MB

    2 GB

    5 GB


    The following SQL statement would create a 500 MB SYSAUX tablespace for the database:

    SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
             SIZE 500M REUSE
             EXTENT MANAGEMENT LOCAL
             SEGMENT SPACE MANAGEMENT AUTO
             ONLINE;
    

    See Also:

    Oracle Database Administrator's Guide for more information about the SYSAUX tablespace
  10. Set the system to spool results to a log file for later verification of success:

    SQL> SPOOL upgrade.log
    
  11. Run the catupgrd.sql script:

    SQL> @catupgrd.sql
    

    The catupgrd.sql script determines which upgrade scripts must be run, runs them, and then shuts down the database. You must run the script in the Oracle Database 11g Release 1 (11.1) environment.

    The upgrade script creates and alters certain data dictionary tables. It also upgrades or installs the following database components in the new Oracle Database 11g Release 1 (11.1) database:

    • Oracle Database Catalog Views

    • Oracle Database Packages and Types

    • JServer JAVA Virtual Machine

    • Oracle Database Java Packages

    • Oracle XDK

    • Oracle Real Application Clusters

    • Oracle Workspace Manager

    • Oracle Multimedia

    • Oracle XML Database

    • OLAP Analytic Workspace

    • Oracle OLAP API

    • OLAP Catalog

    • Oracle Text

    • Spatial

    • Oracle Data Mining

    • Oracle Label Security

    • Messaging Gateway

    • Oracle Expression Filter

    • Oracle Rules Manager

    • Oracle Enterprise Manager Repository

    • Oracle Database Vault

    • Oracle Application Express

  12. Restart the instance to reinitialize the system parameters for normal operation.

    SQL> STARTUP
    

    This restart, following the database shutdown performed as part of the catupgrd.sql script, flushes all caches, clears buffers, and performs other housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software.

    Also, if you encountered a message listing obsolete initialization parameters when you started the database in Step 8, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.

  13. The Post-Upgrade Status Tool provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.

    Run utlu111s.sql to display the results of the upgrade:

    SQL> @utlu111s.sql
    

    The Post-Upgrade Status Tool displays output similar to the following:

    Oracle Database 11.1 Post-Upgrade Status Tool         03-18-2007 22:48:55
    .
    Component                                Status         Version  HH:MM:SS
    .
    Oracle Server
    .                                         VALID      11.1.0.6.0  00:17:31
    JServer JAVA Virtual Machine
    .                                         VALID      11.1.0.6.0  00:02:32
    Oracle Workspace Manager
    .                                         VALID      11.1.0.6.0  00:01:02
    Messaging Gateway
    .                                         VALID      11.1.0.6.0  00:00:46
    OLAP Analytic Workspace
    .                                         VALID      11.1.0.6.0  00:03:20
    OLAP Catalog
    .                                         VALID      11.1.0.6.0  00:05:56
    Oracle OLAP API
    .                                         VALID      11.1.0.6.0  00:00:48
    Oracle Label Security
    .                                         VALID      11.1.0.6.0  00:00:37
    Oracle Enterprise Manager
    .                                         VALID      11.1.0.6.0  00:12:02
    Oracle XDK
    .                                         VALID      11.1.0.6.0  00:00:42
    Oracle Text
    .                                         VALID      11.1.0.6.0  00:01:02
    Oracle XML Database
    .                                         VALID      11.1.0.6.0  00:04:24
    Oracle Database Java Packages
    .                                         VALID      11.1.0.6.0  00:00:27
    Oracle Multimedia
    .                                         VALID      11.1.0.6.0  00:05:44
    Spatial
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 4096 bytes of shared memory ("java
    pool","sun/tools/java/ConstantsSYS","joxlod exec hp",":SGAClass")
    ORA-06512: at "SYS.DBMS_JAVA", line 704
    ORA-06512: at line 1
    .                                         VALID      11.1.0.6.0  00:08:24
    Oracle Ultra Search
    .                                         VALID      11.1.0.6.0  00:03:09
    Oracle Expression Filter
    .                                         VALID      11.1.0.6.0  00:00:38
    Oracle Rules Manager
    .                                         VALID      11.1.0.6.0  00:00:21
    Oracle Application Express
    .                                         VALID     2.2.1.01.01  00:23:25
    Gathering Statistics
    .       00:05:12
    Total Upgrade Time: 01:29:03
    

    If the Post-Upgrade Status Tool returns errors or shows components that are not VALID or not the most recent release, then see "Troubleshoot the Upgrade".

  14. 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
    

    This script can be run concurrently with utlrp.sql (see step 15), if you run it in a new SQL*Plus session.

  15. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

    SQL> @utlrp.sql
    

    Verify that all expected packages and classes are valid:

    SQL> SELECT count(*) FROM dba_invalid_objects;
    SQL> SELECT distinct object_name FROM dba_invalid_objects;
    
  16. Exit SQL*Plus.

Your database is now upgraded to the Oracle Database 11g Release 1 (11.1). Complete the procedures described in Chapter 4, "After Upgrading to the New Release".

WARNING:

If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new Oracle Database installation. Also, before you remove the old Oracle environment, make sure you relocate any data files in that environment to the new Oracle Database environment. See the Oracle Database Administrator's Guide for information about relocating data files.

Troubleshoot the Upgrade

This section explains what to do if something goes wrong with your upgrade. It contain the following topics:

Resource Limits

If you run out of resources during the upgrade, then increase the resource allocation. After increasing the resource allocation, you should perform a SHUTDOWN ABORT and restart the instance (in UPGRADE mode) before rerunning the catupgrd.sql script or restarting DBUA.

There are three resources that generally require increases for a new Oracle Database release:

  • SYSTEM and SYSAUX tablespaces

    Typically you receive one of the following messages during the upgrade if your SYSTEM tablespace size is insufficient:

    ORA-01650: unable to extend rollback segment string by string in tablespace string
    ORA-01651: unable to extend save undo segment by string for tablespace string
    ORA-01652: unable to extend temp segment by string in tablespace string
    ORA-01653: unable to extend table string.string by string in tablespace string
    ORA-01654: unable to extend index string.string by string in tablespace string
    ORA-01655: unable to extend cluster string.string by string in tablespace string
    

    To avoid these errors, set AUTOEXTEND ON MAXSIZE UNLIMITED for the SYSTEM and SYSAUX tablespaces.

  • Shared memory

    You might require larger shared memory pool sizes in some cases. The error message indicates which shared memory initialization parameter must be increased.

    ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")
    

    See Also:

    Oracle Database Reference for information about shared memory initialization parameters.
  • Rollback segments/undo tablespace

    If you are using rollback segments, then you must have a single large (100 MB) PUBLIC rollback segment online while the upgrade scripts are being run. Smaller public rollback segments should be taken offline during the upgrade. Typically you get the following error if your rollback segment size is insufficient:

    ORA-01562: failed to extend rollback segment number string
    

    If you are using an undo tablespace, then be sure it is at least 400 MB.

  • Flash Recovery Area

    If you are using a Flash Recovery Area and it fills up during the upgrade, you will see the following error in the alert log, followed by some suggestions for recovering from the problem:

    ORA-19815: WARNING: db_recovery_file_dest_size of string bytes is 98.99%
    used, and has string remaining bytes available.
    

    Identify the root cause of the problem and take appropriate actions to proceed with the upgrade. To avoid issues during the upgrade, increase the amount of space available in your Flash Recovery Area prior to starting the upgrade.

Component Status

The Post-Upgrade Status Tool should report VALID status for all components at the end of the upgrade. The following list shows and briefly describes other status values that you might see:

  • NO SCRIPT

    The component upgrade script was not found in ORACLE_HOME. Check the install logs, install the component software, and then rerun catupgrd.sql.

  • OPTION OFF

    The server option required for the component was not installed or was not linked with the server. Check the V$OPTION view as well as the install logs. Install the component or relink the server with the required option, and then rerun catupgrd.sql

  • REMOVED

    The component was not upgraded because it was removed from the database.

  • INVALID

    Some objects for the component were invalid at the completion of the upgrade. If there were no errors during the component upgrade, then running utlrp.sql might change the status to VALID without rerunning the entire upgrade. Check the DBA_REGISTRY view after running utlrp.sql.

  • UPGRADING

    The component upgrade did not complete. Resolve the problem and rerun catupgrd.sql.

Rerunning the Upgrade

Follow these steps to rerun the upgrade:

  1. Shut down the database:

    SQL> SHUTDOWN IMMEDIATE
    
  2. Restart the database in UPGRADE mode:

    SQL> STARTUP UPGRADE
    
  3. Rerun catupgrd.sql:

    SQL> @catupgrd.sql
    
  4. Rerun utlu111s.sql:

    SQL> @utlu111s.sql
    

Cancel the Upgrade

If you completed the steps in "Back Up the Database" to back up your database, then the easiest way to cancel the upgrade is to restore that backup. Complete the following steps:

  1. Log in to the system as the owner of the Oracle home directory of the previous release.

  2. Sign on to RMAN:

    rman "target / nocatalog"
    
  3. Issue the following RMAN commands:

    STARTUP NOMOUNT
    RUN
    {
        RESTORE CONTROLFILE FROM 'save_controlfile_location';
        ALTER DATABASE MOUNT;
        RESTORE DATABASE FROM TAG before_upgrade
        ALTER DATABASE OPEN RESETLOGS;
    }
    

Upgrade an ASM Instance with DBUA

It is not possible to upgrade an ASM instance with DBUA when the source and target ASM Oracle homes are owned by different users. Attempting to do so returns an error similar to the following:

PRKH-1014 Current user user_name is not the same as owner owner_name of oracle home

Upgrading a single-instance ASM requires that Oracle Cluster Synchronization Services (CSS) first be configured to run from the new Oracle Database 11g Release 1 (11.1) home. If DBUA detects that this has not been done, it will instruct you to do so. See "Reconfigure Oracle Cluster Synchronization Services" for more information.

Complete the following steps to upgrade an Automatic Storage Management (ASM) instance to Oracle Database 11g Release 1 (11.1):

  1. Do one of the following to start DBUA:

    • On Linux or UNIX platforms, enter the following command at a system prompt in the Oracle Database 11g Release 1 (11.1) environment:

      dbua
      

      Note:

      The dbua executable is usually located in the ORACLE_HOME/bin directory.
    • On Windows operating systems, select Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Database Upgrade Assistant.

    The DBUA Welcome screen appears.

    Description of asm_dbua01.jpg follows
    Description of the illustration asm_dbua01.jpg

  2. If you need help at any screen or want to consult more documentation about DBUA, then click Help to open the online help.

    Click Next.

    The Upgrade Operations page appears with options to upgrade a database or an ASM instance.

    Description of asm_dbua02.jpg follows
    Description of the illustration asm_dbua02.jpg

  3. At the Upgrade Operations page, select Upgrade Automatic Storage Management Instance. This operation upgrades only the Automatic Storage Management instance on the system to 11g Release 1 (11.1).

    The other option, Upgrade a Database, is discussed in "Upgrade a Database Using Database Upgrade Assistant".

    Oracle recommends that you upgrade the database and ASM in separate DBUA sessions, in separate Oracle homes.

    Click Next.

    The DBUA Summary screen appears.

    Description of asm_dbua03.jpg follows
    Description of the illustration asm_dbua03.jpg

  4. The Summary screen shows the name, version, and Oracle home of the old and new ASM instances.

    Check all of the specifications. Then do one of the following:

    • Click Back if anything is incorrect until you reach the screen where you can correct it.

    • Click Finish if everything is correct.

    Note:

    DBUA shuts down the ASM instance during the upgrade process. Neither it nor the databases using it are available for general use during the upgrade.

    The Progress screen appears, and DBUA begins the upgrade.

    Description of asm_dbua04.jpg follows
    Description of the illustration asm_dbua04.jpg

  5. After the upgrade has completed, the following message is displayed on the Progress screen:

    Upgrade is complete. Click "OK" to see the results of the upgrade.
    

    Click OK.

    The Upgrade Results screen appears.

    Description of asm_dbua05.jpg follows
    Description of the illustration asm_dbua05.jpg

  6. The Upgrade Results screen displays information about the previous and upgraded ASM instances. It also includes a summary of the steps performed during the ASM upgrade and the location of log files offering more details about individual upgrade steps.

    Click Close to exit DBUA.

Upgrade an ASM Instance Manually

The following sections guide you through the process of performing a manual upgrade. They assume that you have already installed the Oracle Database 11g Release 1 (11.1) software and run the Pre-Upgrade Information Tool as described in "Run the Pre-Upgrade Information Tool".

Reconfigure Oracle Cluster Synchronization Services

Upgrading a single-instance ASM requires that Oracle Cluster Synchronization Services (CSS) first be configured to run from the new Oracle Database 11g Release 1 (11.1) home. To do this you need to run the following command before upgrading ASM:

localconfig reset [destination_Oracle_home]

where destination_Oracle_home is required if you run the command from the Oracle home where the CSS service is currently configured.

Prepare the New ASM Oracle Home

Complete the following steps to prepare the new Oracle home:

  1. Copy configuration files from the Oracle home of the ASM instance being upgraded to the Oracle Database 11g Release 1 (11.1) Oracle home:

    1. If your parameter file resides within the old environment's Oracle home, then copy it to the new Oracle home. By default, Oracle looks for the parameter file in the ORACLE_HOME/dbs directory on Linux or UNIX platforms and in the ORACLE_HOME\database directory on Windows operating systems. The parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you upgrade to Oracle Database 11g Release 1 (11.1).

      Note:

      It might be necessary to create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) so that you can edit the initialization parameters.
    2. If your parameter file is a text-based initialization parameter file with either an IFILE (include file) or a SPFILE (server parameter file) entry, and the file specified in the IFILE or SPFILE entry resides within the old environment's Oracle home, then copy the file specified by the IFILE or SPFILE entry to the new Oracle home. The file specified in the IFILE or SPFILE entry contains additional initialization parameters.

    3. If you have a password file that resides within the old environment's Oracle home, then move or copy the password file to the Oracle Database 11g Release 1 (11.1) Oracle home.

      The name and location of the password file are operating system-specific. On Linux or UNIX platforms, the default password file is orapwsid, located in the ORACLE_HOME/dbs directory. On Windows operating systems, the default password file is pwdsid.ora, located in the ORACLE_HOME\database directory. In both cases, sid is your Oracle instance ID.

    Note:

    If you are upgrading a clustered ASM, then perform this step on all nodes that have ASM instances configured.
  2. Adjust your parameter file in Oracle Database 11g Release 1 (11.1) by completing the following steps:

    1. Remove obsolete initialization parameters and adjust deprecated initialization parameters. Certain parameters are obsolete in Oracle Database 11g Release 1 (11.1), while other parameters have become deprecated. Remove all obsolete parameters from any parameter file that starts an Oracle Database 11g Release 1 (11.1) instance. Obsolete parameters might cause errors in Oracle Database 11g Release 1 (11.1). Also, alter any parameter whose syntax has changed in the new release.

      The Pre-Upgrade Information Tool displays any deprecated parameters and obsolete parameters it finds in the Deprecated Parameters and Obsolete Parameters sections, respectively.

      See Also:

      Appendix A, "Behavior Changes" for a list of initialization parameters that have been deprecated or have become obsolete, and for information on initialization parameters that have changed in ways that raise compatibility issues between different releases of Oracle Database software
    2. Adjust the values of the initialization parameters to at least the minimum value indicated the Pre-Upgrade Information Tool.

    3. Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.

    4. If the parameter file contains an IFILE entry, then change the IFILE entry in the parameter file to point to the new location of the include file that you specified in Step 1c. Then, edit the file specified in the IFILE entry in the same way that you edited the parameter file in Step ` through Step d.

    Make sure you save all of the files you modified after making these adjustments.

    Note:

    If you are upgrading a clustered ASM, then perform this step on all nodes that have ASM instances configured.

Upgrade ASM

After preparing the new Oracle home, you are ready to proceed with the manual upgrade. Complete the following steps to upgrade ASM:

  1. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE
    
  2. If your operating system is Windows, then complete the following steps:

    1. Stop the OracleASMServiceSID Oracle service of the ASM instance you are upgrading, where SID is the instance name. For example, if your SID is +ASM, then enter the following at a command prompt:

      C:\> NET STOP OracleASMService+ASM
      
    2. Delete the Oracle service at a command prompt using ORADIM.

      If your SID is +ASM, then enter the following command:

      C:\> ORADIM -DELETE -ASMSID +ASM
      
    3. Create the Oracle Database 11g Release 1 (11.1) service at a command prompt using the ORADIM command of the new Oracle Database release:

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

      This syntax includes the following variables:

      Variable Description
      SID The same SID name as the SID of the ASM instance you are upgrading.
      PASSWORD The password for the new Oracle Database 11g Release 1 (11.1) ASM instance. This is the password for the user connected with SYSDBA privileges. The -SYSPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.

      If the default Oracle Database 11g Release 1 (11.1) security settings are in place, then passwords must be at least 8 characters, and passwords such as welcome and oracle are not allowed. See Oracle Database Security Guide for more information.

      USERS The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
      ORACLE_HOME The Oracle home for the new Oracle Database 11g Release 1 (11.1) ASM instance. Ensure that you specify the full path name with the -PFILE option, including the drive letter of the Oracle home directory.

      For example, if your SID is +ASM, your password (INTPWD) is TWxy5791, the maximum number of users (MAXUSERS) is 10, and the ORACLE_HOME directory is C:\ORACLE\PRODUCT\11.1.0\DB, then enter the following command:

      C:\> ORADIM -NEW -ASMSID +ASM -SYSPWD TWxy5791 -MAXUSERS 10
      -STARTMODE AUTO -PFILE C:\ORACLE\PRODUCT\11.1.0\DB\DATABASE\INIT+ASM.ORA
      
  3. If your operating system is Linux or UNIX, then make the following checks:

    1. Your ORACLE_SID is set correctly

    2. The oratab file points to the Oracle home for the new Oracle Database 11g Release 1 (11.1) ASM instance

    3. The following environment variables point to the Oracle Database 11g Release 1 (11.1) directories:

      • ORACLE_HOME

      • PATH

    Note:

    If you are upgrading a clustered ASM, then perform these checks on all nodes that have ASM instances configured.

    See Also:

    Your operating system-specific Oracle Database installation documents for information about setting other important environment variables on your operating system.
  4. Log in to the system as the owner of the Oracle home for the new Oracle Database 11g Release 1 (11.1) ASM instance.

  5. Start SQL*Plus.

  6. Connect to the ASM instance as a user with SYSASM privileges using operating system (OS) authentication.

  7. Start up the instance by issuing the following command:

    SQL> STARTUP
    
  8. If you encounter a message listing obsolete initialization parameters when you start ASM, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.

  9. Grant the SYSMAN privilege to SYS:

    GRANT sysasm TO sys;
    

    This command allows the SYS user to be authenticated using the password file.

  10. Exit SQL*Plus.

Your ASM is now upgraded to the Oracle Database 11g Release 1 (11.1). Complete the ASM-specific procedures described in Chapter 4, "After Upgrading to the New Release".

Troubleshoot the ASM Upgrade

This section explains what to do if something goes wrong with your ASM upgrade.

If you run out of resources during the ASM upgrade, then increase the resource allocation. After increasing the resource allocation, you should perform a SHUTDOWN ABORT and restart the instance before performing a manual ASM upgrade or restarting DBUA.

A new Oracle Database release might require larger shared memory pool sizes in some cases. The error message indicates which shared memory initialization parameter must be increased.

ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

See Also:

Oracle Database Reference for information about shared memory initialization parameters.

Cancel the ASM Upgrade

To cancel an ASM upgrade, start the ASM instance from the old Oracle home.