2 Preparing to Upgrade

This chapter describes the steps to complete before upgrading a database to the new Oracle Database 10g release. This chapter covers in detail Steps 1 through 3 of the upgrade process that were outlined in "Overview of the Database Upgrade Process".

This chapter covers the following topics:

See Also:

Oracle Database Net Services Administrator's Guide for information about upgrade considerations for Oracle Net Services


Some aspects of upgrading are operating system-specific. See your operating system-specific Oracle documentation for additional information about preparing to upgrade.

Prepare to Upgrade

Complete the following tasks to prepare to upgrade:

Become Familiar with the Features of the New Oracle Database 10g Release

Before you plan the upgrade process, become familiar with the features of the new Oracle Database 10g release. Oracle Database New Features is a good starting point for learning the differences between Oracle Database releases. Also, check specific books in the Oracle Database 10g documentation set to find information about new features for a certain component; for example, see Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for changes in Real Application Clusters.


Oracle Database 10g training classes are an excellent way to learn how to take full advantage of the functionality available with the Oracle Database. Connect to the following web page for more information:


Determine the Upgrade Path to the New Oracle Database 10g Release

The path that you must take to upgrade to the new Oracle Database 10g release depends on the release number of your current database. It may not be possible to upgrade directly from your current version of Oracle Database to the latest version. Depending on your current release, you may need to upgrade through one or more intermediate releases to upgrade to the new Oracle Database 10g release.

For example, if the current database is running release 8.1.6, then first upgrade to release 8.1.7 using the instructions in Oracle8i Migration for release 8.1.7. The release 8.1.7 database can then be upgraded to the new Oracle Database 10g release using the instructions in this book.

Table 2-1 contains the required upgrade path for each release of the Oracle Database. Use the upgrade path and the specified documentation to upgrade your database.

Table 2-1 Upgrade Paths

Current Release Upgrade Path

7.3.3 and lower








Direct upgrade is not supported. Upgrade to an intermediate Oracle Database release before you can upgrade to the new Oracle Database 10g release, as follows:

  • 7.3.3 (or lower) -> 7.3.4 -> -> 10.2

  • 7.3.4 -> -> 10.2

  • 8.0.n -> -> 10.2

  • 8.1.n -> -> 10.2

When upgrading to an intermediate Oracle Database release, follow the instructions in the intermediate release's documentation. Then, upgrade the intermediate release database to the new Oracle Database 10g release using the instructions in Chapter 3, "Upgrading to the New Oracle Database 10g Release".

Direct upgrade from, or higher, or higher, and or higher to the newest Oracle Database 10g release is supported. However, you must first apply the specified minimum patch release indicated in the Current Release column.

To upgrade to the new Oracle Database 10g release, follow the instructions in Chapter 3, "Upgrading to the New Oracle Database 10g Release".

Choose an Upgrade Method

The following sections describe the upgrade methods you can use to upgrade your database to the new Oracle Database 10g release:

Database Upgrade Assistant

The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database 10g release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations.

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

Support for Real Application Clusters

In a Real Application Clusters (RAC) environment, the DBUA upgrades all the database and configuration files on all nodes in the cluster.


On Windows operating systems, DBUA does not support a direct upgrade of Oracle Parallel Server version 8.1.7 databases to Oracle Database 10g with RAC. 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 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.

Manual Upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release.

While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.

Before the Upgrade

When manually upgrading a database, perform the following pre-upgrade steps:

  • Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with the new Oracle Database 10g release, and must be run in the environment of the database being upgraded.

    The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database 10g release.

  • Prepare the new Oracle Home.

  • Perform a backup of the database.

Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).

After the Upgrade

Review the upgrade spool log file and use the Post-Upgrade Status Tool. The Post-Upgrade Status Tool is a SQL script that ships with the new Oracle Database 10g release, and should be run in the environment of the new Oracle Database 10g release.


Unlike the DBUA or a manual upgrade, the Export/Import utilities physically copy data from your current database to a new database. You can use either the Oracle Data Pump Export and Import utilities (available as of Oracle Database 10g) or the original Export and Import utilities to perform a full or partial export from your database, followed by a full or partial import into a new Oracle Database 10g database. Export/Import can copy a subset of the data in a database, leaving the database unchanged.

The current database's Export utility copies specified parts of the database into an export dump file. Then, the Import utility of the new Oracle Database 10g release loads the exported data into a new database. However, the new Oracle Database 10g database must already exist before the export dump file can be copied into it.

When importing data from an earlier release, the Oracle Database 10g Import utility makes appropriate changes to data definitions as it reads earlier releases' export dump files.

The following sections highlight aspects of Export/Import that may help you to decide whether to use Export/Import to upgrade your database.

Export/Import Effects on Upgraded Databases

The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database 10g database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database 10g database.

Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.

Export/Import Benefits

Upgrading using Export/Import offers the following benefits:

  • Defragments the data - you can compress the imported data to improve performance.

  • Restructures the database - you can create new tablespaces or modify existing tables, tablespaces, or partitions to be populated by imported data.

  • Enables the copying of specified database objects or users - you can import only the objects, users, and other items that you wish.

  • Serves as a backup archive - you can use a full database export as an archive of the current database.

Time Requirements for Export/Import

Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.

Data Copying

You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement.

Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.

Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables.

See Also:

CREATE TABLE statement in Oracle Database SQL Reference

Choose an Oracle Home Directory for the New Oracle Database 10g Release

You must choose an Oracle home directory for the new Oracle Database 10g release that is separate from the Oracle home directory of your current release. You cannot install the new Oracle Database software into the same Oracle home directory as your current release, unless you are installing an Oracle 10g patch set release. For a patch set release, you may use the same release 10.2 Oracle home.

Using separate installation directories enables you to keep your existing software installed along with the new Oracle Database software. This method enables you to test the upgrade process on a test database before replacing your production environment entirely.

Develop a Testing Plan

You need a series of carefully designed tests to validate all stages of the upgrade process. Executed rigorously and completed successfully, these tests ensure that the process of upgrading the production database is well understood, predictable, and successful. Perform as much testing as possible before upgrading the production database. Do not underestimate the importance of a test program.

The testing plan must include the following types of tests.

Upgrade Testing

Upgrade testing entails planning and testing the upgrade path from your current database to the new Oracle Database, whether you use the DBUA, perform a manual upgrade, or use Export/Import or other data-copying methods.

Regardless of the upgrade method you choose, you must establish, test, and validate an upgrade plan.

Minimal Testing

Minimal testing entails moving all or part of an application from the current database to the new Oracle Database and running the application without enabling any new database features. Minimal testing is a very limited type of testing that may not reveal potential issues that may appear in a "real-world" production environment. However, minimal testing will immediately reveal any application startup or invocation problems.

Functional Testing

Functional testing is a set of tests in which new and existing functionality of the system are tested after the upgrade. Functional testing includes all database, networking, and application components. The objective of functional testing is to verify that each component of the system functions as it did before upgrading and to verify that new functions are working properly.

Integration Testing

Integration testing examines the interaction of each component of the system. Consider the following factors when you plan your integration testing:

  • Pro*C/C++ applications running against a new Oracle Database instance should be tested to ensure that there are no problems with the new software.

  • Graphical user interfaces should be tested with other components.

  • Subtle changes in the new Oracle Database, such as datatypes, data in the data dictionary (additional rows in the data dictionary, object type changes, and so on) can have an effect all the way up to the front-end application, regardless of whether or not the application is directly connected to a new Oracle Database instance.

  • If the connection between two components involves Net8 or Oracle Net Services, then those connections should also be tested and stress tested.

Performance Testing

Performance testing of the new Oracle Database compares the performance of various SQL statements in the new Oracle Database with the statements' performance in the current database. Before upgrading, you should understand the performance profile of the application under the current database. Specifically, you should understand the calls the application makes to the database server.

For example, if you are using Real Application Clusters, and you want to measure the performance gains realized from using cache fusion when you upgrade to the new Oracle Database 10g release, then make sure you record your system's statistics before upgrading. For cache fusion, record the statistics from the V$SYSSTAT and V$INSTANCE_CACHE_TRANSFER views. Doing so enables you to compare pre-cache fusion and post-cache fusion performance statistics.

For best results, run the SQL scripts utlbstat.sql and utlestat.sql to collect V$SYSSTAT statistics for a specific period. Use a collection time frame that most consistently reflects peak production loads with consistent transaction activity levels. To obtain data from V$LOCK_ACTIVITY and V$LOCK_CLASS_PING, use a SELECT * statement at the beginning and end of the statistics collection period. Repeat this process after cache fusion is running on the new Oracle Database release and evaluate your system's performance as described in Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide.

See Also:

Oracle Database Performance Tuning Guide for information about tuning. To thoroughly understand the application's performance profile under the source database, enable the SQL trace facility and profile with TKPROF.

Volume and Load Stress Testing

Volume and load stress testing tests the entire upgraded database under high volume and loads. Volume describes the amount of data being manipulated. Load describes the level of concurrent demand on the system. The objective of volume and load testing is to emulate how a production system might behave under various volumes and loads.

Volume and load stress testing is crucial, but is commonly overlooked. Oracle has found that customers often do not conduct any kind of volume or load stress testing. Instead, customers often rely on benchmarks that do not characterize business applications. Benchmarks of the application should be conducted to uncover problems relating to functionality, performance, and integration, but they cannot replace volume and load stress testing.

After you upgrade the database, you should test the data to ensure that all data is accessible and that the applications function properly. You should also determine whether any database tuning is necessary. If possible, you should automate these testing procedures.

The testing plan should reflect the work performed at the site. You should test the functionality and performance of all applications on the production databases. Gather performance statistics for both normal and peak usage.

Specific Pre-Upgrade and Post-Upgrade Tests

Include the following tests in your testing plan:

  • Timing tests

  • Data dictionary growth observations

  • Database resource usage observations, such as undo and temporary segment usage

Collecting this information will help you compare the current database with the new Oracle Database.

Use EXPLAIN PLAN on both the previous and new databases to determine the execution plan Oracle follows to execute each SQL statement. Use the INTO clause to save this information in tables.

After upgrading, you can compare the execution plans of the new Oracle Database with the execution plans of the current database. If there is a difference, then execute the statement on the new Oracle Database and compare the performance with the performance of the statement executed on the current database.

See Also:

Oracle Database Performance Tuning Guide for more information about EXPLAIN PLAN.

Prepare a Backup Strategy

The ultimate success of your upgrade depends heavily on the design and execution of an appropriate backup strategy. To develop a backup strategy, consider the following questions:

  • How long can the production database remain inoperable before business consequences become intolerable?

  • What backup strategy should be used to meet your availability requirements?

  • Are backups archived in a safe, offsite location?

  • How quickly can backups be restored (including backups in offsite storage)?

  • Have recovery procedures been tested successfully?

Your backup strategy should answer all of these questions and include procedures for successfully backing up and recovering your database.

See Also:

Oracle Database Backup and Recovery Basics for information on database backups

Test the Upgrade Process

Create a test environment that will not interfere with the current production database. Your test environment will depend on the upgrade method you have chosen:

  • If you plan to use the DBUA or perform a manual upgrade, then create a test version (typically a subset) of the current production database to test the upgrade.

  • If you plan to use Export/Import, then export and import small test pieces of the current production database.

Practice upgrading the database using the test environment. The best upgrade test, if possible, is performed on an exact copy of the database to be upgraded, rather than on a downsized copy or test data.


Do not upgrade the actual production database until after you successfully upgrade a test subset of this database and test it with applications, as described in the next step.

Make sure you upgrade any OCI and precompiler applications that you plan to use with your new Oracle Database. Then, you can test these applications on a sample database before upgrading your current production database. See "Upgrading Precompiler and OCI Applications" for more information.

Test the Upgraded Test Database

Perform the planned tests on the current database and on the test database that you upgraded to the new Oracle Database release. Compare the results, noting anomalies. Repeat the test upgrade as many times as necessary.

Test the newly upgraded Oracle Database test database with existing applications to verify that they operate properly with a new Oracle Database. You also might test enhanced functionality by adding features that use the available Oracle Database functionality. However, first make sure that the applications operate in the same manner as they did in the current database.

See Also:

Chapter 6, "Upgrading Your Applications" for more information on using applications with Oracle Database