Migration from MySQL to Oracle – using Oracle Migration Workbench

All statistics on the visits to the posts in the AMIS Technology Weblog are gathered in a MySQL database that sist underneath WordPress. We would like to do some analysis on these statistics, for several reasons: to try out the Oracle Warehouse Builder 10gR2 Beta Paris Release we have currently available, to develop a number of articles and demos around Oracle BI technology in general and of course to learn more about the visitors to our weblog: where do they come from, what days of the week do they visit, which times of the day (when is the best time for us to do maintenance, like an upgrade of WordPress to release 1.5), what topics are they primarily interested in, which browsers do they use etc. This analysis for obvious reasons is to take place from within an Oracle database. That means we have to migrate the data from MySQL to Oracle. In this post, I will roughly describe how such a migration can be done quite easily using the Oracle Migration Workbench (OMWB).

Step 1: Download all required software and documentation

I started out by downloading the Oracle Migration Workbench from OTN as well as the MySQL 4.0 plugin. The Reference Guide on Windows is here as well as the Release Notes. All Migration Workbench Documentation. I had to download the MySQL Connector/J 3.0.x driver as well (from MySQL’s Site).

Step 2: Install the software

• Install Oracle Migration Workbench by extracting the zip-file
• Install MySQL plugin – copy MySQL4.jar to plugins folder
• Create a zip-file for a specific part of the MySQL Connector/J driver and copy it to the MySQL drivers folder

Step 3: Prepare for Migration

• Create a OMWB Repository User – to store meta-data for the Captured Source and the to-be-created target. Following instructions in the doc and connected as SYSDBA in SQL*Plus, I created a database user. Note that when migration is complete this schema can be removed again.
• Ensure that the MySQL (source) database is available as well as the Oracle target database

Step 4 – Run the Oracle Migration Workbench

Run the Oracle Migration Workbench and connect as the Repository User. OMWB will proceed to create a number of tables to hold meta-data.

Run the Capture Source Wizard – this will read the meta-data for the source database: table definitions, constraints, views, triggers/stored procedures (n/a for MySQL 4.0).
Connect to the source (MySQL) database:

Review and edit the default mapping between MySQL constructs and their Oracle equivalents:

The Capture Wizard is populating the Oracle (target) Model from what is read from the Source Model:

OMWB will propose a default mapping between MySQL data-types and Oracle equivalents – it will create a design for the target Oracle database derived from these defaults. You can take matters into your own hands and override these defaults, for example to convert a specific VARCHAR column not to VARCHAR2 but to CHAR or CLOB instead. OMWB will propose to create triggers and/or sequences for the MySQL columns specified with AUTONUMBER .Next, Run the Migration Wizard.

This wizard will take the details of the Source Model as well as the Oracle (target) Model set up by now in the OMWB Repository and run the actual migration. The wizard presents a summary of what it will do:

It will then continue to create database objects and if so desired also migrate the actual data.

This screenshot shows the first stage in the migration: creation of tables, views, constraints etc. The second stage – the actual data migration – is depicted next:

Note that the migration of over 300k records took less than 5 minutes once the source model was loaded.

It is a bit late in the day, but at this point we can take a look at the meta-data held in the OMWB Repository – the Source Model and the Oracle (target) Model. We could have refined it before starting the migration. Of course, we can still remove the migration result (the target schema) and re-run the migration.

Conclusions

Using the Oracle Migration Workbench was an unexpected pleasure. It was easy to install, easy to use and it delivered the goods. In under two hours, I had installed all software, captured the source design, created the target database objects and migrated all data, some 50Mb worth of WebLog statistics. We recently used the OMWB also for an Access to Oracle migration (see post Oracle Warehouse Builder 10gR2 (beta), a first impression), and there too our findings were positive. It is clear that Oracle invests in tools that provide very easy migration from 3rd party databases to Oracle. Needless to say Oracle is not in such a hurry to provide tools for the reverse operation.

Alternatively: use MySQL ODBC driver and Oracle’s Generic Connectivity that can work with ODBC

Instead of performing this one-off migration from MySQL to Oracle, I can try to set up a live-link, that would allow me to live query MySQL from within my Oracle database. If I set up the ODBC based Generic Connectivity link from Oracle, I can create a Database Link in the Oracle database that allows we to directly access the tables in the MySQL Database. Oracle Warehouse Builder for example can use these tables as data sources for ETL operations that transfer data – insert and update – into the Oracle database (data warehouse).

See for example: How to Setup HSODBC to MySQL – (Solaris to Linux). For an example of the Generic Connectivity on Windows, see: December 3, 2004 Making a Connection from Oracle to SQL Server By James Koopmann. Note that in a similar way you can get access from your Oracle database to Excel sheets, Text and CSV files, Access Databases and Foxpro. A description intended for Oracle Discoverer but generally applicable to create a database link to an Excel Spreadsheet is found in Discoverer and Heterogenous Data Services