|
- Create
a Database Link in strmadmin@local.world to point
to the strmadmin user in remote.world database.
CREATE DATABASE LINK remote.world
CONNECT TO strmadmin IDENTIFIED BY strmadmin
USING 'remote.world';
NOTE:
Alternatively you can login as SYS user and create a public database link.
- Login to strmadmin at local.world and remote.world respectively and create a Streams Queue
as follows:
/* Refer to PART-1 of the article series for DBMS_STREAMS_ADM description */
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_user => 'STRMADMIN');
END;
/
- Create
the Capture, propagate and Apply rules in the respective streams
administrator schemas.
Login to destination streams administrator
schema and create the Apply process
/* In this example we create DML apply rules on DEMO.DEPt and DEMO.EMP tables
respectively. We can also create DML /DDL rules for the entire DEMO schema using
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES procedure */
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"DEMO"."DEPT"',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_LOCAL',
queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => false,
source_database => 'LOCAL.WORLD');
/* Add Apply rules for DEMO.DEPT */
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"DEMO"."EMP"',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_LOCAL',
queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => false,
source_database => 'LOCAL.WORLD');
END;
/
Login to source streams administrator schema and
create the Capture and Propagation process
/* Add capture rules to DEMO.DEPT and DEMO.EMP */
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"DEMO"."DEPT"',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => false,
source_database => 'LOCAL.WORLD');
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"DEMO"."EMP"',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => false,
source_database => 'LOCAL.WORLD');
END;
/
/* Add propagation rules to DEMO.DEPT and DEMO.EMP */
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => '"DEMO"."DEPT"',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@REMOTE.WORLD',
include_dml => true,
include_ddl => false,
source_database => 'LOCAL.WORLD');
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => '"DEMO"."EMP"',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@REMOTE.WORLD',
include_dml => true,
include_ddl => false,
source_database => 'LOCAL.WORLD');
END;
/
- Grant
appropriate privileges to the Objects / schema for which the apply
rules have been setup, to apply the DDL or DML changes.
If DEMO.DEPT and DEMO.EMP already
exist in DEMO@remote.world then grant
SELECT, INSERT and UPDATE privileges to the Streams administrator. Otherwise,
with respect to this example, export the tables from the source database as
specified in step 9.
- Set
up the instantiation SCNs for the tables.
In addition to using supplied
PL/SQL packages, you can also use Export/Import Oracle utility to set up
Instantiation SCNs for database objects.
NOTE:
In Oracle streams the tables identified for capturing and applying
changes should have primary keys defined on them.
From the command line do the
following:
exp USERID="STRMADMIN"@local.world TABLES="DEMO"."DEPT", "DEMO"."EMP" FILE=tables.dmp
GRANTS=Y ROWS=Y LOG=exportTables.log OBJECT_CONSISTENT=Y INDEXES=Y
imp USERID="STRMADMIN"@remote.world FULL=Y CONSTRAINTS=Y FILE=tables.dmp
IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log STREAMS_CONFIGURATION=Y
STREAMS_INSTANTIATION=Y
- Now to start the
processes at the respective databases.
/* Start Apply process at the destination database.
Login to STRMADMIN@REMOTE.WORLD*/
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_LOCAL';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_LOCAL');
end if;
END;
/
/* Start Capture process at the destination database
Login to STRMADMIN@LOCAL.WORLD */
DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'STRMADMIN_CAPTURE';
if (v_started = 0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRMADMIN_CAPTURE');
end if;
END;
/
Previous
Next
Prev [1] [2] [3] [4] [5] [6] Next |
