3 Replies Latest reply: Sep 22, 2011 4:08 AM by bhanu kiran RSS

    Streams setup: Data not replicated.

    bhanu kiran
      Hello All,

      I have two databases on same server, I am practising oracle streams.

      Mysetup:


      Source : uat
      Target: prod
      schema name: scott

      source uat:

      create user streamadmin identified by streamadmin default tablespace strepadm temporary tablespace temp;

      create public database link prod connect to streamadmin identified by streamadmin using 'prod';

      **** Created the same user in prod server.

      In UAT

      alter database force logging;

      alter database add supplemental log data;


      alter table SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;
      alter table SCOTT.DEPT ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) columns;


      BEGIN
      DBMS_STREAMS_ADM.SET_UP_QUEUE(
      queue_table => 'STREAMS_QUEUE_TABLE',
      queue_name => 'STREAMS_QUEUE_Q',
      queue_user => 'STREAMADMIN');
      END;
      /


      **** created the same in Target schema: Prod

      Stream propagarion from uat:

      BEGIN

      DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

      schema_name => 'scott',

      streams_name => 'STREAM_PROPAGATE_P1',

      source_queue_name => 'STREAMS_QUEUE_Q',

      destination_queue_name => 'STREAMS_QUEUE_Q@prod',

      include_dml => true,

      include_ddl => true,

      source_database => 'uat');

      END;

      /


      Capture process in uat

      BEGIN

      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

      schema_name => 'scott',

      streams_type => 'CAPTURE',

      streams_name => 'STREAM_CAPTURE_C1',

      queue_name => 'STREAMS_QUEUE_Q',

      include_dml => true,

      include_ddl => true,

      source_database => 'uat');

      END;

      /

      STREAMS OBJECT INSTANTATION

      $exp USERNAME/PASSWORD parfile=exp_streams.par

      vi exp_streams.par

      file=exp_streams.dmp
      log=exp_streams.log
      object_consistent=y
      OWNER=SCOTT
      STATISTICS=NONE

      SCP THE .DMP FILE TO TARGET AND IMPORT IT:

      TARGET:

      imp FROMUSER=SCOTT TOUSER=SCOTT FILE=exp_streams.dmp log=exp_streams.log STREAMS_INSTANTIATION=Y IGNORE=Y COMMIT=Y



      Apply Process in prod
      BEGIN

      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

      schema_name => 'scott',

      streams_type => 'APPLY',

      streams_name => 'STREAM_APPLY_A1',

      queue_name => 'STREAMS_QUEUE_Q',

      include_dml => true,

      include_ddl => true,

      source_database => 'prod');

      END;

      /

      Start capture

      BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
      capture_name => 'STREAM_CAPTURE_C1');
      END;
      /


      uat:

      SQL> conn scott/scott
      Connected.
      SQL> insert into dept values(50,'IT','KMM');

      1 row created.

      SQL> commit;


      ***** But the changes are not applied in Prod
      ------------------------------------------------

      more information:

      SELECT p.PROPAGATION_NAME,
      s.QUEUE_SCHEMA,
      s.QUEUE_NAME,
      s.DBLINK,
      s.SCHEDULE_STATUS
      FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
      WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
      p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
      p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
      p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME


      PROPAGATION_NAME     QUEUE_SCHEMA     QUEUE_NAME       DBLINK                                SCHEDULE_STATUS

      -------------------  ---------------  ---------------  ------------------------------------  ------------------

      STREAM_PROPAGATE_P1  STREAMADMIN      STREAMS_QUEUE_Q  "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD  SCHEDULE ENABLED
      SELECT p.PROPAGATION_NAME,
      s.QUEUE_NAME,
      s.DBLINK,
      s.TOTAL_MSGS,
      s.TOTAL_BYTES
      FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
      WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
      p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
      p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
      p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME

      PROPAGATION_NAME     QUEUE_NAME       DBLINK                                TOTAL_MSGS     TOTAL_BYTES

      -------------------  ---------------  ------------------------------------  -------------  --------------

      STREAM_PROPAGATE_P1  STREAMS_QUEUE_Q  "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD  126            142469

      PROPAGATION_NAME     QUEUE_NAME       DBLINK                                ELAPSED_DEQUEUE_TIME     ELAPSED_PICKLE_TIME     ELAPSED_PROPAGATION_TIME

      -------------------  ---------------  ------------------------------------  -----------------------  ----------------------  ---------------------------

      STREAM_PROPAGATE_P1  STREAMS_QUEUE_Q  "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD  0.003808                 0.063586                1.37545

      PROPAGATION_NAME     QUEUE_NAME       DBLINK                                ELAPSED_DEQUEUE_TIME     ELAPSED_PICKLE_TIME     ELAPSED_PROPAGATION_TIME

      -------------------  ---------------  ------------------------------------  -----------------------  ----------------------  ---------------------------

      STREAM_PROPAGATE_P1  STREAMS_QUEUE_Q  "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD  0.003808                 0.063586                1.37545
      SELECT p.PROPAGATION_NAME,
      s.SUBSCRIBER_ADDRESS,
      s.CURRENT_ENQ_SEQ,
      s.LAST_BROWSED_SEQ,
      s.LAST_DEQUEUED_SEQ,
      s.NUM_MSGS,
      s.TOTAL_SPILLED_MSG
      FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s, V$BUFFERED_QUEUES q
      WHERE q.QUEUE_ID = s.QUEUE_ID AND
      p.SOURCE_QUEUE_OWNER = q.QUEUE_SCHEMA AND
      p.SOURCE_QUEUE_NAME = q.QUEUE_NAME AND
      s.SUBSCRIBER_ADDRESS LIKE '%' || p.DESTINATION_DBLINK

      PROPAGATION_NAME     SUBSCRIBER_ADDRESS                     CURRENT_ENQ_SEQ     LAST_BROWSED_SEQ     LAST_DEQUEUED_SEQ     NUM_MSGS     TOTAL_SPILLED_MSG

      STREAM_PROPAGATE_P1  "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD   0                   126                  0                     85           122
      SQL> SELECT SRC_QUEUE_NAME,
      SRC_DBNAME,
      (ELAPSED_UNPICKLE_TIME / 100) ELAPSED_UNPICKLE_TIME,
      (ELAPSED_RULE_TIME / 100) ELAPSED_RULE_TIME,
      (ELAPSED_ENQUEUE_TIME / 100) ELAPSED_ENQUEUE_TIME
      FROM V$PROPAGATION_RECEIVER 2 3 4 5 6 ;

      SRC_QUEUE_NAME
      ------------------------------
      SRC_DBNAME
      --------------------------------------------------------------------------------------------------------------------------------
      ELAPSED_UNPICKLE_TIME ELAPSED_RULE_TIME ELAPSED_ENQUEUE_TIME
      --------------------- ----------------- --------------------
      STREAMS_QUEUE_Q
      UAT
      *.092667 .311064 .897676*





      ?????? sometime here in this site, the output of the query is not displaying correctly i.e I have given the details two times.

      Edited by: user12178861 on Sep 21, 2011 8:38 AM
        • 1. Re: Streams setup: Data not replicated.
          bhanu kiran
          More information part not showed the values correctly:

          SELECT p.PROPAGATION_NAME,
          s.SUBSCRIBER_ADDRESS,
          s.CURRENT_ENQ_SEQ,
          s.LAST_BROWSED_SEQ,
          s.LAST_DEQUEUED_SEQ,
          s.NUM_MSGS,
          s.TOTAL_SPILLED_MSG
          FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s, V$BUFFERED_QUEUES q
          WHERE q.QUEUE_ID = s.QUEUE_ID AND
          p.SOURCE_QUEUE_OWNER = q.QUEUE_SCHEMA AND
          p.SOURCE_QUEUE_NAME = q.QUEUE_NAME AND
          s.SUBSCRIBER_ADDRESS LIKE '%' || p.DESTINATION_DBLINK

          PROPAGATION_NAME SUBSCRIBER_ADDRESS CURRENT_ENQ_SEQ LAST_BROWSED_SEQ LAST_DEQUEUED_SEQ NUM_MSGS TOTAL_SPILLED_MSG

          STREAM_PROPAGATE_P1  "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD   0                   126                  0                     85           122

          SELECT p.PROPAGATION_NAME,
          s.QUEUE_NAME,
          s.DBLINK,
          (s.ELAPSED_DEQUEUE_TIME / 100) ELAPSED_DEQUEUE_TIME,
          (s.ELAPSED_PICKLE_TIME / 100) ELAPSED_PICKLE_TIME,
          (s.ELAPSED_PROPAGATION_TIME / 100) ELAPSED_PROPAGATION_TIME
          FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
          WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
          p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
          p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
          p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME

          PROPAGATION_NAME QUEUE_NAME DBLINK ELAPSED_DEQUEUE_TIME ELAPSED_PICKLE_TIME ELAPSED_PROPAGATION_TIME
          ------------------- --------------- ------------------------------------ ----------------------- ---------------------- ---------------------------
          STREAM_PROPAGATE_P1 STREAMS_QUEUE_Q "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD 0.003808 0.063586 1.37545



          SELECT p.PROPAGATION_NAME,
          s.QUEUE_NAME,
          s.DBLINK,
          s.TOTAL_MSGS,
          s.TOTAL_BYTES
          FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
          WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
          p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
          p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
          p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME


          PROPAGATION_NAME QUEUE_NAME DBLINK TOTAL_MSGS TOTAL_BYTES
          ------------------- --------------- ------------------------------------ ------------- --------------
          STREAM_PROPAGATE_P1 STREAMS_QUEUE_Q "STREAMADMIN"."STREAMS_QUEUE_Q"@PROD 126 142469


          If you want any more details, I can provide you...Thank youv very much.



          Edited by: user12178861 on Sep 21, 2011 8:38 AM

          Edited by: user12178861 on Sep 21, 2011 8:40 AM

          Edited by: user12178861 on Sep 21, 2011 8:42 AM
          • 2. Re: Streams setup: Data not replicated.
            stevencallan
            Try this:
            How To Setup One-Way SCHEMA Level Streams Replication (Doc ID 301431.1)

            Plus other examples here:
            Master Note for Streams Setup Scripts (Doc ID 789445.1)
            • 3. Re: Streams setup: Data not replicated.
              bhanu kiran
              Thank you,

              I have followed the link, and created streams successfully