2 Preparing to Upgrade

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

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.

Export/Import

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.

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.

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.

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.