RE: multi-master replication

Greg,

"Data/Transaction Replication" in even a modest complex environment can be a nightmare. In my experience "Data/Transaction Replication" works best in system with few internal constraints/dependencies and external programs.

Pushing the same 300 inserts into two databases in two like tables is simple.

But have one database send 150 inserts to one db, while the other db 150 inserts the other way and the both later do updates, on all the data, while all using-firing-triggering internal or external events, while pulling form individual sequences, and maintaining data validity/consistency, all done Asynchronously is where the idea of "Data/Transaction Replication" "falls down". I'm not just talking Oracle constraints and triggers...I mean other dependent transactional programs/validation and the middle tier too.

Oracle Advanced "Data/Transaction" Replication is can work very well...I have used it...used if for Failover too. I hear that Quests SharPlex "Data/Transaction" can work well to.

The problem is that most systems are built and run serially. When we have a "final" result data set and try to "push it" through a like serially system we must un-do or halt many of the serial process that normally would effect that (now complete) transaction...this becomes a head ache and why many are never able to us "Data/Transaction Replication" as a Disaster Recovery option. How well "Data/Transaction Replication" works for your system depends on how much manipulation, constraints, dependences, and consistency your data/transaction will/must incur through the serial process.

Another major issue is "Asynchronously Bi-directional Replication". Even in very simple system with NO dependences/constraints/triggers we still need to maintain data consistency...no duplicate transactions/data. "(Data) Conflict Resolution" rules/plans are painful idea in itself, but must be addressed.

Yet another issue is *change*.
We tried 100% replication on two db's for 2 years and never got to 100%. The db/schema changed so much and so often that we were always un-doing and re-doing the replication...a good idea that was not practical it implement and maintain.

Finally "Data/Transaction Replication" is administratively intense for both day to day data issues and monitoring. And honestly it took a bit of time to truly understand and be comfortable with command-line Oracle Multi-Master Replication...I would not just trust a GUI on this one at all. Also Data/Transaction Replication doesn't often support application upgrades and nor rolling upgrades.

Not to turn you off to "Data/Transaction Replication", but many do not see the hurdles until they are knee deep into it. The devil is in the details for sure. The more simple, static, uni-directional the system, the better chance you have with successful "Data/Transaction Replication".

I have not used Oracle Steams but from what I understand it is still what I consider "Data/Transaction Replication"...and is conceptually and technically similar to Quests SharPlex. I have not used Quests SharPlex, but I did have technical meeting with their reps. In the end (of the meeting) I found all the same issues and "things" the effect Oracle Multi-Master Replication effected them to.

Again not that it doesn't work, but with data replication look for/ask for the issues and problems before the benefits for sure.

hth

Chris Marquez
Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Loughmiller, Greg Sent: Thu 10/20/2005 10:57 AM
To: 'oracle-l_at_freelists.org'
Subject: multi-master replication  

hey folks,  

I believe I may have sent this out before - so go ahead and hammer me accordingly:-) But I have an interesting requirement for an effort......  

Has anyone had any experience in trying to do multi-master replication to keep multiple databases in sync? The assumption is that there would be multiple application/database infrastructures - and the data needs to be consistent between the *stacks*. And in one specific scenario - there could be several million txn's per a 12 hour window of the day:-). A couple of the goals and objectives for this chaos are to try to be data center agnostic for the application-thus the need for the data to be consistent. The additional assumption is that this would provide a level of HA in the event there is a problem with one or more application stack (hmmm..... smells like RAC, taste like RAC - maybe it is RAC but not data center agnostic). The version of Oracle is 9206(for now), on Solaris, using HDS/EMC storage.....  

So I guess my questions are:

1. Can streams keep up in this type of transactional volume?
2. I have my own opinion of this matter - but have you all seen other methods to maintain the data in such a fashion?
3. Other technologies for this type of problem?
4. Or better yet - other potential solutions (don't do multi master but do X,Y,Z )

My personal opinion with the limited exposure to Streams is that it probably could not maintain that type of throughput, as well as placing additional stress on the host as well as the DB. So I thought would throw this out there to get some opinions, thrashing, or ideas from the list.....  

Thanks in advance

Greg Loughmiller  

--
http://www.freelists.org/webpage/oracle-l