Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Streams propagation

RE: Streams propagation

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 21 Mar 2006 11:41:32 -0500
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2C4CB96@EXCHANGE.corp.perceptron.com> 





Paul,



Try the following queries to check on Propagation (those are from some
Matalink doc):



PROMPT PROPAGATION JOBS IN DATABASE


COLUMN 'Source Queue' FORMAT A39


COLUMN 'Destination Queue' FORMAT A39


COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A35



SELECT p.propagation_name, p.SOURCE_QUEUE_OWNER ||'.'||

p.SOURCE_QUEUE_NAME ||'@'||
g.GLOBAL_NAME "Source Queue",
p.DESTINATION_QUEUE_OWNER ||'.'||
p.DESTINATION_QUEUE_NAME ||'@'||
p.DESTINATION_DBLINK "Destination Queue"


FROM DBA_PROPAGATION p, GLOBAL_NAME g;

/***********************************************************************




*****/




PROMPT PROPAGATION RULE SETS IN DATABASE


COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A35
COLUMN Positive HEADING 'Positive|Rule Set' FORMAT A35
COLUMN Negative HEADING 'Negative|Rule Set' FORMAT A35



SELECT propagation_name, rule_set_owner||'.'||rule_set_name Positive,
negative_rule_set_owner||'.'||negative_rule_set_name Negative
FROM dba_propagation;

/***********************************************************************




*****/




PROMPT  STREAMS PROPAGATION RULES CONFIGURED WITH DBMS_STREAMS_ADM

PACKAGE



col NAME Heading 'Name' format a25 wrap


col PropNAME format a25 Heading 'Propagation Name'
col object format a25 wrap


col source_database format a15 wrap


col RULE format a35 wrap


col TYPE format a15 wrap


col dml_condition format a40 wrap


break on name



SELECT streams_name NAME,schema_name||'.'||object_name OBJECT,
rule_set_type,


source_database,


streams_rule_type ||' '||Rule_type TYPE ,
include_tagged_lcr,


rule_owner||'.'||rule_name RULE


FROM dba_streams_rules 


WHERE streams_type = 'PROPAGATION'


ORDER BY name,object, source_database, rule_set_type,rule;

/***********************************************************************




*****/




PROMPT  PROPAGATION RULES BY RULE SET


col RULE_SET format a25 wrap


col RULE_NAME format a25 wrap


col condition format a60 wrap


set long 1000


break on RULE_SET


set long 1000



SELECT rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,
rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION
FROM dba_rule_set_rules rsr, dba_rules r
WHERE rsr.rule_name = r.rule_name AND rsr.rule_owner = r.rule_owner
AND rule_set_name IN (SELECT rule_set_name FROM dba_propagation)
ORDER BY rsr.rule_set_owner,rsr.rule_set_name;

/***********************************************************************




*****/




PROMPT  SCHEDULE FOR EACH PROPAGATION


COLUMN START_DATE HEADING 'Start Date'


COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999
COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8
COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999
COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8
COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99
COLUMN LAST_ERROR_MSG HEADING 'Error Message' FORMAT A50
COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999
COLUMN CURRENT_START_DATE HEADING 'Current|Start' FORMAT A17

COLUMN LAST_RUN_DATE HEADING 'Last|Run' FORMAT A17
COLUMN NEXT_RUN_DATE HEADING 'Next|Run' FORMAT A17
COLUMN LAST_ERROR_DATE HEADING 'Last|Error' FORMAT A17





SELECT p.propagation_name,


TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,
s.PROPAGATION_WINDOW, s.NEXT_TIME, s.LATENCY,
DECODE(s.SCHEDULE_DISABLED, 'Y', 'Disabled', 'N', 'Enabled')
SCHEDULE_DISABLED,



s.PROCESS_NAME, s.total_bytes, s.FAILURES, s.LAST_ERROR_MSG
FROM dba_queue_schedules s, dba_propagation p
WHERE p.destination_dblink = s.destination
AND s.schema = p.source_queue_owner


AND s.qname = p.source_queue_name;



SELECT p.propagation_name, 

TO_CHAR(s.LAST_RUN_DATE, 'HH24:MI:SS MM/DD/YY') LAST_RUN_DATE,
TO_CHAR(s.CURRENT_START_DATE, 'HH24:MI:SS MM/DD/YY') CURRENT_START_DATE,
TO_CHAR(s.NEXT_RUN_DATE, 'HH24:MI:SS MM/DD/YY') NEXT_RUN_DATE,
TO_CHAR(s.LAST_ERROR_DATE, 'HH24:MI:SS MM/DD/YY') LAST_ERROR_DATE


FROM dba_queue_schedules s, dba_propagation p
WHERE p.destination_dblink = s.destination
AND s.schema = p.source_queue_owner


AND s.qname = p.source_queue_name;

/***********************************************************************




*****/




PROMPT  PROPAGATION RECEIVER STATISTICS (on "receiving" end)



column src_queue_name HEADING 'Source|Queue|Name'
column src_dbname HEADING 'Source|Database|Name'

column startup_time HEADING 'Startup|Time'
column elapsed_unpickle_time HEADING 'Elapsed|Unpickle Time|(Seconds'
column elapsed_rule_time HEADING 'Elapsed|Rule Time|(Seconds)'
column elapsed_enqueue_time HEADING 'Elapsed|Enqueue Time|(Seconds)'





SELECT



src_dbname,src_queue_name,startup_time,high_water_mark,acknowledgement,
elapsed_unpickle_time, elapsed_rule_time, elapsed_enqueue_time
FROM gv$propagation_receiver;

/***********************************************************************




*****/




Igor 



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Baumgartel, Paul
Sent: Tuesday, March 21, 2006 11:01 AM


To: ORACLE-L


Subject: Streams propagation



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



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


Received on Tue Mar 21 2006 - 10:41:32 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US