The
example in this article illustrates the replication of a source database (
DB10GSR1 ) to a destination database ( DB10GSR2 )
1. Set up ARCHIVELOG mode.
2. Set up the Streams administrator.
3. Set initialization parameters.
4. Create a database link.
5. Set up source and destination
queues.
6. Set up supplemental logging at the
source database.
7. Configure the capture process at the
source database.
8. Configure the propagation process.
9. Create the destination table.
10. Grant object privileges.
11. Set the instantiation system change
number (SCN).
12. Configure the apply process at the
destination database.
13. Start the capture and apply processes.
1. Set up ARCHIVELOG mode. The Streams
capture process reads information from the redo log files of the database.
2. Set up the Streams administrator.
Don't use this account for any other purpose, and don't use the SYS or the
SYSTEM user as the Streams administrator.The Streams administrator creates some
tables in its default tablespace.
CREATE
TABLESPACE streams_tbs DATAFILE
'/u01/app/oracle/product/10.1.0/db_1/oradata/db10gsr1/streams_tbs.dbf' SIZE
25M;
Now
create the Streams administrator user in the database, as follows:
CREATE
USER strmadmin
IDENTIFIED
BY strmadmin
DEFAULT
TABLESPACE streams_tbs
TEMPORARY
TABLESPACE temp
QUOTA
UNLIMITED ON streams_tbs;
Now
grant the CONNECT, RESOURCE , and DBA roles to the Streams administrator:
GRANT
CONNECT, RESOURCE, DBA TO strmadmin;
Use
the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package to grant
the required privileges to the Streams administrator:
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
Note
that Streams does not require you to use the following roles, but granting
these roles can assist with administration:
GRANT
SELECT_CATALOG_ROLE TO strmadmin;
GRANT
SELECT ANY DICTIONARY TO strmadmin;
Complete
the preceding steps to set up a Streams administrator on each of the databases
participating in the Streams data sharing environment. In this example, these
steps need to be executed on both the DB10GSR1 and the DB10GSR2 databases.
3. Set initialization parameters. Set
specific initialization parameters at the databases participating in the
Streams data sharing. Table 1 describes these initialization parameters and the
values they must be set to.
PARAMETER SOURCE DESTINATION
GLOBAL_NAMES TRUE TRUE
COMPATIBLE 10.1.0 10.1.0
JOB_QUEUE_PROCESSES 2 (or above) N/A
STREAMS_POOL_SIZE 200MB (Min) 200MB
(Min)
4. Create a database link. You need to
create a database link from the source database to the destination database. In
this example, you will create a database link from DB10GSR1 to DB10GSR2 .
CONNECT
strmadmin/strmadmin@DB10GSR1
CREATE DATABASE LINK DB10GSR2
CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING 'DB10GSR2';
5. Set up source and destination
queues. The data moves from the source to the destination database through
queues. Use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to set
up the queues. By default, this procedure creates a queue table named
streams_queue_table and a queue named streams_queue . You can override these
names by specifying the queue_table and queue_name parameters of the procedure
SET_UP_QUEUE . The default names work well, unless you want to create multiple
queues and multiple queue tables. For the purpose of the replication example in
this article, you will accept the default names, by executing the procedure as
follows:
EXEC
DBMS_STREAMS_ADM.SET_UP_QUEUE();
Execute
this procedure on both the source and the destination databases. When you
execute it on the source database ( DB10GSR1 ), it creates a source queue, and
when you execute it on the destination database ( DB10GSR2 ), it creates a
destination queue. Because you are choosing to accept the default queue name,
the names of the source and destination queues will be the same. In a later
step, you will associate these two queues with the propagation process.
6. Set up supplemental logging at the
source database on the tables being changed.
ALTER TABLE emp
ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE) COLUMNS;
7. Configure the capture process at the
source database. The capture process initiates replication, by capturing the
changes in the source database. It then formats each change into an LCR and
enqueues the LCRs. Create a capture process to extract the changes from the
redo logs. You can configure a capture process to run on the source database
called a local capture or remotely on another database called a downstream
capture. While creating a capture process, you add rules to specify which
changes to capture and which ones to discard. The rules are combined into rule
sets. The capture process can have a positive rule set and a negative rule set.
For a replication capture process to work, you need to add a positive rule set
that specifies that the data manipulation language (DML) changes as well as the
data definition language (DDL) changes be captured. Do this by using the
ADD_TABLE_RULES procedure of the DBMS_STREAMS_ADM package:
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'scott.emp',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name =>
'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
This
procedure call creates the local Streams capture process, named capture_stream
. Note that you can use the optional parameter source_database to specify
another database for a downstream capture. If you omit this parameter (as in
this example) or set the parameter value to NULL, the procedure will create a
local capture process.
The
streams_type parameter indicates that this procedure will create a capture
process. (Note that the same procedure will later be used to create an apply
process.) The inclusion_rule parameter with the value set to true means that
this procedure will create a positive rule set for the capture process. The
true value for the include_dml parameter means that a rule will be created for
DML changes, and a true value for the include_ddl parameter means that a rule
will be created for DDL changes. The table_name parameter indicates that these
rules are associated with the EMP table in the SCOTT schema. You can also set
up rules for a schema or for the entire database, using the ADD_SCHEMA_RULES
and ADD_GLOBAL_RULES procedures, respectively. See the PL/SQL Packages and
Types Reference manual for more information on these packages.
The
queue, streams_queue , specified in this procedure call was created by the
SET_UP_QUEUE procedure call in Step 5 .
8. Configure the propagation process.
Once the changes are captured and enqueued, propagate the changes to the
destination database. To do so, you create a propagation process and associate
the source queue with the destination queue. You create the source and
destination queues (each named the default streams_queue ) in the source and
destination databases, respectively, in Step 5 by calling the SET_UP_QUEUE
procedure.
The
procedure in Listing 1 creates a propagation process and adds rules to the
positive rule set of that process.
This
procedure call creates a propagation process, named DB10GSR1_to_DB10GSR2 , for
which the source queue is the streams_queue in the DB10GSR1 database and the
destination queue is the streams_queue in the DB10GSR2 database. This procedure
also adds DML and DDL rules to the positive rule set.
9. Create the destination table. Before
you can start replicating the DDL and DML changes on a source table to the
destination database, the table must exist in the destination database. If the
table doesn't already exist in the destination database, you have several
options for creating the object in the destination database, such as using Data
Pump, export/import, RMAN, transportable tablespaces, and so on.
10. Grant object privileges. On the
destination database, the Streams administrator applies the changes captured
from the source database. To be able to apply the changes to the destination
tables, the Streams administrator must have the required privileges on the relevant
objects. For example, if the changes need to be applied to the EMP table of the
SCOTT schema, you need to execute the following after connecting to the
destination ( DB10GSR2 ) database:
GRANT
ALL ON scott.emp TO strmadmin;
Because
you set up the Streams administrator with the DBA role in this example (in Step
2 ), you can choose to skip this step. But it is a good idea to explicitly
grant the required object privileges to the Streams administrator.
11. Set the instantiation System Change
Number (SCN). Now you need to set the instantiation SCN for the table from the
source database you want to replicate. This ensures that the changes in the
source table captured before the instantiation SCN is set will not be applied
at the destination database. The procedure in Listing 2 sets the instantiation
SCN for the EMP table in the destination database, by fetching the current SCN
from the source database. Execute the procedure in Listing 2 by connecting to
the source database ( DB10GSR1 ) as the Streams administrator.
Listing
2 shows how the GET_SYSTEM_CHANGE_NUMBER procedure of the package
DBMS_FLASHBACK returns the current SCN of the source database. This SCN is used
to set the instantiation SCN for the EMP table of the SCOTT schema in the
destination database. Note that the SET_TABLE_INSTANTIATION_SCN procedure of
the DBMS_APPLY_ADM package is called via the DB10GSR2 database link, which
means that although this procedure is being called while you are connected to
DB10GSR1 , it will be executed in the DB10GSR2 database.
If
the table didn't previously exist in the destination database and you use
export/import, Data Pump, or transportable tablespaces to copy it, the
instantiation SCN will be set automatically for the table.
12. Configure the apply process at the
destination database. Now you create an apply process and associate the
destination queue with it. You also add rules for the apply process. You
achieve this by calling the ADD_TABLE_RULES procedure of the DBMS_STREAMS_ADM
package:
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'scott.emp',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name =>
'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'DB10GSR1',
inclusion_rule => true);
END;
/
This
procedure creates an apply process (as indicated by the streams_type parameter)
for the EMP table in the SCOTT schema. The apply process, named apply_stream ,
is associated with the streams_queue . The apply process also adds DML and DDL
rules to the positive rule set (as indicated by the parameter inclusion_rule ).
Note
that earlier you used DBMS_STREAMS_ADM.ADD_TABLE_RULES to create (and add rules
for) a capture process ( Step 7 ).
13. Start the capture and apply processes.
Now that you have configured all necessary objects and processes, all you need
to do is start the capture and apply processes. To start the capture process,
connect to the source database and execute the START_CAPTURE procedure of the
DBMS_CAPTURE_ADM package:
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name =>
'capture_stream');
END;
/
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name =>
'capture_stream');
END;
/
select
capture_name ,STATUS from dba_capture;
Similarly,
to start the apply process, connect to the destination database and execute the
START_APPLY process of the DBMS_APPLY_ADM package. However, before you do that,
best practices recommend that you set the disable_on_error parameter of the
apply process to n , so that the apply process will continue even if it
encounters some errors.
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name
=> 'apply_stream',
parameter
=> 'disable_on_error',
value
=> 'n');
END;
/
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name
=> 'apply_stream');
END;
/
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name
=> 'apply_stream');
END;
/
Now
the Streams replication environment is ready, and DML and DDL changes in the
SCOTT.EMP table in the source database will be replicated in the corresponding
table in the destination database.
Conclusion
Oracle
Streams can capture, propagate, and apply changes in the database
automatically, including both DML and DDL changes. Applications requiring
replication, data warehouses, database migrations, and database upgrades can
all benefit from Oracle Streams.
