Synchronizing two databases
Date: Thu, 8 Apr 2010 10:56:31 -0700 (PDT)
Message-ID: <8de91f03-97a8-4dd5-9d7b-1fea7f70e5f8_at_u22g2000yqf.googlegroups.com>
Database A is 10.2.0.1
Database B is 10.2.0.4
Both on Win2003 servers.
Database A is our live environment, there are billing, accounting, customer control and other features on it. There is a client server application (old last-century Forms 6i app) that has about 30 users feeding database A 24x7.
Database B is our web database. We crafted a small app with mod pl/sql Oracle Apache http server so our customers and suppliers can check the status of their business without the need to use phone or mail. This database has some of database A's tables, some with the same column structure, some with less columns. Some indexes on B does not exist on A, because as B is web and all, it needs all optimization we can use.
Due to management demand, the server which hosts database B can not see the server which hosts database A. Actually, server B can not see any computer on the internal network. Network guys made a way that I can have database A see database B, but the other way around is not allowed.
Database B must be almost real time for the web application make sense. The way we did it was creating triggers on database A for each table that needs to be replicated. This trigger will record every change on the underlying table and record them on a temporary table. Then, a schedule runs on database A every 15 minutes inserting, deleting or updating tables on database B based on what's on the temp table, through a stored procedure that uses a database link to push data from A to B.
The problem is that the web app grow out of control. This method started rendering missing and different records from A to B.
I thought about materialized views on B instead of tables, but B can not see A (only A can see B), so as far as my knowledge goes, MVs are out. There is no way for me to convince management to let B see A, we all know that Oracle http server is safe, but mgmt does not want to take the slightest risk of having our information hacked. We have quite a few tables that has sensitive data that are "not for everyone's eyes".
It will be much appreciated if any one could suggest a better idea... Thing is getting out of control! :) Sometimes takes the developers ages to find why a report on the web gives different results than one generated by the client-server application.
Any hint is much appreciated.
- Tiago