Oracle9i Database Migration

This manual guides you through the process of planning and executing migrations, upgrades, and downgrades on the Oracle database system. In addition, this manual provides information about compatibility, about upgrading applications to the current release of Oracle, and about important changes in the current release, such as initialization parameter changes and data dictionary changes.Oracle9i Database Migration contains information that describes the features and functionality of the Oracle9i (also known as the standard edition) and the Oracle9i Enterprise Edition products. Oracle9i and the Oracle9i Enterprise Edition have the same basic features. However, several advanced features are available only with the Enterprise Edition, and some of these are optional. For example, to use application failover, you must have the Enterprise Edition with the Oracle9i Real Application Clusters option. Send Us Your Comments......Page 15 Preface......Page 17 1 Introduction......Page 27 Versions and Releases......Page 28 Source Database and Target Database......Page 29 Running Multiple Versions and Releases of Oracle......Page 30 Install Version 7 and Version 8 Databases in Multiple Oracle Homes......Page 31 Install Version 7 and Version 8 Databases on Separate Computers......Page 32 Migrate a Version 7 Database to a Version 8 Database......Page 33 Upgrade a Release 8.0 Database to a Release 9.0.0 Database......Page 34 Upgrade a Release 8.1 Database to the Current Release......Page 35 Migrate Version 7 Clients to Version 8 Clients......Page 36 Running Scripts......Page 37 Changing Word-Size......Page 38 Deinstalling Options......Page 39 2 Overview of Migration......Page 41 Overview of Migration Steps......Page 42 Step 1: Prepare to Migrate......Page 44 Step 4: Prepare and Preserve the Source Database......Page 45 Step 6: Tune and Adjust the New Production Database......Page 46 Role of the Application Developer During Migration......Page 47 3 Preparing to Migrate......Page 49 Become Familiar with the Features of the New Database......Page 50 Choose a Migration Method......Page 51 Assess System Requirements vs. Resources Available......Page 64 Choose an Oracle Home Directory for the New Release......Page 67 Avoid Common Migration Problems......Page 68 Develop a Testing Plan......Page 70 Test the Migrated Test Database......Page 74 4 Migrating from Oracle7 Using the Migration Utility......Page 75 Documentation Roadmap for Using the Migration Utility......Page 76 Outline of the Migration Process......Page 77 Using the Migration Utility......Page 78 Space Requirements......Page 79 Block Size Considerations......Page 80 Migrating a System with Oracle Parallel Server Installed......Page 81 Character Set Considerations......Page 82 Prepare the Oracle7 Source Database for Migration......Page 83 Prepare for Migration on Windows Platforms......Page 87 Install the Release 9.0.1 Oracle Software......Page 89 Review Migration Utility Command-Line Options......Page 91 Prepare the Oracle7 Environment for Migration on UNIX Operating Systems......Page 92 Perform Migration Steps in the Oracle7 Environment......Page 94 Run the Migration Utility......Page 96 Preserve the Oracle7 Source Database......Page 100 Perform Migration Steps in the Oracle9i Environment......Page 101 Abandoning the Migration......Page 109 5 Migrating from Oracle7 Using the Oracle Data Migration Assistant......Page 111 Documentation Roadmap for Using the Oracle Data Migration Assistant......Page 112 Start with an Oracle7 Database Supported by the Oracle Data Migration Assistant......Page 114 Space Requirements......Page 115 Block Size Considerations......Page 116 Migrating to a Different Operating System......Page 117 Distributed Database Considerations......Page 118 Prepare the Oracle7 Source Database for Migration......Page 119 Prepare for Migration on a Windows Platform......Page 123 Install the Release 9.0.1 Oracle Software and Migrate the Database......Page 125 Running the Oracle Data Migration Assistant Independently......Page 130 Finish the Migration......Page 131 Troubleshooting Errors During Migration......Page 133 Abandoning the Migration......Page 134 6 Migrating Using Export/Import......Page 135 Import Utility Requirements......Page 136 Migrate the Source Database Using Export/Import......Page 137 7 Upgrading from an Older Release of Oracle to the New Oracle9i Release......Page 139 Character Set Considerations......Page 140 Considerations for Replication Environments......Page 141 Upgrading Oracle Parallel Server......Page 142 Prepare to Upgrade......Page 143 Upgrade the Database......Page 145 Upgrading Specific Components......Page 162 Upgrading JServer......Page 163 Upgrading XDK for Java......Page 165 Upgrading Session Namespace, CORBA, and OSE......Page 166 Upgrading JSP......Page 168 Upgrading Oracle Text......Page 169 Upgrading Oracle Label Security......Page 172 Upgrading Oracle9i Real Application Clusters......Page 173 Upgrading Materialized Views......Page 174 Upgrading the Advanced Queuing Option......Page 175 Upgrading the Recovery Catalog......Page 176 Recompiling Invalid PL/SQL Modules......Page 177 Changing the Word-Size of Your Current Release......Page 178 8 After Migrating or Upgrading the Database......Page 183 Change the Passwords for Oracle-Supplied Accounts......Page 184 Upgrade User NCHAR Columns......Page 185 Migrate Your Initialization Parameter File to a Server Parameter File......Page 186 Install Component Java Classes......Page 187 Migrate Tables from LONGs to LOBs......Page 189 Migrate Your Server Manager Line Mode Scripts to SQL*Plus......Page 191 Migrate or Upgrade Your Standby Database......Page 192 Adjust Your Initialization Parameter File for the New Release......Page 194 Normalize Filenames on Windows Platforms......Page 195 Rebuild Unusable Bitmap Indexes......Page 197 Test the Database and Compare Results......Page 198 Rebuild Unusable Function-Based Indexes......Page 199 9 Compatibility and Interoperability......Page 201 The COMPATIBLE Initialization Parameter......Page 202 Tablespaces......Page 209 Built-In Datatypes......Page 210 Distributed Databases......Page 211 Features Requiring 8.1.0 or Higher Compatibility Level......Page 212 Tablespaces......Page 213 Partitioning......Page 214 Oracle Parallel Server......Page 215 Distributed Databases......Page 216 Data Access......Page 217 What Is Interoperability?......Page 218 Compatibility and Interoperability Issues......Page 219 Applications......Page 220 The STARTUP Command......Page 227 Tablespaces and Datafiles......Page 228 Data Dictionary......Page 229 Schema Objects......Page 230 Datatypes......Page 231 User-Defined Datatypes......Page 234 SQL and PL/SQL......Page 235 Advanced Queuing (AQ)......Page 237 Procedures and Packages......Page 238 Oracle9i Real Application Clusters......Page 239 Database Security......Page 242 Database Backup and Recovery......Page 243 Distributed Databases......Page 248 SQL*Net or Oracle Net......Page 249 Export/Import......Page 251 Miscellaneous Compatibility and Interoperability Issues......Page 253 10 Upgrading Your Applications......Page 255 Compatibility Issues for Applications......Page 256 Understanding Software Upgrades and Your Client/Server Configuration......Page 257 Compatibility Rules for Applications When Upgrading Oracle Software......Page 258 Upgrading Options for Your Precompiler and OCI Applications......Page 261 Upgrading SQL*Plus Scripts......Page 265 Upgrading Oracle7 Forms or Oracle Developer Applications......Page 266 11 Migrating from Server Manager to SQL*Plus......Page 267 Starting SQL*Plus......Page 268 Commands Introduced in SQL*Plus Release 8.1......Page 269 Commands Common to Server Manager and SQL*Plus......Page 271 SQL*Plus Equivalents for Server Manager Commands......Page 272 Syntax Differences......Page 275 Comments......Page 276 Blank Lines......Page 278 The Hyphen Continuation Character......Page 279 Ampersands......Page 280 COMMIT Command......Page 282 12 Migration Issues for Physical Rowids......Page 283 Migrating Applications and Data......Page 284 Rowid Conversion Types......Page 285 Rowid Conversion Functions......Page 286 Conversion Procedure Examples......Page 288 Version 7 and Version 6 Client Compatibility Issues......Page 289 Accessing an Oracle9i Database from an Oracle7 Client......Page 290 Frequently Asked Questions About Rowid Migration......Page 291 13 Downgrading to Release 8.1......Page 293 Checking the Compatibility Level of Your Database......Page 294 Reset Database Compatibility......Page 295 Downgrade the Database......Page 296 Regenerating Replication Support......Page 306 14 Removing Incompatibilities Before Downgrading to Release 8.1......Page 309 Identifying Incompatibilities......Page 310 Tablespaces......Page 311 Schema Objects......Page 312 Datatypes......Page 314 User-Defined Datatypes......Page 320 SQL and PL/SQL......Page 321 Tablespaces......Page 322 Datatypes......Page 324 Constraints and Triggers......Page 326 Security......Page 327 Continue with Your Downgrade to Release 8.1......Page 328 15 Downgrading to an Older Release of Oracle......Page 329 Downgrading a Database That Contains New or Changed Data......Page 330 Alternative Downgrading Methods......Page 331 Downgrading a Database That Does Not Contain New or Changed Data......Page 332 Downgrading a Database That Contains New or Changed Data......Page 333 Alternative Downgrading Methods......Page 334 A Troubleshooting Migration Problems......Page 335 Expected Errors During Upgrade......Page 336 Problems Using the Migration Utility or the Oracle Data Migration Assistant......Page 337 Incorrect AUDIT_TRAIL Parameter Setting......Page 338 OPTIMAL Setting for the SYSTEM Rollback Segment......Page 339 Problems at the ALTER DATABASE CONVERT Statement......Page 340 Convert File Not Found......Page 341 REMOTE_LOGIN_PASSWORDFILE Initialization Parameter Set to EXCLUSIVE......Page 342 Rerunning the ALTER DATABASE CONVERT Statement......Page 343 Datafile Version Integrity Problem......Page 344 Migration Utility Messages......Page 345 B Changes to Initialization Parameters......Page 357 Initialization Parameters Added in Oracle9i......Page 358 Initialization Parameters Obsolete in Oracle9i......Page 359 Initialization Parameters Added in Version 8......Page 360 Initialization Parameters Obsolete in Version 8......Page 362 The ORACLE_TRACE_ENABLE Parameter......Page 365 SORT_AREA_SIZE and SORT_DIRECT_WRITES Parameters......Page 366 The DB_DOMAIN Parameter......Page 367 Parallel Execution Allocated from Large Pool......Page 368 Archive Log Destination Parameters......Page 371 C Changes to Static Data Dictionary Views......Page 375 Static Data Dictionary Views Added in Oracle9i......Page 376 Static Data Dictionary Views with Added Columns in Oracle9i......Page 378 Static Data Dictionary Views Added in Version 8......Page 381 Static Data Dictionary Views with Added Columns in Version 8......Page 386 Static Data Dictionary Views with Dropped Columns in Version 8......Page 389 Static Data Dictionary Views with Columns That May Return Nulls......Page 391 Static Data Dictionary Views Obsolete in Version 8......Page 393 D Changes to Dynamic Performance Views......Page 395 Dynamic Performance Views Added in Oracle9i......Page 396 Dynamic Performance Views Renamed in Oracle9i......Page 397 Dynamic Performance Views with Dropped Columns in Oracle9i......Page 399 Dynamic Performance Views Obsolete in Oracle9i......Page 401 Dynamic Performance Views Added in Version 8......Page 402 Dynamic Performance Views with Added Columns in Version 8......Page 407 Dynamic Performance Views with Dropped Columns in Version 8......Page 408 Dynamic Performance Views with Renamed Columns in Version 8......Page 409 Dynamic Performance Views Obsolete in Version 8......Page 410 E New Internal Datatypes and SQL Functions......Page 411 SQL Functions Added in Oracle9i......Page 412 Internal Datatypes Added in Version 8......Page 413 SQL Functions Added in Version 8......Page 414 F Migration and Compatibility for Oracle Net Services......Page 417 Overview of Unsupported Oracle Net Services Features in Release 9.0......Page 418 Unsupported Parameters......Page 420 Unsupported Control Utility Commands......Page 422 Oracle9i Database Connections......Page 423 Oracle8 or Oracle7 Database Connections......Page 424 Oracle Names......Page 425 Using the Oracle Net Manager to Handle Compatibility Issues......Page 426 Step 1: Verify Service Name and Instance Name......Page 427 Step 4: Perform Functional Upgrade and Migration......Page 428 Using Oracle Names Version 9......Page 431 Migrating from Oracle Names Version 2 Using a Database......Page 432 Migrating from Oracle Names Version 2 with the Dynamic Discovery Option......Page 434 Migrating from ROSFILES......Page 436 Migrating Region Checkpoint Files to Domain and Topology Checkpoint Files......Page 439 Reviewing Migration Checklist......Page 440 G Migration and Compatibility for Replication Environments......Page 441 Migration Overview for Replication......Page 442 Migrating All Sites at Once......Page 444 Migrating Incrementally......Page 447 Preparing Oracle7 Master Sites for Incremental Migration......Page 448 Incremental Migration of Materialized View Sites......Page 449 Incremental Migration of Master Sites......Page 452 Primary Key Materialized View Conversion at Master Sites......Page 457 Primary Key Materialized View Conversion at Materialized View Sites......Page 458 Features Requiring Oracle9i......Page 460 Features Requiring Oracle8 or Higher......Page 461 Obsolete Procedures......Page 462 Index......Page 463