Oracle9i Database Migration

✍ Scribed by Morales T.

• Year: 2001
• Tongue: English
• Leaves: 484
• Edition: release 9.0.1
• Category: Library

✦ Synopsis

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.

✦ Table of Contents

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 SQLPlus......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
SQLNet 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 SQLPlus Scripts......Page 265
Upgrading Oracle7 Forms or Oracle Developer Applications......Page 266
11 Migrating from Server Manager to SQLPlus......Page 267
Starting SQLPlus......Page 268
Commands Introduced in SQLPlus Release 8.1......Page 269
Commands Common to Server Manager and SQLPlus......Page 271
SQLPlus 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