0

I'm trying to migrate an Oracle schema as well as all of the data within the DB to a MySQL database. I want to then make an EC2 app on AWS to kick off whatever I've written for this. Are there any approaches that one might say is better than another (for instance, use Ruby/Rails instead of Perl)? Is there a tool that I might be able to wrap into an app and place it on my EC2 instance?

Full disclosure, I'm pretty much a complete newbie when it comes to AWS, so if there is something really basic that I'm missing (you could use some type of Linux-based MySQL Workbench-like tool) I would definitely appreciate that.

I realize this is a bit specific, thank you in advance!

  • Why MySQL? Why not PostgreSQL? MySQL might be a good choice for you, but did you consider other options? – Colin 't Hart May 30 '15 at 14:37
  • AWS provides hosting for both Oracle and MySQL: aws.amazon.com/rds – teambob Jun 4 '15 at 0:25
1

There are two tasks here. The migration to MySQL and setting up AWS. Setting up AWS is off topic here - try Superuser or Stackoverflow.

You are in for a tough job. MySQL is a much simpler database and does not have as many features as Oracle. Also many things in Oracle are non-standard making it difficult to migrate to / from.

The process is basically as follows:

  • Dump Oracle database schema / DDL. You will end up with a file full of CREATE statements
  • Update the DDL to allow for types that Oracle does not support. Read the MySQL manual. Use InnoDB as the backend
  • Dump Oracle functions
  • Rewrite the Oracle functions. This is probably going to be the most time consuming step. You will have to look at the Oracle and MySQL documentation
  • Run your converted DDL and functions against your new MySQL database
  • Dump the data from Oracle
  • Restore the data into MySQL. You may have to temporarily disable some contraints
1

We recently migrated Oracle 10g/11G databases to AWS and some databases we migrated to MySQL. We used Attunity Replicate for the Oracle->MySQL, which let us keep the AWS database in sync with the Oracle database until cutover. The issues you will hit as other replies indicate are stored procedures and functions, some data type issues, issues with Oracle sequences. Actually we migrated to RDS in most cases although some we kept in EC2.

1

There is a tool in Python-land called etlalchemy that will take care of this for you in 4 lines of code.

To install:

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

To run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# Migrate from Oracle -> MySQL
src = ETLAlchemySource("oracle+cx_oracle://user:passwd@hostname/SID")
tgt = ETLAlchemyTarget("mysql://user:passwd@aws_hostname/dbname",
                          drop_database=True)
tgt.addSource(src)
tgt.migrate()

This will carry out the following steps to migrate your database:

  1. Migrate Schema
  2. Migrate Data
  3. Migrate Indexes
  4. Migrate Constraints

You will have to port the Oracle functions yourself, but 90% of the work is done with etlalchemy out-of-the-box. The solution is free, open source, and works across other RDBMS's including PostgreSQL, SQL Server and SQLite.

(*Note: I am the creator of this project, so please reach out if you have any trouble using it! You can read more about it here)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.