Migrating MySQL to Oracle Part I

Thursday Jan 10th 2008 by Sean Hull

This two part article discusses all of the steps involved in migrating a database and its attendant applications, questions to ask yourself, what to watch out for, and how to perform the whole thing within a limited window of downtime.

1. Introduction

As you might imagine, migrating a database and its attendant applications is no small affair.  Throw into the mix a switch of database platform, and you certainly have your hands full.  In this two part article, we'll be discussing all of the steps involved in this process, questions to ask yourself, what to watch out for, and how to perform the whole thing within a limited window of downtime.  At the end, we'll go over the process of "upgrading" your skills, be it through books and publications, forums, training, or certification.

2. Assessment of Migration Plan

Before you do anything, you will need to put together a plan or outline of what's to be done.  I know, I know, no one likes to write documentation.  But it's an important step because on the tech side, it will force you to determine what needs to be done, and on the management side, it will illustrate step-by-step the process, so they can see the complexity, consider business impact, cost of requisite systems, resources required to execute the migration, and overall timeframe.

What should this assessment include specifically?  Well first, you should investigate what version of MySQL you're using currently.  Do a review of what features you're using, such as datatypes (numeric, character, enumerated, etc), what type of storage engines you're using such as MyISAM, Innodb, and so on.  Consider the index types you're using, and additional features such as Views and Triggers that may be in use inside your database.  Also, audit the stored procedures you have in your database, as these will need to be rewritten.  Consider MySQL replication carefully as well.  If it is setup for the whole db, it will match more closely to Oracle's standby database or DataGuard feature, whereas if you're using table by table replication with the wildcard syntax in your my.cnf file, this will more likely be done using materialized views (formerly called snapshots) in Oracle.

On the Oracle side, you'll need to look of course at the version you'll be running, how the various MySQL datatypes, and objects map into Oracle.  Consider the autoincrement attribute on primary keys.  Although Oracle does have sequences, sequences differ slightly from how autoincrement works in MySQL.  There is no guarantee of consecutive numbers, for instance.  Therefore, you may experience gaps in your key values based on how Oracle caches sequences.  Consider how this might affect your application’s behavior.  Although not directly part of the migration process, you'll need to consider how you want to backup your Oracle systems as well.  The export facility fairly closely matches mysqldump on the MySQL side, and RMAN of course provides all the hotbackup functionality you might find in utilities like innobackup or the underlying ibbackup available with MySQL.

In your assessment document, make a note of all of these findings, and be as thorough as possible.  The more you do the due diligence now, the fewer gotchas you'll have later on in the process.

3. Development Migration and Setup

3a. Preparation

Preparation will involve setting up your development environment.  That includes requisitioning the right hardware, setup at the data center, installation of Oracle software, and building your starter database.  Consider Oracle's best practices when doing this setup, such as using RAID 10 for your underlying disk storage, layout your redologs, allocate sufficient system and sysaux tablespace and so on.  You'll also want to consider the character set of your new database.  Beware of US7ASCII as it can limit you from European and accented characters.  Better to choose WE8ISO8859P1.  If you may be using Asian characters in your database, you'll have to look into the National Language Services (NLS) features of Oracle, and consider multi-byte character sets.  You'll also create tablespaces for data and index.  Consider the size of your source objects.  At the filesystem you can use "du -sm dir_name" of your data directory.  Give yourself another 25-50% space to make room for growth.  

3b. Database Structure Migration 

On the MySQL side, dumping the structure of a database (which becomes a schema in Oracle) you can do the following:

$ mysqldump --no-data my_db_name > my_db_name.mysql

Because of slight differences in syntax,  you cannot run this script as-is in Oracle.   What you can do is rewrite these CREATE TABLE and CREATE INDEX statements yourself.  Obviously if there are huge numbers of objects in your database, you'll want to consider an automated way to do this.  We'll discuss Oracle's Migration Workbench in part II of this article, but for now we recommend running through the objects one by one.  This will also provide a sort of inventory of objects in your database, and force you to be thorough and pour through them all in turn.  It will also be prudent to get developers involved in this process.  By running through the tables and objects to change their syntax, they will on the one hand brush up, or get up to speed on Oracle syntax, and on the other, really comb through the objects to consider what changes might be required in their application code.  

Ultimately, application changes will be a large part of porting to a new database engine, no matter how diligent your developers may have been in writing database independent code.  So again, this manual process will force a fine tooth comb upon the intricacies of your database.

4. Conclusion

Yes, migration of a database application to a new platform like Oracle is no simple task, but it can be done, and with proper preparation, it can be managed.  Do a proper assessment of your current systems before any systems setup, and review the time, hardware, software, and people requirements with management before moving forward.

In part II of this article, we'll discuss moving the actual data from production, review Oracle's Migration Workbench which may save you some time, and discuss potential application issues, and challenges.  We'll then review the whole process, and carefully plan the final migration of your production database and applications.  

» See All Articles by Columnist Sean Hull

Mobile Site | Full Site