Automate Syncing Oracle Tables With MySQL Tables

Question

The university I work at uses Oracle for the database system. We currently have programs we run at night to download what we need into some local Access tables for our testing needs. Access is getting to small for this now and we need something bigger. Also, the nightly jobs require constant maintance to keep working (because of network issues, table changes, bad code :) ) and I would like to eliminate them to free us up for more important things.

I am most familiar with MySQL so I setup a test MySQL server. What is the best way to automate copying the needed tables from Oracle to MySQL?

Edit: I accepted the answer. I don't like the answer but it seems to be correct based on further research and the lack of other answers provided. Thanks to all for pondering my question and answering it.

Answer 1

Could you just copy the Oracle tables and then set them up as linked tables in MS Access? This way the front-end stays the same plus you keep everything in Oracle (less moving parts than exporting and importing).

Answer 2

We've done that already. The problem is the Oracle database gets pretty hammered in the day and it slows down a good bit. We are doing some pretty heavy queries and we would like to have our own copy to work with.

Answer 3

I don't think there is really anything that is going to do this. If you could setup a local Oracle database, then most likely you could as oracle has various means of keeping two databases "in sync", provided they are both Oracle.

If you must use mysql, then likely you are going to just have to write something to sync the data, this is of course always going to run in the same problems you currently have with the access "database".

You could setup something with HSODBC and triggers, but

1. I've found HSODBC to be very memory hungry
2. This is only going to add more load to your DB, which you say is already heavily loaded during the day.

If the main thing you are doing is wanting a local Test copy of your oracle database, you would be best to setup syncing with a local version of oracle, as far as I can tell from the licenses, oracle is free for development copies ( I have seen some posts to the contrary, but if you find that is the case, you could always use something like Oracle XE)

Answer 4

Please tell me at this point you have looked into some of the free tools out there, many of them GUI's to sync up Oracle and MySQL? Seek, ye shall find... :)