Oracle Migration Workbench - Part Two

Wednesday Nov 10th 2004 by Steve Callan

At the end of Part One, we were at the point where MySQL was ready to be installed. In part two of this series, we will go through some quick setup steps, create a user with some privileges, and load data - using several methods - into a MySQL database. Once the setup of the MySQL database is complete, we will be ready to start using Migration Workbench.

At the end of Part One, we were at the point where MySQL was ready to be installed. In part two of this series, we will go through some quick setup steps, create a user with some privileges, and load data - using several methods - into a MySQL database. Once the setup of the MySQL database is complete, we will be ready to start using Migration Workbench.

Starting and Using MySQL

MySQL (the company) provides user documentation in the Docs directory of where you installed MySQL (the product). Assuming you used the default installation target (on Windows) of C:\mysql, the HTML-based documentation, using a table of contents similar to what you see when looking at Oracle's list of books, is located at C:\mysql\Docs. The MySQL Tutorial book published by MySQL Press is a condensed version of the documentation.

After installing MySQL, create a configuration file (my.ini, located in C:\Windows) using the following parameters (or just cut and paste what is shown below):

# turn on binary logging and slow query logging

# InnoDB config
# This is the basic config as suggested in the book
innodb_data_file_path = ibdata1:10M:autoextend
# Set buffer pool size to
# 50-80% of computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
# Set the log file size to about
# 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
# Set flush_log_at_trx_commit
# to 0 if you can afford losing
# some last transactions

Assuming you are in c:\mysql\bin, install MySQL as a service using :

c:\mysql\bin> mysqld-max --install

Once you see that the service has been created, you can start MySQL using the Services control panel or by issuing the net start service_name (net start mysql) command at the command prompt.

To connect as "root" privileged user much like sys and system), at the command prompt enter

c:\mysql\bin> mysql -u root

Once connected (you'll see a "mysql>" prompt), set a password for root using

mysql> set password for root@localhost=password('admin');

I used "admin" for its obviously hard to guess and highly secure value; you can use whatever you want. Log out of MySQL using a "\q" and then log back in using a slightly different format (add a "-p" parameter). You will be prompted for root's password.

After logging in (without specifying a database name as a parameter), you are connected, but to what? MySQL installs with two databases: mysql and test. The MySQL database is analogous to Oracle's data dictionary, and the test database is like the seed database Oracle Universal Installer creates, but test has nothing in it. To select a database for use, use "use database_name" at the MySQL prompt.

To see what is in the MySQL database, let's "use" it and issue a "show tables;" command as root.

Note that some commands (actually, most of them) require a semicolon. It seems that it would be more consistent to allow "show tables" without a semicolon given that "use database_name" does not require it. To make things simple, end every command (except the ones beginning with a backslash) with a semicolon.

The next three steps will delete anonymous accounts, create a database named "OMWB" and create a "tiger identified by scott" user with certain privileges on the OMWB database. Try not to be overwhelmed by the complexity of the username and password combination I will be using in this example.

If you use the MySQL Tutorial book's example for creating a user, some of the privileges shown in the example grant statement (create temporary tables, lock tables, and show databases) do not work unless you're using version 4 of MySQL. The following code can be copied into a command prompt window (Hello Microsoft, why can't we just call it a DOS window?):

delete from user where user='';
delete from db where user='';
flush privileges;

create database omwb;

grant create, delete, index,
insert, select, update
on omwb.*
to tiger identified by 'scott';

If you need (or want) to re-run the create database statement, just add "drop database if exists OMWB;" before the create statement.

Getting data into a MySQL database

As an Oracle wizard, no doubt you are intimately familiar with how to create tables and insert data. There is very little difference when using MySQL. Three of the biggest differences are:

  • To make a MySQL table more like Oracle, specify the type of table at the end of a create table statement. The type to specify is "InnoDB" and is specified with a "type=InnoDB" clause at the end of the create table statement.

  • Your choice of datatypes is quite similar, but note that Oracle's VARCHAR2 is MySQL's VARCHAR, and that number datatypes are slightly different. If you just want a whole number, use "int" and for decimal type numbers, use decimal(L,P) where L is the length and P is the precision.

  • MySQL's date format may cause a problem for you as it uses a YYYY-MM-DD format. In a way, that is actually a lot more convenient as there is no doubt as to whether or not you are using day/month or month/day.

If you like SQL*Loader, you will like MySQL's data loading and outputting via an INFILE and OUTFILE. Even before getting to Oracle Migration Workbench, you have the means to transfer data, even if it is just one table at a time.

I have taken the Scott schema and arranged it into a MySQL suitable format. The root user will create the tables and perform the "load data infile" to populate the "msemp" table. The example uses the same table names but with an "ms" placed in front (msemp, msdept, and so on). You can choose how the data is delimited in the infile (comma separated or tab delimited, for example). I used tab delimited to make the data easier to read (plus that is the default), but the CSV version works just as well.

You can create the comma separated version of the infile by using

select empno||','||ename||','||job||','||mgr||','
||comm||','||deptno "MySQL data load example"
from emp;

The tab delimited version can be created in same manner, or you can use the following (I cut off the some letters so the columns line up nicely):

The command syntax to load data using an infile is

load data infile 'c:\\mysql\\load_msemp_table.txt'
into table msemp
ignore 3 lines;

The "ignore 3 lines;" takes care of the two comment lines and the blank line before the data.

Data from the scott.emp table used in
the MySQL tiger.msemp table example

7369	SMITH	CLERK	7902	1980-12-17	800		NULL	20
7499	ALLEN	SALESMA	7698	1981-02-20	1600	300		30
7521	WARD	SALESMA	7698	1981-02-22	1250	500		30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7654	MARTIN	SALESMA	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL	10
7788	SCOTT	ANALYST	7566	1987-07-13	3000	NULL	20
7839	KING	PRESIDE	NULL	1981-11-17	5000	NULL	10
7844	TURNER	SALESMA	7698	1981-09-08	1500	0		30
7876	ADAMS	CLERK	7788	1987-07-13	1100	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950		NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL	10

What follows are the commands to create the tables and use the infile to load the msemp table. Create a data infile and reference where shown below, using the backslash to escape the Windows directory path backslash separator.

use omwb;

create table msdept 
  deptno decimal(2) not null primary key,
  dname varchar(14),
  loc varchar(13)
) type=InnoDB;

create table msemp
  empno int not null auto_increment primary key,
  ename varchar(10),
  job varchar(9),
  mgr int,
  hiredate date not null,
  sal decimal(7,2),
  comm decimal(7,2),
  deptno int not null references msdept(deptno)
) type=InnoDB;

create table msbonus
  ename varchar(10),
  job varchar(9),
  sal int,
  comm int
) type=InnoDB;

create table mssalgrade
  grade int,
  losal int,
  hisal int
) type=InnoDB;

load data infile 'c:\\mysql\\load_msemp_table.txt'
into table msemp
ignore 3 lines;

The end result in MySQL, using "show tables;" and "select * from msemp;" is:

Connect as tiger and load the remaining tables.

use omwb;
insert into msdept values
insert into mssalgrade values (1,700,1200);
insert into mssalgrade values (2,1201,1400);
insert into mssalgrade values (3,1401,2000);
insert into mssalgrade values (4,2001,3000);
insert into mssalgrade values (5,3001,9999);

If you want to see MySQL's "interpretation" of the OMWB database schema, use the following at a DOS prompt and then view the resulting file:

c:\mysql\bin> mysqldump -u root -padmin --opt omwb > omwb_dumpfile.sql

This should remind you of how you can do the same thing using Oracle's export utility. This concludes the setup phase of MySQL. Take note of the fact that Migration Workbench connects to your MySQL database as the user "root." In a way, the MySQL user named tiger wasn't necessary, but creating him parallels the privileged user versus schema owner concept in Oracle.

Before leaving MySQL, issue a "\s" at the MySQL prompt and note the port number shown (TCP port of 3306 shown below). Migration Workbench will use that port number to establish a connection between your Oracle and MySQL databases.

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.

Click for larger image

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.


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

Mobile Site | Full Site