||Oracle Replication Tips by Burleson
Oracle replication Assigning a Prefix Based on Site
A more flexible (and scalable) approach is to have all sites start their
sequence at 1
and add a site prefix to the sequence number to generate the key. At
NAVDB.WORLD, the first three rows created would have the keys navdb1,
navdb2, and navdb3. At the master site MYDB.WORLD, the first three
would be mydb1, mydb2, and mydb3. As these rows are propagated none of
the keys will collide.
In real life, keys are not always created on sequences. In cases
such as the author_key column in the PUBS schema, you may need keys
generated from a central location. All master sites obtain their
keys for that table from the central repository. You must either
require that only that site creates the records or create a
procedure on the remote site that will fetch the next key from the
depository. For efficiency, you could create a trigger that
pre-fetches a number of keys and places them in a table and provides
them as needed on the local site, fetching additional keys as
It is always a much better solution to use the natural key of the
table, if one exists, rather than a derived key. A natural key can
avoid a great deal of trouble.
As you can see, conflict avoidance must be part of your replication
planning. Once the replication environment has been created and is
operating, it is much harder to avoid conflicts. Also, insure that
your conflict avoidance method is flexible enough to handle adding
and removing master sites.
This is an
excerpt from Oracle Replication By Rampant TechPress (only $19.95).
You can click here to order a copy and get instant access to the code
Get a Personal Oracle
The author is now offering personal mentors for
Oracle DBAs where you can have an Oracle expert right at your
fingertips, anytime day or night. We work with hundreds of Oracle
databases every year, so we know exactly how to quickly assist you
with any Oracle replication question.
You can get me personally, or any Oracle Certified replication DBA
with more than 20 years of full-time IT experience.