Migrating MySQL to Oracle Part 2
February 14, 2008
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
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.
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.