Streams propagation

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Tue, 21 Mar 2006 16:00:43 -0000
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0A18A06E@ENYC11P32005.corpny.csfb.com>

I'm new to Streams and having a problem with message propagation, which I think I've isolated but am not sure how to fix. This is 10gR2 on Linux.

The doc chapter on "Monitoring Streams Queues and Propagations" shows the following query to get info on propagations from buffered queues:

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.DESTINATION_DBLINK = s.DBLINK AND
        p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME;

In my case, no rows are returned. The reason is the discrepancy in values between dba_propagation and v$propagation_sender:

SOURCE_QUEUE_OWNER SOURCE_QUEUE_NAME DESTINATION_DBLIN

------------------- ------------------- -----------------
STRMADMIN           ODS_ARCH_Q          QNYCSR40.WORLD

QUEUE_SCHEMA        QUEUE_NAME          DBLINK
------------        ---------------     ----------
STRMADMIN           ODS_ARCH_Q          "STRMADMIN"."ODS_ARCH_Q"@QNYCSR40.WORLD

I created the propagation for each table using the following syntax

DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(

    table_name                => 'ODS.&1', 
    streams_name              => 'PROPAGATE_ARCH', 
    source_queue_name         => 'STRMADMIN.ODS_ARCH_Q',
    destination_queue_name    => 'STRMADMIN.ODS_ARCH_Q_at_QNYCSR40.WORLD',
    include_dml               => true,
    include_ddl               => true,
    source_database           => 'DNYCSR40.WORLD',
    inclusion_rule            => true,
    queue_to_queue            => true);

which appears correct based on the documentation. So either the documentation for this procedure is wrong, or the query to monitor propagation is wrong, but I suspect the latter because propagation is not working. Any ideas appreciated.

Paul Baumgartel
paul.baumgartel_at_credit-suisse.com
212.538.1143

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 21 2006 - 10:00:43 CST