Unanswered: periodic Data Migration from Oracle to MySQL
I am working with a project where I have to regularly migrate data from Oracle database to MySQL database. The Oracle database resides at client and there is no administrative access. I can only query the data. For MySQL, I have administrative control.
The data being taken from Oracle database is real-time events. If a row is removed, that event is no longer valid. If a row is added, its a new event and if row is modified, it is updated event (all events are timestamped).
There are two questions:
1. What is the best way to migrate data from Oracle to MySQL. I have several solutions (ETL, Java program, etc.) but I want to know if there is any better solution.
2. Since all row changes need to be reflected in MySQL, the easiest way is to purge table in MySQL and copy all the information from Oracle. Since this is real time events and a huge number of events coming in, is there a way to only get a changeset? I can get the one for added/modified by checking timestamp but not sure how to sync the rows that are deleted in oracle.