Add Streams replication to new server node
Oracle Database Tips by Donald BurlesonJanuary 10, 2013
What is the sequence of steps to add Streams
replication to a new server instance? I have the new
instance ready, I just want to replication to also go to
this new server node. What are the steps for adding
This DBA has determined the
correct sequence of commands to set up oracle replication
(and to add new node to existing configuration). Just to
mention, I use low level commands from dbms_capture/propagation/apply_adm
to get full control, not simplified versions from streams
dbms_capture_adm.prepare_table_instantiation not only
records Lowest SCN (thing you can see in description of this
function in Oracle documentation), but, what is much more
important, it writes information about Streams Data
Dictionary to Redo Log. This information is a map between
identifiers of database objects used in Redo Log and their
real names. Because on a remote database database objects
with the same name could have different internal
identifiers, Streams Data Dictionary have to be somehow
populated by capture process from source to apply database
before apply process could apply incoming LCRs.
Here is more complete description of Oracle Streams Data
If you don't know about it, you could
think that "I had already instantiated this table for
replication 2 months ago, so I don't need to do it again".
But if you know that instantiation writes mapping
information to Redo Log, you know that capture process have
to somehow scan it: If you performed a dictionary build
after table had been instantiated, and you want to create
capture process with first_scn equal to SCN of that
build, then you have to reinstantiate your table for
Actually, according to Oracle
(unfortunately, scattered) documentation, following
conditions have to be met:
1. The database
object must have been prepared for instantiation before the
new capture process is created.
2. The start
SCN for the new capture process must correspond to a time
before the database object was prepared for instantiation.
3.The redo logs for the time
corresponding to the specified start SCN must be available.
Additional redo logs previous to the start SCN might be
required as well.
If you will not correctly
reinstantiate a table for replication (even if you had
instantiated it in the past), apply process will silently
ignore corresponding LCRs without generation of any errors
(the problem I faced which led to start of this topic).
So the steps to add a new node are following:
(Optional) SrcDB: run dbms_capture_adm.build
procedure to speed up start of capture process to be
2. (Optional) SrcDB: reinstantiate objects
to be replicated if you perform build from step 1.
SrcDB -> NewDB: export/import objects to be replicated.
4. NewDB: instantiate objects to be replicated.
5. SrcDB, NewDB: set up capture, propagation and apply
6. SrcDB, NewDB: set instantiation SCNs
for objects to which changes will be applied using
information about just created capture process on peer DB (I
use value of start_scn parameter of the peer capture
7. ScrDB, NewDB: start capture, propagation
and apply processes.
It is crucial that on source DB
build is done before export, because we want also to capture
changes that will be made at [export, replication set up]
time interval. Also you have to explicitly specify
first_scn parameter for capture process. Otherwise
implicit call to dbms_capture_adm.build will be made
at capture process creation time and this process won't
capture changes from the time interval discussed above.
This sequence is correct if NewDB will receive LCRs only
from one DB. If NewDB is supposed to receive LCR's from
multiple sources, then it depends on your replication
topology. For example, I've managed to include new node to
N-way replication without stopping or locking any node (and
at the end got identical set of table records of replicated
table on all DBs including new one), but steps of course are
a bit more complex.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.