2 Preparing to Upgrade Oracle Database

Before the Upgrade

The following list provides a high-level summary of the manual upgrade steps:

• Analyze the database using the Pre-Upgrade Information Tool. The Pre-Upgrade Information Tool is a SQL script that is supplied with the new Oracle Database 11g release, and DBUA uses this script as part of its upgrade process. Run the script on the database you are upgrading.

  The Pre-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 11g release.

• Prepare the new Oracle home.

  See Also:

  "Choose a Location for the New Oracle Home for Upgrading Oracle Database" for more information

• Perform a backup of the database.

Depending on the release of the database being upgraded, you might be required to perform additional pre-upgrade steps:

• Adjust the parameter file for the upgrade.

• Remove obsolete initialization parameters.

• Adjust initialization parameters that might cause upgrade problems.

  See Also:

  Oracle Database Administrator's Guide and Oracle Real Application Clusters Administration and Deployment Guide

• Set the COMPATIBLE parameter if not already explicitly set.

  See Also:

  "Setting the COMPATIBLE Initialization Parameter" for information about setting the COMPATIBLE initialization parameter

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 11g release. You run the Post-Upgrade Status Tool in the environment of the new release. The Post-Upgrade Status Tool can be run any time after upgrading the 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 11g 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 11g 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 might 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, might cause unexpected performance problems.

Export/Import Benefits

Upgrading using Data Pump Export/Import:

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

• Facilitates side-by-side testing of the old and new versions of Oracle Database because an entirely new database is created.

• Enables the copying of specified database objects or users. Importing only the objects, users, and other items you need is useful for establishing a test environment for the new software on only a subset of the production data. Data Pump Export / Import provides flexible data subsetting capabilities.

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

• Enables the upgraded database to be established on an operating system or hardware platform that is different from that which is supporting the database being upgraded.Network-based Data Pump Import allows the new Oracle database to be directly loaded across the network from the old database being upgraded. Thus, no intervening dump files are required.

Time Requirements for Export/Import

Upgrading an entire database by using Export/Import can take a long time, especially compared to using DBUA or performing a manual upgrade. Therefore, you might be required 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.

Database Replay

Starting with Oracle Database 11g Release 1 (11.1), you can use the new Database Replay feature to perform real-world testing of a database upgrade on your site's production workload before actually upgrading the production database. This feature captures the actual database workload on the production system and replays it on the test system. It also provides analysis and reporting to highlight potential problems—for example, errors encountered, divergence in performance, and so forth. In addition, all the regular Enterprise Manager performance monitoring and reporting tools such as Automatic Database Diagnostic Monitor, Automatic Workload Repository (AWR), and Activity Session History are available to address any problems.

SQL Performance Analyzer

Starting with Oracle Database 11g Release 1 (11.1), you can use the SQL Performance Analyzer to forecast the impact of system changes on a SQL workload. SQL Performance Analyzer enables evaluating the impact of a change such as database upgrade by identifying the SQL statements impacted by the upgrade and measuring their performance divergence. The analysis enables you to assess the overall effect of the upgrade on SQL performance and makes it possible to avoid any negative outcome before users can be impacted.

SQL Plan Management

SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information. SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements, with most of the plan changes resulting in either no performance change or improvement. However, certain plan changes may cause performance regressions.

SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information. If you are performing a database upgrade that installs a new optimizer version, then it can result in plan changes for a small percentage of SQL statements, with most of the plan changes resulting in either no performance change or improvement. However, certain plan changes may cause performance regressions.

With SQL plan management, the optimizer automatically manages execution plans and ensures that only known or verified plans are used. When a new plan is found for a SQL statement, the plan is not used until it has been verified by the database to have comparable or better performance than the current plan. Therefore, if you seed SQL plan management with your current (pre-Oracle Database 11g) execution plan, which is to become the SQL plan baseline for each statement, then the optimizer uses these plans after the upgrade. If the Oracle Database 11g optimizer determines that a different plan is necessary, then the new plan is queued for verification and is not used until it has been confirmed to have comparable or better performance than the current plan.

There are two ways to seed or populate a SQL Management Base (SMB) with execution plans:

• Automatic capture of execution plans (available starting with Oracle Database 11g)

• Bulk load execution plans or preexisting SQL plan baselines

Bulk loading of execution plans or SQL plan baselines is especially useful when upgrading a database from a previous release to Oracle Database 11g. SQL plans that are bulk loaded are automatically accepted and added to existing or new plan histories as SQL plan baselines.

To bulk load the SQL Management Base as part of an upgrade:

• Populate the execution plans for a given SQL Tuning Set (STS), as described in "Bulk Loading a SQL Management Base with a SQL Tuning Set (STS)".

  Or

• Unpack existing SQL plan baselines from a staging table, as described in "Unpacking Existing Oracle Database SQL Plan Baselines from a Staging Table".

Bulk Loading a SQL Management Base with a SQL Tuning Set (STS)

To bulk load the SQL Management Base with an execution plan from an STS 

1. In Oracle Database 10g Release 2 (10.2), create an STS that includes the execution plan for each of the SQL statements.

2. Load the STS into a staging table and export the staging table into a dump file.

3. Import the staging table from a dump file into Oracle Database 11g and unload the STS.

4. Use Oracle Enterprise Manager or DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load the execution plans into the SQL Management Base.

Unpacking Existing Oracle Database SQL Plan Baselines from a Staging Table

Perform the steps in this procedure to test and tune all of your critical SQL queries on an Oracle Database 11g test environment and then move those exact SQL execution plans to your Oracle Database 11g production environment.

To test and tune your critical SQL queries on an Oracle Database 11g test environment 

1. On the Oracle Database 11g test system, after completing all testing and tuning, use the DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE procedure or Enterprise Manager to load all of the execution plans in the cursor cache into the SQL Management Base.

2. Create a staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure.

3. Pack the SQL plan baselines you created in step 1 into the staging table using the DBMS_SPM.PACK_STGTAB_BASELINE function.

4. Export the staging table into a flat file using the Export command or Data Pump.

5. Transfer this flat file to the target system.

6. Import the staging table from the flat file using the Import command or Data Pump.

7. Unpack the SQL plan baselines from the staging table into the SQL Management Base on the target system using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.