I work at a college that runs Oracle as the main database. Right now we have batch jobs that run every night to pull data into local Access databases for our dev work. But Access is getting too small for what we need and these overnight processes break all the time due to network problems and code issues. I want to get rid of these jobs so we can focus on better things.
I know MySQL pretty well so I got a test MySQL instance running. What would be the best approach to automatically transfer specific tables from our Oracle system to MySQL? I need something reliable that won’t need constant fixes.
We did this same Oracle-to-MySQL migration two years ago when our nightly ETL jobs wouldn’t stop crashing. AWS Database Migration Service saved us, even though we weren’t fully in the cloud yet. You can run DMS on-premises or hybrid - it handles Oracle-to-MySQL conversion really well with almost no downtime. The continuous replication killed our fragile overnight batch windows for good. Took about a week to set up, but we haven’t had one sync failure since. Way cheaper than enterprise stuff like GoldenGate, and AWS handles the network resilience and error recovery headaches. Check it out if you want something that actually works without building your own streaming setup.
honestly, just use pentaho data integration (now hitachi vantara). it’s got built-in oracle and mysql connectors, and you can schedule incremental loads instead of full dumps. way easier than setting up kafka infrastructure if you don’t already have it running.
I’ve hit the same Oracle to MySQL migration issues in higher ed. Network problems always mess up overnight batch jobs. Try Apache Kafka with Debezium for change data capture - it’s open source and handles network drops way better than batch jobs. Debezium grabs Oracle transaction logs and streams changes to Kafka topics, then you consume those into MySQL in near real-time. Takes work upfront but it’s bulletproof once you get it running. You could also use Oracle’s Advanced Queuing with a simple Python or Java service that processes the queue and updates MySQL. Better error handling and retry logic than what you’ve got now.
Oracle GoldenGate is your best bet for syncing Oracle and MySQL data reliably. It handles real-time replication and bounces back from network issues automatically. Yeah, there’s licensing costs and setup work, but you’ll spend way less time maintaining it later. Want something cheaper? Try Oracle Database Gateway with MySQL’s federated storage - you can query Oracle tables directly from MySQL without building complex jobs. Or go custom with Oracle’s Change Data Capture plus MySQL’s LOAD DATA INFILE. This combo only grabs changed records, which cuts down transfer times and keeps your current process mostly intact.