Synchronizing data between MySQL and Oracle databases?

Question

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.

Answer 1

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.

Answer 2

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).

Answer 3

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.

Answer 4

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.