Remote DBA Plans
Remote DBA Service

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. These are:

1. For small instances (not more that a gig or two) export the Oracle7 database, build the Oracle9i 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 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 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 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.

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. 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 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 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. ORA_NLS33=$ORACLE_HOME/migrate

12. 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:

13. Start up the Oracle7 database in normal mode.

14. Drop the user “migrate.”

15. Rerun CATALOG.SQL.

16. If using parallel server, rerun CATPARR.SQL.

17. If using Symmetric Replication, run CATREP.SQL.

18. Note: This will be a 7.3.4 database if you abandon at this point.

19. Remove any obsolete initialization parameters from the databases init<SID>.ora file.

20. Set compatible to 9.0.0.0 or not at all.

21. Change the locations specified by the control_files parameter to a new location.

22. Remove the old control files; they will re-re-created.

23. From SQLPLUS, issue these commands: CONNECT INTERNAL and STARTUP NOMOUNT.

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

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

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

27. 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 1.29).

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

Figure 1.31 Recompiling PL/SQL modules.

Figure 1.32 One more chance to back up your database.

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 back out.

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 are upgrading!

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.

Figure 1.36 Password review screen.

Figure 1.37 SQLPLUS screen showing latest version.

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

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