Migration Utility (MIG)
Oracle Tips by Burleson Consulting
No, this isn’t a new Russian fighter plane.
MIG is the migration utility that Oracle has provided to get your
Oracle7 database into an Oracle9i database. Essentially, there are two
main paths and a rocky third to migrate from Oracle7 to Oracle9i.
For small instances (not more that a gig or two) export the
Oracle7 database, build the Oracle9i database and import.
For large instances (many gigs), use the MIG facility.
For those who like pain, unload all Oracle7 tables into flat
files, build the Oracle9i database using DDL scripts, use SQL loader
to reload data. This would also include CTAS and COPY scenarios.
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 8. These
* Datafile file headers
* Data dictionary
* Controlfile structure
* Rollback segment structure
The MIG utility, properly used, ensures
that the existing Oracle7 structures are altered to the new Oracle9i
structures. This is a one-way path; once started, the only way to go
back to the Oracle7 instance you knew and loved is to recover from the
backup or export that you dutifully made prior to starting…right?
Let’s take a more detailed look at the
actual procedure to use the MIG utility.
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.
Back up the source Oracle database, or perform a complete
Drop any users or roles named “migrate.”
Resolve all pending transactions in a distributed environment.
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).
Shut down normal (not immediate or abort).
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 the MIG utility into the Oracle7 ORACLE_HOME by using
OUI from X-windows on UNIX or its equivalent on your operating system.
Unset the TWO_TASK environmental variable on UNIX, or
ORA_DFLT_HOLSTER on VMS.
Set the following init.ora parameter (or its equivalent location on
Run the MIG utility on the Oracle7 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 Oracle7 catalog views, but
you can recover them by doing the following if you need to abandon the
migration at this point:
Start up the Oracle7 database in normal mode.
Drop the user “migrate.”
If using parallel server, rerun CATPARR.SQL.
If using Symmetric Replication, run CATREP.SQL.
Note: This will be a 7.3.4 database if you abandon at this point.
Remove any obsolete initialization parameters from the databases
Set compatible to 18.104.22.168 or not at all.
Change the locations specified by the control_files parameter to a new
Remove the old control files; they will re-re-created.
From SQLPLUS, issue these commands: CONNECT INTERNAL and STARTUP
From SQLPLUS, the Remote 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.
The Remote 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.
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.
Shut down and back up your new Oracle9i-ready database.
Using Oracle Data Migration Assistant (ODMA)
The Oracle Data Migration Assistant allows an
Oracle8 or 8i database to be upgraded to 9i. This is considered a
release-to-release upgrade, not a migration, according to Oracle
support. ODMA is a command-line utility written in Java. This means
that a compatible JDK or JRE must be installed. For Linux, this would
be jdk118_v3 or jre118_v3 from Blackdown or the equivalent Sun
release. I also found that, for Linux, the local LANG variable had to
be unset or segmentation faults will occur.
Once all the prerequisites are met, you
can run ODMA simply by CD'ing to the Oracle bin directory and typing
“ODMA” at the command line (see Figure 1.26).
Figure 1.26 Example invocation of ODMA.
The screen in Figure 1.27 will be
displayed once ODMA configures. Most problems with ODMA come from
improper settings for PATH, CLASSPATH, and LD_LIBRARY_PATH. If you
have multiple databases, each will be shown on the main screen and you
can select the one you wish to update.
Figure 1.27 ODMA main screen.
In the next screen, you have the
opportunity to change initialization files, database password entry,
and Oracle home location (see Figure 1.28). Following this screen, the
Assistant retrieves database information from your system (see Figure
Figure 1.28 Home and Password screen.
Figure 1.29 Message stating database
information is being retrieved.
Once the database information is
retrieved, the options screen is displayed (see Figure 1.30). The
allowed options consist only of the capability to move datafiles and
to recompile PL/SQL packages (Figures 1.31 and 1.32). Note that you
are limited to only one location to which to move the datafiles.
Figure 1.30 Choosing between Custom or Default
Figure 1.31 Recompiling PL/SQL modules.
Figure 1.32 One more chance to back up your
Once you have selected the two options,
the conversion is ready to begin. The Assistant reminds you to back
up your database, as shown in Figure 1.32.
Figure 1.33 Summary view and last chance to
Once you to either back up your
database or skip this screen, you are given a summary screen and one
more chance to back out as shown in Figure 1.33.
Figure 1.34 Saying yes to the upgrade.
If you choose Yes on the message screen
shown in Figure 1.34, the upgrade begins, as shown in Figure 1.35.
Figure 1.35 The screen we've waited for: we
Once the upgrade begins, it may take
several hours to complete. For example, on a 700-meg, almost-empty
8.1.7 database on SuSE Linux 7.2 with a 450-MgHz CPU, 512-meg memory
and a single 30-gig disk, this process took seven hours. Using the U*.sql
manual upgrade, this only took three hours; we can only assume that
ODMA performs all data block conversions while the manual process
waits for dbwr to do them as the blocks are accessed.
Once the upgrade completes, you are
given a review screen for log reviews (see Figure 1.36). I had to run
the netca (Net Configuration Assistant) to convert my tnsnames.ora and
listener.ora before my Java application could connect using JDBC. Just
to show you I got it running, Figure 1.37 shows an SQLPLUS startup in
the new 9.0.1 instance. You can see my Java application running in
Figure 1.36 Password review screen.
Figure 1.37 SQLPLUS screen showing latest
Figure 1.38 Java application fully functional!
Pitfalls to Avoid
So what about the pitfalls? What are they?
Honestly, it would be impossible to tell you all the possible points
of failure, but most will be resource-related, such as not enough
space. Let’s look at a short list of possible points of failure:
* You don’t have enough space in the SYSTEM
tablespace when using MIG or ODMA to migrate. The MIG or ODMA will
complain and abort if it doesn’t have the space to create the new
dictionary tables. You will need at least two times the space your
current dictionary occupies as free space in the SYSTEM tablespace to
use MIG. You can run MIG in CHECK_ONLY mode to verify available space
(among other nice-to-knows). The Oracle9i binaries take up to three
times the size of Oracle7 binaries, so make sure there is enough free
space on the disk to accommodate them.
* 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.
* If 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 export/import or sqlloader. I opt for
export/import in this situation.
* 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.
* Performing migration steps out of order.
Obviously, don’t do this.
* Not fulfilling the prerequisites for
migration (see the first sections above).
* Allowing other users to access the database
* Database must be at least 7.3.4. I’m not
kidding; it checks for this and errors out if it isn’t.
* If you are re-creating control files in a
different location, be sure permissions are set properly.
* Be sure all tablespaces were either online
or in an offline normal or temporary status when the Oracle8 instance
shut down. Be sure there is no outstanding undo in any of the
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.