Migrating MySQL to Oracle Part 2

1. Introduction

As you
might imagine, migrating a database and it’s 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. Moving Production Data to Development Systems

Where
are you starting, and where are you going?

It sounds
like a philosophical question, but of course, you have to ask this question
before you do any migrating.  It is the first step in planning your
migration.

Versions
of MySQL vary dramatically from 3.x to 4.x, 5.0 and 5.1.  So depending on
which version you’re starting from and what features you are currently using
you’ll have to manage those objects and create them in Oracle.  
Specifically views, roles, stored procedures, and triggers in MySQL 5.x will
have to be looked at carefully.

The
actually data move can be done by dumping MySQL data with mysqldump to csv
files, and then loaded using SQL*Loader in Oracle.  You could also write
some custom Perl code for instance, which opens a connection to the MySQL
database, copying data row by row into Oracle using associative arrays. 
Even if you use the Migration Workbench to move 4.x MySQL data, you’ll want to
do some sort of checksum checks to verify that your target data is consistent
with what was found in MySQL.

3. Oracle Migration Workbench

The very
first thing to keep in mind about the Oracle Migration Workbench is that it only
supports MySQL 4.x.  MySQL 5.0 was first released in December of
2003.  Since the upgrade from 4.x to 5.x is so simple, and most current
Linux distros come with 5.x there are probably few sites still on 4.x. 
That said if you’re on 3.x or 4.x, Oracle’s Migration Workbench may be of
assistance to you.  Those versions were quite a bit simpler too. 
Mainly your objects were tables, indexes, check constraints, primary and
foreign keys.  OMW also supports enumerated datatypes, mentioned
previously.  Oracle also claims support for privileges, and users, however
these objects are handled slightly differently in MySQL and Oracle, so your
mileage may vary.

Using
the Oracle Migration Workbench, you first login to your source db, then Oracle
installs relevant plugins to match that type of rdbms.  It then presents
you with a screen listing the "source model" and "oracle
model".  These refer to the respective schemas and the branches
reveal all the objects contained in the db.  At that point, you launch the
"capture wizard".  Step one specifies source db details, step
two the databases you want to capture, and step 3, the most important one, outlines
datatype mappings.

It is at
step 3 where you have the opportunity to specify larger sizes, or new datatypes
to hold your MySQL data in Oracle.  As much as the migration workbench
presents you with a graphical interface, and wizard to help you along, this is
the main step and as with the manual method, requires you to carefully scan
through all the datatypes of columns in your tables, to make sure they are
mapped the way you’d like.  Step 4 allows you to create the oracle
model.  You can wait on this step if you like.

Oracle
will then load the source data, and when you are ready, siphon that data into
the target Oracle database you have created.  In the Oracle docs on the
OTN website, the demo shows zero errors, and zero warnings.  Oh, if only
the real world were is neat and friendly as the marketing materials show
us.  Unfortunately, you are sure to have a whole litany of errors and
warnings here, not because Oracle hasn’t crossed every T, and dotted every I,
but rather because the process is intricate, relying on a lot of pieces being
lined up exactly so, in order to work flawlessly.  When you reach this
step in the GUI process, and you get warnings, you’ll be forced to step back to
the command line, and resolve the issues by hand anyway.  In the end your
"automatic" process will really help lead you through the process,
but will still require a certain amount of manual intervention. 

4. DB Application Migration

As
complicated as the database migration may turn out to be, it will be simple
compared to the application migration.  If only it were as simple as
changing a database connection descriptor in PHP or Perl, and all your database
independent code magically works.  The reality is that despite SQL 92
standard, each database’s implementation is subtly different.  

To start
with SQL queries may suddenly have syntax errors, as the syntax of MySQL and
Oracle may be slightly different.  Once you’ve resolved those issues,
things like the MySQL LIMIT clause will have to be converted to Oracle using
the pseudo column ROWNUM.  Beware though, as Oracle’s implementation is a
bit finicky.  It does not support the "LIMIT 5,10" syntax as in
MySQL, and those will have to be rewritten as subqueries.  In addition,
the optimizer in Oracle is sure to handle the same queries differently, so you’ll
need to go through the execution plan of most if not all your queries, to
verify that they are performant in oracle.   If you’re using stored
procedures in MySQL 5.x those will have to be rewritten.

5. Review & Test of Development Migration

This step is a collaborative effort between development, to make the code work,
and QA to test individual pieces to make sure all the different options, and
components behave as they did against MySQL datastore.

It may
turn out that some parts of your application need to be rewritten, if not for
syntax and fundamental behavioral reasons, then possibly for performance
reasons.  Again, all of this will require delicate testing long before you
are ready to do it in production.

Also,
especially for any financial data, but for any data that could have changes
with rounding, be sure that both the database migration preserved enough
precision, and also that the application will get expected precision returned.

6. Plan Production Migration

Once
you’ve run through the whole process of migrating your database, and ported
your source code, and tested it, you have a good idea of what the production
move will look like.  You should have ironed out bugs in the application,
verified that the application is behaving as before, and that new datatypes, or
changed columns are resulting in any application errors.  

Also
prepare a rollback plan in the event that something unforeseen happens during
the maintenance downtime.  Always plan for the worst, but aim for success.

7. Execute Production Migration

Once you
have all the steps ironed out, and a specific timed window setup to do the
production migration, you can perform the final migration during off hours.

You’ll
then do a final test of production systems as you did during the QA process on
test, and verify that the application is ready to go live on Oracle.  

8. Skills Upgrade

To be sure,
the migration and porting of the application has been a learning experience for
a team previously familiar with MySQL.  However, there are surely a *LOT* of new features in Oracle that
your team will want to consider.  Partitioned tables, transportable tablespaces,
flashback features, data encryption, parallel features, dataguard and other
high availability features, and on and on.  It might even pay to have your
team take some Oracle introductory classes, writing optimal SQL, useful
features in Oracle, and so on.

9. Conclusion

Migrating from MySQL to Oracle can seem deceptively simple.
 If you’re still on 3.x or 4.x, Migration Workbench may help you in the
process, but there are sure to be many manual pieces to the pie.  Plan the
process, test each step of the way, manage the final production migration, and
prepare a backup plan and you will be in good shape along the way.

»


See All Articles by Columnist
Sean Hull

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at [email protected] or visit http://www.iheavy.com for more info about consulting services.
Previous articleYan Pan
Next articleValentina 3.5.2 Update Released

Latest Articles