| 10 g AQ propagation to a remote queue not working [message #249223] |
Tue, 03 July 2007 12:22  |
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  |
 |
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;
/
--***********
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
