Reprinted with Permission by Quest Software April  2002
 

Migrating to Oracle9i
Michael R. Ault, TUSC

Introduction

The long awaited moment approaches. The long heralded release of ORACLE9i into production has/is happening so now what do we as DBAs and application developers do? We migrate. Of course we should take the proper steps to ensure that we migrate like graceful geese flying across a sunset rather than lemmings dashing forward over a cliff (see figure 1). At TUSC we prefer the graceful flight across the sunset.

Planning Migration

How do we accomplish this graceful migration? Planning. Planning is the only way to minimize points of possible failure in your migration path from ORACLE8 (or earlier versions) to ORACLE9i. In this article I will attempt to provide a logical framework for planning your migration and hopefully shine a light on the pitfalls that you should avoid along the way (See figure 2).

Have you known someone who immediately takes new items (VCRs, computers, TVs) right out of the box, plugs them in and never reads instructions? Remember how later they fret because the blankity-blank item won’t do what they want (or more likely, they just don’t know how to make it do what they want?) They didn’t prepare to migrate to their new appliance. This preparation should have involved reviewing the installation and operation manuals, performing controlled tests of new features and then finally bringing the new appliance into full use. The same is true with ORACLE9i.

Preparing to Migrate

Invariably other “experts” (using the term loosely) and I will be asked hundreds of questions about installation and migration to ORACLE9i that should have been answered by reading the documentation and testing new features before implementation. You have to take the time to study the new features sections of the manuals, read the readme.doc and review utility scripts for “hidden” changes.

Back when the Oracle8 migration was the rage I mentioned the compatible parameter in one of my columns…a couple of weeks afterward Oracle came out with a spate of bugs that related to the parameter… were the two related? Probably. It just demonstrates that people thought they had “migrated” to a new version of Oracle, when in fact, because of the setting of the parameter, they hadn’t even tested the new features that depended on a new redo log format! Reading the documentation before they migrated would have prevented this.

How about the person who buys one of those great new sport utility vehicles only to discover it won’t fit in their garage? Obviously they didn’t check on available resources before migrating to a new vehicle. ORACLE9i will take up to 150% more space for initial load than ORACLE8, do you have enough free space? You may want to run both an ORACLE9i and your ORACLE8 system in parallel, do you have enough memory (9i may require twice as much memory) or enough CPU? You will require at least 50 meg of free space in an 8i SYSTEM tablespace or the upgrade will fail.

Getting back to our geese verses lemmings migration analogy, which chose the better migration path (overlooking hunters in this case…)? Don’t begin your migration without a clear plan of how to get from your ORACLE8 instance to your ORACLE9i instance. Decide on your path, preferably one that doesn’t lead over a cliff! Review the possible methods to migrate your system, choose the best for your situation and then plan, plan, plan! In Oracle9i migration you have the choice between export and import, The Data Migration GUI tool and the migration command line utility. Of course there is also the CTAS method but that is only applicable to real S and M fanatics. In a 7.x to 9i all of the methods are applicable, however in an 8.x to 9i only the Data Migration assistant, export and import, unload and SQLLOADER or CTAS are available.

Finally, how do you know your migration was successful? Do you have a test plan? Did you evaluate your existing system prior to migration to check its performance characteristics? Do you have a standard set of test cases to run against the old database and the new? How will you know that the system is functioning correctly after the migration is “complete”?

Test the Migration Process

I bet the Donner party wished they could have tested their migration plan and I’ll bet the lemmings who dashed off the cliff into the sea did as well. If at all possible, even if it is on a small database that you create specifically for the test (in fact, this is the best way), test your migration path. Repeat the test until you are sure exactly what you are doing. I know the Bosses will be yelling if you are a bit late with the production migration, but I’ll bet the Donner party had wished they had been late for their date with history. Hurrying to meet a schedule is probably the best path to meet disaster I know of. When we rush we forget important steps, overlook potential problems and just plain do stupid things. 

Test the Migrated Test Instance

If you are lucky enough to have the space available to do a non-production test migration, be sure to have test plans available to test that what you ended up with is at least as good as what you started with. Find the causes of any anomalous behavior and fix them before you spend all weekend migrating your production database only to have it malfunction, when the boss is looking of course.

Protect Your Retreat Path

Damn the torpedoes! Full speed ahead! May be fine for Naval engagements but it is no way to migrate an Oracle instance. Protect your retreat path by ensuring you have a complete backup of your ORACLE8 instance.

Ensure you parse out enough time to allow multiple re-installs and migrations as needed. If you plan out the time needed to the nearest second, chances are you won’t make your schedule. I’m not saying use the Scottie method (for those non-Star Trek aficionados Scottie was the Chief Engineer on the Star Ship Enterprise, he would always pad his repair estimates by a factor of 3 or 4…then deliver ahead of schedule). Better to finish early than to have everyone breathing down your neck because you didn’t meet your schedule.

Again, cut a full backup or a full export of your source database, this cannot be stressed enough. At worst having a full backup wastes some disk or tape space for a while, at best it will save your hide from falling over the cliff with the lemmings.

Prepare the source database as completely as possible. Remove unneeded tables, views, users. Do space management (why take problems with you?) Consolidate multiple datafiles, or, conversely, split up datafiles that are too big to perform properly. Tune your source as well as you can and have it running at tip-top performance. 

Take Flight! (or fall of the cliff…) Migrate the Source Database

Following the pre-tested methodology migrate the source database to its new ORACLE9i home. Immediately after the migration successfully completes shutdown and perform a complete cold backup. This gives you a starting point should something go awry with subsequent testing. An export will do nearly as well, but don’t use a hot backup at this point, a hot backup will not afford full recoverability at this stage of migration! The backup should contain all datafiles, control files, redo logs, parameter files, and SQL scripts used to build any of the database objects.

The Three T’s: Tune, Tweak and Test the New Database

Using the knowledge you gained from your thorough review of the documents, readme’s and utility scripts, tune and tweak the new ORACLE9i instance to optimum performance. Once the database is tuned, test using your pre-developed test cases.

What Next?

Once the database is migrated to the ORACLE9i instance structure, you need to consider what features you want to implement (after all, if you didn’t want the new features, why migrate?) and how you are going to implement them.

ORACLE9i offers a plethora of new features, including automated rollback (now called undo) segment administration, multiple block sizes in the SGA and tablespaces, and the ability to change SGA parameters on the fly. You may be shifting to the new release to overcome bugs that where present in the previous release, or, only for specific new features that aren’t mentioned here. 

Let me make a statement that I’m sure will have some folks shuddering, if you are completely happy with your application don’t force fit it into these new features. Change for the sake of change is stupid. If there is a good, viable reason to implement these new features, by all means do so, but don’t be a lemming and just follow the herd (do lemmings run in herds?) over the cliff. ORACLE9i will function very well with an ORACLE8 application resting inside of it. Don’t feel that you must convert your applications immediately to the new features. Take some time and get familiar with the ride of the new database and watch for its quirks before you start pell-mell conversion.

A Detailed Look at the MIG 

No, this isn’t a new Russian Fighter plane. MIG is the migration utility that Oracle has provided to get your ORACLE8 database into an ORACLE9i database. Essentially there are two main paths and a rocky third to migrate from ORACLE8 to ORACLE9i. These are:

  1. For small instances (not more that a gig or two) export the ORACLE8 database, build the ORACLE9i database and import.
  2. For large instances (many gig) use the MIG facility.
  3. For those who like pain, unload all ORACLE8 tables into flat files, build ORACLE9i database using DDL scripts, use SQL loader to reload data.

The MIG path of course involves the use of the MIG utility. ORACLE9i has changes to virtually all database structures if you are upgrading from a release prior to 8i:

Datafile file headers
Data dictionary
Controlfile structure
Rollback Segment structure

The MIG9 utility, properly used, makes sure that the existing ORACLE8 structures are altered to the new ORACLE9i structures. This is a one-way path and once started the only way to go back to the ORACLE8 instance you knew and loved is to recover from the backup or export you dutifully made prior to starting…right?

Let’s take a more detailed look at the actual procedure to use the MIG80 utility.

  1. You must start at 7.3.x (or higher) release level of Oracle. A version 6 database must be migrated to at least 7.3.x before it can be converted to ORACLE9i.
  2. Backup the source ORACLE database, or, perform a complete export. 
  3. Drop any users or roles named “migrate”. 
  4. Resolve all pending transactions in a distributed environment.
  5. Bring all tablespaces online, or, make sure they are offline normal or temporary, not immediate. Resolve any save undo situations in tablespaces (see migration manual).
  6. Shutdown normal (not immediate or abort).
  7. Install the ORACLE9i software, do not do a “COMPLETE” install as this will attempt to build an ORACLE9i instance and may damage your existing instance beyond recovery. Do a partial, software only install. 
  8. Install the MIG90 utility into the ORACLE8 ORACLE_HOME by using OUI from X-windows on UNIX or its equivalent on your operating system.
  9. Unset the TWO_TASK environmental variable on UNIX or ORA_DFLT_HOLSTER on VMS.
  10. Set the following init.ora parameter:
    ORA_NLS33=$ORACLE_HOME/migrate (or its equivalent location on your system)
  11. Run the MIG90 utility on the ORACLE8 database according to the directions for your system. This creates an ORACLE9i data dictionary and a binary convert file. You will need 1.5 times the amount of space that your current dictionary occupies as free space in your SYSTEM tablespace area for the new dictionary. If you aren’t sure you have the space, run MIG in CHECK_ONLY mode first.You aren’t past the point of no return…yet. This step obliterates the ORACLE8 catalog views, but, you can recover them by doing the following if you need to abandon the migration at this point:
    1. Startup the ORACLE8 database in normal mode.
    2. Drop the user “migrate”
    3. Rerun CATALOG.SQL
    4. If using parallel server, rerun CATPARR.SQL
    5. If using Symmetric Replication run CATREP.SQL
    One thing to note, this will be a 8.1.7 database if you abandon at this point.
  12. Remove any obsolete initialization parameters from the databases init<SID>.ora file.
    1. Set compatible to 9.0.0.0 or not at all.
    2. Change the locations specified by the control_files parameter to a new location.
  13. Remove the old control files, they will re recreated.
  14. From SQLPLUS issue the commands: CONNECT INTERNAL and STARTUP NOMOUNT.
  15. From SQLPLUS the DBA issues the ALTER DATABASE CONVERT command on the ORACLE9i side. This command creates a new controlfile, converts all online file headers to ORACLE9i format, and mounts the ORACLE9i instance. This is the point of no return.
  16. The DBA issues the ALTER DATABASE OPEN RESETLOGS command on the ORACLE9i side which automatically converts all objects and users defined in the new dictionary to ORACLE9i specifications. It also converts all rollback segments to ORACLE9i format.
  17. Finish converting the catalog to a full ORACLE9i catalog by running cat9000.sql, usually located in the $ORACLE_HOME/rdbms/admin subdirectory on UNIX. Then run catalog.sql located in the same place. Finally, run catproc.sql to rebuild the PL/SQL and utility packages. If needed, also run any other “cat”.sql scripts to install any purchased options as required.
  18. Shutdown and backup your new ORACLE9i ready database.

Pitfalls to Avoid

So, what about the pitfalls? What are they? I wish I knew. Honestly, it would be impossible to tell you all the possible points of failure. Essentially most will be resource related such as not enough space. Let’s look at a quick list of possible points of failure:

  1. Not enough space in the SYSTEM tablespace when using MIG to migrate. The MIG will complain and abort if it doesn’t have the space to create the new dictionary tables. You will need at least 2 times the space your current dictionary occupies as free space in the SYSTEM tablespace to use MIG. Run MIG in CHECK_ONLY mode to verify space is available (among other nice to knows). The Oracle9i binaries take up to 3 times the size of Oracle7 binaries, make sure there is enough freespace on the disk to accommodate them.
  2. If you are not using the export/import method, both databases must have matching block sizes and block size must be at least 2048 bytes. ORACLE9i will not accept a smaller block size than 2048 bytes.
  3. You are attempting to migrate from a 32 bit machine to a 64 bit machine using MIG. Come on, get serious. The only methods that work are methods two and three (export/import or sqlloader) I opt for export/import in this situation.
  4. Going from one character set to another is verboten (forbidden…don’t you learn a lot reading this stuff?). For MIG this isn’t a problem, but for the other methods it could be. Be sure to check your NLS parameters. 
  5. Performing migration steps out of order. Obviously, don’t do this.
  6. Not fulfilling the prerequisites for migration (see the first sections above).
  7. Allowing other users to access the database during migration.
  8. Database must be at least 7.3.4, I’m not kidding, it checks for this and errors out if it isn’t.
  9. If re-creating control files in a different location, be sure permissions are set properly.
  10. Be sure all tablespaces where either online or in an offline normal or temporary status when the ORACLE8 instance shutdown. Be sure there is no outstanding undo in any of the tablespaces.
  11. Be sure your initialization file is smaller than 2k in size. (This has always been a limit, it just wasn’t checked for before, anything over the 2k size was ignored and not read…)

In Conclusion…..

ORACLE9i offers numerous new features. ORACLE9i changes the fundamental structures of the database compared to ORACLE8 or earlier versions. The migration to ORACLE9i is not a simple process of shutting down in one version and starting up in the other, it is a complex operation requiring planning (at least for large databases which must use the MIG80 utility). However, in speaking with several DBAs who have done it, as long as you plan properly and follow directions you shouldn’t have any problems.