Oracle Migration Workbench - Part Two - Page 3
November 10, 2004
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:
Workbench Reference Guide for MySQL 3.22, 3.23, 4.x Migrations
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.
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.