Re: What is the best way to replicate few tables between 2 Oracle 10g severs ?
From: Phil H <phil_herring_at_yahoo.com.au>
Date: Tue, 21 Apr 2009 17:08:08 -0700 (PDT)
Message-ID: <26f97178-e933-4d8f-8636-b8054d8a5138_at_y6g2000prf.googlegroups.com>
One solution: triggers on the "replicated" tables log the PKs of changed rows to tables. A batch job runs every 2 minutes, reading the changed rows and pushing the changes to database B. The change log is deleted once all the changes are committed.
Date: Tue, 21 Apr 2009 17:08:08 -0700 (PDT)
Message-ID: <26f97178-e933-4d8f-8636-b8054d8a5138_at_y6g2000prf.googlegroups.com>
One solution: triggers on the "replicated" tables log the PKs of changed rows to tables. A batch job runs every 2 minutes, reading the changed rows and pushing the changes to database B. The change log is deleted once all the changes are committed.
I've done a similar thing, and it can usually be made to work, depending on your DB design. For example, LOBs will be a problem. You also have the additional load of the batch process on your A database.
Then again, all you're doing is reinventing fast-refresh materialized views, which seems pointless. Permit me to ask: why can't you have a DB link from B to A? The DB link owner can have as few privileges as it needs to replicate the data - basically, read access to the base tables and their MV log tables. There's nothing there that it wouldn't have in the B database, anyway.
- Phil