12 Replies Latest reply: Mar 13, 2014 7:54 AM by tvCa-Oracle RSS

    two directional replication


      dear all,

                  i want to know how to replicate a single table bidirectionally in two databases.


        • 1. Re: two directional replication

          I'm not aware of any standard solution for that, so I would do this :


          On each database, create one Database Link, obviously pointing to the other database.

          Each database/server should then run some code that checks the missing records from the local table, and then adds them from the info in the other table.


          But then comes the hardest part; missing records (based on primary key) is easy to fix. But, if you have updated info in a record, things become more complex. And because none is master of truth, you can have at 1 time, updated info in the SAME record at the same time. And that update is not necessary equal. So, what do you do then ?


          Besides from that problem, the above should work. I'm on purpose doing the SAME action on both sides (so 2 database links where technically 1 would be enough) but I do that so I :

          - have the SAME change to either database

          - have no database which is master, in any way, they're both the same, on purpose



          edit: And, if you do a delete on either one, there must be a way to know the one table misses a record, not the other table having a new record.

          • 2. Re: two directional replication



            Consider Oracle Streams. If I understand your post correctly then you are actually looking for distributed environment and Oracle Streams can accomplish that.



            • 3. Re: two directional replication

              for one table bidirectional replication i cant buy oracle stream license.

              • 4. Re: two directional replication

                941949 wrote:


                for one table bidirectional replication i cant buy oracle stream license.


                If you have Enterprise Edition licences, Streams is included. So is Advanced Replication, which is probably better for just one table. If you have Standard Edition licences, then updateable  materialized view replication works no problem.

                • 5. Re: two directional replication
                  Javier Morales

                  You have a nice tool called Oracle GoldenGate, Please, check that OBE link about how to make a bi-directional replication.


                  OGG on Linux Oracle 11g

                  • 6. Re: two directional replication

                    plz,check the steps shown below. am i going wrong anywhere,plz correct me? and this is working one way replication only how can i get two way replication?


                    TEST DB(SOURCE)

                    sql>select name,status from v$database,v$instance;

                    name   status
                    ----   -------
                    TEST    OPEN

                    sql>create user u1 identified by u1;

                    sql>grant connect,resource to u1;

                    sql>create table t1 (n number(10));

                    sql>insert into t1 values(&n);

                    sql>select count(*) from t1;


                    sql>create materialized view log on t1 with rowid;


                    ORCLDB (materialized view site OR TARGET)
                    sql>CREATE USER U2 IDENTIFIED BY U2;


                    conn u2/u2

                    sql>create database link test connect to u1 identified by u1 using 'test'

                    sql>conn u1/u1

                    sql>create materialized view t1 refresh fast start

                    with sysdate next sysdate+interval '2' minute with rowid for update as select * from t1@test;

                    sql>select count(*) from t1

                    • 7. Re: two directional replication

                      I don't think a Materialised View allows bidirectional replication

                      • 8. Re: two directional replication

                        You need to put the materialized view into a replication group, or the changes made to the view will not be pushed back to the master site. You also need to consider what conflict resolution rules you want to use if  a row is updated at the both the master site and the MV site within your two minute  replication window.

                        Basically, I think you need to study the relevant chapters of the Advanced Replication manual

                        • 9. Re: two directional replication

                          As someone who has done this: unless your application has been designed to handle bi-directional replication, it is unlikely that you'll get a satisfactory outcome. There will always be a lag between the two sites, which means you'll get data conflicts, and the built-in conflict resolution methods don't work 100% of the time, which means you'll have to monitor the DB for failures, and spend time fixing them when they occur. The various methods available also have limitations that you'll have to accommodate. E.g., last time I looked, replication via updatable MVs usually only works if you forbid deletes on your data, so you'll have to change your application to cater for that.


                          In short: just don't.

                          • 10. Re: two directional replication
                            i want to know how to replicate a single table bidirectionally in two databases.

                            At best that would be a solution to a problem and you haven't given us ANY information about any problem.


                            What PROBLEM are you trying to solve?


                            Why have you chosen that as the solution to solve it?

                            • 11. Re: two directional replication

                              we have one table say 'X'  in dev and test.

                              every time users ask to refresh table with dev  for updatable data and vice-versa.

                              for that reason i m going with materialized view replication.

                              • 12. Re: two directional replication

                                Didn't know you can do with Materialised View, but if you can, maybe that's the best method if you want to do this with 1 table only.


                                Setting up another database with only 1 table in it, would be a bit too much effort for the issue given. But, if you expand the issue to a whole series of tables, it would make sense somewhere.