Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

1 Introduction

Overview of the Database Upgrade Process

Role of the Database Administrator During the Upgrade

Role of the Application Developer During the Upgrade

Oracle Release Numbers

Running Multiple Oracle Releases

Install Databases in Multiple Oracle Homes on the Same Computer

Install Databases in Multiple Oracle Homes on Separate Computers

Upgrade a Database to the Current Release

Upgrade Clients to the Current Release

Using Optimal Flexible Architecture (OFA)

Changing Word Size

Rolling Upgrades

Deinstalling Options

2 Preparing to Upgrade

Prepare to Upgrade

Become Familiar with the Features of the New Oracle Database 10g Release

Determine the Upgrade Path to the New Oracle Database 10g Release

Choose an Upgrade Method

Database Upgrade Assistant

Choose an Oracle Home Directory for the New Oracle Database 10g Release

Prepare a Backup Strategy

Develop a Testing Plan

Upgrade Testing

Minimal Testing

Functional Testing

Integration Testing

Performance Testing

Volume and Load Stress Testing

Specific Pre-Upgrade and Post-Upgrade Tests

Test the Upgrade Process

Test the Upgraded Test Database

3 Upgrading a Database to the New Oracle Database 10g Release

System Considerations and Requirements

Upgrading a Cluster Database

Gather Optimizer Statistics Before the Upgrade

Upgrading Your Operating System

Migrating Data to a Different Operating System

Install the Release 10.1 Oracle Software

Upgrade the Database Using the Database Upgrade Assistant

Starting the Database Upgrade Assistant

Database Upgrade Assistant Command Line Options

Upgrade the Database

Using the Database Upgrade Assistant in Silent Mode

Upgrade the Database Manually

Analyze the Database to be Upgraded

Update Parameters

Deprecated Parameters

Obsolete Parameters

SYSAUX Tablespace

Backup the Database

Upgrade the Database

Troubleshooting the Upgrade

SYSTEM Tablespace

Public Rollback Segment

Abandoning the Upgrade

4 After Upgrading a Database

Tasks to Complete After Upgrading Your Database

Back Up the Database

Change Passwords for Oracle-Supplied Accounts

Upgrading from the Standard Edition to the Enterprise Edition

Upgrading and Tablespace Alerts

Migrate Your Oracle Managed Files

Migrate Your Initialization Parameter File to a Server Parameter File

Migrate Tables from LONGs to LOBs

Modify Your listener.ora File

Upgrade Your Standby Database

Prepare to Upgrade

Upgrade the Production Site

Upgrade or Downgrade the Oracle Data Guard Broker

Upgrading Oracle Text

Supplied Knowledge Bases

Copy Files from the Previous ORACLE_HOME to the New ORACLE_HOME

Add New Features as Appropriate

Develop New Administrative Procedures as Needed

Adjust Your Parameter File for the New Release

Tasks to Complete Only After Upgrading a Release 8.1.7 or Lower Database

Upgrade User NCHAR Columns

Migrate Your Server Manager Line Mode Scripts to SQL*Plus

Tasks to Complete Only After Upgrading a Release 8.0.6 Database

Avoid Problems with Parallel Execution

Normalize Filenames on Windows Operating Systems

Rebuild Unusable Function-Based Indexes

Upgrade Materialized Views

Upgrade Your Queue Tables

Upgrade the Recovery Catalog

Upgrade Statistics Tables Created by the DBMS_STATS Package

Test the Database and Compare Results

Tune the Upgraded Database

Changing the Word Size of Your Current Release

5 Compatibility and Interoperability

What Is Compatibility?

The COMPATIBLE Initialization Parameter

Downgrading and Compatibility

How the COMPATIBLE Initialization Parameter Operates

Compatibility Level

Checking the Current Value of the COMPATIBLE Initialization Parameter

When to Set the COMPATIBLE Initialization Parameter

Setting the COMPATIBLE Initialization Parameter

Raising the COMPATIBLE Initialization Parameter

What Is Interoperability?

Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.1

Rule-Based Optimizer Desupported

Optimizer Statistics

COMPUTE STATISTICS Clause of CREATE INDEX

SKIP_UNUSABLE_INDEXES

Invalid Synonyms After an Upgrade

Transaction and Space

Recovery and Data Guard

CREATE DATABASE

Real Application Clusters

Materialized Views

Change Data Capture

Change in the Default Archival Processing to Remote Archive Destinations

Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.2

Locally Managed SYSTEM Tablespace

New AnyData DAtatypes

Dictionary Managed Tablespaces

Change in Compatibility for Automatic Segment-Space Managed Tablespaces

Compatibility and Object Types

Oracle Managed Files

Log Format Change with Parallel Redo

Oracle Dynamic Services

Oracle Syndication Server

Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.0.1

The STARTUP Command

Change in Default Parameter File Selection

Tablespaces and Datafiles

CREATE TABLESPACE: New Behavior

Default Temporary Tablespaces

Undo Tablespaces

Datetime and Interval Datatypes

Database Character Sets

AL24UTFFSS Character Set Desupported

User-Defined Datatypes

Subtypes and Non-Final Types

Oracle Replication

CHAR Column Length Semantics

NCHAR or NVARCHAR2 Columns

Compatibility and Interoperability Issues Introduced in Oracle8i Release 8.1

General Compatibility and Interoperability Issues for Applications

OCI Applications

Precompiler Applications

PL/SQL Applications

Tablespaces and Datafiles

Transportable Tablespace

Data Dictionary

Data Dictionary Protection

Bitmap Index Protection

Large Objects (LOBs)

NCHAR and NLS Environment Variables and Compatibility

User-Defined Datatypes

New Format for User-Defined Datatypes

Varrays Stored as LOBs

Functions GREATEST_LB, LEAST_UB, and TO_LABEL Desupported

SQL Scripts utlchain.sql and utlchn1.sql

SQL Scripts utlexcpt.sql and utlexpt1.sql

Advanced Queuing (AQ)

Interoperability and the Sender's ID Column

Rule Based Subscriptions

Oracle Optimizer

Real Application Clusters

INSTANCES Keyword in PARALLEL Clause

Database Security

Enterprise User Management

Database Backup and Recovery

Recovery Manager

Recovery Manager Commands

Datafile Backups

Standby Database

Fast-Start On-Demand Rollback and Fast-Start Parallel Rollback

Archiving of Redo Logs

Oracle Media Management API and Proxy Copy

Distributed Databases

Materialized Views

Oracle Replication

Heterogeneous Services Agents

Service Naming and Connection Load Balancing

6 Upgrading Your Applications

Overview of Upgrading Applications

Compatibility Issues for Applications

Upgrading Precompiler and OCI Applications

Understanding Software Upgrades and Your Client/Server Configuration

Types of Software Upgrades

Possible Client/Server Configurations

Compatibility Rules for Applications When Upgrading Client/Server Software

Upgrading the Oracle Database Server Software

Upgrading the Oracle Database Client Software

Upgrading Options for Your Precompiler and OCI Applications

Option 1: Leave the Application Unchanged

Option 2: Relink the Application with the New Oracle Database 10g Libraries

Option 3: Precompile or Compile the Application Using the New Software

Option 4: Change the Application Code to Use New Oracle Database 10g Features

Upgrading SQL*Plus Scripts

Upgrading Oracle Forms or Oracle Developer Applications

7 Downgrading a Database Back to the Previous Oracle Database Release

Supported Releases for Downgrading

Check for Incompatibilities

Perform a Full Offline Backup

Downgrade the Database

8 Data Copying Using Export/Import

Export and Import Requirements

Export/Import Usage on Data Incompatible with a Previous Release

Upgrade the Database Using Export/Import

A Initialization Parameter and Data Dictionary Changes

Initialization Parameter Changes

Deprecated Initialization Parameters

Initialization Parameters Deprecated in Release 10.1

Initialization Parameters Deprecated in Release 9.2

Initialization Parameters Deprecated in Release 9.0.1

Obsolete Initialization Parameters

Initialization Parameters Obsolete in Release 10.1

Initialization Parameters Obsolete in Release 9.2

Initialization Parameters Obsolete in Release 9.0.1

Initialization Parameters Obsolete in Release 8.1

Compatibility Issues with Initialization Parameters

Change in Behavior for SESSION_CACHED_CURSORS

New default value for DB_BLOCK_SIZE

OPTIMIZER_MAX_PERMUTATIONS and OPTIMIZER_FEATURES_ENABLE

Change in Behavior for LOG_ARCHIVE_FORMAT

New Default Value for PGA_AGGREGATE_TARGET

Change in Behavior for SHARED_POOL_SIZE

Shared Server Parameters

New Default Value for DISPATCHERS

New Default Value for SHARED_SERVERS

New Default Value for MAX_SHARED_SERVERS

New Default Value for SHARED_SERVER_SESSIONS

New Default Value for CIRCUITS

New Default Value for MAX_DISPATCHERS

New Default Value for DB_BLOCK_CHECKSUM

Maximum Number of Job Queue Processes

SORT_AREA_SIZE and SORT_DIRECT_WRITES Parameters

New Default Value for LOG_CHECKPOINT_TIMEOUT

The O7_DICTIONARY_ACCESSIBILITY Parameter

The DB_DOMAIN Parameter

Parallel Execution Allocated from Large Pool

Retaining Parameter Settings without Modifications

Using PARALLEL_AUTOMATIC_TUNING

Using PARALLEL_AUTOMATIC_TUNING and Setting LARGE_POOL_SIZE

Using PARALLEL_AUTOMATIC_TUNING without Modifying SHARED_POOL_SIZE

Archive Log Destination Parameters

Changing to the New Archive Log Destination Parameters

Changing Back to the Old Archive Log Destination Parameters

Possible Errors During the Transition in Parameters

Static Data Dictionary View Changes

Deprecated Static Data Dictionary Views

Static Data Dictionary Views Deprecated in Release 10.1

Static Data Dictionary Views Deprecated in Release 9.2

Static Data Dictionary Views Deprecated in Release 9.0.1

Static Data Dictionary Views Deprecated in Release 8.1

Obsolete Static Data Dictionary Views

Static Data Dictionary Views Obsolete in Release 10.1

Static Data Dictionary Views with Renamed Columns

Static Data Dictionary Views with Renamed Columns in Release 9.0.1

Static Data Dictionary Views with Dropped Columns

Static Data Dictionary Views with Dropped Columns in Release 9.0.1

Static Data Dictionary Views with Dropped Columns in Release 8.1

Static Data Dictionary Views with Columns That May Return Nulls

Dynamic Performance View Changes

Deprecated Dynamic Performance Views

Dynamic Performance Views Deprecated in Release 10.1

Dynamic Performance Views Deprecated in Release 9.2

Dynamic Performance Views Deprecated in Release 9.0.1

Obsolete Dynamic Performance Views

Dynamic Performance Views Obsolete in Release 10.1

Dynamic Performance Views Obsolete in Release 9.2

Dynamic Performance Views Obsolete in Release 9.0.1

Dynamic Performance Views Obsolete in Release 8.1

Dynamic Performance Views with Renamed Columns

Dynamic Performance Views with Renamed Columns in Release 9.2

Dynamic Performance Views with Renamed Columns in Release 9.0.1

Dynamic Performance Views with Renamed Columns in Release 8.1

Dynamic Performance Views with Dropped Columns

Dynamic Performance Views with Dropped Columns in Release 9.2

Dynamic Performance Views with Dropped Columns in Release 9.0.1

Dynamic Performance Views with Dropped Columns in Release 8.1

B Migrating from Server Manager to SQL*Plus

Startup Differences

Starting Server Manager

Starting SQL*Plus

Starting SQL*Plus with the NOLOG Option

Starting SQL*Plus with Connect Information

Starting SQL*Plus without Options or Connect Information

Commands Introduced in SQL*Plus Release 8.1

Commands Common to Server Manager and SQL*Plus

SQL*Plus Equivalents for Server Manager Commands

Possible Differences in the SET TIMING Command

Server Manager Commands Unavailable in SQL*Plus

Syntax Differences

REMARK Command (or REM)

SQL Comment Delimiters, /* ... */

ANSI/ISO Comments, --

Server Manager Pound (#) Comments

The Hyphen Continuation Character

Hyphens Used as Dividing Lines

Hyphens Used as Minus Signs

Using the SET ESCAPE Command

Using the SET DEFINE OFF Command

CREATE TYPE and CREATE LIBRARY Commands

Index