Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation
• Conventions

1 Introduction

• Overview of the Database Upgrade Process
• 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)
• Converting Databases to 64-bit Oracle Database Software
• Rolling Upgrades
• Moving From the Standard Edition to the Enterprise Edition

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
      • Support for Real Application Clusters
      • Support for Automatic Storage Management
    • Manual Upgrade
      • Before the Upgrade
      • After the Upgrade
    • Export/Import
      • Export/Import Effects on Upgraded Databases
      • Export/Import Benefits
      • Time Requirements for Export/Import
    • Data Copying
  • Choose an Oracle Home Directory for the New Oracle Database 10g Release
  • 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
  • Prepare a Backup Strategy
• Test the Upgrade Process
• Test the Upgraded Test Database

3 Upgrading to the New Oracle Database 10g Release

• System Considerations and Requirements
  • Upgrading a Cluster Database
  • Upgrading With Read-Only and Offline Tablespaces
  • Upgrading Standby Databases
    • Upgrading Databases in an Oracle Data Guard Configuration
    • Upgrading Databases in a Broker Configuration
    • Using SQL Apply to Perform a Rolling Upgrade of Oracle Databases
  • Upgrading Your Operating System
  • Migrating Data to a Different Operating System
• Install the Release 10.2 Oracle Software
• Install the Latest Available Patch Set Release and Any Required Patches
• Run the Pre-Upgrade Information Tool
  • Using the Pre-Upgrade Information Tool
    • Database
    • Logfiles
    • Tablespaces
    • Update Parameters
    • Deprecated Parameters
    • Obsolete Parameters
    • Components
    • Miscellaneous Warnings
    • SYSAUX Tablespace
  • Issues Requiring Further Analysis Prior to Upgrading
    • Deprecated CONNECT Role
    • Database Links With Passwords
    • TIMESTAMP WITH TIMEZONE Datatype
    • Release 8.1.7 National Character Set
    • Optimizer Statistics
• Run the Oracle Net Configuration Assistant
• Upgrade the Database Using the Database Upgrade Assistant
  • Using the Database Upgrade Assistant Graphical User Interface
  • Using the Database Upgrade Assistant in Silent Mode
• Upgrade the Database Manually
  • Back Up the Database
  • Prepare the New Oracle Home
  • Upgrade the Database
  • Troubleshoot the Upgrade
    • SYSTEM and SYSAUX Tablespace
    • Shared Memory
    • Public Rollback Segment
  • Abandon the Upgrade

4 After Upgrading a Database

• Tasks to Complete After All Upgrades
  • Back Up the Database
  • Update Environment Variables After the Upgrade (UNIX Systems Only)
  • Set Threshold Values for Tablespace Alerts
  • Migrate Tables from the LONG Datatype to the LOB Datatype
  • Upgrade the TIMESTAMP Data
  • Use the Latest Time Zone File for Clients
  • Upgrade the Recovery Catalog
  • Upgrade Statistics Tables Created by the DBMS_STATS Package
  • Upgrade Externally Authenticated SSL Users
  • Install Supplied Knowledge Bases
  • Upgrade Change Data Capture
  • Configure Secure HTTP
  • Provide Anonymous Access to XML DB Repository Data via HTTP
  • Update Your HTML DB Configuration
  • Add New Features as Appropriate
  • Develop New Administrative Procedures as Needed
• Tasks to Complete Only After Manual Upgrades
  • Change Passwords for Oracle-Supplied Accounts
  • Migrate Your Initialization Parameter File to a Server Parameter File
  • Upgrade Oracle Text
  • Upgrade the Oracle Cluster Registry (OCR) Configuration
  • Adjust the Initialization Parameter File for the New Release
  • Install and Configure Enterprise Manager Database Control
• Tasks to Complete Only After Upgrading a Release 8.1.7 Database
  • Upgrade User NCHAR Columns
  • Migrate Your Server Manager Line Mode Scripts to SQL*Plus

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
• What Is Interoperability?
• Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.2
  • SQL
  • CONNECT Role
  • Timezone Files
• Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.1
  • SQL Optimizer
    • Rule-Based Optimizer Desupported
    • Optimizer Statistics
    • COMPUTE STATISTICS Clause of CREATE INDEX
    • SKIP_UNUSABLE_INDEXES
  • SQL
  • Invalid Synonyms After an Upgrade
  • Manageability
  • Transaction and Space
  • Recovery and Data Guard
  • RMAN
  • CREATE DATABASE
  • Real Application Clusters
  • Materialized Views
  • Change Data Capture
  • Change in the Default Archival Processing to Remote Archive Destinations
  • Limitations on NCHAR Datatypes
  • PL/SQL Native Compilation
• 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
  • Oracle OLAP
  • 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
  • Datatypes
    • Datetime and Interval Datatypes
    • Database Character Sets
    • AL24UTFFSS Character Set Desupported
  • User-Defined Datatypes
    • Subtypes and Non-Final Types
      • Release 8.1 Clients Accessing a Release 9.0.1 or Higher Server
      • Release 9.0.1 and Higher Clients Accessing a Release 8.1 Server
  • Oracle Replication
    • CHAR Column Length Semantics
    • NCHAR or NVARCHAR2 Columns

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
      • Major Database Release Upgrade
      • Database Maintenance Release Upgrade
    • Possible Client/Server Configurations
      • Different Computers
      • Different Oracle Home Directories on the Same Computer
      • Same Oracle Home
  • Compatibility Rules for Applications When Upgrading Client/Server Software
    • Upgrading the Oracle Database Server Software
      • If You Do Not Change the Client Environment, Then You Do Not Need to Relink.
      • Applications Can Run Against Newer or Older Oracle Database Server Releases
    • Upgrading the Oracle Database Client Software
      • Applications Can Be Linked with Newer Libraries
      • Statically-Linked Applications Always Need to be Relinked
      • Dynamically-Linked Applications Need To Be Relinked When Upgrading to a Major Release
  • Upgrading Options for Your Precompiler and OCI Applications
    • Option 1: Leave the Application Unchanged
    • Option 2: Precompile or Compile the Application Using the New Software
    • Option 3: Change the Application Code to Use New Oracle Database 10g Features
      • Changing Precompiler Applications
      • Changing OCI Applications
• Upgrading SQL*Plus Scripts and PL/SQL
• 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
• Perform Post-Downgrade Tasks

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.2
    • 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.2
    • Initialization Parameters Obsolete in Release 10.1
    • Initialization Parameters Obsolete in Release 9.2
    • Initialization Parameters Obsolete in Release 9.0.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
  • New Default Value for LOG_CHECKPOINT_TIMEOUT
  • The O7_DICTIONARY_ACCESSIBILITY Parameter
• 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
  • 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 10.2
    • Static Data Dictionary Views with Dropped Columns in Release 9.0.1
• 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 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 Dropped Columns
    • Dynamic Performance Views with Dropped Columns in Release 9.2
    • Dynamic Performance Views with Dropped Columns in Release 9.0.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
  • 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
  • Comments
    • REMARK Command (or REM)
    • SQL Comment Delimiters, /* ... */
    • ANSI/ISO Comments, --
    • Server Manager Pound (#) Comments
  • Blank Lines
  • The Hyphen Continuation Character
    • Hyphens Used as Dividing Lines
    • Hyphens Used as Minus Signs
  • Ampersands
    • Using the SET ESCAPE Command
    • Using the SET DEFINE OFF Command
  • CREATE TYPE and CREATE LIBRARY Commands
  • COMMIT Command

C Gathering Optimizer Statistics

• Collecting Statistics for System Component Schemas
• Creating a Statistics Table

D Using the Database Upgrade Assistant

Index