2 Replies Latest reply: Feb 20, 2013 11:57 PM by AsimDeo RSS

    Schema Level Bidirectional streams - works only in one direction

    AsimDeo
      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