Friday, 13 April 2018

ORA-25207: enqueue failed, queue is disabled from enqueueing - Oracle Database

ORA-25207: enqueue failed, ORA-06512: at "SYS.DBMS_AQ" in Oracle Database

ORA-25207: enqueue failed, ORA-06512: at "SYS.DBMS_AQ"



It was the time when I was trying to reconfigure the IFS ERP Application with a newly created database on which a fresh import of the database was done. At the middle of reconfigure operation, the below mentioned error appeared in the log which failed the remaining reconfigure operation.

Error in Ant-script: server\install_fndbas.xml
Reported error is: The following error occurred while executing this line:
D:\IFS_WEB\CURADEV9\repository\server\install_fndbas.xml:723: ifs.fnd.base.ApplicationException: ORA-25207: enqueue failed, queue IFSAPP.BATCH_PROC_QUEUE is disabled from enqueueing
ORA-06512: at "SYS.DBMS_AQ", line 180

Since the error was related to back-end i.e. Oracle Database so here is this post regarding ORA-25207.

Reason:


After the Full Import using IMPDP (Import via Data Pump), somehow the enqueue & dequeue options were disable for the queue "BATCH_PROC_QUEUE". To check the same, I ran the below mentioned query,

SQL> col owner for a30;
SQL> col name for a30;
SQL> select owner,name,enqueue_enabled,dequeue_enabled from dba_queues where name like 'BATCH_PROC_QUEUE';

OWNER                          NAME                           ENQUEUE DEQUEUE
------------------------------ ------------------------------ ------- -------
IFSAPP                         BATCH_PROC_QUEUE                 NO     NO

Resolution:


In order to fix the same, I needed to enable the Enqueue and Dequeue operation for the BATCH_PROC_QUEUE, so I simply ran the below mentioned query from SYS user,

SQL> exec dbms_aqadm.start_queue(queue_name => 'IFSAPP.BATCH_PROC_QUEUE');

PL/SQL procedure successfully completed.

Note: The owner of the queue can be different from SYS so don't forget to mention the Queue Owner with the Queue name while running the above mentioned query or the query might fail.

Run the below mentioned query to verify the changes,

SQL> select owner,name,enqueue_enabled,dequeue_enabled from dba_queues where name like ‘BATCH_PROC_QUEUE’;

OWNER                          NAME                           ENQUEUE DEQUEUE
------------------------------ ------------------------------ ------- -------
IFSAPP                         BATCH_PROC_QUEUE                 YES     YES

After this, I was able to reconfigure the application successfully.

For more details regarding this error, please follow Oracle Doc ID 135692.1

I hope this helps !!

1 comment: