Skip Headers

Oracle® Database Upgrade Guide
10g Release 1 (10.1)

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

Go to previous page
Previous
Go to next page
Next
View PDF

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, which were outlined in "Overview of the Database Upgrade Process".

This chapter covers the following topics:


See Also:

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


Note:

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 Real Application Clusters Installation and Configuration Guide for changes in Real Application Clusters.


Note:

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:

http://education.oracle.com/


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

7.3.4

8.0.3

8.0.4

8.0.5

Direct upgrade is not supported. Complete the following steps to upgrade to the new Oracle Database 10g release:
  1. Upgrade to an intermediate Oracle Database release using the instructions in the intermediate release' s documentation.

  2. Upgrade the intermediate release database to the new Oracle Database 10g release using the instructions in Chapter 3, " Upgrading a Database to the New Oracle Database 10g Release".

8.0.6 Direct upgrade is supported. Upgrade to the new Oracle Database 10g release using the instructions in Chapter 3, " Upgrading a Database to the New Oracle Database 10g Release".
8.1.5

8.1.6

Direct upgrade is not supported. Complete the following steps to upgrade to the new Oracle Database 10g release:
  1. Upgrade to an intermediate Oracle Database release using the instructions in the intermediate release's documentation.

  2. Upgrade the intermediate release database to the new Oracle Database 10g release using the instructions in Chapter 3, " Upgrading a Database to the New Oracle Database 10g Release".

8.1.7

9.0.1

9.2

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

If a direct upgrade is not supported from the release number of your database, then you must first upgrade your database to an intermediate Oracle release. The database can then be upgraded from this intermediate release to the new Oracle Database 10g release.


Note:

Depending on your current release, you may need to upgrade through multiple intermediate releases in order to upgrade to the new Oracle Database 10g release.

For example, if your current release is release 8.1.6, then you will need to 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.

Choose an Upgrade Method

Choose one of the upgrade methods outlined in "Overview of the Database Upgrade Process" to upgrade your database to the new Oracle Database 10g release. The following sections describe each of the upgrade methods in detail.

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 Database Upgrade Assistant automates the upgrade process by performing all of the tasks normally performed manually. The Database Upgrade Assistant makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations.

For example, the Database Upgrade Assistant recommends sizing information for the new SYSAUX tablespace, which is required in Oracle Database 10g.

Before the Upgrade

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

  • It checks for any invalid user accounts or roles

  • It checks for any invalid datatypes

  • It checks for any desupported character sets

  • It checks for adequate resources, including rollback segments, tablespaces, and free disk space

  • It optionally backs up all necessary files

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

During the Upgrade

The Database Upgrade Assistant automatically modifies or creates new required tablespaces, invokes the appropriate upgrade scripts, archives the redo logs, and disables archiving during the upgrade phase.

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

Real Application Clusters Support

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

Support for Silent Mode

The Database Upgrade Assistant supports a silent mode of operation where no user interface is presented to the user. Silent mode allows you to use a single command for the upgrade.

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. The Database Upgrade Assistant performs all necessary pre-upgrade and upgrade steps.

Before the Upgrade

When manually upgrading a database, you must 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. Before starting up the new Oracle Database 10g release, make the necessary adjustments to the database.

  • Perform a backup of the database.

  • Add free space to any tablespaces in the database that require additional space, and drop and re-create any redo log files whose size is insufficient for the upgrade.

  • Adjust the parameter file for the upgrade, removing obsolete initialization parameters and adjusting initialization parameters that might cause upgrade problems.

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

After the Upgrade

View the status of the upgrade using the Post-Upgrade Status Tool. The Upgrade Status Tool is a SQL script that ships with the new Oracle Database 10g release, and must be run in the environment of the new Oracle Database 10g release.

Export/Import

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 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 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 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 copy data from one database table to another 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 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.

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.

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

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

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 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 Oracle Database release and evaluate your system's performance as described in Oracle Real Application Clusters Deployment and Performance 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 rollback 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.

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.


Caution:

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