3

I am developing a web apps, this web application will be using MySQL.

Now I need to replicate my client's Oracle database into MySQL, only a few tables will be involved.. a table can be up to 2-3 million rows.

I only have SELECT privilege on this Oracle, so don't ask me to install any kind of service on the Oracle machine. I have complete control on the MySQL side however.

The replication is only one way (Oracle to MySQL). I can write a simple script to truncate MySQL table and repopulate it every night but I think this is very inefficient, there must be a better way.

Is there any free tools I can use? Expensive database replication system is definitely out of the question.

Rosdi
  • 229
  • 2
  • 3
  • 11

2 Answers2

1

If you are developing a webapp anyways, I would also create a script php/asp/whatever that queries the oracle tables and imports the data that you want into mysql - every X minutes/hours (cron or windows service).

You can keep track of the data that was imported on the last run by saving the last item imported (oracle table unique key ('id')) in a "special" mysql table that will indicate where you left off and the data that now needs to be imported.

Autobyte
  • 171
  • 3
  • 8
  • You do realize I still need to truncate MySQL table every time I start the job and start over from 0? The data could have changed, even though the oracle key id is still the same. – Rosdi May 03 '10 at 02:25
  • Why are you truncating the MySQL DB? Do you need only that day's data? and after you truncate you reload all the data or just some of it? – Autobyte May 04 '10 at 03:11
0

Sounds like you may be limited to truncate and fill by only having SELECT.

By any chance do the records have a timestamp to let you know when they were last changed?

If you don't have any other access then I am not thinking they would be willing to configure something like Oracle's Change Data Capture feature. If they would configure this for you then you could take an initial dump of the table, then incrementally read changes from the associated Change Table, then purge the Change Table when you were done. Very much roll your own. An intro to CDC is here: link .. You would just need select access to the Change Table associated with your Oracle table and ability to purge the changes once you applied them to your database.

A product I used to use ($$$ so I won't go into details but it is an Oracle Golden Gate related product) does a checksum on source and target rows first, then copies across the rows that are different. This takes some processing power to generate the checksums but can greatly reduce the amount of data moved across the wire.

David Mann
  • 251
  • 1
  • 5