TIP #18: handy queries for Stream administration (Propagation process)

Basically in propagation, two views are important : DBA_QUEUE_SCHEDULES and
DBA_PROPAGATION.
The followings are handy statements for propagation administration of stream

--- Disable propagation
begin
dbms_aqadm.disable_propagation_schedule('STRMADMIN.STREAMS_QUEUE', 'OEMREP.US.ORACLE.COM');
end;
/
--- Enable propagation
begin
dbms_aqadm.enable_propagation_schedule('&source_queue_name_with_owner', '&database_link');
end;
/
-- Info about propagation
select * from dba_propagation;

-- Find out source and destination propagation
SELECT 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;

-- Find propagation parameters
SELECT s.*FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION pWHERE p.PROPAGATION_NAME = 'STRMADMIN_PROPAGATE'AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;

---- How to change parameters
-- Propagation job sets to 15min to propagates events every 15 minutes
-- Each propagation lasting max 300 second
-- 25 second wait before new events in a completely propagated queue are propagated
BEGIN
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(queue_name => '&source_queue_name',destination => '&database_link_name',duration => 300,next_time => 'SYSDATE + 900/86400',latency => 25);
END;
/

-- Find out progpagation rule set
SELECT RULE_SET_OWNER, RULE_SET_NAMEFROM DBA_PROPAGATIONWHERE PROPAGATION_NAME = '&propagation_name';

select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
TABLE_OWNER'.'TABLE_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_TABLE_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name)union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
SCHEMA_NAME,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_SCHEMA_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name)union all
select STREAMS_NAME,STREAMS_TYPE,RULE_TYPE,RULE_NAME,
null,RULE_OWNER,RULE_CONDITION
from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name);

-- Which DML/DDL rules the capture process is capturing
select * from "DBA_STREAMS_TABLE_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');

-- Which schame rules the capture process is capturing
select * from "DBA_STREAMS_SCHEMA_RULES" where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');

-- Which global rules the capture process is capturing
select *

from "DBA_STREAMS_GLOBAL_RULES"
where streams_type='PROPAGATION' and rule_name in (select rule_name from DBA_RULE_SET_RULES a, dba_propagation b where a.rule_set_name=b.rule_set_name and capture_name='&propagation_name');

------ More about propagation job
SELECT 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.FAILURES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = '&propagation_name'
AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;

-------- Total number of bytes which was propagated.
SELECT s.TOTAL_TIME_IN_sec, s.TOTAL_NUMBER, s.TOTAL_BYTES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = '&propagation_name'
AND p.DESTINATION_DBLINK = s.DESTINATIONAND s.SCHEMA = p.SOURCE_QUEUE_OWNERAND s.QNAME = p.SOURCE_QUEUE_NAME;

1 comment:

yj855458 said...

SUCCESS STARTS TODAY!! you can earn more than 5000 dollars a week makingso so easy visit here