Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has been great - it replicates transactions from the Tandem-NSK source and can apply the changes into any supported database - they're using MySQL at the remote end. We're switching our billing system to Oracle, and while we could continue to use Goldengate to move these logs, I'd like to see if there's another option.

We initially chose Goldengate because nothing else could get data out of the Tandem NSK, but now that we're moving to Oracle, there may be some more native (or at least simpler) choices. I've got nothing against them - like I said, it works great - but I'm hoping that two mainstrem databases are easier to do replication between than the NSK.

Are there any products of methods that would help get transactional data from an Oracle system into an MySQL database? I'm not sure if there's any way to do this kind of replication natively (I know we can do Oracle -> MSSQL using native replication, but not any way to target MySQL that I'm aware of), or if anybody knows of a product that could facilitate this (and costs less than Goldengate).

Thanks for any suggestions!

share|improve this question
    
I've added some more detail about what I'm trying to do - I'm not interested in the migration to the new system, but in getting our transactional to the vendor. –  SqlRyan Oct 24 '12 at 16:18
    
If you already have GoldenGate, then perhaps you should consider just using it. It supports Oracle and MySQL both. HVR is a less expensive real time data integration alternative that has been reccommended to me on more than one occasion. –  Roy Jan 22 '13 at 20:23
add comment

1 Answer 1

From your question, it doesn't sound like you need true transactional semantics, and that asynchronous replication would be fine.

If that's the case, it's fairly trivial to build a replication system using triggers within Oracle to track data changes, and then replaying those changes into MySQL, using something like perl (or other scripting languages) as the glue code to talk between both systems using SQL.

This might sound like it would be a lot of overhead, but we've built numerous systems like this for our clients, and the overhead from Oracle's standpoint is pretty low; we've run it on some OLTP systems powering top 1000 websites that had hefty transactional workloads, and Oracle handled it like a champ.

share|improve this answer
    
I can understand setting up the triggers on the Oracle tables to track changes, but then how would you actually transport the changes? Are you saying I should write to a log file of some kind and have my scripting language read and transport those changes? I'd prefer the triggers do as little as possible, so they're quick and don't impact the source system any more than necessary. –  SqlRyan Mar 24 '13 at 6:39
    
The exact code for the trigger would depend on how much detail you need; for example, they could write to a table just the PK field, and then you have an outside script read the changed PK, grab the rows from the main table that match, and then insert/update rows in your target database based on that. If you need more fine grained control, you can have the triggers track more info (more fields, the dml operation, etc...). –  xzilla Mar 26 '13 at 18:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.