Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses Master-Slave Oracle Replication

When using Oracle snapshots, developers also have to face the issue of what to do when a slave database becomes unavailable. Should the update proceed to update only the available slave databases, or should the update wait until all slave databases are online and available for updating? If the developer chooses the simpler of the two methods--waiting for all slave databases to be available--then the developer takes the risk that a major failure of one slave database will affect the currency of the other slave databases. The developer will also have to account for the possibility that a transaction may abort due to some shortage of database resources (i.e., shortage in storage area, shortage of available tablespace, and so forth) and the previously made changes to the slave databases will have to be rolled back.

On the other hand, if developers choose to propagate changes regardless of availability, the change propagation subsystem must be able to track the changes to each slave database. The most common approach for slave tracking is to have the propagator task reference a change table that keeps a list of all changes and a set of flags to indicate which slave databases have been updated. Only after all of the slave databases are successfully updated will a row be deleted from the change database.

Another popular approach for warehouse replication is to avoid the use of Oracle snapshots and simply copy the tables using the Oracle export/import utility. Updates are achieved by rerunning nightly warehouse population jobs once for each replicated table.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.