3 Upgrading to the New Release

Oracle ASM is Installed with Oracle Grid Infrastructure

In past releases, Oracle ASM was installed as part of the Oracle Database installation. With Oracle Database 11g Release 2 (11.2), Oracle ASM is part of an Oracle grid infrastructure installation. If you want to upgrade an existing Oracle ASM installation, then you must upgrade Oracle ASM by running an Oracle grid infrastructure upgrade.

Upgrading Oracle Clusterware and Automatic Storage Management (ASM) Databases

Prior to Oracle Database 11g, 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, the same user that owned the Oracle Clusterware 10g software must perform the Oracle Clusterware 11g upgrade.

Starting with Oracle Database 11g release 2 (11.2), the Oracle Clusterware software must be upgraded to a new directory, called the Oracle grid infrastructure home. Also, ASM and Oracle Clusterware (and Oracle Restart for single-instance databases) must run in the same Oracle grid infrastructure home.

For a single-instance configuration, ASM and Oracle Restart run from the Oracle grid infrastructure home, and the cluster synchronization services daemon (CSS) runs from this same home. Thus, ASM and CSS will be upgraded to Oracle Database 11g Release 2 (11.2) at the same time.

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:

ORACLE_HOME/bin/localconfig delete

If you do not know whether or not your configuration includes Oracle ASM, then issue the following SQL statement on the database instance:

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.

CREATE USER user_name IDENTIFIED BY password;

Upgrading with Read-Only and Offline Tablespaces

Oracle Database 11g Release 1 (11.1) and later releases 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.

Upgrading Using Standby Databases

To upgrade the Oracle Database software when standby databases are present in an Oracle Data Guard configuration, see 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.

During a rolling upgrade, you can run different releases of Oracle Database software on the primary and standby databases while you upgrade them, one at a time, incurring minimal downtime on the primary database by using either of the following methods:

• SQL Apply and logical standby databases

  Starting with Oracle Database 10g Release 1 (10.1.0.3), you can use Oracle Data Guard SQL Apply on a logical standby database to perform a rolling upgrade to the new Oracle Database 11g release. For example, you can upgrade the Oracle Database software from patchset release 10.1.0.n to the next database 10.1.0.(n+1) patchset release, or upgrade Oracle Database 10g Release 1 (10.1) to Oracle Database 11g Release 2 (11.2).

• Use of physical standby database for rolling upgrades

  A physical standby database can now take advantage of the rolling upgrade feature provided by a logical standby. Through the use of the new KEEP IDENTITY clause option to the SQL ALTER DATABASE RECOVER TO LOGICAL STANDBY statement, a physical standby database can be temporarily converted into a logical standby database for the rolling upgrade, and then reverted back to the original configuration of a primary database and a physical standby database when the upgrade is done.

Upgrading Your Operating System

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

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 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 the new Oracle Database 11g release 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 My Oracle Support (http://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 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 SYSAUX 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 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.

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.

Upgrading Databases That Use 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.

Pre-Upgrade Information Tool Miscellaneous Warnings

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 the new Oracle Database 11g release:

• Deprecated CONNECT Role

• Access Control to Network Utility Packages

• Database Links with Passwords

• TIMESTAMP WITH TIME ZONE Data Type

• Optimizer Statistics

• Invalid Objects

• Save Oracle Enterprise Manager Database Control Data

• Complete Materialized View Refreshes

• Ensure No Files Need Media Recovery

• Ensure No Files Are in Backup Mode

• Resolve Outstanding Distributed Transactions

• Sync Standby Database with the Primary Database

• Purging the Database Recycle Bin

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');

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;

SELECT * FROM v$recover_file;

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

SELECT * FROM dba_2pc_pending;

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

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

PURGE DBA_RECYCLEBIN

Using the DBUA Graphical User Interface

If you installed Oracle Database 11g Release 2 (11.2) 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 returns an error asking you to disable Database Vault prior to upgrade. See "Upgrading Databases That Use Oracle Database Vault".

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

During the upgrade, DBUA automatically modifies or creates new required tablespaces and invokes the appropriate upgrade scripts, as follows:

• If the datafiles are auto extensible and have enough disk space to grow, then DBUA continues with the upgrade.

• If the datafiles are not autoextensible, then DBUA prompts the user and makes the files auto extensible.

• If the tablespaces are auto extensible and the MAXSIZE initialization parameter needs adjustment, then DBUA prompts for the same and adjusts the MAXSIZE parameter.

• If there is not enough disk space to grow, then DBUA prompts you to create space (by adding more datafiles). DBUA does not add new datafiles because DBUA cannot determine where to create the files.

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 2 (11.2) 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 displays.

   
   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 Select Database page appears, listing the databases available for upgrade.

   
   Description of the illustration dbua02.jpg
   
   

3. Select the database you want to upgrade to Oracle Database 11g Release 2 (11.2).

   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 Upgrade Options screen displays.

   
   Description of the illustration dbua03.jpg
   
   

4. The Upgrade Options screens allows you to set the following options:

   Recompile invalid objects at the end of upgrade

   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 2 (11.2), 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.

   Degree of Parallelism

   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.

   Backup Database

   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.

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

   
   Description of the illustration dbua04.jpg
   
   

5. 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 Grid Infrastructure Installation Guide for information about installing and configuring ASM

     • "Managing ASM Instances With ASM Configuration Assistant" in the Oracle Database Storage Administrator's Guide

     Click Next.

     The Recovery and Diagnostic Locations screen displays, where you can designate a Fast Recovery Area for your database.

     
     Description of the illustration dbua05.jpg
     
     

   Fast Recovery Area is an Oracle managed disk location used for storing backup and recovery related files. Oracle strongly recommends configuring a Fast Recovery Area, because it significantly enhances speed, reliability, and manageability of the database recovery process. The location of the Fast Recovery Area 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 5, or if an Oracle Express Edition database is being upgraded to Oracle Enterprise Edition, then you must configure a Fast Recovery Area. If a Fast Recovery Area is already configured, then the current settings are retained but the screen will display to allow you to override these values.

6. Do one of the following:

   • Accept the default Fast Recovery Area location

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

   • Click Browse and select a different Fast Recovery Area location

   Do one of the following:

   • Accept the default size for the Fast Recovery Area

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

   • Click the up or down arrows to adjust the Fast Recovery Area size

   See Also:

   Oracle Database 2 Day DBA for more information about configuring the Fast Recovery Area

   Click Next.

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

   
   Description of the illustration dbua06.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.

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

   • Register with Grid Control for centralized 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 Oracle Enterprise Manager Grid Control.

   • Configure Database Control for local 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 Alert 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 Recipient 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 Disk Backup to Recovery Area

     • Enter a start time in the Backup Start Time field

     • Enter host credentials in the OS Username and Password fields

     If you select the Enable Daily Disk Backup to Recovery Area 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 displays.

   
   Description of the illustration dbua07.jpg
   
   

8. 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 2 (11.2) 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.

   The Summary screen appears.

   
   Description of the illustration dbua08.jpg
   
   

9. 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 displays and DBUA begins the upgrade.

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

11. 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 2 (11.2) 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-3 describes the various options and their parameters that are supported by DBUA.

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';
   }
   

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 2 (11.2) 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 2 (11.2).

      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 2 (11.2) 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 2 (11.2) by completing the following steps:

   1. Remove obsolete initialization parameters and adjust deprecated initialization parameters. Certain parameters are obsolete in Oracle Database 11g Release 2 (11.2), while other parameters have become deprecated. Remove all obsolete parameters from any parameter file that starts an Oracle Database 11g Release 2 (11.2) instance. Obsolete parameters might cause errors in Oracle Database 11g Release 2 (11.2). 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 2 (11.2). 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 values indicated by 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 1. c. Then, edit the file specified in the IFILE entry in the same way that you edited the parameter file in Step a 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 2 (11.2) 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 2 (11.2) 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 2 (11.2) 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 2 (11.2) 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.2.0\DB, then enter the following command:

      C:\> ORADIM -NEW -SID ORCL -SYSPWD TWxy5791 -MAXUSERS 10
      -STARTMODE AUTO -PFILE C:\ORACLE\PRODUCT\11.2.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 2 (11.2) Oracle home

   3. The following environment variables point to the Oracle Database 11g Release 2 (11.2) 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 2 (11.2) 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 command:

   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 2 (11.2) 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 the new Oracle Database 11g release, 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-4 can be used to determine an optimal size for the SYSAUX tablespace.

   Table 3-4 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
    

    Note:

    If you did not run the Pre-Upgrade Information Tool, the catupgrd.sql script will terminate with one of the following errors:

    ORA-00942: table or view does not exist
    ORA-00904: "TZ_VERSION": invalid identifier
    ORA-01722: invalid number 
    

    If you receive any of these errors, issue the SHUTDOWN ABORT statement, revert to the original Oracle home directory, and run the Pre-Upgrade Information Tool (utlu112i.sql) as described in "Run the Pre-Upgrade Information Tool".

    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 2 (11.2) 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 2 (11.2) 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 utlu112s.sql to display the results of the upgrade:

    SQL> @utlu112s.sql
    

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

    Oracle Database 11.2 Post-Upgrade Status Tool         03-18-2007 22:48:55
    .
    Component                                Status         Version  HH:MM:SS
    .
    Oracle Server
    .                                         VALID      11.2.0.0.1  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 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;
    

    Note:

    If the pre-upgrade information tool detected INVALID objects and populated the registry$sys_inv_objs and registry$nonsys_inv_objs tables, then execute ORACLE_HOME/rdbms/admin/utluiobj.sql to display only those objects which are newly invalid as a result of the upgrade process. The utluiobj.sql script will only display objects that are now INVALID but which were VALID prior to the upgrade.

16. Exit SQL*Plus.

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

Troubleshoot the Upgrade

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

• Resource Limits

• Manual Workaround for ORA-01408

• Running the DBMS_DST Package After Upgrade Can Result in ORA-01822

• Component Status

• Rerunning the Upgrade

SQL> create index system.repcat$_audit_column_f2_idx on
2  system.repcat$_audit_column(base_sname,base_oname,base_conflict_type_id,
3  base_reference_name)
4  /
system.repcat$_audit_column(base_sname,base_oname,base_conflict_type_id,
                          *ERROR at line 2:
ORA-01408: such column list already indexed

ERROR at line 1:
@  ORA-01882: timezone region not found
@  ORA-06512: at "SYS.DBMS_DST", line 113
@  ORA-06512: at "SYS.DBMS_DST", line 1101
@  ORA-06512: at line 1

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 Manually

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

• Reconfigure Oracle Cluster Synchronization Services

• Prepare the New ASM Oracle Home

• Upgrade ASM

• Troubleshoot the ASM Upgrade

• Cancel the ASM Upgrade

localconfig reset [destination_Oracle_home]

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