Migrate from Oracle to MySQL

Question

We ran into serious performance problems with our Oracle database and we would like to try to migrate it to a MySQL-based database (either MySQL directly or, more preferably, Infobright).

The thing is, we need to let the old and the new system overlap for at least some weeks if not months, before we actually know, if all the features of the new database match our needs.

So, here is our situation:

The Oracle database consists of multiple tables with each millions of rows. During the day, there are literally thousands of statements, which we cannot stop for migration.

Every morning, new data is imported into the Oracle database, replacing some thousands of rows. Copying this process is not a problem, so we could, in theory, import in both databases in parallel.

But, and here the challenge lies, for this to work we need to have an export from the Oracle database with a consistent state from one day. (We cannot export some tables on Monday and some others on Tuesday, etc.) This means, that at least the export should be finished in less than one day.

Our first thought was to dump the schema, but I wasn't able to find a tool to import an Oracle dump file into MySQL. Exporting tables in CSV files might work, but I'm afraid it could take too long.

So my question now is:

What should I do? Is there any tool to import Oracle dump files into MySQL? Does anybody have any experience with such a large-scale migration?

PS: Please, don't suggest performance optimization techniques for Oracle, we already tried a lot :-)

Edit: We already tried some ETL tools before, only to find out, that they were not fast enough: Exporting only one table already took more than 4 hours ...

2nd Edit: Come on folks ... did nobody ever try to export a whole database as fast as possible and convert the data so that it can be imported into another database system?

Answer 1

I recently released etlalchemy to accomplish this task. It is an open-sourced solution which allows migration between any 2 SQL databases with 4 lines of Python, and was initially designed to migrate from Oracle to MySQL. Support has been added for MySQL, PostgreSQL, Oracle, SQLite and SQL Server.

This will take care of migrating schema (arguably the most challenging), data, indexes and constraints, with many more options available.

To install:

$ pip install etlalchemy

On El Capitan: pip install --ignore-installed etlalchemy

To run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

orcl_db_source = ETLAlchemySource("oracle+cx_oracle://username:password@hostname/ORACLE_SID")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name", drop_database=True)
mysql_db_target.addSource(orcl_db_source)
mysql_db_target.migrate()

Concerning performance, this tool utilizes BULK import tools across various RDBMS such as mysqlimport and COPY FROM (postgresql) to carry out migrations efficiently. I was able to migrate a 5GB SQL Server database with 33,105,951 rows into MySQL in 40 minutes, and a 3GB 7,000,000 row Oracle database to MySQL in 13 minutes.

To get more background on the origins of the project, check out this post. If you get any errors running the tool, open an issue on the github repo and I'll patch it up in less than a week!

(To install the "cx_Oracle" Python driver, follow these instructions)

Answer 2

You can use Python, SQL*Plus and mysql.exe (MySQL client) script to copy whole table of just query results. It will be portable because all those tools exist on Windows and Linux.

When I had to do it I implemented following steps using Python:

1. Extract data into CSV file using SQL*Plus.
2. Load dump file into MySQL using mysql.exe.

You can improve performance by performing parallel load using Tables/Partitions/Sub-partitions.

Disclosure: Oracle-to-MySQL-Data-Migrator is the script I wrote for data integration between Oracle and MySQL on Windows OS.