Oracle Queue_to_Queue传播不传播消息，但不报告错误

我们正在尝试设置两个JMS (RIB版本13.1.8到RIB版本16.0.0) 之间的AQ计划传播。我们按照所有文档步骤进行设置，但是即使创建并运行AQ作业，消息也不会从队列中出队，它仍然处于就绪状态，并且oracle警报日志中没有显示日志。

请检查以下必要步骤以重新创建此设置:

-我们正在使用核心/默认RIB AQ主题 (ETASNOUTAT和etasnout)

-在两个jms db之间创建公共数据库链接
创建公共数据库链接cyarib13torib16
连接到admribaq
由 标识
使用 '(描述 =
(地址列表 =
(地址 = (协议 = TCP)(主机 = xxxx )(端口 = 1521))
)
(连接 _ 数据 =
(服务器 = 专用)
(服务名称 = devrib)
)
) ';
/

-将订阅服务器添加到源队列 (指向目标队列的代理属性)
开始
dbms_aqadm.add_subscriber (队列名称 => 'ETASNOUTAT'，
订阅者 => 系统.aq $ _ 代理 ('cya_propagation_sub_1 '，'ADMRIBAQ.Etasnot @ CYARIB13TORIB16'，空)，
queue_to_queue => true);
结束;
/

-- 调度消息从本地ETASNOUTAT到远程ETASNOUTAT的传播
开始
dbms_aqadm.schedule_propagation (queue_name => 'ETASNOUTAT'，-- 源队列
目的地 => 'cyarib13torib16'，-dblink
开始时间 => 系统日期，-- 开始时间
duration => null，-- 这个sched作业将运行多少次
next_time => 'sysdate 1/720 '，-- 下一次执行时间
延迟 => 0，-- 响应时间
destination_queue => 'ADMRIBAQ.ETASNOUT' -- 目标队列
);
结束;
/

-aq作业已创建并正在运行
从dba_scheduler_jobs中选择 *
其中工作名称如 “AQ_JOB $ %”

从dba_scheduler_job_run_details中选择 *
其中工作名称如 “AQ_JOB $ %”

-- aq消息仍在源主题处于就绪状态
从aq $ etasnoutatable中选择 *

-在目标主题中找不到aq消息
从aq $ etasnouttable中选择 *

我对肋骨了解不多。下面是在两个数据库中传播的从上到下的示例。使用此作为指南，以查看是否错过了任何步骤 (例如，已启动/启用的队列，已提交的事务等)。

--
-- SOURCE
--

SQL> conn scott/tiger@db122
Connected.

SQL> exec dbms_aqadm.stop_queue(queue_name => 'qsrc'); exception when others then null;

PL/SQL procedure successfully completed.

SQL> exec dbms_aqadm.drop_queue(queue_name => 'qsrc'); exception when others then null;

PL/SQL procedure successfully completed.

SQL> exec dbms_aqadm.drop_queue_table(queue_table => 'qsrc_t'); exception when others then null;

PL/SQL procedure successfully completed.

SQL> drop type qpayload;
drop type qpayload
*
ERROR at line 1:
ORA-04043: object QPAYLOAD does not exist


SQL> drop database link scott_dblink;
drop database link scott_dblink
                   *
ERROR at line 1:
ORA-02024: database link not found


SQL> create database link scott_dblink connect to scott identified by tiger using 'db11';

Database link created.

SQL> select * from tab@scott_dblink;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

4 rows selected.

SQL> create or replace type qpayload as object (message CLOB);
  2  /

Type created.

SQL> begin
  2    dbms_aqadm.create_queue_table (queue_table => 'qsrc_t',
  3                                   queue_payload_type => 'scott.qpayload',
  4                                   multiple_consumers => true);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec  dbms_aqadm.create_queue (queue_name => 'qsrc',queue_table => 'qsrc_t');

PL/SQL procedure successfully completed.

SQL> exec  dbms_aqadm.start_queue (queue_name => 'qsrc');

PL/SQL procedure successfully completed.

SQL> begin
  2    dbms_aqadm.add_subscriber (queue_name => 'qsrc',
  3                               subscriber => sys.aq$_agent(name => 'qsrc_subscriber',
  4                                                           address => 'qdest@scott_dblink',
  5                                                           protocol => 0 ),
  6                               queue_to_queue => true);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    dbms_aqadm.schedule_propagation(queue_name => 'scott.qsrc',
  3                                    latency => 1,
  4                                    destination => 'scott_dblink',
  5                                    destination_queue => 'scott.qdest'
  6                                    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

--
-- TARGET
--
SQL> conn scott/tiger
Connected.

SQL> exec dbms_aqadm.stop_queue(queue_name => 'qdest'); exception when others then null;

PL/SQL procedure successfully completed.

SQL> exec dbms_aqadm.drop_queue(queue_name => 'qdest'); exception when others then null;

PL/SQL procedure successfully completed.

SQL> exec dbms_aqadm.drop_queue_table(queue_table => 'qdest_t'); exception when others then null;

PL/SQL procedure successfully completed.

SQL> drop type qpayload;
drop type qpayload
*
ERROR at line 1:
ORA-04043: object QPAYLOAD does not exist


SQL> create or replace type qpayload as object (message CLOB);
  2  /

Type created.

SQL>
SQL> begin
  2
  3    dbms_aqadm.create_queue_table (queue_table => 'qdest_t',
  4                                   queue_payload_type => 'scott.qpayload',
  5                                   multiple_consumers => true );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    dbms_aqadm.create_queue (queue_name => 'qdest',
  3                             queue_table => 'qdest_t');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec  dbms_aqadm.start_queue (queue_name => 'qdest');

PL/SQL procedure successfully completed.



--
-- SOURCE
--


SQL>
SQL> declare
  2    l_msgid       raw(16);
  3    l_enq_options dbms_aq.enqueue_options_t;
  4    l_msg_props   dbms_aq.message_properties_t;
  5  begin
  6    
  7    for i in 1 .. 10
  8    loop
  9      dbms_aq.enqueue(queue_name => 'qsrc',
 10                      enqueue_options => l_enq_options,
 11                      message_properties => l_msg_props,
 12                      payload => qpayload(i||'blah blah blah'),
 13                      msgid => l_msgid);
 14    end loop;
 15    commit;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>

--
-- TARGET
--

SQL> select msgid, t.user_data.message from AQ$_QDEST_T_F t;

MSGID                            USER_DATA.MESSAGE
-------------------------------- --------------------------------------------------------------------------------
103065B9747040C9BD81D2B5CC5898AF 1blah blah blah
7D5FA3A399CE4E339A4F4B5345447576 2blah blah blah
2A9BB58793B946639FFE4765640FC1F2 3blah blah blah
343CD1D3E1B54822B9DBE5FC3A7431AA 4blah blah blah
8ECDACCFF13E49C69FB33756D137E456 5blah blah blah
5D1D6D58C27A41B2B6F31E47A7322B08 6blah blah blah
CEBECA13BB5B42A48A86FC9248D10A7F 7blah blah blah
DF07C198716B482F99335F50E6E47F74 8blah blah blah
EE1EF54546254FC59AD8458C0AD4B0FB 9blah blah blah
977A51C714BD4F2685B0F62E206EB078 10blah blah blah

10 rows selected.

SQL>

复制