Re: What is the best way to replicate few tables between 2 Oracle 10g severs ?
From: Palooka <nobody_at_nowhere.com>
Date: Fri, 17 Apr 2009 20:02:33 +0100
Message-ID: <en4Gl.43159$cX1.31758_at_newsfe28.ams2>
Veeru71 wrote:
> We need to replicate (1-way) a bunch of tables from Server-A to
> Server-B in close to real-time.
> Server-B is located in a foreign country.
>
> Apporx. # of tables to be replicated : 50 (size : 50 GB)
> Approx amount of data that gets replicated (changed data) per day :
> 100 MB
> Oarcle Versions : 10g, Solaris
>
> Constraints (due to some security reasons):
> - Server-B SHOULD NOT have any DB-Links to connect to Server-A
> - No UserId/Password of Server-A should be used anywhere in scripts/
> code/config files, etc on Server-B.
>
>
> What is the best way to achieve this ? Initially we thought of using
> Matreialized Views, but because of the above constraint, it may not
> be feasible.
>
> Do you think Oracle Streams would work ? Would it be an over-kill ?
> Any other options ?
>
> Thanks for your help.
Given those constraints, MVs are out.
Date: Fri, 17 Apr 2009 20:02:33 +0100
Message-ID: <en4Gl.43159$cX1.31758_at_newsfe28.ams2>
Veeru71 wrote:
> We need to replicate (1-way) a bunch of tables from Server-A to
> Server-B in close to real-time.
> Server-B is located in a foreign country.
>
> Apporx. # of tables to be replicated : 50 (size : 50 GB)
> Approx amount of data that gets replicated (changed data) per day :
> 100 MB
> Oarcle Versions : 10g, Solaris
>
> Constraints (due to some security reasons):
> - Server-B SHOULD NOT have any DB-Links to connect to Server-A
> - No UserId/Password of Server-A should be used anywhere in scripts/
> code/config files, etc on Server-B.
>
>
> What is the best way to achieve this ? Initially we thought of using
> Matreialized Views, but because of the above constraint, it may not
> be feasible.
>
> Do you think Oracle Streams would work ? Would it be an over-kill ?
> Any other options ?
>
> Thanks for your help.
Given those constraints, MVs are out.
"Close to real time" is a bit vague. I'd be inclined to ask questions as to the exact requirement.
One option which springs to mind is to create a private db link from A to B; not the other way around, since that is proscribed (no password needed anywhere, btw). Then either use triggers to replicate from A to B (no data loss, but slow, and doesn't work if B is down), or to store the changes to A in some kind of deltas table in A, for asynchronous propagation from A to B by a separate process (faster on A, and still works if B is down, but there is a data lag).
Palooka Received on Fri Apr 17 2009 - 14:02:33 CDT