Skip Headers

Oracle9i Database Migration
Release 2 (9.2)

Part Number A96530-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Preparing to Upgrade

This chapter covers the steps that must be completed before you upgrade a production database. This chapter covers in detail Steps 1 through 3 of the upgrade process, which were outlined in "Overview of Database Migration".

This chapter covers the following topics:

Prepare to Upgrade

Complete the following tasks to prepare to upgrade:

Become Familiar with the Features of the New Release

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


Oracle9i training classes are an excellent way to learn how to take full advantage of the functionality available with Oracle9i. Connect to the following Web page for more information:

Determine Your Upgrade Path to the New Release

The path that you must take to upgrade to the new Oracle9i release depends on the release of your database. Table 2-1 contains the required upgrade path for each release of Oracle. 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. Complete the following steps to upgrade to the new release:

  1. Upgrade to release 7.3.4 using the instructions in release 7.3 of Oracle7 Server Migration and in the release 7.3.4 README.
  2. Upgrade the release 7.3.4 database to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release" and Appendix D, "Upgrading an Oracle7 Database Using the MIG Utility".


Direct upgrade is supported. Upgrade to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release" and Appendix D, "Upgrading an Oracle7 Database Using the MIG Utility".




Direct upgrade is not supported. Complete the following steps to upgrade to the new release:

  1. Upgrade to release 8.0.6 using the instructions in the release 8.0.6 READMEMIG.doc file.
  2. Upgrade the release 8.0.6 database to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release".


Direct upgrade is supported. Upgrade to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release".



Direct upgrade is not supported. Complete the following steps to upgrade to the new release:

  1. Upgrade to release 8.1.7 using the instructions in Oracle8i Migration.
  2. Upgrade the release 8.1.7 database to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release".



Direct upgrade is supported. Upgrade to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release".

If the release number of your database is not supported, then you must first upgrade your database to a supported Oracle release before upgrading to the new Oracle9i release.

Choose an Upgrade Method

Choose one of the following methods to upgrade your database to the new Oracle9i release:

The following sections describe each of the upgrade methods in detail, and discuss advantages and disadvantages of each method.

Database Upgrade Assistant

The Database Upgrade Assistant is a graphical user interface (GUI) tool that provides a simplified upgrade of a database to the new Oracle9i release. Online Help is available to assist you in its use.

The Database Upgrade Assistant performs all of the following pre-upgrade steps:

During the upgrade process, the Database Upgrade Assistant runs all necessary SQL scripts and utilities, removes obsolete initialization parameters and adjusts deprecated initialization parameters, and creates detailed logs for all SQL scripts and utilities executed during the upgrade.

When the upgrade is complete, the Database Upgrade Assistant provides a results dialog, describing all the details of the upgrade.

Starting with release 9.2, the Database Upgrade Assistant supports the upgrading of cluster databases.

Advantages of Using the Database Upgrade Assistant

The following are some advantages of using the Database Upgrade Assistant:

The Database Upgrade Assistant performs several steps to accomplish the upgrade. It filters out all expected errors generated by the upgrade scripts.

If an unexpected error occurs during the upgrade, then the Database Upgrade Assistant gives you the option of skipping the current step and moving on to the next step of the upgrade. After the upgrade is complete, you can fix the cause of any errors and restart the Database Upgrade Assistant. The Database Upgrade Assistant resumes the upgrade by completing any steps that were skipped.

For example, if an unexpected error occurs during the upgrade of Oracle Spatial, then you can skip the Oracle Spatial upgrade and move on to the next component's upgrade. After the Database Upgrade Assistant has finished upgrading all components, you can restart the Database Upgrade Assistant to upgrade Oracle Spatial.

Manual Upgrade

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

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

Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps.

While a manual upgrade gives you finer control over the upgrade process, it is susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order. The Database Upgrade Assistant performs all necessary pre-upgrade and upgrade steps.


Unlike the Database Upgrade Assistant or a manual upgrade, the Export/Import utilities physically copy data from your current database to a new database. The current database's Export utility copies specified parts of the database into an export dump file. Then, the Import utility of the new Oracle9i release loads the exported data into a new Oracle9i database. However, the new Oracle9i database must already exist before the export dump file can be copied into it.

When importing data from an earlier release, the Oracle9i 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 Oracle9i 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 Oracle9i 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:

Time Requirements for Export/Import

Upgrading an entire database by using Export/Import can take a long time, especially compared to using the Database Upgrade Assistant 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 Oracle9i 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 Oracle database using database links. For example, you can copy data from one database table to another database table with the SQL*Plus COPY command, or 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 thus a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables.

Choose an Oracle Home Directory for the New Release

You must choose an Oracle home directory for the new Oracle9i release that is separate from the Oracle home directory of your current release. You cannot install the new Oracle9i software into the same Oracle home directory as your current release.

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

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:

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

See Also:

Oracle7 Server Administrator's Guide for Oracle7 databases and Oracle9i User-Managed Backup and Recovery Guide for Oracle9i databases

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 Oracle9i database, whether you use the Database Upgrade Assistant, 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 Oracle9i 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:

Performance Testing

Performance testing of the new Oracle9i database compares the performance of various SQL statements in the new Oracle9i 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 kernel.

For example, if you are using Oracle9i Real Application Clusters, and you want to measure the performance gains realized from using cache fusion when you upgrade to the new Oracle9i release, then make sure you record your system's statistics before upgrading. For cache fusion, record the statistics from the V$SYSSTAT, V$LOCK_ACTIVITY, and V$LOCK_CLASS_PING 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 timeframe 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 Oracle9i release and evaluate your system's performance as described in Oracle9i Real Application Clusters Deployment and Performance.

See Also:

Oracle9i Database Performance Tuning Guide and Reference 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 Corporation 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:

Collecting this information will help you compare the current database with the new Oracle9i 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 Oracle9i database with the execution plans of the current database. If there is a difference, then execute the statement on the new Oracle9i database and compare the performance with the performance of the statement executed on the current database.

See Also:

Oracle9i Database Performance Tuning Guide and Reference for more information about EXPLAIN PLAN.

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:

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 Oracle9i 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 Oracle9i release. Compare the results, noting anomalies. Repeat the test upgrade as many times as necessary.

Test the newly upgraded Oracle9i test database with existing applications to verify that they operate properly with a new Oracle9i database. You also might test enhanced functionality by adding features that use the available Oracle9i 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 Oracle9i