Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently our application is running using Oracle 11g database. We are developing newer version of the application which runs on MySQL cluster. We need to migrate the data from Oracle database to MySQL database. This is not a direct copy of database objects. In newer version of the application schema(table structure and relationships) is changed. And audit tables also needs to be copied. Is there any way to map tables, columns from old Oracle schema and new MySQL schema and apply copy so that the data will be copied?

We are thinking doing this activity in two steps. 1. Convert Oracle database to MySQL database. 2. Writing stored procedure to copy table by table.

Is there any best practices while performing this kind of database migration from one RDBMS vendor to another? Is there any open source or commercial tools available for this?

share|improve this question
1  
There are already answers for such question on StackOverflow, please refer existing answers if you do not find answer, then create question. –  Glenn McKay Dec 4 '13 at 10:02

4 Answers 4

I recommend to do this migration in two steps.

  1. Use MySQL Workbench to migrate most of the db objects and data to MySQL (table data will be copied by this process too). See Using the MySQL Workbench Migration Wizard for details. This will leave you with manual work for specific objects like stored procedures. No tool can do an automatic migration as stored routines differ vastly between Oracle and MySQL.
  2. Us MySQL Workbench to reverse engineer the migrated db into a model.
  3. Use this model to adjust objects to your new schema structure.
  4. Synchronize the model to your server to apply the changes. Depending on what changes you made this might be non-destructive, but of course, having a backup is always a good idea.

You can repeat steps 3 and 4 as many times as you wish to incrementally adjust your schema. Synchronization will also take over any changes made in schema by others (two way sync).

share|improve this answer
    
Since reading this answer I've attempted it and it doesn't look like MySQL Workbench migration wizard will connect to an Oracle database at all. –  ryvantage Mar 24 at 12:15
    
It should be possible by using the generic ODBC connector, but be warned, there can be data type conversion problems and others, as there is no real migration from Oracle to MySQL and probably will never be. –  Mike Lischke Apr 1 at 7:01
    
So does it work or not? "should be possible" did you try it? What were the results? If there are problems, what does successfully transfer? It sounds like, even if you are able to somehow get the generic ODBC connector to work, you'll be left with migration errors and end up with nothing. "This will leave you with manual work for specific objects like stored procedures." So tables and data should transfer successfully but not SPs? Is this your experience or speculation? Any success with transferring tables and data with the method in your answer? –  ryvantage Apr 1 at 11:55
    
Ok, you want it crystal clear? Here it is: migration from Oracle to MySQL is not supported by MySQL Workbench. All my suggestions above are just that, ideas one can try out to get it at least partially working. Nothing of that is being tested, however, because, as I said, Oracle to MySQL migration is not supported. –  Mike Lischke Apr 1 at 16:03

I would prefer SQLyog for this. SQLyog provides Import External Data to streamline the transfer of data from any ODBC compliant data source to MySQL. It requires an appropriate ODBC-driver (matching the data source) installed on the system where SQLyog is running.

share|improve this answer

I recently converted an Oracle DB into MySQL using a program called DBConvert There is a free version that inserts watermarks into large tables, but that can be fixed. ;) It's not perfect and might take some tinkering to fix up after the conversion.

share|improve this answer

http://bugs.mysql.com/bug.php?id=66609

MySQL workbench does not support "Migration from Oracle DBs is not supported: http://dev.mysql.com/doc/workbench/en/wb-migration-database-concepts.html"

share|improve this answer
    
You haven't given a solution to the question. Also that bug indicates the migration utility that ships with workbench will work fine. –  James Anderson Nov 3 '14 at 7:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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