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.
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.
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?
sql>select name,status from v$database,v$instance;
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;
sql>GRANT CONNECT,RESOURCE,CREATE MATERIALIZED VIEW,CREATE DATABASE LINK TO U2;
sql>create database link test connect to u1 identified by u1 using 'test'
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
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
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.
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.