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.

I want to sync data between Oracle & MySQL databases. What is the best possible way to achieve this?

Are there any tools/software available?

This is not a one-time but an ongoing operation. The data set is also quite large, containing almost 300 tables.

share|improve this question
2  
Is this a one-time operation? Or an ongoing operation? If ongoing, how frequently? How large is the data set? –  Justin Cave Feb 21 '12 at 17:46
    
There are DBLinks in Oracle to Link to multiple database engines: stackoverflow.com/questions/3992573/… –  Karlson Feb 21 '12 at 18:17
    
Which direction would you like to go in? The Title says MySQL->Oracle and the question states the opposite. Does this need to happen in realtime, or be batched? –  FreshPhilOfSO Apr 27 '12 at 16:11
add comment

migrated from stackoverflow.com Feb 23 '12 at 19:08

This question came from our site for professional and enthusiast programmers.

4 Answers

A tool specifically setup for this task is Oracle GoldenGate. It supports bi-directional replication amongst others between Oracle and mysql. For documentation start here: otn Oracle GoldenGate overview

For a onetime operation cheaper alternatives exist but for something that should be running and keep running for longer periods of time, GoldenGate is your friend.

share|improve this answer
add comment

Take a look at Tungsten Replicator, which can replicate MySQL to Oracle and Oracle to MySQL (this is not a one-time import but an ongoing replication).

share|improve this answer
add comment

If it is a one time operation, it is really easy to import an Oracle database using the Workbench suite http://en.wikipedia.org/wiki/MySQL_Workbench

If you need to sync frequently, one option may be a cron job that run a mysqldump that only dump certain tables and pipe it into a file that is inserted to Oracle. This cron job may run as often as necessary.

The cron may run a dump query like

mysqldump -uusernavme -ppassword --replace --complete-insert --no-create-db --where='changed>(UNIX_TIMESTAMP()-86400 )' databasename tablename_to_be_dumped | command_to_import_to_Oracle

--no-create-db will avoid dumping CREATE TABLEs

--where='changed>(UNIX_TIMESTAMP()-86400)' Will only dump tables changed the past 24 houres, if the table has such a time stamped column named changed

--replace will will force an update of records in the destination database having same PRIMARY KEY as the exported record.

share|improve this answer
add comment

SQLyog has Import External Data feature which will streamline the transfer of data from any ODBC compliant data source to MySQL,

This import mechanism provides powerful and effective features, such as: Incremental import: Using special SQL statements and Triggers you can perform conditional data import. For example, you can run the import procedure daily and order it only to import data that appeared during the last day.

Import sessions storage for future use: You can save your import session with all its settings and preferences and later use it repeatedly.

Import sessions scheduling: You can order your import session to be scheduled, and SQLyog will automatically create a standard MS Windows scheduled task where you can specify your schedule preferences depending on your needs.

Also you can schedule a job periodically using this tool, I hope this tool will help you out.

share|improve this answer
add comment

Your Answer

 
discard

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