Starting Oracle Migration Workbench
First of all, become
familiar with the MySQL reference guide that installs with OMWB. You will find
this in <base directory where you installed it>\Omwb\docs\mysql\toc.htm,
or you can look for this at OTN:
Oracle® Migration
Workbench Reference Guide for MySQL 3.22, 3.23, 4.x Migrations
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP
Part Number: B13911-01
Release 10.1.0 supports 4.x,
but elsewhere (e.g., the survey you fill out before downloading OMWB), Oracle
says Migration Workbench does not support 4.x (4.0 and 4.1). That's why we're
using a 3.23 release for this series.
It appears that the MySQL
datatypes used in the OMWB database will successfully translate to Oracle.
MySQL integer (INT) data translates to Oracle Number(10,0), which may be a
consideration when using large numbers for identification/ID purposes.
Prior to using Migration
Workbench (we will be using the "Oracle" option), create a user in
your Oracle target database with at least the CONNECT and RESOURCE privileges.
In my database, I created tiger/scott.
Elsewhere in Oracle's
documentation, you are directed to grant a laundry list of privileges to your
user. Cut and paste the code below (using "tiger"). With all of those
privileges, why not just grant DBA to this user? About the only thing this user
cannot perform is import/export full database. Whatever.
GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE,
ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE,
CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER,
CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE,
DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER,
DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE,
UPDATE ANY TABLE TO tiger;
Start Migration Workbench
using the OMWB MS-DOS batch file found in (for example) c:\omwb\bin and enter
your connection information.
Select Yes to create the
repository.
If you do not already have
the plugin, you will need to download two items. The first is Oracle's plugin
for 3.23, and the other is a JDBC-related file from MySQL (the links for both
are on the Migration Workbench download page). If you do not have one or more plugins
installed, you will see the message shown below.
Unzip or extract the file
from MySQL (named mysql-connector-java-3.0.15-ga). Navigate to the mysql-connector-java-3.0.15-ga
directory and send the com folder to a zipped file name mysql-connector-java.
Then (it's almost over), copy or move that zipped file to your OMWB drivers
directory.
After placing the MySQL jar
file in the OMWB plugins directory, and copying the zipped mysql-connector-java
file to the drivers directory, the next time you start OMWB, you will see:
If your plugin installation
was successful, you will be directed to a Capture Wizard window.
Enter the password for root.
I used localhost for my server, confirmed the 3306 port number from before.
Select the OMWB, (or
whatever you named it), database. Note that the MySQL database does not appear
in the list of available databases.
Accept the data mappings as
shown.
Select Yes and click Next.
Confirm your settings and
click Finish.
If all goes well, you will
see numerous information messages pass by and a final results window.
Click OK to dismiss the
results window, and OK to dismiss the status window. Then click Yes to create
the schemas.
The next window is a bit
confusing (it looks like you are starting over), but the subsequent windows now
deal with the Oracle database.
Instead of tiger (to ensure
sufficient privileges), I logged in using the system account.
The wizard detects this is
the first time, so the database will be configured.
Select Yes, that's why we're
doing this in the first place.
Everything is already
selected, and that is what we will accept.
Confirm and click Finish.
There is one error and one
warning. What happened?
We will investigate the
error and warning in the next article. Did the MySQL OMWB database and its data
get migrated? Let's see.
The migration process took
care of the different DATA data type format. The migrated tables belong to an
Oracle user named root. Change root's password to something you know and
connect as root and view the data in the migrated tables.
What happened to Tiger?
Tiger became the repository owner, not the schema owner. More on that in the
next article.
In Closing
This part of the Migration
Workbench series has given you enough information to create a MySQL database,
import and insert data into it, and migrate it to an Oracle database. That is
not a bad first attempt at using this tool, plus you now know enough to find
your way around a MySQL database. In the next part, I will go into more detail
about what is available in the Oracle Migration Workbench console and start the
groundwork for using SQL Server in the same manner as we did with MySQL.
»
See All Articles by Columnist Steve Callan
» 
Comment and Contribute
