Thanks for the question, Thiago Vidal Passos.
Asked: July 26, 2017 - 2:10 pm UTC
Last updated: August 02, 2017 - 3:16 am UTC
Version: RIB version 13.1.8
Viewed 1000+ times
You Asked
We are trying to set up an AQ Schedule Propagation between two JMS (RIB version 13.1.8 to RIB version 16.0.0). We followed all documentation steps to set this up, but even with the AQ JOB created and running, the message is not being dequeued from the queue, it is still there under READY status and no logs were showed up in oracle alert log.
Please, check below the necessary steps to recreate this setup:
--We are using core/default RIB AQ Topic (ETASNOUTAT and ETASNOUT)
--create a public database link between both jms db
create public database link cyarib13torib16
connect to admribaq
identified by <pwd>
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx )(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devrib)
)
)';
/
--add a subscriber to source queue (agent properties pointing to destination queue)
begin
dbms_aqadm.add_subscriber (queue_name => 'ETASNOUTAT',
subscriber => sys.aq$_agent ('CYA_PROPAGATION_SUB_1', 'ADMRIBAQ.ETASNOUT@CYARIB13TORIB16', null),
queue_to_queue => true);
end;
/
--schedule propagation of message from local ETASNOUTAT to remote ETASNOUT
begin
dbms_aqadm.schedule_propagation (queue_name => 'ETASNOUTAT', --source queue
destination => 'CYARIB13TORIB16', --dblink
start_time => sysdate, --start time
duration => null, --how many times this sched job will run
next_time => 'sysdate +1/720', --next execution time
latency => 0, --response time
destination_queue => 'ADMRIBAQ.ETASNOUT'--destination queue
);
end;
/
--aq job has been created and its running
select * from dba_scheduler_jobs
where job_name like 'AQ_JOB$%'
select * from dba_scheduler_job_run_details
where job_name like 'AQ_JOB$%'
--aq message still in the source topic under READY status
select * from aq$etasnoutattable
--no aq message found in the destination topic
select * from aq$etasnouttable
and Connor said...
I dont know much about RIB. Here's a top to bottom example of propagation across two databases. Use this as a guide to see if any step was missed (eg queues started/enabled, transactions committed, etc).
--
-- 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>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment