Re: Doubts on Data Guard and Replication

What you are describing is peer-to-peer, or multimaster, database replication. Oracle advanced replication is an option. The application and database need to be designed to support peer-to-peer replication. I.e. not every application and database design will perform correctly by just enabling peer-to-peer replication. There are a number of issues to consider. I would thoroughly review the documentation and run test cases against every case you can think of.

Requiring data changes to appear at both locations at the same moment
(synchronous replication) costs you availability. With synchronous
replication when one of the databases is unavailable, the other database also stops processing data. When the network fails, both databases will essentially become unavailable - queries will still run though. In addition to network bandwidth, latency may also be an issue.

You might need to use asynchronous replication to increase availability and performance. If you can go asynchronous then instead of Advanced Replication, consider using Oracle Streams or SharePlex. With asynchronous replication, changes to the same column might occur at both peers "at the same time" creating a conflict. Procedures to resolve these conflicts to a consistent value must be created for every table - or your tables get out-of-sync. Conflict resolution might require schema and application changes. Consider what could happen when there is a conflict on a value that represents an amount. If absolute values are used the conflict might resolve into an incorrect value - deltas have to be used.

Other interesting issues to consider: primary keys (make sure you have them); sequences; triggers; cascades; synchronizing/repairing out-of-sync tables or the entire database; schema changes; security; replication latency; rolling upgrades?; parallel propagation; deadlock
(avoidance); transaction interference - can replication interfere with
transactions? e.g. what happens if queue space is exhausted or the database is quiesced?

Switching to a standby database does incur *some* downtime. It's not an automatic event. The application will probably fail and need to be restarted once the standby comes back online. If you can not afford any downtime you will probably want RAC. Using Oracle's Transparent Application Failover (TAF), applications can fail over to another instance transparent to the end user. Alternatively, you can failover to another peer in your replicated environment. However, there may be some data loss that has to be handled. If a few minutes of downtime is acceptable, a server cluster might be sufficient. If you can afford a longer outage then a standby database should be sufficient. Received on Mon Nov 22 2004 - 15:13:17 CST