Oracle Migration Workbench -
By Steve Callan
At the end of Part Two, we finished loading data from the MySQL database
into the Oracle database/repository. The owner of the imported
data was root, the privileged owner in the MySQL database "omwb."
In Part Three, we will go into more detail about the Migration
Workbench console and learn how to configure some options. At the
end of this article, we will start our preparation for using OMWB
to migrate a SQL Server 2000 database.
What's in Oracle Migration Workbench?
With Migration Workbench, like most Oracle
products, once you dig into the documentation, you will find a lot
of useful information. Several interesting features include the
installation/deinstallation process, enabling the use of a browser
other than Internet Explorer, quick access to documentation, and
customizing/viewing the migration log.
With the 10g version of OMWB, installation (in
case you have not already done it) is very straightforward.
Download the utility and unzip it. That is about as simple as it
gets. If installing OMWB on user-type PC's, you may need to
perform the installation as a member of the administrator group,
but that is true of many PC's where regular users do not have
install software privileges.
A quick mention of Windows administration: user
installs are controlled via a group policy, and the Group Policy
editor is accessed via Start>Run>gpedit.msc. You do not normally
see the Group Policy console, and the gpedit.msc command is but
one way to access it. Note the next to last setting in the list --
change the state via right click Properties.
Does uninstalling Migration Workbench require
use of the Oracle Universal Installer? The answer is: it depends
on which version of OMWB you are using. With version 10g, all you
need to do is delete the OMWB install directory. With version
18.104.22.168 or earlier, you need OUI.
Migration Workbench comes with a product
overview or Quick Tour (found under the Help menu). If you are a
non-Internet Explorer type of person, you can tell OMWB which
browser to use. For example, to use Netscape, all you have to do
is edit the state.properties file found in the bin directory. The
file generally looks like this after installation (substitute
username, host, port, and SID as necessary):
To set Netscape as your browser of choice to
see the Quick Tour's HTML pages, supply the path to the browser
executable (no quotation marks needed):
To confirm this, start OMWB and enter the Quick
The Help menu on the Migration Workbench
console is probably the best help menu from Oracle.
Notice that the list of options contains a link
to the MySQL Reference Guide, among other documents. When we cover
a SQL Server migration, we will come back here to see if the SQL
Server Reference Guide is installed (via HTML pages). The help
menu items map to the following Oracle document part numbers/PDF
files, which beats having to download these from OTN.
Oracle Part Number (PDF document)
MySQL 3.22/3.23 Reference Guide
Frequently Asked Questions
One last item on the menu to mention is the
Online Technical Support. Selecting that option shows the
What is noteworthy about this is the amount of
support you can get from Oracle -- even if you do not have a CSI.
Granted, you probably won't have immediate priority, but what
other Oracle products can you name that offer free technical
support via email?
A troubleshooting road map can be found at the
oracle web site that
is pretty useful. In part, it shows:
Finally, one other neat feature of OMWB is the
option of customizing the informational items recorded in
migration log. The types of messages are found under
Tools>Options. The types of messages you can choose to have shown
(or hidden) are informational, error, warning, summary, and debug.
In the last article, there was a picture of the
migration log. The log showed a detailed list of what took place
during the migration. A very nice feature of OMWB is its ability
to present the log to you in HTML format, and the reporting is
fairly impressive. Access the report via Report>Generate Database
Migration Report. The report is fairly well cross-referenced.
The report on the database shows the following.
Drilling down into the report, we can see that
the warning previously reported is related to a data type mismatch
All I entered for the hiredate was the date, so
the loss of more granular information (in this case) is no big
deal. If milliseconds matter, then you will want to be sure your
source reflects that information. If it is not there to begin
with, Oracle certainly cannot divine it (hence, the default time
The Oracle Model versus the Oracle database
What is the difference between the Oracle Model
and the Oracle database? In a way, you can view the Oracle Model as
Oracle's interpretation of what the source database looks like. This
interpretation is stored in the repository, and the migration takes
that picture and creates the actual Oracle database objects.
The source model is represented in the Source
You can then compare it to the Oracle Model,
shown by selecting the other tab at the bottom of the console.
This visual representation of the schema (the
objects, not the actual data) is useful in helping you understand
how the source was translated into Oracle. We will see this again
with SQL Server.
Getting Started with SQL Server
As mentioned when we entered the MySQL phase,
there will be some administrative overhead involved in getting your
SQL Server migration environment set up and configured. If this is
the first time you have laid hands on SQL Server, you will be amazed
at how similar Microsoft's flagship database product is to Oracle.
If you are familiar with Oracle's Standard Edition One (appears in
the 10g family), you will immediately see how SE1 is geared to
compete with SQL Server in the small-to-medium business market. You
can read more about SE1 at the Oracle website.
Where to get SQL Server
Microsoft has a similar setup compared to Oracle.
Oracle's OTN is Microsoft's MSDN, but one main difference has to
do with downloading Microsoft products. You can get SQL Server off
of Microsoft's main site as opposed to the MSDN site (or domain). If you want a CD, you can order that from Microsoft (see the
instructions at the bottom of the page). Note that this is only a
120-day license (as opposed to no time limit on Oracle products).
Installation is simple, but if you want a step-by-step guide to read
before hand, you'll need to invest in a book or two.
SAMS' Teach Yourself Microsoft SQL Server 2000 in 21 Days (Second
Edition) takes you through the installation process (and includes numerous
screen shots). What I really like about the installation coverage in
the book (Day 2) is the section on "Postinstallation: What Did You
Get?" If you are familiar with Access, then you will recognize the
Northwind database. Because of its popularity in Access, Microsoft
includes Northwind with SQL Server. In the SQL Server to Oracle
migration process using Migration Workbench, the Northwind database
will be the source I will use.
The two critical tasks to get set up for SQL
Server are to install it and create a user account on the Northwind
database. If you use the SAMS book as a guide, look at Chapter 10
and create a user who has Northwind as his default database (plus
grant a healthy dose of privileges for now). If you do not have the
book, you can use the online books (Start>Programs>Microsoft SQL
Look for Logging In to SQL Server. If you think
"Enterprise Manager" and follow what seems logical (keeping in mind
how OEM works in Oracle), navigate to the Northwind database users
and create a user (you).
Using the Enterprise Manager, add yourself as a
user of the Northwind database.
These screenshots show how I created my account
using my Windows login account.
You can also use Action>New Database User via the
console menu options, and if you want the step-by-step approach, use
the Create Login Wizard (via Tools>Wizards).
Once you have a usable account, login via the SQL
Query Analyzer, drill down to the Northwind database and try your
hand at running a query.
This part of the series covered more features of
Migration Workbench and helped to get you started on becoming
familiar with SQL Server. In the next part, I will go into more
detail about SQL Server (how to translate "SQL Server" into
"Oracle") and start the migration process for migrating the
Northwind database to an Oracle database. Additionally, we will take
a quick look at SQL Server's ability to connect to other databases.