Start of Advanced Queue job hanging in Oracle 19c

 

Oracle Advanced Queuing (AQ) is a database-integrated messaging infrastructure in Oracle Database. AQ leverages the functionality of the Oracle database to store messages in persistent queues. All operational benefits of the Oracle database such as high availability, scalability and reliability are applicable to the messages and queues in

In this blog post I am discussing about specific issue where I am not able to start the Advanced queue. I created a clone of source PDB(prodpoc) from one CDB to another CDB on same ExaCC VM with different PDB name(pprd8) in target CDB.

After I started new PDB in read-write mode, I am not able to start the advanced Queue and its hanging.

execute DBMS_AQADM.START_QUEUE('TEST_Q', TRUE, TRUE);

After troubleshoot and go through with logs I found this queue is still using old service name with old PDB name in it. 

When I checked the services I found the AQ using service which still point to old PDB.

(sys$cdcadmin.bep_multi_consumer_queue.prodpoc.test.ca)

select name, network_name, con_id# from cdb_service$;
NAME                                     NETWORK_NAME                                                    CON_ID#
---------------------------------------- ------------------------------------------------------------ ----------
cdbpprdxdb                               cdbpprdxdb                                                            1
cdbpprd_58s_yyz.test.ca                  cdbpprd_58s_yyz.test.ca                                               1
cdbpprd1_pprd8.test.ca                   cdbpprd1_pprd8.test.ca                                                3
cdcadmin.bep_multi_consumer_queue        sys$cdcadmin.bep_multi_consumer_queue.prodpoc.test.ca                 3
prodpoc_srv.test.ca                      prodpoc_srv.test.ca                                                   3
pprd8                                    pprd8                                                                 3

12 rows selected.

This service looks like system generated service and we can not change it, I tried to edit or recreate this service but still queue was hanging.

The only solution I found is to re-create the PDB using SERVICE_NAME_CONVERT parameter.

so I drop the PPRD8 PDB and recreate using below command..

CREATE PLUGGABLE DATABASE PPRD8  USING '/stage/PRODPOC.xml' copy standbys=NONE SERVICE_NAME_CONVERT=('PRODPOC','PPRD8') parallel 4;

After creating the PDB again with above parameter now I am able to start the queue and issue resolved.