Migrating MySQL to Oracle

This article is from 2006. MySQL has come a long way since then. MySQL 5.5 is very robust and feature-rich, matching Oracle in many different areas including datatypes, stored procedures and functions, high availability solutions, ACID compliance and MVCC, hot backups, cold backups and dumps, full text, and other index options, materialized views and much more. Here’s a high-level MySQL feature guide.

What really separates the two technologies is culture. MySQL, rooted in the open-source tradition is much more do-it-yourself, leaning towards roll-your-own solutions in many cases. Meanwhile, Oracle provides named and proven paths to solve specific problems.

You might also check out: Migrating MySQL to Oracle Guide which is a larger multi-part series & work in progress.

Lastly these may be helpful – Migration to MySQL – Limitations, Anomalies, Considerations & Strengths and also Oracle to MySQL Migration Considerations

Introduction

MySQL is a powerful database platform for many simple web-based applications. Some of its power and speed come from its simplicity. MySQL is actually a database without proper transactions. What this means in terms of speed is dramatic.

However, it also means you cannot roll back an update that encounters problems halfway through, and other sessions in the database will immediately see changes. There are many dramatic ramifications of this, as we’ll discuss later.

Lastly, there are limitations on dataset size. Oracle can obviously handle tables of a terabyte and larger.

However, since MySQL implements a table as one file, filesize limits as well as internal data organization, and indexing can become major limitations as tables grow to millions of rows and higher.

When you begin to hit these limitations, whether in your application complexity, or the size of your data, you may consider moving your data and application to the Oracle Database. There you will have a rich set of features both on the programming side with stored procedures, views, materialized views, triggers, and so on.

You will also have support for tables and indexes of virtually limitless size, full transaction support, and even sophisticated High Availability features such as Advanced Replication, Data Guard, and even clustering with Oracle’s Real Application Clusters technology.

With all these enticing features and robustness, you’re eager to jump into Oracle. But don’t move so fast. There is a tremendous amount of planning involved with both moving your data and porting and testing your application on the new platform. Add to that Oracle licensing, and you’ll need some time to get there.

MySQL vs Oracle – Feature Comparisons

MySQL is a database fit for some types of applications. These tend to be smaller applications or those that integrate applications with less sophisticated needs than those running Oracle on the backend.

It makes sense at this point to go through a feature comparison and see what features MySQL shares with Oracle. Here’s a more in-depth feature comparison of MySQL and Oracle.

MySQL shares with Oracle good support for database access including ODBC and JDBC drivers, as well as access libraries for Perl, Python, and PHP. MySQL and Oracle both support binary large objects, characters, numeric, and date datatypes.

They both have primary and unique keys, and as of 4.x with InnoDB, MySQL has foreign keys, and transactions including READ UNCOMMITED, READ COMMITED, REPEATABLE READ, and SERIALIZABLE.

Both databases have sophisticated language and character set support. MySQL can do table locking and recently improved to include row-level locking. What’s more, if you don’t need transactions, MyISAM tables are extremely fast.

MySQL also includes a good data dump utility which you’ll see in action below when we migrate to Oracle. And lastly both databases of course include good B-tree indexes, which no database could be without.

There are, however, quite a number of features we find in Oracle as standard, which remain missing in MySQL. Until recently that included row-level locking, true transactions, and subqueries although as of 4.x those seem to be present.

However, those have been present, and core technologies in Oracle for years, with very stable and solid implementation, you’re sure to achieve dramatic performance on TPC benchmarks. Views are still absent in MySQL, though they may be around the corner with subqueries available now.

Of course, a lot of the high-end Oracle features remain completely absent from MySQL, and may never be added. Features such as parallel query, and partitioned tables, which include a whole host of special features such as the ability to take one partition offline without impacting queries on the rest of the table.

The indexing on partition tables is sophisticated too, allowing partition elimination, and range scans on indexes of specific partitions. There are other large database features such as special functions for star queries. Oracle has terabyte databases in production, so this fact speaks for itself.

MySQL still has a somewhat limited set of index types. For instance, Oracle has a reverse key, bitmap, and function-based indexes, as well as index-organized tables. These are all very powerful features for developers who are trying to squeeze that last bit of performance out of complex SQL queries against large tables.

Although MySQL does provide some index statistic collection, Oracle provides the full set of statistics, including histograms, and makes great use of it inside the Cost-Based Optimizer.

These statistics allow Oracle to better determine the best method of getting the data for your query and putting it together for you with the least use of resources in terms of memory cache, and disk access.

This is really key for a database. When running large multi-user applications all of which are vying for data in different tables, you want to load just the data you need, and nothing more.

Avoiding full table scans by using the proper index, and using various indexes in a join operation to pull fewer rows from the underlying tables means less disk I/O, which other processes are fighting for, and less use of cache, leaving more for other processes.

MySQL still does not have privilege groups, called ROLES in Oracle.

Oracle can also provide column-level privilege control, called virtual private database and although we don’t see it used a lot in Oracle deployments, MySQL lacks this feature as well.

MySQL does not have hot backups which have been an integral part of Oracle for years. (There are hot backups now – 2012 – in MySQL.

In addition, Oracle’s RMAN has become a sophisticated piece of software and has grown to be very stable, providing block-level backups so only the data that changed can be included in subsequent backups. This makes nightly backups smaller overall. It also aids tremendously during recovery, providing a lot of automation, and assistance, during those times when you need it most.

MySQL’s method is to dump data, and further if you want to guarantee a point-in-time dump of your data, you have to lock all the tables in the database, potentially slowing down your application tremendously.

Lastly, MySQL does not have automatic or point-in-time recovery, a real limitation on heavy-use production databases.

MySQL also has some limitations on row sizes. MyISAM tables, for instance, can have a maximum of 64k of data per row, and InnoDB tables 8k per row. This does not include BLOB and TEXT types.

MySQL does not include database links such as those found in Oracle allowing you to query a table from an external database inside a query in a local database. There is the federated storage engine, but reports are that it’s rather buggy. DB Links can be useful for moving data between databases and is key to implementing advanced replication in Oracle.

Lastly, MySQL has had some database size limitations. In 3.22 it could only access 4GB of data, however, as of 3.23 the only limitation has been with your operating system and the size of files it can handle.

On Linux with LFS or RaiserFS, this limitation is effectively eliminated. However, Oracle still has incredibly sophisticated storage capabilities, allowing virtually unlimited data files to be associated with a tablespace, and thus virtually limitless-sized tables.

Updated note: In 5.5 and newer versions of MySQL there are no database size limitations. Also, with Innodb, you can use global tablespaces or one tablespace per table depending on your configuration. With most databases sitting on RAID or SAN these days, you’re getting pretty much the same deal with both MySQL & Oracle storage-wise.

Migration Preparation

So, you’ve seen what you can do with Oracle, and management has invested in licensing, and you’re now ready to get things set up in your development environment.

Now is the time to really get up to speed with Oracle. This goes for both Database Administration knowledge, as well as developer and programmer knowledge. The latter requires that you know a lot about Oracle’s features, in particular those that are relevant to your application. The former requires you to understand DBA roles, manage database files, memory structures, backups, and so on and so forth.

Thomas Kyte’s books are really excellent and highly recommended. Check out “Expert One on One” on Wrox Press, and his newer title, “Effective Oracle by Design” which is on Oracle Press. He also has a website, http://asktom.oracle.com.

Also, check out Kevin Loney + Marlene Therault’s DBA Handbook on Oracle Press. Of course, don’t forget to read the Oracle docs, which are quite good. Start with the concept manual for the version of Oracle you plan to go with.

In planning a migration, the first thing you should do is take a look at the number and types of tables in your database. Do that in MySQL as follows:

SQL> show table status

+——+——–+———+————+

+——+——–+———+————+

| t | InnoDB | 9 | Fixed |

| u | MyISAM | 9 | Fixed |

+——+——–+———+————+

2 rows in set (0.05 sec)

This output is truncated but serves as a useful example. You will see the tables, types, and a lot of other information about the tables you will be moving.

You’ll next want to review the datatypes of your various tables. CHAR in MySQL maps to CHAR in Oracle, VARCHAR to VARCHAR2, and the various Large Object types to RAW or BLOB in Oracle. DATE, DATETIME, and TIME map to Oracle’s DATE datatype, while TIMESTAMP and YEAR map to NUMBER. Lastly all of the various INT datatypes in MySQL map to NUMBER in Oracle and FLOAT, DOUBLE, REAL, and DECIMAL all map to FLOAT.

To get information about them you can use the ‘describe’ SQL command much like Oracle’s own describe:

Another way to get useful descriptions of tables is to use the mysqldump command. Here ‘test’ is the name of the database, and ‘t’ is the name of the table. Leave the table name off and the output will include all the tables in that database.

$ mysqldump test t
--
-- Table structure for table `t`
--
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int(11) NOT NULL auto_increment,
  `s` char(60) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There’s actually quite a bit more output, and depending on the version of MySQL you may see additional comment lines etc. You’ll probably want to redirect the output to a file. Do so as follows:

  $ mysqldump test t > t_table.mysql$ mysqldump test > test_db.mysql

You will also want to get a sense of which columns are indexed to be sure that they get indexed in your Oracle database. Here is an example of how to list the indexes on a table:

An enumerated datatype is one where you define a static set of values upfront. Oracle does not currently have such a datatype. The closest equivalent is a VARCHAR2 of sufficient size to hold all the various types. Migration Workbench will

do just that. If you want Oracle to enforce your set of values add a check constraint on that column.

Lastly, if you’re experiencing serious performance problems in MySQL, take a look at the slow query log. MySQL can be configured to dump queries which do not execute within a certain number of seconds, to a log file for your review.

You can then use the EXPLAIN facility, a much-simplified version of the facility found in Oracle, to tune queries for a better execution path, possibly requiring a new index on a certain column or an index rebuild. In many instances restructuring a query can be of substantial benefit.

What’s more many of these skills of tuning and optimizing queries will translate directly to Oracle. Queries are the lifeblood of your application. Bad ones can kill overall database performance by filling the cache with useless blocks of data and pushing out previously cached, and important data.

What’s more inefficient queries cause more disk I/O which impacts the overall performance of your database. These issues hold true for all databases, and getting proficient with them now will bring you up to speed faster with Oracle as you migrate.

Moving Your Data Between MySQL and Oracle

At this point, we’re still presuming that you will be moving your data by hand. This is not because we are gluttons for punishment. It is more to illustrate important points. Doing things by hand goes over each and every detail of the process so you understand it. You’ll need to when things go wrong, as they inevitably will.

So, we’re discussing moving the schema, and then the data by hand for all tables, however, you may end up following the instructions below for using the Oracle Migration Workbench, and then only doing one or two special tables by hand.

Or you may decide to use Migration Workbench to build scripts for you as a starting point, and then aggressively edit those for your particular needs. All these options are valid.

So, at this point, you need to dump data. If you want to load data with Oracle’s SQL*Loader, an easy format to start with is CSV or Separated Values file.

To dump one table named ‘t’ from the database named ‘test’ use this bit of code. Note that we’ve broken things up into multiple lines to easily illustrate what’s happening with all those messy SED commands.

You’re welcome to modify them for your needs but this works as-is. Note that ^V requires you to type ctrl-V and requires you to type ^I ctrl-I. Read your editor manual for details on inserting control characters into a file.

 !/bin/bash
  get all rows from table t of database test
  add double quote at beginning
  replace tabs with ","
  add double quote at end
  remove header line
echo 'select * from t;' | mysql test 
 | sed -e 's/^/"/' 
 | sed -e 's/^V^I/","/' 
 | sed -e 's/$/"/' 
 | tail -n +2

Now is your chance to really put all those Oracle skills to work. You want to have CREATE TABLE statements to build each table, and scripts are an excellent way to get you going. They also self-document the process.

Here is an example of how to recreate the above very simple table in Oracle. Edit a file called t.sql and include these lines:

create table T (
  id   NUMBER(10,0) primary key,
  s    CHAR(60));Save the file, and then fire up sqlplus and do:SQL> @t.sql
Table created.
SQL> desc t;
 Name Null?    Type
 ID NOT NULL NUMBER(10)
 S CHAR(60)

Now use SQL*Loader to load the CSV data you created earlier. To do that you’ll need to create a control file. This tells SQL*Loader exactly what to do. Edit a file t.ctl and put this in the file:

  LOAD DATA
  REPLACE
  INTO TABLE t
  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """
  TRAILING NULLCOLS
  (id INTEGER EXTERNAL, s CHAR)

Once you’re done, save the file, and execute the following:

  $ sqlldr your_username control=t.ctl data=t.dat log=t.log bad=t.bad

This should load your data into the tablet that you created earlier. Check the log and bad files for details, and errors.

As with the Oracle Migration documentation, and any good documentation, we’ll emphasize and reemphasize the need and importance of testing. You cannot assume that your script does things right, nor can you assume that the script Oracle’s Migration Workbench created will do things right. There are an infinite number of anomalies for any dataset, and testing your application is the only way you can verify you are in good shape.

What’s more, you also need to verify that your data is correct. Suppose you have a banking application, and you are moving customer data from MySQL to Oracle. Suppose you have records of monthly deposits and withdrawals against that account.

You move the data from MySQL into Oracle, and the web or client-based frontend is up and running again, after extensive porting, and testing. Does this guarantee that all the data is correct?

Not at all. It means the right fields are in the right places, and probably the datatypes are correct. Imagine that that customer had a very high balance, and when moving to Oracle the field size was too small, and perhaps when the data was loaded, the inserted value failed or was set to a default value like 0.

Obviously, you don’t want to find out when that customer comes calling. You want to look through the log files when the data is loaded, and then run some verification software against the db to compare values in the old db and the new db, or to calculate checksums such as running through deposits and withdrawals to make sure the current balances check out. This is really the most important step in the process and can’t be overstated.

Migration Workbench is Oracle’s Recommended Solution

Oracle’s Migration Workbench is a Java-based GUI tool that runs on various Windows versions and comes complete with wizards to help you perform your migration. The process is divided into three steps.

First, you capture your target database. This is done with a series of plugins that support various databases including of course MySQL. One plugin is available to handle 3.22 and 3.23 of MySQL and another one handles 4.x versions of MySQL.

Capturing the source database is the same as the process we describe above manually of looking at your tables in mysql, and the columns, and indexes you are using. This is practical and feasible for a small number of tables, however, with hundreds or even thousands of tables, Oracle Migration Workbench becomes more and more of a necessity.

Second, you migrate the source model to Oracle. This is the process where the Migration Workbench recreates all tables found in the source database, including columns of equivalent datatypes. We describe mappings of MySQL to Oracle datatypes above. Note that Oracle does not have ENUM or an enumerated datatype per se, but it can still migrate this data and does so to VARCHAR2 in Oracle.

The third and last step is the review the scripts that the Migration Workbench has created, make any changes or modifications, and then run them to move your data from your source MySQL database into your new Oracle database.

Conclusion

One thing that is important to remember about migration is that it will take a lot more time, and end up being a lot more complicated than you expected. I liked this about the documentation. They make it clear from the beginning that planning will be a tremendous help to you in estimating time and delivering successfully within budget.

The documentation is also very thorough in its coverage of MySQL datatypes, and how they translate to Oracle datatypes, as described earlier in this article. And of course, there is a strong emphasis on testing. The Migration Workbench provides customizable scripts that both document actions to be performed and provide a way for you to get your fingers into the works.

Keep in mind while using the Migration Workbench that it is NOT all or nothing. You can use the Migration Workbench, and then edit the scripts to leave certain tables alone, or you can migrate them all, and then drop the few you want to do by hand using the methods we describe above. Ultimately a mix of the two will probably serve your needs best, as there is always some amount of manual intervention you want to perform for certain tables.

A migration between two databases is not a trivial undertaking. You have a lot of data and an application that relies on it all being in the right format, with the right relationships. Moving to a new database, with a larger feature set, slightly different syntax, and different ways of doing things takes time and attention, but in the end, you’ll be up and running on a sophisticated, scalable, world-class database platform.

Oracle has a great set of resources on OTN devoted to migrating to Oracle. In particular, there is the Migration Technology Center