Advanced Queue Remote
来源:互联网 发布:ie浏览器官方下载 mac 编辑:程序博客网 时间:2024/06/06 13:56
Diagram
From the diagram, we can see two following point:
- queue1 and queue2 should enable both enqueue and dequeue operation.
- when dequeue from queue2@db2, consumer name should be same as subscriber in queue@db1
Implement scirpt
DB1
conn / as sysdba@db1
create role my_aq_adm_role ;
grant connect,resource, AQ_ADMINISTRATOR_ROLE to my_aq_adm_role;
create role my_aq_user_role;
grant connect, resource,aq_user_role to my_aq_user_role;
exec dbms_aqadm.grant_system_privilege(privilege=>'ENQUEUE_ANY',grantee=>'my_aq_user_role',admin_option=>false);
exec dbms_aqadm.grant_system_privilege(privilege=>'DEQUEUE_ANY',grantee=>'my_aq_user_role',admin_option=>false);
Scenario 1:
1. create user aqadm and grant queue admin privilege. create payload
conn / as sysdba@db1
create user aqadm identified by aqadm;
grant my_aq_adm_role to aqadm;
conn aqadm/aqadm@db1
create type my_payload is object(msg varchar2(100));
/
grant execute on my_payload to my_aq_user_role;
2. create user aquser and grant queue user privilege
conn / as sysdba@db1
create user aquser identified by aquser;
grant my_aq_user_role to aquser;
3. use aqadm create queue table and queue(msg_queue, payload=aqadm.my_payload)
conn aqadm/aqadm@db1
exec dbms_aqadm.create_queue_table(queue_table => 'MSG_QUEUE_TAB',queue_payload_type => 'aqadm.my_payload',multiple_consumers => true);
exec dbms_aqadm.create_queue(queue_name => 'MSG_QUEUE',queue_table => 'MSG_QUEUE_TAB');
exec dbms_aqadm.start_queue(queue_name => 'MSG_QUEUE',enqueue => TRUE,dequeue => TRUE);
exec dbms_aqadm.add_subscriber(queue_name => 'MSG_QUEUE',subscriber => SYS.AQ$_AGENT('OMS','aqadm.FF_msg_queue@FF_OMS_Q.WALMART.COM',NULL));
EXEC dbms_aqadm.schedule_propagation(queue_name => 'MSG_QUEUE',destination => 'FF_OMS_Q.WALMART.COM');
4. use aquser to enqueue message to aqadm.FF_msg_queue@FF_OMS_Q.WALMART.COM
conn aquser/aquser@db1
DECLARE
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(16);
my_message aqadm.my_payload :=aqadm.my_payload('From aquser@oms');
BEGIN
DBMS_AQ.ENQUEUE(
queue_name => 'aqadm.msg_queue',
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
COMMIT;
END;
/
DB2
conn / as sysdba@db2
create user aqadm identified by aqadm;
grant my_aq_adm_role to aqadm;
alter user aqadm quota unlimited on system;
conn aqadm/aqadm@db2
create type my_payload is object(msg varchar2(100));
/
grant execute on my_payload to my_aq_user_role;
2. create user aquser and grant queue user privilege
conn / as sysdba@db2
create user aquser identified by aquser;
grant my_aq_user_role to aquser;
3. use aqadm create queue table and queue(msg_queue, payload=aqadm.my_payload)
conn aqadm/aqadm@db2
exec dbms_aqadm.create_queue_table(queue_table => 'FF_MSG_QUEUE_TAB',queue_payload_type => 'aqadm.my_payload',multiple_consumers => true);
exec dbms_aqadm.create_queue(queue_name => 'FF_MSG_QUEUE',queue_table => 'FF_MSG_QUEUE_TAB');
exec dbms_aqadm.start_queue(queue_name => 'FF_MSG_QUEUE',enqueue => true,dequeue => TRUE);
--exec dbms_aqadm.add_subscriber(queue_name => 'FF_MSG_QUEUE',subscriber => SYS.AQ$_AGENT('FF',null,NULL));
exec dbms_aqadm.add_subscriber(queue_name => 'FF_MSG_QUEUE',subscriber => SYS.AQ$_AGENT('OMS',null,NULL));
conn aquser/aquser@db2
SET SERVEROUTPUT ON;
DECLARE
queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(2000);
my_message aqadm.my_payload;
BEGIN
queue_options.consumer_name :='OMS';
queue_options.navigation := dbms_aq.first_message;
DBMS_AQ.DEQUEUE(
queue_name => 'aqadm.ff_msg_queue',
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id );
COMMIT;
DBMS_OUTPUT.PUT_LINE('This message is '||my_message.msg);
END;
/
Troubshooting
when there is error during the process of enqueuing and dequeuing, there are some views and table you may need to refer to.
- user_queue_schedule: informaton about the propagation happened right now.
- queue_table(msg_queue_table,ff_msg_queue_tab@ff_oms_q.walmart.com): when enqueue message from msg_queue, dblink dequeue the message from source queue(msg_queue) immediately and equeue to the destination queue. Then at the destination, the real customer can dequeue the message.
- Advanced Queue Remote
- Advanced Queue—Local
- Oracle Advanced Queue (DBMS_AQ/DMBS_AQADM)
- Oracle高级队列(Advanced Queue)初体验
- JMS+Oracle Advanced Queue(AQ)用法实践
- Stream Advanced Queue--学习笔记一
- Oracle高级队列(Advanced Queue)初体验
- LFI2RCE (Local File Inclusion to Remote Code Execution) advanced exploitation: /proc shortcuts
- Oracle Advance Queue or Advanced Queuing from .NET (C#/VB/MC++)
- PAT (Advanced Level) 1004. Counting Leaves (30) 层序遍历,两个queue辅助
- PAT (Advanced Level) 1056. Mice and Rice (25) 模拟比赛,用queue辅助
- Queue
- queue
- Queue
- QUEUE ~
- queue
- queue
- queue
- LocalAlloc,VirtualAlloc,malloc,new的异同
- GridView最简单的分页
- DicomImage::createWindowsDIB的用法
- 123
- Msys/MinGW与Cygwin/gcc
- Advanced Queue Remote
- 面试经验谈
- MinGW介绍与使用
- 日期类mydate的测试类testmydate
- 网络广告的优势与特点
- blkid 命令的用法
- VS2005下Qt编程--1
- Response.AddHeader使用实例收集 如何做下载,下载中文显示乱码怎么办 ContentType类型大全
- Chapter 6 Reusing Classes