RE: Streams propagation
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <paul.baumgartel@xxxxxxxxxxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Mar 2006 11:41:32 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxxx',
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@xxxxxxxxxxxxxxxxx
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
========================================================================
======
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
