Tuesday, August 12, 2008

Migrate a Database Schema from Oracle to MySQL

Steps to migrate a DB schema from Oracle (in this case, Oracle 9i) to MySQL (in my case, MySQL 5.0). Please don't do this unless you know what you are getting into: * If you're using OS X, you'll need to have VMWare Fusion or Parallels setup, since only the MySQL GUI Tools for Windows contains the MySQL Migration Toolkit, at least at time of posting. * To run the MySQL Migration Toolkit, you'll need to have Java 5 or higher installed. * Install the MySQL Migration Toolkit for Windows. * Using MySQL Administrator, or your favorite method, create a target schema that you want to migrate to, and setup a new user for it, and give it full access rights to that schema if you're willing. (Don't forget to keep the host/port/user/pass handy for later on.) * See the video on the MySQL page for a tutorial on what to do next. * It states that there can be a huge performance hit and ton of network traffic generated if you don't have the MySQL Migration Service either running on the source (server running Oracle) or target (server running MySQL) server, and the Migration Toolkit suggests that it should be running on the source machine. In our case, the servers aren't running any Desktop UI, and they especially aren't running Windows. I haven't found the Migration Service that is mentioned yet. Not sure where to download that. However, never mind that - you can do it without it, but it will be probably almost 2x as slow. So we have to do it the slow way, but it turned out it wasn't so slow when we omitted the big table. * I told the tool to "Download Driver from the Web" but the link it is pointing to is bad. If you are looking for the Oracle JDBC driver for your database, at time of posting it can be found here. * In Object selection, if you have tables with a lot of data that you don't want transfered to MySQL, then just deselect them here. Then you can manually create them later, but you will have saved a ton of time and bandwidth during the migration. Since I was doing this for the purpose of copying a production database to a temporary development environment, I deselected a stats table with millions of rows and saved hours of waiting time. * There are several differences between the example how-to video and reality (at time of posting). The following will mention some. * The video shows the Migration method as "Oracle Standard" instead of "Oracle default" which it shows up as in the MySQL Migration Toolkit v1.1.12. * The video shows view migration with the result "The migration of Oracle views is not implemented yet." however when I migrated a view in MySQL Migration Toolkit v1.1.12, the result was "The generated SQL has to be checked manually." * The video does not show the Object Creation Options page after the Manual Editing page, but there was one for me. It asks if you want to Create Objects Online (create objects on the target database) and/or Create Script File for Create Statements. I just went with Create Objects Online, since it said if there is a problem I can fix the statement manually. * The schema creation was successful, but I got a lot of errors. Thankfully, it appears that they were all because it was trying to drop tables in the target database, which weren't there because I had created a new empty schema and given the user full rights to it (via MySQL Administrator). * Bulk Data Transfer - this is the big step (if you have a lot of data). It has a progress bar that keeps resetting and it doesn't estimate how much data you have to move. This is not fast at all when you have huge tables, but I wouldn't necessarily expect it to be. I ended up cancelling this the first time through, going back and deselecting a table that had several million rows (with the intention to go back and recreate it manually later), and then running again and went from it taking hours to only taking a few minutes. * It worked!

No comments: