MySQL to Oracle DB Migration

No specific answer, but some genaral thoughts based on my experiences with migration.

I've found that there normally isn't one tool that does the whole migration job well, and by whole job I mean:

1. Fast
2. Handles all data types, scenarios

And that is from Oracle to Oracle!!

Last project we tried Oracle Golden Gate, and found there were issues with that.

We always end up with a hybrid approach, somethings like:

1. Extract all DDL manually and pre-create objects - there are weaknesses in the stagndard tools that confound them when extracting DDL, e.g. we found 10g expdp did not handle some quirky PLSQL well, so we resorted to extracting this ourselves.

2. Some tables work well with SQL Loader, others with GG, others (rare) with a custom extract and load process. We had over 3,500 tables and identified about 100 that worked better done as SQLLoader rather than GG. When I say better I mean with data handling and speed of migration. We created different groups of processing each group having a different method.

3. Once we have an overall hybrid scheme that works, we tune, mainly by splitting that task into parallel processes, both the export and import side.

All my migrations have been big projects where we have shifted from one Oracle system/server to another, always with the target being a newer version of OS and Oracle.

So, I would imagine that migration between non-Oracle and Oracle will through up even more challenges, and probably not as trivial as imply clicking a few buttons in SQL*Developer.

You may find the expected content from the SQL developer documentation at the Oracle website. There are migration information available for all Microsoft Access users, MySQL users, Microsoft SQL Server and Sybase Adaptive Server users.

You can also download the tutorial in forms of PDF (best for offline viewing and printing), ePub (best for most mobile devices) and Mobi (best for Amazon Kindle devices).

Recently, I have successfully migrated the MySQL database to Oracle database. Below are detail steps:

Operating System: Desktop Ubuntu local and Desktop Ubuntu on amazon aws

Please Note: Here I am using aws desktop ubuntu server because my mysql database was pretty big. In my case there were 800 tables, 200 views, procedures, triggers, and functions. The total size of the database was almost 20GB. In case of small database I would recommend to use local ubuntu server.

Tools Used: SQL Developer, VNCServer, Remote Desktop Client, JAVA 8, Third Party MySql JDBC Driver

1. Setup ec2 ubuntu desktop server : https://www.youtube.com/watch?v=ljvgwmJCUjw

2. Install SQL Developer on #1

• Download the SQL Developer package from this link : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

• Accept the license agreement and download "Other Platforms" for ubuntu.

• Install the SQL developer package as the following.

sudo apt-get install sqldeveloper-package debhelper openjdk-7-jdk
openjdk-7-jre icedtea-7-plugin

• Now all that you need to do is to run the command (you might have a different version)

make-sqldeveloper-package sqldeveloper-4.1.3.20.78-no-jre.zip

This will generate a debian package that you can use to install SQL developer.

• Now install the resulting .deb package using the command (Your deb might have a different version too)

sudo dpkg -i sqldeveloper_4.1.3.20.78+0.2.4-1_all.deb

• In my case, I have used java 8.

3. Once you have done with your SQL developer installation on your newly created ec2 instance with VNCServer then all you need to do is to connect to that ec2 instance with the Remote Desktop Client by default available in your ubuntu local machine.

• Use IP:1 with user/pass setup for VNCServer in #1
• You can see the remote ec2 ubuntu desktop server. You have to grab the keyboard inputs from the Remote Desktop tool if you want to tab inside the remote server.
• Once you get connected with the remote client, open SQL Developer from the terminal or from the explorer.

sqldeveloper

• Follow the migration steps provided by Oracle corporation: http://www.oracle.com/technetwork/database/migration/mysql-093223.html

Please Note: While following the migration steps provided by the oracle they will ask for the destination database connection i.e. oracle database connection. This is not the database where your MySQL database will be migrated. Instead, this database connection will be used for the migration process. Your database connection user must have user and database create privileges. Once your connection have user create privilege, then migration process automatically create the corresponding database user in Oracle database[if you have mysql_test_db in MySQL database, same mysql_test_db will be created in Oracle db too].

I recently used sqline's tool http://www.sqlines.com/cmd to convert a dump from mysql in the form of an .sql script to an (almost) Oracle-compatible sql script.

sqlines31113\sqlines.exe -s=mysql -t=oracle "-in=$infile"

I just had to (semi-manually) fix some things in the output and then I could run it on my oracle database.