Table Of ContentOracle9i
Database Migration
Release 1 (9.0.1)
July 2001
Part No. A90191-02
Oracle9i Database Migration, Release 1 (9.0.1)
Part No. A90191-02
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Author: Tony Morales
Graphic Artist: Valarie Moore
Contributors: NipunAgarwal,RickAnderson,VikasArora,NeerjaBhatt,BillBridge,RaeBurns,Thong
Bui, Ben Chang, Debashish Chatterjee, Lakshminaray Chidambaran, Eugene Chong, George Claborn,
David Colello, Jay Davison, Alan Downing, Sreenivas Gollapudi, Brajesh Goyal, Michael Hartstein,
Jeffrey Hebert, Thuvan Hoang, Wei Huang, Robert Jenkins, Sanjeev Jhala, Christopher Jones, Sanjay
Kaluskar, Dhiraj Kapoor, Vishwanath Karra, Mark Kennedy, Susan Kotsovolos, Viswanathan
Krishnamurthy, Muralidhar Krishnaprasad, Thomas Kurian, Paul Lane, Gordon Larimer, Simon Law,
Jing Liu, Juan Loaiza, Neil Le, J. Bill Lee, Bill Maimone, Raghu Mani, Shailendra Mishra, Ari Mozes,
Kannan Muthukkaruppan, Subramanian Muralidhar, Ravi Murthy, Karuna Muthiah, Miranda Nash,
Mark Niebur, Anil Nori, Peter Ogilvie, Irene Paradisis, Rosanne Park, Elizabeth Pitt, Greg Pongracz,
FrancoPutzolu,N.C.Ramesh,PaulRaveling,AnnRhee,RichardSarwal,AjaySethi,CarolSexton,Helen
Slaterry, James Stamos, Debbie Steiner, Alex Tsukerman, Randy Urbano, Guhan Viswanathan, Steven
Wertheimer, Rick Wessman, Andrew Witkowski, Lik Wong, Aravind Yalamanchi, Qin Yu
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle8i, Oracle9i, PL/SQL, Pro*Ada, Pro*COBOL, Pro*C,
Pro*C/C++, SQL*Plus, and Trusted Oracle are trademarks or registered trademarks of Oracle
Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments.................................................................................................................. xv
Preface......................................................................................................................................................... xvii
Audience............................................................................................................................................. xviii
Organization....................................................................................................................................... xviii
Related Documentation...................................................................................................................... xxi
Conventions......................................................................................................................................... xxii
Documentation Accessibility........................................................................................................... xxiv
1 Introduction
Terminology......................................................................................................................................... 1-2
Versions and Releases.................................................................................................................. 1-2
Migration vs. Upgrading............................................................................................................. 1-3
Source Database and Target Database...................................................................................... 1-3
Running Multiple Versions and Releases of Oracle.................................................................... 1-4
Install Version 7 and Version 8 Databases in Multiple Oracle Homes................................. 1-5
Install Version 7 and Version 8 Databases on Separate Computers..................................... 1-6
Migrate a Version 7 Database to a Version 8 Database........................................................... 1-7
Upgrade a Release 8.0 Database to a Release 9.0.0 Database................................................. 1-8
Upgrade a Release 8.1 Database to the Current Release......................................................... 1-9
Migrate Version 7 Clients to Version 8 Clients...................................................................... 1-10
Using Optimal Flexible Architecture (OFA)................................................................................ 1-11
Running Scripts................................................................................................................................ 1-11
Changing Word-Size........................................................................................................................ 1-12
iii
Rolling Upgrades for Oracle9i Real Application Clusters....................................................... 1-13
Deinstalling Options........................................................................................................................ 1-13
2 Overview of Migration
Overview of Migration Steps........................................................................................................... 2-2
Step 1: Prepare to Migrate........................................................................................................... 2-4
Step 2: Test the Migration Process.............................................................................................. 2-5
Step 3: Test the Migrated Test Database.................................................................................... 2-5
Step 4: Prepare and Preserve the Source Database.................................................................. 2-5
Step 5: Migrate the Production Database.................................................................................. 2-6
Step 6: Tune and Adjust the New Production Database......................................................... 2-6
Role of the Database Administrator During Migration.............................................................. 2-7
Role of the Application Developer During Migration................................................................ 2-7
3 Preparing to Migrate
Prepare to Migrate............................................................................................................................... 3-2
Become Familiar with the Features of the New Database...................................................... 3-2
Choose a Migration Method....................................................................................................... 3-3
Assess System Requirements vs. Resources Available......................................................... 3-16
Choose an Oracle Home Directory for the New Release...................................................... 3-19
Avoid Common Migration Problems...................................................................................... 3-20
Prepare a Backup Strategy......................................................................................................... 3-22
Develop a Testing Plan.............................................................................................................. 3-22
Test the Migration Process.............................................................................................................. 3-26
Test the Migrated Test Database.................................................................................................... 3-26
4 Migrating from Oracle7 Using the Migration Utility
Documentation Roadmap for Using the Migration Utility........................................................ 4-2
Overview of Migration Using the Migration Utility................................................................... 4-3
Outline of the Migration Process................................................................................................ 4-3
Using the Migration Utility......................................................................................................... 4-4
System Considerations and Requirements.................................................................................... 4-5
Space Requirements..................................................................................................................... 4-5
Block Size Considerations............................................................................................................ 4-6
iv
Considerations for SQL*Net....................................................................................................... 4-7
Considerations for Replication Environments......................................................................... 4-7
Migrating a System with Oracle Parallel Server Installed...................................................... 4-7
Considerations for Migrating from ConText to Oracle Text.................................................. 4-8
Migrating to a Different Operating System.............................................................................. 4-8
Character Set Considerations...................................................................................................... 4-8
Distributed Database Considerations........................................................................................ 4-9
Prepare the Oracle7 Source Database for Migration................................................................... 4-9
Prepare for Migration on Windows Platforms....................................................................... 4-13
Install the Release 9.0.1 Oracle Software..................................................................................... 4-15
After Installing Oracle9i on a Windows Platform................................................................. 4-17
Review Migration Utility Command-Line Options................................................................... 4-17
Migrate the Oracle7 Source Database........................................................................................... 4-18
Prepare the Oracle7 Environment for Migration on UNIX Operating Systems............... 4-18
Perform Migration Steps in the Oracle7 Environment......................................................... 4-20
Run the Migration Utility.......................................................................................................... 4-22
Check the Migration Results..................................................................................................... 4-26
Preserve the Oracle7 Source Database..................................................................................... 4-26
Perform Migration Steps in the Oracle9i Environment........................................................ 4-27
Troubleshooting Errors During Migration.................................................................................. 4-35
Abandoning the Migration............................................................................................................. 4-35
5 Migrating from Oracle7 Using the Oracle Data Migration Assistant
Documentation Roadmap for Using the Oracle Data Migration Assistant............................ 5-2
Overview of Migration Using the Oracle Data Migration Assistant....................................... 5-4
Restrictions Related to the Oracle Data Migration Assistant................................................. 5-4
Start with an Oracle7 Database Supported by the Oracle Data Migration Assistant......... 5-4
Downgrading................................................................................................................................ 5-5
System Considerations and Requirements.................................................................................... 5-5
Space Requirements..................................................................................................................... 5-5
Block Size Considerations........................................................................................................... 5-6
Considerations for SQL*Net....................................................................................................... 5-7
Considerations for Replication Environments......................................................................... 5-7
Considerations for Migrating from ConText to Oracle Text.................................................. 5-7
Migrating to a Different Operating System.............................................................................. 5-7
v
Character Set Considerations...................................................................................................... 5-8
Distributed Database Considerations........................................................................................ 5-8
Prepare the Oracle7 Source Database for Migration.................................................................... 5-9
Prepare for Migration on a Windows Platform..................................................................... 5-13
Install the Release 9.0.1 Oracle Software and Migrate the Database...................................... 5-15
Running the Oracle Data Migration Assistant Independently............................................ 5-20
Finish the Migration......................................................................................................................... 5-21
Troubleshooting Errors During Migration.................................................................................. 5-23
Abandoning the Migration............................................................................................................. 5-24
6 Migrating Using Export/Import
Basics of Export/Import...................................................................................................................... 6-2
Export Utility Requirements....................................................................................................... 6-2
Import Utility Requirements....................................................................................................... 6-2
Additional Options....................................................................................................................... 6-3
Migrate the Source Database Using Export/Import..................................................................... 6-3
7 Upgrading from an Older Release of Oracle to the New Oracle9i Release
Upgrade Paths...................................................................................................................................... 7-2
Upgrading the Database to the New Oracle9i Release................................................................ 7-2
Character Set Considerations...................................................................................................... 7-2
Considerations for Replication Environments......................................................................... 7-3
Upgrading Oracle Parallel Server.............................................................................................. 7-4
Prepare to Upgrade...................................................................................................................... 7-5
Upgrade the Database.................................................................................................................. 7-7
Upgrading Specific Components................................................................................................... 7-24
Upgrading JServer...................................................................................................................... 7-25
Upgrading XDK for Java........................................................................................................... 7-27
Upgrading Session Namespace, CORBA, and OSE............................................................... 7-28
Upgrading JSP............................................................................................................................. 7-30
Upgrading Oracle Spatial.......................................................................................................... 7-31
UpgradinginterMedia................................................................................................................ 7-31
Upgrading Oracle Text............................................................................................................... 7-31
Upgrading Oracle Label Security............................................................................................. 7-34
Upgrading Oracle9i Real Application Clusters...................................................................... 7-35
vi
Upgrading Materialized Views................................................................................................ 7-36
Upgrading the Advanced Queuing Option............................................................................ 7-37
Upgrading the Recovery Catalog............................................................................................. 7-38
Upgrading Statistics Tables Created by the DBMS_STATS Package................................. 7-39
Recompiling Invalid PL/SQL Modules.................................................................................. 7-39
Changing the Word-Size of Your Current Release..................................................................... 7-40
8 After Migrating or Upgrading the Database
Tasks to Complete After Migrating or Upgrading Your Database............................................ 8-2
Back Up the Database ................................................................................................................. 8-2
Change the Passwords for Oracle-Supplied Accounts........................................................... 8-2
Upgrade User NCHAR Columns............................................................................................... 8-3
Migrate Your Initialization Parameter File to a Server Parameter File................................ 8-4
Install Component Java Classes................................................................................................. 8-5
Migrate Tables from LONGs to LOBs....................................................................................... 8-7
Check for Bad Date Constraints................................................................................................. 8-9
Migrate Your Server Manager Line Mode Scripts to SQL*Plus............................................ 8-9
Avoid Problems with Parallel Execution................................................................................ 8-10
Modify Your listener.ora File.................................................................................................... 8-10
Migrate or Upgrade Your Standby Database......................................................................... 8-10
Add New Features as Appropriate.......................................................................................... 8-12
Develop New Administrative Procedures as Needed.......................................................... 8-12
Adjust Your Initialization Parameter File for the New Release........................................... 8-12
Normalize Filenames on Windows Platforms....................................................................... 8-13
Tasks to Complete Only After Migrating Your Database......................................................... 8-15
Rebuild Unusable Bitmap Indexes........................................................................................... 8-15
Migrate Partition Views to Partition Tables........................................................................... 8-16
Migrate or Upgrade to the New Release of Oracle Net (Optional)..................................... 8-16
Test the Database and Compare Results................................................................................. 8-16
Tune the Migrated Database..................................................................................................... 8-17
Task to Complete Only After Upgrading Your Database......................................................... 8-17
Rebuild Unusable Function-Based Indexes............................................................................ 8-17
9 Compatibility and Interoperability
What Is Compatibility?...................................................................................................................... 9-2
vii
The COMPATIBLE Initialization Parameter............................................................................ 9-2
Features Requiring 9.0.0 or Higher Compatibility Level............................................................ 9-9
Tablespaces.................................................................................................................................... 9-9
Schema Objects............................................................................................................................ 9-10
Partitioning.................................................................................................................................. 9-10
Built-In Datatypes....................................................................................................................... 9-10
User-Defined Datatypes............................................................................................................. 9-11
Data Protection............................................................................................................................ 9-11
Distributed Databases................................................................................................................ 9-11
Data Access.................................................................................................................................. 9-12
Data Warehousing...................................................................................................................... 9-12
Features Requiring 8.1.0 or Higher Compatibility Level.......................................................... 9-12
Applications................................................................................................................................. 9-13
Tablespaces.................................................................................................................................. 9-13
Schema Objects............................................................................................................................ 9-14
Partitioning.................................................................................................................................. 9-14
Built-In Datatypes....................................................................................................................... 9-15
User-Defined Datatypes............................................................................................................. 9-15
Oracle Parallel Server................................................................................................................. 9-15
Data Protection............................................................................................................................ 9-16
Distributed Databases................................................................................................................ 9-16
Data Access.................................................................................................................................. 9-17
Data Warehousing...................................................................................................................... 9-18
Spatial and Visual Information................................................................................................. 9-18
What Is Interoperability?................................................................................................................. 9-18
Compatibility and Interoperability Issues.................................................................................. 9-19
Applications................................................................................................................................. 9-20
The STARTUP Command......................................................................................................... 9-27
Tablespaces and Datafiles.......................................................................................................... 9-28
Data Dictionary........................................................................................................................... 9-29
Schema Objects............................................................................................................................ 9-30
Datatypes..................................................................................................................................... 9-31
User-Defined Datatypes............................................................................................................. 9-34
SQL and PL/SQL........................................................................................................................ 9-35
Advanced Queuing (AQ)........................................................................................................... 9-37
viii
Procedures and Packages.......................................................................................................... 9-38
Oracle Optimizer........................................................................................................................ 9-39
Oracle9i Real Application Clusters.......................................................................................... 9-39
Database Security....................................................................................................................... 9-42
Database Backup and Recovery............................................................................................... 9-43
Distributed Databases................................................................................................................ 9-48
SQL*Net or Oracle Net.............................................................................................................. 9-49
Export/Import............................................................................................................................ 9-51
Miscellaneous Compatibility and Interoperability Issues.................................................... 9-53
10 Upgrading Your Applications
Overview of Upgrading Applications to Oracle9i..................................................................... 10-2
Compatibility Issues for Applications..................................................................................... 10-2
Upgrading Precompiler and OCI Applications.......................................................................... 10-3
Understanding Software Upgrades and Your Client/Server Configuration.................... 10-3
Compatibility Rules for Applications When Upgrading Oracle Software........................ 10-4
Upgrading Options for Your Precompiler and OCI Applications...................................... 10-7
Upgrading SQL*Plus Scripts........................................................................................................ 10-11
Upgrading Oracle7 Forms or Oracle Developer Applications............................................... 10-12
11 Migrating from Server Manager to SQL*Plus
Startup Differences.......................................................................................................................... 11-2
Starting Server Manager............................................................................................................ 11-2
Starting SQL*Plus....................................................................................................................... 11-2
Commands......................................................................................................................................... 11-3
Commands Introduced in SQL*Plus Release 8.1................................................................... 11-3
Commands Common to Server Manager and SQL*Plus...................................................... 11-5
SQL*Plus Equivalents for Server Manager Commands....................................................... 11-6
Possible Differences in the SET TIMING Command............................................................ 11-9
Server Manager Commands Unavailable in SQL*Plus......................................................... 11-9
Syntax Differences............................................................................................................................ 11-9
Comments.................................................................................................................................. 11-10
Blank Lines................................................................................................................................ 11-12
The Hyphen Continuation Character.................................................................................... 11-13
Ampersands.............................................................................................................................. 11-14
ix
CREATE TYPE and CREATE LIBRARY Commands.......................................................... 11-16
COMMIT Command................................................................................................................ 11-16
12 Migration Issues for Physical Rowids
Migrating Applications and Data.................................................................................................. 12-2
The DBMS_ROWID Package......................................................................................................... 12-3
Rowid Conversion Types.......................................................................................................... 12-3
Rowid Conversion Functions.................................................................................................... 12-4
Conversion Procedure Examples............................................................................................. 12-6
Snapshot Refresh.............................................................................................................................. 12-7
Version 7 and Version 6 Client Compatibility Issues................................................................ 12-7
ROWID Migration and Compatibility Issues............................................................................. 12-8
Accessing an Oracle7 Database from an Oracle9i Client...................................................... 12-8
Accessing an Oracle9i Database from an Oracle7 Client...................................................... 12-8
Import and Export...................................................................................................................... 12-9
Frequently Asked Questions About Rowid Migration............................................................. 12-9
13 Downgrading to Release 8.1
Perform a Full Offline Backup....................................................................................................... 13-2
Remove Incompatibilities............................................................................................................... 13-2
Checking the Compatibility Level of Your Database............................................................ 13-2
Reset Database Compatibility........................................................................................................ 13-3
Downgrade the Database................................................................................................................ 13-4
Regenerating Replication Support......................................................................................... 13-14
14 Removing Incompatibilities Before Downgrading to Release 8.1
Identifying Incompatibilities......................................................................................................... 14-2
Removing Incompatibilities Before Downgrading to Release 8.1.6 or Release 8.1.7.......... 14-3
Tablespaces.................................................................................................................................. 14-3
Schema Objects............................................................................................................................ 14-4
Partitioning.................................................................................................................................. 14-6
Datatypes..................................................................................................................................... 14-6
User-Defined Datatypes........................................................................................................... 14-12
SQL and PL/SQL...................................................................................................................... 14-13
x
