topic Re: Table replication over different ORACLE instances in Operating System - HP-UX

Table replication over different ORACLE instances

Enrico Venturi — Mon, 19 Mar 2007 09:53:52 GMT

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

Re: Table replication over different ORACLE instances

Oviwan — Mon, 19 Mar 2007 10:24:20 GMT

Hey

Try it with database link and triggers?

Regards

Re: Table replication over different ORACLE instances

Enrico Venturi — Mon, 19 Mar 2007 10:30:31 GMT

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

regards
Enrico

Re: Table replication over different ORACLE instances

Oviwan — Mon, 19 Mar 2007 10:43:44 GMT

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

Re: Table replication over different ORACLE instances

Enrico Venturi — Mon, 19 Mar 2007 11:07:13 GMT

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?

Re: Table replication over different ORACLE instances

Oviwan — Mon, 19 Mar 2007 11:15:45 GMT

google it, you will find a lot of information.

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

Re: Table replication over different ORACLE instances

Eric Antunes — Tue, 20 Mar 2007 10:31:56 GMT

Hi Enrico,

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

Best Regards,

Eric Antunes

Re: Table replication over different ORACLE instances

Yogeeraj_1 — Thu, 22 Mar 2007 05:03:47 GMT

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