Migration to Oracle9i
Oracle Tips by Burleson Consulting
Once the installation of the new Oracle9i
software is complete, what do we, as Remote 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.20). Naturally, as
Remote DBAs, we prefer the graceful flight across the sunset.
Figure 1.20 Usual migration path.
How do we accomplish this graceful migration?
Planning. Planning is the only way to minimize points of possible
failure in your migration path from Oracle 6.x, Oracle 7.x, or
Oracle8/8i to Oracle9i. In this section, 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
Have you known someone who immediately
takes new items (VCRs, computers, TVs) right out of the box, plugs
them in, and never reads instructions? Later, they fret because the blankity-blank item won’t do what they want (or, more likely, they
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 (see Figure 1.21).
Figure 1.21 Proper migration path.
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 could have been answered if the people
asking the questions had read the documentation and tested new
features before implementation. The point is, 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 later, Oracle announced a spate of bugs that related
to the parameter. Were the two related? Probably. It demonstrated that
people who thought they had “migrated” to a new version of Oracle hadn’t. Why? 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
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 available resources
before “migrating” to a new vehicle. Oracle9i will take up to 150
percent more space for initial load than Oracle8 and up to three times
the space of Oracle 7.3. Do you have enough free space? You may want
to run both an Oracle9i and an Oracle8i (or earlier release) system in
parallel. Do you have enough memory (9i may require twice to three
times as much memory as previous releases (512 megabytes on Linux, for
example) or enough CPU? You will require at least 50 meg of free space
in an 8i SYSTEM tablespace to upgrade to 9i, or the upgrade will fail.
You may also want to take note that the default installation of an
Oracle9i database contains a SYSTEM tablespace that is near 350
megabytes in size with only 40 Kilobytes free, you should plan your
Getting back to our geese versus
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 Oracle8i or earlier
instance to your Oracle9i instance. Decide on your path, preferably
one that doesn’t lead you 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 6.x version
database, you must first migrate to Oracle7.3.x and then migrate the
7.3.x database into 9i. In a 7.3.x to 9i, all of the methods are
applicable; however, in an 8.1.x to 9i, only the Data Migration
Assistant, export and import, unload, and SQLLOADER or CTAS are
available. Figures 1.22 through 1.25 show these migration paths.
Figure 1.22 Oracle6 migration paths.
Figure 1.23 Oracle7 migration paths.
Figure 1.24 Oracle8 migration paths.
Figure 1.25 Oracle8i migration paths.
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 that 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 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 do stupid things.
Test the Migrated Test Instance
If you are lucky enough to have the space
available to do a nonproduction test migration, be sure to have test
plans available to verify 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.
Code Depot for Full Scripts
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.