Oracle Advanced Queuing (AQ) is a database-integrated messaging infrastructure in the 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 apply to the messages and queues.
 

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

After I started the new PDB in read-write mode, I was not able to start the advanced Queue, and it’s hanging.

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

After troubleshooting and going through logs I found this queue is still using the old service name with the old PDB name in it. 

When I checked the services I found the AQ using a service that still points to the 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 system-generated service and we can not change it, I tried to edit or recreate this service but still queue was still hanging.

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

so I dropped the PPRD8 PDB and recreated using the 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 the above parameter now I am able to start the queue and the issue resolved.