by Burleson Consulting
The Data Warehouse Development Life Cycle
Distributed Oracle Data WarehousesMaster-Slave Oracle Replication
Using Oracle snapshots to create master-slave replication has become
a very popular method for distributing warehouse data. In order to
create a master-slave configuration with Oracle, the following three
step are required:
1. Define and populate the slave database, using copies of the table
descriptions from the master catalog tables. This is done with the
CREATE SNAPSHOT command.
2. Create the propagation routines on the host database, and
establish host gateways into the slave databases. This is done with
the REFRESH clause of the CREATE SNAPSHOT statement.
3. Define snapshot log tables to hold updates that will be
propagated from the original table to the replicated table.
The foremost concern when creating snapshots is the time interval in
which the replicated tables will be refreshed. Data warehouse
developers use database replication because they cannot afford the
overhead of instant database updates (the two-phase commit issue).
When using replication, developers must determine the length of the
time lag between master database updates and slave table updates.
Some data warehouse developers choose to allow updates to occur at
predefined time intervals (say, each day), ensuring that updates
occur when the time interval is reached. Another approach is to base
slave table updates on the level of activity in the slave databases.
A predefined threshold can be defined to start the slave updates
when all of the slave databases fall below the activity threshold.
This method allows slave tables to be updated when they are not
busy, but, on the downside, end users can never be sure about the
currency of their slave database. One interesting approach to this
problem is to have each slave database automatically poll the master
when it is not busy to see if there are any updates awaiting
This is an excerpt from "High Performance
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive
Reference , with
over 900 pages of BC's favorite tuning tips &
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.