This discussion is archived
2 Replies Latest reply: Feb 20, 2013 9:57 PM by AsimDeo RSS

Schema Level Bidirectional streams - works only in one direction

AsimDeo Newbie
Currently Being Moderated
Hi All,

we are implementing bidirectional streams at schema level(using scott schema for testing).
Our environment and different parameters are:

Source:
OS =Win2003 64bit
DB Version= 10.2.0.5.0 64bit
DB SID=CIBSPROD
log_archive_dest_1 LOCATION=E:\DBFILES\ArchiveLog\CIBSPROD\PrimaryRole VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIBSPROD
log_archive_dest_2 SERVICE=CIBSREP LGWR ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CIBSREP
job_queue_processes=2

Dest:
OS =Win2003 64bit
DB Version= 10.2.0.5.0 64bit
DB SID=CIBSREP
log_archive_dest_1 LOCATION=E:\DBFILES\ArchiveLog\CIBSREP\PrimaryRole VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=CIBSREP
log_archive_dest_2 SERVICE=CIBSPROD LGWR ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CIBSPROD
job_queue_processes=2

Follow the "Streams Bi-Directional Setup [ID 471845.1]" article

Problem we are facing is changes are propagating from Source(CIBSPROD)  to Destination(CIBSREP) BUT NOT from Destination to Source Database(although archivelogs are shipping from Destination to Source).

Executed below script for configuration:
SET ECHO ON
SPOOL strm-reconfig-scott.out
conn sys/&sys_pwd_source@CIBSPROD as sysdba
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION; 
DROP USER STRMADMIN CASCADE;
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs; 
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
EXECute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
drop database link CIBSREP;
create public database link CIBSREP connect to strmadmin identified by strmadmin using 'CIBSREP'; 

conn sys/&sys_pwd_downstream@CIBSREP as sysdba
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION; 
DROP USER STRMADMIN CASCADE;
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs; 
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
EXECute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
drop user SCOTT CASCADE;
create user SCOTT identified by scott175;
GRANT CONNECT, RESOURCE to SCOTT;
drop database link CIBSPROD;
create public database link CIBSPROD connect to strmadmin identified by strmadmin using 'CIBSPROD'; 

--Set up 2 queues for Capture and apply in CIBSPROD Database 
conn strmadmin/strmadmin@CIBSPROD 
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'APPLY_CIBSPROD_TAB', queue_name => 'APPLY_CIBSPROD', queue_user => 'strmadmin');  
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'CAPTURE_CIBSPROD_TAB', queue_name => 'CAPTURE_CIBSPROD', queue_user => 'strmadmin'); 

--Set up 2 queues for Capture and apply in CIBSREP Database 
conn strmadmin/strmadmin@CIBSREP 
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'APPLY_CIBSREP_TAB', queue_name => 'APPLY_CIBSREP', queue_user => 'strmadmin');  
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'CAPTURE_CIBSREP_TAB', queue_name => 'CAPTURE_CIBSREP', queue_user => 'strmadmin'); 

--Configure capture,apply and propagation process on CIBSPROD database.
conn strmadmin/strmadmin@CIBSPROD 
EXEC dbms_streams_adm.add_schema_rules ( schema_name => 'scott', streams_type => 'CAPTURE', streams_name => 'CAPTURE_CIBSPROD', queue_name => 'CAPTURE_CIBSPROD', include_dml => true, include_ddl => true, inclusion_rule => true); 
EXEC dbms_streams_adm.add_schema_rules ( schema_name => 'scott', streams_type => 'APPLY', streams_name => 'APPLY_CIBSPROD', queue_name => 'APPLY_CIBSPROD', include_dml => true, include_ddl => true, source_database => 'CIBSREP'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSPROD', parameter => 'PARALLELISM', value => '5'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSPROD', parameter => '_HASH_TABLE_SIZE', value => '10000000'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSPROD', parameter => 'TXN_LCR_SPILL_THRESHOLD', value => '1000000'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSPROD', parameter => 'DISABLE_ON_ERROR', value => 'N'); 
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSPROD', parameter => '_dynamic_stmts',value => 'Y');
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSPROD', parameter => 'COMMIT_SERIALIZATION',value => 'NONE');
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSPROD', parameter => '_RESTRICT_ALL_REF_CONS',value => 'N');
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSPROD', parameter => 'ALLOW_DUPLICATE_ROWS',value => 'Y');
EXEC dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'scott', streams_name => 'PROP_CIBSPROD_to_CIBSREP', source_queue_name => 'CAPTURE_CIBSPROD', destination_queue_name => 'APPLY_CIBSREP@CIBSREP', include_dml => true, include_ddl => true, source_database => 'CIBSPROD'); 

--Configure capture,apply and propagation process process on CIBSREP Database 
conn strmadmin/strmadmin@CIBSREP
EXEC dbms_streams_adm.add_schema_rules ( schema_name => 'scott', streams_type => 'CAPTURE', streams_name => 'CAPTURE_CIBSREP', queue_name => 'CAPTURE_CIBSREP', include_dml => true, include_ddl => true);
EXEC dbms_streams_adm.add_schema_rules ( schema_name => 'scott', streams_type => 'APPLY', streams_name => 'APPLY_CIBSREP', queue_name => 'APPLY_CIBSREP', include_dml => true, include_ddl => true, source_database => 'CIBSPROD'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSREP', parameter => 'PARALLELISM', value => '5'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSREP', parameter => '_HASH_TABLE_SIZE', value => '10000000'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSREP', parameter => 'TXN_LCR_SPILL_THRESHOLD', value => '1000000'); 
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_CIBSREP', parameter => 'DISABLE_ON_ERROR', value => 'N'); 
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSREP', parameter => '_dynamic_stmts',value => 'Y');
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSREP', parameter => 'COMMIT_SERIALIZATION',value => 'NONE');
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSREP', parameter => '_RESTRICT_ALL_REF_CONS',value => 'N');
EXEC DBMS_APPLY_ADM.Set_parameter(apply_name => 'APPLY_CIBSREP', parameter => 'ALLOW_DUPLICATE_ROWS',value => 'Y');
EXEC dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'scott', streams_name => 'PROP_CIBSREP_to_CIBSPROD', source_queue_name => 'CAPTURE_CIBSREP', destination_queue_name => 'APPLY_CIBSPROD@CIBSPROD', include_dml => true, include_ddl => true, source_database => 'CIBSREP'); 

--Import export schema 
host exp USERID=SYSTEM/cibsmgr@CIBSPROD parfile=expparfile-scott.txt
host imp USERID=SYSTEM/cibsmgr@CIBSREP parfile=impparfile-scott.txt

--Start capture and apply processes on CIBSREP 
conn strmadmin/strmadmin@CIBSREP
EXEC dbms_capture_adm.start_capture (capture_name=>'CAPTURE_CIBSREP'); 
EXEC DBMS_APPLY_ADM.START_APPLY(apply_name => 'APPLY_CIBSREP'); 

--Start capture and apply processes on CIBSPROD
conn strmadmin/strmadmin@CIBSPROD
EXEC dbms_capture_adm.start_capture (capture_name=>'CAPTURE_CIBSPROD'); 
EXEC DBMS_APPLY_ADM.START_APPLY(apply_name => 'APPLY_CIBSPROD'); 


SPOOL OFF 
What we have missed in the configuration?

Regards,
Asim

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points