Upgrading a Database

To upgrade an existing database to the current release of the Oracle Database, use the Database Upgrade Assistant (DBUA).

Overview of the Database Upgrade Assistant

The Database Upgrade Assistant (DBUA) guides you through the upgrade process and configures the database for the new release. The DBUA automates the upgrade process and makes appropriate recommendations for configuration options such as tablespaces and redo logs.

The DBUA provides support for Real Application Clusters (RAC) and Automatic Storage Management (ASM).

Pre-Upgrade Checks

The Database Upgrade Assistant does not begin the upgrade until it completes all of the following pre-upgrade steps:

Checks for any invalid user accounts or roles
Checks for any invalid datatypes or invalid objects
Checks for any desupported character sets
Checks for adequate resources, including rollback segments, tablespaces, and free disk space
Checks for any missing SQL scripts needed for the upgrade
Backs up all necessary files (optional).

Automated Upgrade Tasks

After completing the pre-upgrade steps, the Database Upgrade Assistant automatically performs the following tasks:

Modifies or creates new required tablespaces
Invokes the appropriate upgrade scripts
Archives redo logs
Disables archiving during the upgrade phase

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

Support for Real Application Clusters

The Database Upgrade Assistant is fully compliant with the Real Application Clusters (RAC) environment. In a RAC environment, the DBUA upgrades all the database and configuration files on all nodes in the cluster.

Note:

On Windows operating systems, the DBUA does not support a direct upgrade of Oracle Parallel Server version 8.1.7 databases to Oracle Database 10g with RAC. You must first manually upgrade the Oracle Parallel Server database to Oracle Real Application Clusters Oracle9i release 2 (9.2), and then upgrade it to Oracle Database 10g with Real Application Clusters (RAC) using the DBUA.

Support for Automatic Storage Management

The DBUA supports upgrades of databases that use Automatic Storage Management (ASM). If an ASM instance is detected, you have the choice of updating both the database and ASM or only the ASM instance.

Support for Silent Mode

The Database Upgrade Assistant supports a silent mode of operation in which no user interface is presented to the user. Silent mode enables you to use a single statement for the upgrade.

Oracle Database Version Restrictions

The Database Upgrade Assistant supports the following versions of the Oracle Database for upgrading to Oracle Database 10g:

Oracle Database version 8.0.6 (single instance databases only)
Oracle8i release 8.1.7 (single instance and Real Application Clusters databases)
Oracle9i release 1 (9.0.1) (single instance and Real Application Clusters databases)
Oracle9i release 2 (9.2.0) (single instance and Real Application Cluster databases)

If your database version is not in this list, you need to upgrade first to the closest release listed. You can then upgrade the database to the current release.

Starting the Database Upgrade Assistant

If you install Oracle Database software only and specify that you are upgrading an existing database to the new Oracle release, then the Database Upgrade Assistant (DBUA) is launched automatically at the end of the software install. You can then continue as described in "Upgrading the Database Using the Database Upgrade Assistant".

If you perform a software-only install and do not upgrade your database at that time, then you can do so later by launching the DBUA.

Be aware of the following before you begin using the DBUA:

You must run the Oracle Net Configuration Assistant before running the DBUA.
If the database instance is not running, the DBUA will try to start the instance with the default initialization parameter file. If that fails, you will be prompted to provide the name of the correct initialization parameter file or to start the instance. If the instance is already up and running, the DBUA connects to it.

Note:

If you stop the upgrade, but do not restore the database, then you should not restart the DBUA until you start up the existing database in UPGRADE mode using the 10.2 server. You cannot go back to the original server unless you restore your database.

If you restore your database manually (not using the DBUA), then remove the following file before starting the DBUA:

$10.2OracleHome/cfgtoollogs/dbua/logs/Welcome_<SID>.txt

The presence of this file indicates to the DBUA that this is a re-run operation.

To launch the DBUA on the Windows operating system choose the following menu option:

Start, Programs, Oracle - HOME_NAME, Configuration and Migration Tools, Database Upgrade Assistant

To launch the DBUA on a UNIX or Linux operating system, enter the following at a command prompt:

dbua

This command is typically found in your $ORACLE_HOME/bin directory.

Upgrading the Database Using the Database Upgrade Assistant

Complete the following steps to upgrade a database using the Database Upgrade Assistant (DBUA). If you need help at any screen or want to consult more documentation about the DBUA, then click the Help button to open the online help.

At the Welcome screen of the DBUA make sure the database being upgraded meets the specified conditions. Then, click Next.
If an ASM instance is detected on the system, then the Upgrade Operations page is displayed. From this page you can choose to upgrade only the ASM instance or the database. If you choose to upgrade the database and if the database is using ASM, then the DBUA will prompt you whether to upgrade the ASM instance along with the database. Oracle recommends that you upgrade the database and ASM in separate DBUA sessions, in separate Oracle homes.
At the Selecting a Database Instance screen, select the database you want to upgrade from the Available Databases table. Then, click Next.

You can select only one database at a time. If you are running the DBUA from a user account that does not have SYSDBA privileges, then enter the user name and password credentials to enable SYSDBA privileges for the selected database.

The DBUA analyzes the database, performing pre-upgrade checks and displaying warnings as necessary:
- It checks for any online redo log files of a size less than 4 MB. If such files are found, then the DBUA gives the option to drop or create new online redo log files.
- It checks the initialization parameter file for any obsolete or deprecated initialization parameters.
At the Creating the SYSAUX Tablespace screen, specify the attributes for the SYSAUX tablespace, which is added automatically to all new Oracle Database 10g databases you create. Then, click Next.

See Also:
Oracle Database Administrator's Guide for more information about the SYSAUX tablespace

Many of the attributes of the SYSAUX tablespace are set automatically and cannot be modified. For example, the SYSAUX tablespace is set to use Automatic Segment-Space Management. Nevertheless, you can specify the location of the data file, the default size of the SYSAUX tablespace, and its autoextend attributes.

Note:
If you specify an existing datafile for the SYSAUX tablespace, then you must select Reuse Existing File Name. Otherwise, the DBUA alerts you that the file already exists.
When you upgrade your database to the new Oracle Database 10g release, many of the PL/SQL modules in the database become invalid. By default, the Oracle Database will recompile invalid PL/SQL modules as they are used, but this takes time and can result in poor performance. To eliminate these performance issues, select Recompile invalid objects at the end of upgrade from the Recompile Invalid Objects screen. All the invalid PL/SQL modules will be recompiled immediately after the upgrade is performed.

If you have multiple CPUs, the DBUA automatically adds a Degree of Parallelism menu to the Recompile Invalid Objects screen. Parallel processing reduces the time it takes to recompile all the invalid PL/SQL modules in your database. The DBUA automatically sets the degree of parallelism to one less than the number of available CPUs. You can select a different value from the menu.

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

If the database is in ARCHIVELOG mode, you can change it to NOARCHIVELOG mode.

Then, click Next.
At the Choosing a Database Backup Procedure screen, specify whether or not you want the DBUA to back up your database for you. Then, click Next.

If you choose not to use the DBUA for your backup, then it is assumed that you have already backed up your database using your own backup procedures. It is strongly recommended that you back up your database before the upgrade. If errors occur during the upgrade, then you may need to restore the database from the backup.

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

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

In addition, the DBUA creates a batch file in the specified directory. Use the batch file appropriate to your system to restore the database files:
- Windows: db_name_restore.bat
- UNIX: db_name_restore.sh
At the Management Options screen, you have the option of setting up your database so it can be managed with Enterprise Manager.

Note:
The Management Options screen described in this step is not displayed if an Oracle Database 10g release 10.1 database is already being monitored with Enterprise Manager.
1. The DBUA checks to see if the Oracle Management Agent has been installed on the host computer. If it has, select the Grid Control option and select an Oracle Management Service from the list. When you finish installing the Oracle Database, the database is automatically available as a managed target within the Oracle Enterprise Manager Grid Control.
2. To configure the database so it can be managed with the Oracle Enterprise Manager Database Control, select the Database Control option. This provides Web-based features for monitoring and administering the single-instance or cluster database you are installing.
3. Select Enable Email Notifications to enable 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.
4. Select Enable Daily Backups to use the Oracle-suggested backup strategy to back up your entire database with a minimum amount of configuration. At the start time you enter, Enterprise Manager backs up the database to the Flash Recovery Area that you specify later on the Recovery Configuration screen of the DBUA.
5. Click Next.
At the Database Credentials screen, secure your database with passwords for your Enterprise Manager accounts. You can set a single password for each of the listed Enterprise Manager user accounts, or you can enhance the security of the accounts by providing unique passwords for each user.
At the Recovery Configuration screen, specify a Flash Recovery Area and enable archiving. It is important to configure the database so you can recover your data in the event of a system failure.

The Flash Recovery Area can be used to recover data that would otherwise be lost during a system failure; this location is also used by Enterprise Manager if you have enabled local management and daily backups on the Management Options screen in the DBUA.
At the Network Configuration for the Database screen, there are two tabs:
- The Listeners tab is displayed if you have more than one listener in the Oracle Database 10g release 10.2 Oracle home. Select the listeners for which you would like to register the upgraded database.
- The Directory Service tab is displayed if you have directory services configured in the Oracle Database 10g release 10.2 Oracle home. You can select whether or not to register the upgraded database with the directory service.
At the Summary screen, make sure all of the specifications are correct. The DBUA lists the initialization parameters that will be set for the database during the upgrade. The COMPATIBLE initialization parameter will be set to at least 9.2.0. Click Back to correct any errors, then click Finish.
A Progress dialog appears and the DBUA begins to perform the upgrade.

You may encounter error messages with Ignore and Abort choices:
- Ignore - Ignores the error and proceeds with the upgrade. The ignored errors are logged and shown later in the summary. After the upgrade is complete, you can fix the problem, restart the DBUA, and complete the skipped steps.
- Abort - Stops the upgrade process.The DBUA prompts you to restore the database if the database backup was taken by the DBUA. After the database has been restored, correct the error and restart the DBUA to perform the upgrade again. If you do not restore the database, then the DBUA leaves the database in its current state so that you can proceed with a manual upgrade.
After the upgrade has completed, the following message is displayed:
```
Upgrade has been completed. Click the "OK" button to see the results of the upgrade.
```
Click OK. The Checking Upgrade Results screen appears.
At the Checking Upgrade Results screen, examine the results of the upgrade, manage the passwords in the upgraded database, and, if necessary, restore the original database settings.

The upgrade results summary describes the original and upgraded databases and changes made to the initialization parameters. The upgrade results also include a Step Execution Summary that describes the steps performed during the database upgrade. The summary provides each step name, the log file for the step, and the status. In some cases, you can click the status to display details about the execution step. The Step Execution Summary also includes the directory where the various log files are stored after the upgrade. You can examine any of 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.

The Password Management section of the screen enables you to unlock and set passwords for various users in the newly upgraded database. 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, normally only the following users are unlocked:
- SYS
- SYSTEM
If you have enabled Local Management with Enterprise Manager, then the SYSMAN and DBSNMP accounts are also unlocked. These accounts allow Enterprise Manager to gather monitoring data from the database.

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, change all user passwords immediately after you upgrade your database.

If you are not satisfied with the upgrade results, click Restore. Depending on the method you used to back up your database, the restore operation performs one of the following tasks:
- If you used the 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, restore the backup you created with your own backup utilities.
If you are satisfied with the upgrade results, click Exit to quit the DBUA. The DBUA removes the entry of the upgraded database from the old listener.ora file and reloads the listener of the old database.
At the Changes in Default Behavior screen, the DBUA displays changes in behavior of Oracle Database 10g from that of previous releases. In some cases the default values of some initialization parameters have changed. In other cases some new behavior or requirement has been introduced that may affect current scripts or applications.
See the Oracle Database Upgrade Guide for information on additional tasks that should be completed after upgrading a database.

WARNING:

If you retain the old Oracle software, never start the upgraded database with it. 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 datafiles in that environment to the new Oracle Database environment. See the Oracle Database Administrator's Guide for information about relocating datafiles.