Home » Server Options » Streams & AQ » 1 Table in 2 different Schemas on Single Database (Sun Solaris 2.8)
1 Table in 2 different Schemas on Single Database [message #343887] Thu, 28 August 2008 07:08
shreeshsikaria
Messages: 3
Registered: August 2008
Location: India
Junior Member
Aim : To keep 2 tables ( of same structure ) in different schemas on same database in sync using Streams.

Problem : On monitoring the data dict tables , i found that Capture process is generating LCR's , Propagation is propagating LCR's , Apply reader is getting LCR's , Apply co-ordinator is getting LCR's but APPLY SERVER IS NOT DOING ANYTHING.

I have attached the code below.
Any help on this shall be appreciated.


Source Table : mary.cbus_contact
Destination Table : jack.cbus_contact

--i have created the stradmin user with requiered privs , logminer TS before.
--1. Add supplemental logging , privileges to stradmin
Alter table mary.cbus_contact add supplemental log data (ALL) columns;
dbms_streams_auth.grant_admin_privilege('stradmin');

--2. Set up the source queue
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stradmin.queue_table1',
queue_name => 'stradmin.capture_contact_queue',
queue_user => 'STRADMIN');
END;
/

--3. Set up the destination queue.
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stradmin.queue_table2',
queue_name => 'stradmin.apply_contact_queue',
queue_user => 'STRADMIN');
END;
/

--4. Create the apply process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
TABLE_NAME => 'jack.cbus_contact',
STREAMS_TYPE => 'apply',
STREAMS_NAME => 'contact_apply',
queue_name => 'stradmin.apply_contact_queue',
include_dml => true,
include_ddl => false,
source_database => 'SmaryDEV1.OSS.myCompany.COM',
inclusion_rule => true);
END;
/

--5.Alter the apply process
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY
(
apply_name => 'contact_apply',
apply_user => 'jack'
);
END;
/

--6. Apply process should not stop on just one error.
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER
(
apply_name => 'contact_apply',
parameter => 'DISABLE_ON_ERROR',
value => 'N'
);
END;
/

-- 7. Set up the propagation process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES
(
table_name => 'mary.cbus_contact',
streams_name => 'contact_propagate',
source_queue_name => 'stradmin.capture_contact_queue',
include_dml => true,
include_ddl => false,
source_database => 'SmaryDEV1.OSS.myCompany.COM',
inclusion_rule => true);
END;
/

-- 8. Set up the capture process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'mary.cbus_contact',
streams_type => 'capture',
streams_name => 'contact_capture',
queue_name => 'stradmin.capture_contact_queue',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => NULL,
inclusion_rule => true);
END;
/

-- 9. Prepare the source table for instantiation
begin
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
table_name => 'mary.cbus_contact');
END;
/

-- 10 . Export from CBUS_CONTACT table from mary schema to jack schema
exp userid=mary tables=cbus_contact file=mary.dmp rows=N log=mary.log_exp object_consistent=Y INDEXES=Y STATISTICS=NONE
imp fromuser=mary touser=jack CONSTRAINTS=Y FILE=mary.dmp grants=y rows=Y log=mary.log_imp streams_instantiation=Y IGNORE=Y

-- reinstantiation of apply is not needed if doing exp imp above
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'mary.cbus_contact',
source_database_name => 'smarydev1.oss.myCompany.com',
instantiation_scn => iscn);
END;
/

-- 11. Start the apply process
BEGIN
DBMS_APPLY_ADM.START_APPLY
(
apply_name => 'contact_apply'
);
END;
/

-- 12 . Start the capture process
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE
(
capture_name => 'contact_capture'
);
END;
/
Previous Topic: Streams - Mismatched columns
Next Topic: One database( two schemas) streams Configuration
Goto Forum:
  


Current Time: Thu Mar 28 03:18:04 CDT 2024