Env: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Hello All, We have been using Oracle Advanced queues for over a year now. Each queue has its own Subscriber/Consumer linked with a dequeue procedure. Except for our production environment all other Oracle instances (exact copy for development and testing/QA) the subscribers stopped working. Enqueue works, so the queue is getting stacked up with READY Status but Subscriber doesn't pick up for Dequeue. We are just fortunate that our main production instance doesn't have this issue, however, we are fearful. Following are a list of actions I took before reaching this community:
Dropped and recreated the Queue obj, Queue table, Queue, and Subscriber/Consumer. Started the Queue. Complied the enqueue and dequeue procedures and created a subscriber (Sample attached)
Enqueue works. All records go into READY status. The queue record shows ENQ_TIME/USER/TXN_ID but DEQ columns are empty. The Consumer column also has the subscriber name.
We enabled and disabled all the ENQ and DEQ using command and TOAD.
We copied the entire schema from our PRODUCTION instance into our development instance with no luck.
When I create a new Test Queue and Subscriber/Consumer it works. Just the existing Subscribers that have been running for over a year don't seem to start.
Just to be safe we gave all the grants associated (scrip included).
GRANT EXECUTE ON SYS.DBMS_AQADM to OURSCHEMA;
GRANT EXECUTE ON SYS.DBMS_AQ to OURSCHEMA;
GRANT CREATE ANY TYPE TO OURSCHEMA;
GRANT CONNECT, RESOURCE, aq_administrator_role TO OURSCHEMA;
GRANT aq_administrator_role TO OURSCHEMA;
GRANT EXECUTE ON DBMS_AQADM to OURSCHEMA;
GRANT EXECUTE ON DBMS_AQ TO OURSCHEMA;
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'OURSCHEMA',
admin_option => FALSE);
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'OURSCHEMA',
admin_option => FALSE);
-- remove subscriber
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('INTFC_FILE_QUEUE_SUBSCRIBER',NULL, NULL);
DBMS_AQADM.REMOVE_SUBSCRIBER(
queue_name => 'INTFC_FILE_QUEUE',
subscriber => subscriber);
END;
-- Create Subscriber
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER (
QUEUE_NAME => 'FILE_QUEUE',
SUBSCRIBER =>
SYS.AQ$_AGENT ('INTFC_FILE_QUEUE_SUBSCRIBER2', 'FILE_QUEUE', NULL));
DBMS_AQ.REGISTER (SYS.AQ$_REG_INFO_LIST (SYS.AQ$_REG_INFO (
'INTFC_FILE_QUEUE:FILE_QUEUE_SUBSCRIBER',
DBMS_AQ.NAMESPACE_AQ,
'PLSQL://FILE_TXN_DEQUEUE_PROCEDURE',
HEXTORAW ('FF'))),
1);
END;
/
END;