Re: Bi-Directional replication

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 28 Sep 2016 07:48:11 -0400

Vadim, that doesn't really sound like a good design. It is impossible to do what you ask. I suspect you will need some kind of arbitration daemon that will look through the collisions and only accept the "right ones", according to the business criteria. However, mistakes will happen and the performance will suffer. Federated design following the principle that "all databases are created equal" has not been particularly successful. What you need is a hierarchical approach, in which some databases are more equal than the others, and decide which changes are accepted and which are not. That implies a master site.

On 09/27/2016 02:36 PM, Vadim Keylis 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?


On Tue, Sep 27, 2016 at 7:03 AM, Neil Chandler <neil_chandler@xxxxxxxxxxx <mailto:neil_chandler@xxxxxxxxxxx>> 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.


    Neil Chandler


            On Sep 26, 2016, at 5:04 PM, Vadim Keylis
            <vkeylis2009@xxxxxxxxx <mailto:vkeylis2009@xxxxxxxxx>> 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,


Mladen Gogala
Oracle DBA

Other related posts: