RE: Bi-Directional replication

Oh, I wouldn't say that. I remember back in the Oracle 7.3 days being called in to help Oracle Support (way back then I ran the replication training for customers here in Oz) with a customer that had set up an EIGHT way multi-master with NO conflict resolution. Ah, those were the days! J  

Pete

Pete Sharman
Database Architect, DBaaS / DBLM
Enterprise Manager Product Suite
33 Benson Crescent CALWELL ACT 2905 AUSTRALIA

Phone: HYPERLINK "tel:+61262924095"+61262924095 | | Mobile: +61414443449 Email: HYPERLINK "mailto:pete.sharman_at_oracle.com"pete.sharman_at_oracle.com Twitter: @SharmanPete LinkedIn: au.linkedin.com/in/petesharman Website: petewhodidnottweet.com

"Controlling developers is like herding cats."

Kevin Loney, Oracle DBA Handbook  

"Oh no, it's not, it's much harder than that!"

Bruce Pihlamae, long term Oracle DBA

From: Ryan January [mailto:rjanuary_at_gmail.com] Sent: Wednesday, September 28, 2016 04:40 AM To: vkeylis2009_at_gmail.com
Cc: Oracle-l <oracle-l_at_freelists.org> Subject: Re: Bi-Directional replication  

This is impossible to accomplish at the database level. This is why it's crucial to have complete buy in from both the business and application perspective.  

On Sep 27, 2016, at 1:36 PM, Vadim Keylis <HYPERLINK "mailto:vkeylis2009_at_gmail.com"vkeylis2009_at_gmail.com> wrote:  

Good morning everyone. Thanks so much for your valuable advises. What is the best approach to guarantee that any single row in any given table is updated only in one data center?    

Vadim    

On Tue, Sep 27, 2016 at 7:03 AM, Neil Chandler <HYPERLINK "mailto:neil_chandler_at_hotmail.com"neil_chandler_at_hotmail.com> wrote:

I agree with Mark and Ryan. Here's a few thoughts from me:

• Don't do it! It's complex and a bit dangerous. Get it wrong and you have 2 (or more) corrupt DB's.
• Don't use Table-based sequences (e.g. for a no-gap sequence): you can't have no-gap sequences.
• If you are using SEQUENCEs, don't replicate them but ensure they can't produce the same value in each DB. In the past I have used stepped increments (DB-1: start with 1 increment by 10: DB-2 start with 2 increment by 10: DB-3 start with 3... etc) so rows from DB-1 always end in a 1, etc... Don't start one at 1,000,000,000,000 and the next at 2,000,000,000,000.
• database should be in forced logging mode with plenty of supplemental logging to support the keys - so you're going to get (a bit) more redo.
• Every table should have a PK or UK. If it doesn't, add a surrogate one. Trying to uniquely identify rows with multiple columns will require lots of extra redo supplemental logging (all columns) at the very least.
• consider how you will *prove* you have the same data in all DB's. What is your reconciliation process?
• Can you replicate the DDL for schema change? This can make life a lot simpler (I'm not knowledgeable about DBVisit, only Goldengate) when keeping the DB's aligned if the tool will do it for you. Doing it manually can be particularly painful.
• you will get conflicts. you need to consider the conflict resolution rules carefully as when you have a conflict that is not auto-resolved by a rule, all replication will stop until you resolve it introducing significant replication delay.
• You will probably have problems with encrypted or compressed tablespaces - check with the vendor.
• Remember, a replicated DB is not a backed-up DB. As I have had to explain to a client in the past.

Look at MOS Article "1296168.1" which gives a scripts which will review your current schema for GoldenGate Classic Capture, which is basically the same method as used by DBVisit to extract transaction change vectors.

regards

Neil Chandler

[snip]    

On Sep 26, 2016, at 5:04 PM, Vadim Keylis <HYPERLINK "mailto:vkeylis2009_at_gmail.com"vkeylis2009_at_gmail.com> wrote:  

Good afternoon everyone. I am working on designing POC bi-directional replication between two data centers using dbvisit application. What are the general best practices when designing database for bidirectional replication ? What are the general design consideration should be made from your experience for bi-directional replication?  

Thanks so much in advance,

Vadim  

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

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