Home » Server Options » Streams & AQ » 10 g AQ propagation to a remote queue not working
10 g AQ propagation to a remote queue not working [message #249223] Tue, 03 July 2007 12:22 Go to next message
ptap
Messages: 4
Registered: August 2006
Location: Dublin
Junior Member
Hi,

we have set up 2 queues:
- a source queue q1 on Database A
- a target queue q2 on Database B (in the same schema name as in A)

- Enqueue/dequeue works on both databases individually.
- We have set up a remote subscriber on B using DBMS_AQADM.add_subscriber
subscriber_agent := SYS.aq$_agent (p_subscriber, c_resv_report_queue, NULL);
DBMS_AQADM.add_subscriber (queue_name => c_resvevent_queue,
subscriber => subscriber_agent)
where c_resvevent_queue is q1
and c_report_queue is q2@B
- We verified our queue types with DBMS_AQADM.VERIFY_QUEUE_TYPES
- We used the following to set up propagation:
DBMS_AQADM.SCHEDULE_PROPAGATION(
queue_name => src_queue_name,
destination => destination,
destination_queue => destination_queue)
- we tried to use dbms_aqadm.alter_propagation_schedule to set up some properties such as latency
duration 30;
next_time 'SYSDATE + 60/86400';
latency 2;

But the messages stay in the source queue with a status of READY and do not propagate. when looking at DBA_QUEUE_SCHEDULES and sys.aq$_schedules there are signs of a schedule taking place and a job no being used but no apparent error occur and the messages aren't going anywhere.
See extract of one message below:

resv_report_queue: Messages (aq$resv_report_queuetable)

QUEUE MSG_ID MSG_STATE EXPIRATION ENQ_TIME DEQ_TIME
------------------------------ -------------------------------- ---------------- ---------- --------
USER_DATA(RESV_CODE, RESV_HISTORY_SEQUENCE)
----------------------------------------------------------------------------------------------------
PROPAGATED_MSGID EXPIRATION_REASON CONSUMER_NAME
-------------------------------- ------------------- ------------------------------
RESV_REPORT_QUEUE 345DF23F295F14D3E04400144F5273ED READY 03-JUL-07
RESV_DETAIL('AB0000001', 1)
REPORTLOAD

Has anyone experience in setting up propagation? What are we likely to be missing? Any suggestions?

We would be very grateful

Philippe
Re: 10 g AQ propagation to a remote queue not working [message #629970 is a reply to message #249223] Thu, 18 December 2014 04:07 Go to previous message
m3025869
Messages: 3
Registered: December 2014
Junior Member
Here is example to propagate message from Q1 to Q2 ( Q1 at Database-A & Q2 at Database-B)

1. Create database link from Database-A to Database-B (e.g. DBLINKATOB)

2.Create object type for message payload on both databases ( Database-A & Database-B )

CREATE OR REPLACE TYPE q_msg_tp1 AS OBJECT (
msg VARCHAR2(40)
);

3. CREATE Q1 on Database-A
Begin
DBMS_AQADM.create_queue_table(
queue_table => 'q1_tab',
queue_payload_type => 'q_msg_tp1',
multiple_consumers => TRUE);

DBMS_AQADM.create_queue(
queue_name => 'q1',
queue_table => 'q1_tab');

DBMS_AQADM.start_queue(
queue_name => 'q1',
enqueue => TRUE);
End;

4. Schedule propagation on Q1 on Database-A

DECLARE
v_qname varchar2(50):='q1';
v_dest varchar2(50):='DBLINKATOB';
BEGIN
DBMS_AQADM.Schedule_Propagation(Queue_Name => v_qname,
Destination => v_dest,
Start_Time => sysdate,
Latency => 0);

END;
/
--------------

5. CREATE Q2 on Database-B
Begin
DBMS_AQADM.create_queue_table(
queue_table => 'q2_tab',
queue_payload_type => 'q_msg_tp1',
multiple_consumers => TRUE);

DBMS_AQADM.create_queue(
queue_name => 'q2',
queue_table => 'q2_tab');

DBMS_AQADM.start_queue(
queue_name => 'q2',
enqueue => TRUE);
End;
---

6. ENQUEUE message in Q1
Declare
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message q_msg_tp1;
recipients DBMS_AQ.aq$_recipient_list_t;
p_add varchar2(100);
BEGIN

p_add:='SCHEMA.Q2@DBLINKATOB';
recipients(1) := SYS.aq$_agent('RECIPIENT', p_add, null);
message_properties.recipient_list := recipients;

message := q_msg_tp1('MSG_'||to_char(sysdate,'DD/MM/YYYY hh24:mi:ss'));

dbms_aq.enqueue(queue_name => 'Q1',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
commit;
end;
/


--***************

7. Dequeue message from Q2 at Database-B

DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg q_msg_tp1;

BEGIN
l_dequeue_options.consumer_name:='RECIPIENT';
DBMS_AQ.dequeue(queue_name => 'Q2',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
DBMS_OUTPUT.put_line('Message : ' || l_event_msg.msg);
DBMS_OUTPUT.put_line('------');
COMMIT;

END;
/

--***********
Previous Topic: propagation between 3 queues( Oracle AQ)
Next Topic: Working with two queues owned by different users
Goto Forum:
  


Current Time: Thu Mar 28 10:12:17 CDT 2024