Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Oracle MIG utility

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 Oracle database. Essentially, there are two main paths and a rocky third to migrate from Oracle7 to Oracle. These are:

1. For small instances (not more that a gig or two) export the Oracle7 database, build the Oracle database and import.
2. For large instances (many gigs), use the MIG facility.
3. For those who like pain, unload all Oracle7 tables into flat files, build the Oracle 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. Oracle has changes to virtually all database structures if you are upgrading from a release prior to 8. These include:

  • 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 Oracle 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.

  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 Oracle.
  2. Back up 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. Shut down normal (not immediate or abort).
  7. Install the Oracle software. Do not do a “complete” install, as this will attempt to build an Oracle instance and may damage your existing instance beyond recovery. Do a partial, software-only, install.
  8. Install the MIG utility into the Oracle7 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 (or its equivalent location on your system):

  11. Run the MIG utility on the Oracle7 database according to the directions for your system. This creates an Oracle 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:

    a. Start up the Oracle7 database in normal mode.
    b. Drop the user “migrate.”
    c. Rerun CATALOG.SQL.
    d. If using parallel server, rerun CATPARR.SQL.
    e. If using Symmetric Replication, run CATREP.SQL.

    Note: This will be a 7.3.4 database if you abandon at this point.
  12. Remove any obsolete initialization parameters from the databases init<SID>.ora file.

    a. Set compatible to or not at all.
    b. Change the locations specified by the control_files parameter to a new location.
  13. Remove the old control files; they will re-re-created.
  14. From SQLPLUS, issue these commands: CONNECT INTERNAL and STARTUP NOMOUNT.
  15. From SQLPLUS, the DBA issues the ALTER DATABASE CONVERT command on the Oracle side. This command creates a new controlfile, converts all online file headers to Oracle format, and mounts the Oracle instance. This is the point of no return.
  16. The DBA issues the ALTER DATABASE OPEN RESETLOGS command on the Oracle side, which automatically converts all objects and users defined in the new dictionary to Oracle specifications. It also converts all rollback segments to Oracle format.
  17. Finish converting the catalog to a full Oracle 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. Shut down and back up your new Oracle-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

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.

In the next screen, you have the opportunity to change initialization files, database password entry, and Oracle home location. Following this screen, the Assistant retrieves database information from your system 

Once the database information is retrieved, the options screen is displayed. The allowed options consist only of the capability to move datafiles and to recompile PL/SQL packages. Note that you are limited to only one location to which to move the datafiles.

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.

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.

If you choose Yes on the message screen shown in Figure 1.34, the upgrade begins, as shown in Figure 1.35.

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.37.

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 Oracle 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. Oracle 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 during migration.
  • 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 tablespaces.3

As stated earlier, Oracle offers numerous new features. It changes the fundamental structures of the database, compared to Oracle8 or earlier versions. The migration to Oracle 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 MIG or ODMA utilities). However, according to several DBAs who have done it, as long as you plan properly and follow directions, you shouldn’t have any problems.

This is an excerpt by Mike Ault’s book “Oracle Administration & Management”.  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.