Oracle Migration Workbench - Part 1

Wednesday Oct 27th 2004 by Steve Callan

By the end of this series, not only will you be familiar with two other widely used and popular database systems, but you will also know how to migrate data from these systems into an Oracle database.

Let me start with a bad news/good news situation. The bad news: as far as job searches are concerned, it is becoming readily apparent that knowing only Oracle is not good enough anymore. Personally, I have taken major hits for not knowing SQL Server and have heard some static about DB2. The good news: ignorance is curable through education, and if you are somewhat adept with Oracle, learning the mechanics, architecture, and administration of other relational database systems is easy (or easier, anyway). You know there are tables, you know about constraints, and you probably have some coding experience (why do SQL Server people seem to think triggers are the end all and be all of database programming?). In addition, of course, you have dealt with the wicked witches of the East and West (backup/recovery, and performance tuning).

In this series, I will cover two of the "other guys" and go into detail about leveraging what you know about Oracle into quickly assimilating, or at the least, becoming conversant with the details of SQL Server and MySQL. Why those two systems, you may be asking. I believe the reasons for covering SQL Server are fairly obvious, but the selection of MySQL may not be readily apparent. If you are looking at this article via a databasejournal.com link, note the "php" in the URL. The PHP scripting language is frequently used with MySQL and many web sites use a PHP/MySQL combination.

MySQL compares quite favorably against Oracle and SQL Server in terms of speed, performance and scalability. One area where MySQL far exceeds Oracle - in a good way - is in pricing. If not free, depending on your use of MySQL, then you are looking at mere hundreds of dollars in licensing fees as opposed to Oracle's many thousands. The applicability of using databases with web sites is abundantly obvious. How does all of that happen? The "how" of connecting Oracle to a web site via MySQL should be of interest to you. Being able to tie in an Oracle database to a web site via MySQL graduates you from your shrink-wrapped application to deploying your application on the web.

What tools are out there to help you migrate data to (or from) Oracle? The focus of this series is on Oracle's Migration Workbench (OMWB). The product description of OMWB is shown below.

The Oracle Migration Workbench (Migration Workbench) is a tool that simplifies the process of migrating data and applications from non-Oracle databases to Oracle. The Migration Workbench allows you to quickly and easily migrate an entire database (data and schema, including triggers and stored procedures) in an integrated, visual environment. The Migration Workbench employs an intuitive user interface and a series of wizards to simplify the migration process. To ensure portability, all components of the Migration Workbench are written in Java.

Before getting into the details of acquiring, installing, and using OMWB, the remainder of this article will help you get acquainted with MySQL. In Part 2, I'll provide some sample data for a MySQL database that we will use for migrating to an Oracle database. Subsequent parts of this series will cover the same approach for SQL Server.

More about MySQL

If you could look only at the text on Oracle's education/certification pages and the counterpart pages found at MySQL's web site and could not see the name of the product, you almost would not be able to tell the sites apart. At http://www.mysql.com/training/, you see links to training, certification, and partners.

Which web site is this from?

XXXXX regularly offers training courses around the world, in-house training courses, and offers certification for developers who want to prove their expertise in using and deploying XXXXX database products.

XXXXX training courses are delivered regularly all over the world by our expert trainers, who work closely with our team of developers to make sure their students learn everything they need to know about using XXXXX software to the fullest.

And this one?

Our training representatives will work with you to customize solutions to help your work group, department, or global enterprise achieve the results you envision.

The first two quotes are from MySQL, and the third one is from Oracle.

With respect to certification, the motivation for being certified is also similar.

Getting certified with XXXXX can bring you the credibility you deserve for your knowledge, skill, and experience on the job as [a certified user] and will provide you with a market recognized credential that can lead to success.

Certified users of computer software often find that their proven credentials help them get farther in their field, giving their personal careers a boost, and bettering their company's chances of retaining old and getting new customers.

The top quote is from Oracle and the bottom one is from MySQL. Without getting into the merits of certification, spending the time and effort to get certified is worthwhile in and of itself for the simple reason that you are going to learn something (anywhere from a little to a lot) about a product.

Something to consider about becoming proficient in designing and managing database (even if it uses Oracle just a little bit) driven web sites is the potential to work with Oracle even more. Consider this extract from a recent posting on Monster:

...seeking an experienced MySql DBA to work on new projects. This position will involve 50% DBA work with MySql, and 50% split between development, SQL Server, software testing, etc...

And these from Dice:

Seeking strong Data Base Administrator. Will provide support for company's MySQL & Oracle databases on an IBM/Red Hat platform.

We are currently seeking a Junior Database and Systems Developer and Administrator who will develop and maintain database applications, install, maintain and tune Oracle and MySQL databases and UNIX/Linux systems in a distributed environment.

The point of all of this is to state the case that MySQL is used by many organizations in conjunction with Oracle.

Where to get MySQL

Knowing about and using the features available at Oracle Technology Network (OTN) will give you a leg up on navigating your way through MySQL's web site. With minimal effort, you can start downloading MySQL from http://dev.mysql.com/downloads. For Windows, the download (of version 4.0, the current general release) is just over 24MB in size. Just like Oracle, MySQL comes with free documentation. Read on before you start downloading MySQL.

Click for larger image

If you want a good, get your feet wet type of book, MySQL Tutorial, published by MySQL Press is certainly a good start. Another book, which combines PHP and MySQL and contains an overview of MySQL database administration, is SitePoint's Build Your Own Database Driven Website Using PHP and MySQL (get the newly released 3rd edition).

The tutorial book from MySQL Press is very straightforward and will help you install and configure a working MySQL database in short order.

If you are going to download MySQL for use with Oracle Migration Workbench, take note that OMWB supports up to version 3.23 of MySQL. The current version is in the 4.0 series, so you will have to navigate to the archived products section. If you use this link (http://downloads.mysql.com/archives.php?p=mysql-3.23&o=-win), scroll down to the bottom of the page to get the 12.8M 15 Sep 2003 version for Windows (or whichever platform you want to install MySQL on).

Where to Get Oracle Migration Workbench

If you are thinking you already have OMWB because you installed the Enterprise Edition, you are wrong. OMWB is a separate utility you acquire separately from the RDBMS product. Like most every other Oracle product, you can get OMWB from the OTN website (look under Technologies, Utilities, and Drivers heading at http://www.oracle.com/technology/software/index.html, in the middle column about halfway down). I will cover the installation of OMWB in the next article, but if you want to get started on your own, you can take the Quick Tour that comes with this utility.

In Closing

There is a lot of material to cover in this series, but at the end of it, not only will you be familiar with two other widely used and popular database systems, but you will also know how to migrate data from these systems into an Oracle database.

» See All Articles by Columnist Steve Callan

Mobile Site | Full Site