2

I have 2 Oracle 10.2.0.4 servers, one located within our internal intranet network, and another located in our DMZ network for public access. For security reasons I am unable to open up bidirectional firewall rules, I can only establish a DB link from intranet -> DMZ, but not the other direction.

My requirement is to keep tables syncronized at a scheduled interval between the two databases. My initial plan was to use materialized views, but this technology requires bidirectional database links which I cannot do.

Anyone have any suggestions on how to achieve this would be greatly appreciated. I'm trying to stay away from 3rd party products as I feel there must be a simple solution out there but am happy to hear any insights.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
BJH
  • 11
  • 5
  • Are you modifying data in both databases so the replication has to be bidirectional? Or is one of the two a read-only copy of the other? – Justin Cave Mar 28 '12 at 18:04
  • Ideally, it's be great to have bidirectional updates for the application requirements. Although if you have thoughts on a "read-only" method I'd be interested to hear that as well. Right now what we're thinking for a read-only solution is some kind of data dump (transportable table space) onto a mapped network drive and then a scheduled process to upload the new data. The problem here is this is a "stupid" refresh, i.e. the entire table gets overwritten versus just the transfering deltas since the last update. – BJH Mar 28 '12 at 18:40
  • I'm not sure that I follow. Are you modifying data in both databases? And do you need the changes to flow in both directions? Or are you stating that would be nice to have but unnecessary and data only needs to flow in one direction. If so, which direction? – Justin Cave Mar 28 '12 at 18:42
  • It would be very very nice to have table syncronization between the two databases (so bidirectional). So I could have two identical applications interacting with their respective databases while keeping data consistent between the two. – BJH Mar 28 '12 at 18:48
  • OK. I'll read that to mean that you need bidirectional replication. What are the security restrictions that you have? Are you certain that you couldn't, for example, use Oracle Connection Manager to proxy the connection between the DMZ database and the internal database? Can you send any data from the DMZ database to the internal database? For example, could the redo logs from the DMZ database be copied inside the firewall so that you could use Streams? Or only what you can query over the database link from the internal database? – Justin Cave Mar 28 '12 at 19:01
  • Security restrictions are basically there's a firewall between the 2 and we can only open up ports allowing intranet-> DMZ but not the other. – BJH Mar 28 '12 at 19:07
  • We could lay the redo logs out in the DMZ space, and the intranet could periodically pick them up and apply the redo logs. Would that work with Streams? I'm not limited to just the DB Link, file transfer is acceptable as long as it obeys the firewall limitations. – BJH Mar 28 '12 at 19:10
  • So to close out my question. We ended up using streams for the 1 directional data push. To establish streams you need an initial bidirectional network traffic. This isn't possible through the DMZ. So you setup the source DB (A) with streams replicating to a dummy DB (B) named the same as the destination DB (C). The dummy DB is located on the same network as A. Once streams is established between the two, update the host file on Source DB server to point instead from B to C. This has achieved the replication I was looking for. – BJH May 17 '12 at 22:03
  • Please post the answer as an answer to your question. – Justin Cave May 17 '12 at 22:05

1 Answers1

0

So to close out my question. We ended up using streams for the 1 directional data push. To establish streams you need an initial bidirectional network traffic. This isn't possible through the DMZ. So you setup the source DB (A) with streams replicating to a dummy DB (B) named the same as the destination DB (C). The dummy DB is located on the same network as A. Once streams is established between the two, update the host file on Source DB server to point instead from B to C. This has achieved the replication I was looking for.

BJH
  • 11
  • 5