| Oracle9i Database Migration Release 2 (9.2) Part Number A96530-02 |
|
|
View PDF |
| Oracle9i Database Migration Release 2 (9.2) Part Number A96530-02 |
|
|
View PDF |
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:
See Also:
|
Complete the following tasks to prepare to upgrade:
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.
|
Note: 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: |
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.
| Current Release | Upgrade Path |
|---|---|
|
7.3.3 and Lower |
Direct upgrade is not supported. Complete the following steps to upgrade to the new release:
|
|
7.3.4 |
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". |
|
8.0.5 |
Direct upgrade is not supported. Complete the following steps to upgrade to the new release:
|
|
8.0.6 |
Direct upgrade is supported. Upgrade to the new release using the instructions in Chapter 3, "Upgrading a Database to the New Oracle9i Release". |
|
8.1.6 |
Direct upgrade is not supported. Complete the following steps to upgrade to the new release:
|
|
9.0.1 |
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 one of the following methods to upgrade your database to the new Oracle9i release:
The Database Upgrade Assistant can be launched by the Oracle Universal Installer, depending upon the type of installation that you select, and provides a graphical user interface (GUI) that guides you through the upgrade of a database. During installation, you can choose to not use the Database Upgrade Assistant, instead choosing to launch it as a standalone tool at any time in the future to upgrade a database.
A manual upgrade provides a command line upgrade of a database, using SQL scripts and utilities.
Export/Import can copy a subset of the data in a database. Export/Import leaves the database unchanged, and makes a copy of the data.
COPY command or the AS clause of the CREATE TABLE SQL statement.
Data copying can copy a subset of the data in a database. Data copying leaves the database unchanged, and makes a copy of the data.
The following sections describe each of the upgrade methods in detail, and discuss advantages and disadvantages of each method.
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:
SYSTEM tablespace.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.
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.
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:
SYSTEM tablespace exists, and add free space if it does not.You must also remove obsolete initialization parameters from your parameter file, and account for other initialization parameter changes, such as initialization parameters that have been deprecated.
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.
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.
Upgrading using Export/Import offers the following benefits:
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.
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.
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.
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 |
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 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 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 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 examines the interaction of each component of the system. Consider the following factors when you plan your integration 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 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.
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. |
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 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.
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 |
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|