| |
 |
|
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.
- 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.
- Back up the source Oracle database, or perform a complete
export.
- 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 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.
- 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 your system):
ORA_NLS33=$ORACLE_HOME/migrate
- 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.
- Remove any obsolete initialization parameters from the
databases init<SID>.ora file.
a. Set compatible to 9.0.0.0 or not at all.
b. Change the locations specified by the control_files parameter
to a new location.
- Remove the old control files; they will re-re-created.
- From SQLPLUS, issue these commands: CONNECT INTERNAL and
STARTUP NOMOUNT.
- 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.
- 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.
- 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.
- 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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
