Help : a simple database replicate (recovery) design
I am bulding a mission-critical financial application, which is backed by a Oracle 8i DB (Master DB) . We have a replicate DB (Slave DB) running on Data Recovery (DR) server at remote site. This 2nd DB acts as a recovery backup. The objective is to building a system that will make sure all the changes imposed on Primary DB will be reflected on Seconrdary DB.
This is a pretty standard scheme for any company. I know there're vendor software that serve this DB replication purpose. In fact, we have been using Vertitas Volume Replicator (VVR) to perform replication from Master DB to Slave DB. However, management for some reasons thinks we should get rid of VVR. Major reasons are:
1) Unstable (it keeps crashing on us, and we dun have people who know how to manage it. Learning curve is steep for VVR)
2) Insufficient support (We're in asia and support is US-based that can be reached only by email)
So, we would like to build a system that will replace VVR possessing only its basic function. (synchronizing data between Master and Slave DBs) -- In case of Master DB crashing, Slave DB will have the up-to-date data just like master DB before crashing.
It might sounds easy. However there're several challenges.
1),what to be synchronized? Should be synchronize both local and remote query can commit? How can we make sure that data are consistent across these 2 DBs? The WAN connection between 2 DBs is 4mb/sec. So performance inversely correlates with the degree of data consistency.
2) simulates all kinds of corner cases.. (master DB crashes, WAN connection crashes..)
One teammate suggested using log-based approached.