Skip to Main Content
  • Questions
  • Queue_to_Queue Propagation does not Propagate Messages but no errors are reported

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Comments

Not working

arunsuba, March 19, 2019 - 3:11 pm UTC

I followed the same steps (12c) and its not working. and i couldnt see any data in the target system. also no error. any latest update?

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.