I have 3 Oracle database instances with the same data and scheme that I want to keep in sync, the instances are geographically separated by large distances and will have reads and writes both locally and from over the WAN. Is there anything built in to Oracle for doing this? The synchronization doesn't need to be done in real-time, it can be an end of day process that makes sure they are synced up and generates a report of discrepancies etc... Has anyone dealt with an issue like this?
A:
I haven't used this personally, but I believe Oracle's Data Replication feature will serve this need nicely.
RC
2010-04-16 17:05:20
Thanks, I'll check and see what version we are using of Oracle. How is the performance of this for resources separated by large distances do you know? I've just started looking into this, I had been told by someone working on the project that you could only do replication between 1 master and 1 slave which didn't sound right to me but I'm by no means an expert in this area.
Jeremy T
2010-04-16 17:11:36
I've never used this feature so I couldn't speak to the performance of it. Based on experience with other Oracle features/products, I would expect it to be pretty good. I've used their DB Migration features many times and have been really happy with it. As dpbradley stated, it's the Oracle Streams from the Data Replication suite you'll be looking to use.
RC
2010-04-16 17:48:10
A:
As far as Oracle native functionality, you'll be using Streams (assuming you are on 10/11g).
Somewhat time-consuming to configure, but full-featured.
[Edit in response to one of your comments]
I've seen replication work with up to three db's with conflict resolution set to first timestamp "wins" in case of PK conflicts. If most of the new/modified records originate at one of your sites and just need to be migrated to the other two you shouldn't have a problem. If you have significant DML at each site, then it helps to have a PK that is a surrogate key maintained by an Oracle sequence set to increment by 3 and starting with 1 at the first site, 2 at the second, etc., so that each site generates non-conflicting keys for inserts. I would imagine that if you have high transaction rates at each site you're going to hit some Streams infrastructure limits at some point.
dpbradley
2010-04-16 17:07:22