Oracle advanced queue propagation not working for me -

Oracle advanced queue propagation not working for me -

i'd set propagation in oracle aq (11).

i'd propagate queue "q" in queue table "qt" queue "qd" in queue table "qtd".

this setup:

declare subscriber sys.aq$_agent; begin dbms_aqadm.create_queue_table(queue_table=>'qt',multiple_consumers=>true,queue_payload_type=>'raw'); dbms_aqadm.create_queue_table(queue_table=>'qtd',queue_payload_type=>'raw'); dbms_aqadm.create_queue(queue_name => 'q', queue_table => 'qt'); dbms_aqadm.create_queue(queue_name => 'qd', queue_table => 'qtd'); dbms_aqadm.start_queue(queue_name => 'q'); dbms_aqadm.start_queue(queue_name => 'qd'); subscriber := sys.aq$_agent('sub', 'qd', null); dbms_aqadm.add_subscriber(queue_name => 'q',subscriber => subscriber, queue_to_queue => true); dbms_aqadm.schedule_propagation(queue_name => 'q'); end; /

i send message java aqapi client. message sent without error, can see in "q" queue:

select * qt; "q_name" "msgid" "corrid" "priority" "state" "delay" "expiration" "time_manager_info" "local_order_no" "chain_no" "cscn" "dscn" "enq_time" "enq_uid" "enq_tid" "deq_time" "deq_uid" "deq_tid" "retry_count" "exception_qschema" "exception_queue" "step_no" "recipient_key" "dequeue_msgid" "sender_name" "sender_address" "sender_protocol" "user_data" "user_prop" "q" fc914bfdc7489ecee040010a393f3dd1 "" 1 0 0 0 0 0 24-jun-14 07.56.27.258348000 "riskopall" "9.5.283837" "" "" 0 "" "" 0 0 "" "" 0 (blob)

but can't see in "qd" destination queue:

select * qtd;

shows empty result.

do have thought what's wrong it?

i tried enable_propagation_schedule, it's enabled after schedule_propagation. results in error.

i checked page: http://docs.oracle.com/cd/b28359_01/server.111/b28420/aq_trbl.htm can't find dba_queue_schedules view. have admin rights. shall it? how shall troubleshoot propagation?

any help apreciated!

solved it!

if target queue (where messages propagated to) single-consumer queue subscriber name must set null! problem. documented in 11g doc:

http://docs.oracle.com/cd/b28359_01/server.111/b28420/aq_admin.htm#i1008642 :

"the agent name should null if destination queue single consumer queue."

the problematic line in setup:

subscriber := sys.aq$_agent('sub', 'qd', null);

it should have been:

subscriber := sys.aq$_agent(null, 'qd', null);

in finding out problem nice troubleshooting guide below helpful:

https://blogs.oracle.com/db/entry/oracle_support_master_note_for_troubleshooting_advanced_queuing_and_oracle_streams_propagation_issue

section 4.3 advises check alert logs. checked them , indeed in trace file found

kwqpdest: exception 24039 kwqpdest: error 24039 propagating "test"."qd"

after not hard find out why 24039 thrown.

so afterall here working setup in 11g server. propagates messages source 3 targets. source multi consumer queue (it must be), targets single consumer queues:

begin dbms_aqadm.drop_queue_table(queue_table=>'qt', forcefulness => true); dbms_aqadm.drop_queue_table(queue_table=>'qtd', forcefulness => true); end; / declare subscriber sys.aq$_agent; begin dbms_aqadm.create_queue_table(queue_table=>'qt',multiple_consumers=>true, queue_payload_type=>'sys.aq$_jms_text_message'); dbms_aqadm.create_queue_table(queue_table=>'qtd',multiple_consumers=>false, queue_payload_type=>'sys.aq$_jms_text_message'); dbms_aqadm.create_queue(queue_name => 'q', queue_table => 'qt'); dbms_aqadm.create_queue(queue_name => 'qd1', queue_table => 'qtd'); dbms_aqadm.create_queue(queue_name => 'qd2', queue_table => 'qtd'); dbms_aqadm.create_queue(queue_name => 'qd3', queue_table => 'qtd'); dbms_aqadm.start_queue(queue_name => 'q'); dbms_aqadm.start_queue(queue_name => 'qd1'); dbms_aqadm.start_queue(queue_name => 'qd2'); dbms_aqadm.start_queue(queue_name => 'qd3'); subscriber := sys.aq$_agent(null, 'qd1', null); dbms_aqadm.add_subscriber(queue_name => 'q',subscriber => subscriber); subscriber := sys.aq$_agent(null, 'qd2', null); dbms_aqadm.add_subscriber(queue_name => 'q',subscriber => subscriber); subscriber := sys.aq$_agent(null, 'qd3', null); dbms_aqadm.add_subscriber(queue_name => 'q',subscriber => subscriber); dbms_aqadm.schedule_propagation(queue_name => 'q', latency => 0); end;

oracle queue propagation