Table replication over different ORACLE instances

[Enrico Venturi]

Hello colleagues,
I've a question about distributed databases synchronization over geographic networks.
We've an ORACLE instance rdbms_A in a platform host_A where an application app_A is running.
We've an ORACLE instance rdbms_B in a platform host_B where an application app_V is running.
rdbms_A and rdbms_B have different schemas.
rdbms_A has a table "paths" composed by a set of attributes; a view "paths_4BM" is built on top of "paths": this view contains a subset of "paths" attributes.
rdbms_B has a table "paths_RM" identical to "paths_4BM".
The data insert sequence is the following:
> the data are inserted, modified or deleted only in rdbms_A; only "paths" is modified (and the view changes accordingly).
> the data in rdbms_B should be kept aligned.
We need to keep aligned "paths_RM" to "paths_4BM" in an automatic way.
Are there any ORACLE embedded mechanisms to automatically updated tables on RDBMS B when the (linked) data on RDBMS A change?
We need a "per table" relation.

Thanks
Enrico

[Oviwan]

Hey

Try it with database link and triggers?

Regards

[Enrico Venturi]

I'm confused:
what should I do?!?
Please go into details.

regards
Enrico

[Oviwan]

Create a Database link in rdbms_A to rdbms_B.

Synchron:
now create a trigger (on insert or/and on update event) on table "paths" which writes the data directly through the database link in the table of the rdbms_B database.

Asynchron:
Or you can write a job in rdbms_B, that for e.g. all two minutes reads the new data in table paths of rdbms_A.

Regards

[Enrico Venturi]

ehm... what do you mean with "create a database link in rdbms A to rdbms B"?
How can I do it?
Are there any specific ORACLE commands?

[Oviwan]

google it, you will find a lot of information.

e.g.
http://www.psoug.org/reference/db_link.html

[Eric Antunes]

Hi Enrico,

If you are on 10g, read Metalink Note 394575.1 about streams replication.

Best Regards,

Eric Antunes

[Yogeeraj_1 · Accepted solution]

hi Enrico,

one way to achieve this is to create a synonym using a database link as follows:

CREATE OR REPLACE PUBLIC SYNONYM paths_4bm FOR .paths@db_link_to_rdbms_A;


hope this helps!

kind regards
yogeeraj