Subscription Notification Not Working After Oracle 12c to 19c Upgrade

Hi ​@AshenR,

As a common practice, we should perform a recompilation of oracle internal packages and components, in case you find set of invalid objects right after this of import db event.

As a workaround for this (since this seems to be an Oracle common issues as it seems), you can look into the following.

• While it may show queues as enabled from application end, the Oracle database internally manages system queues such as SYS.AQ_SRVNTFN_TABLE_Q_1.

• If this queue is missing or inactive, notifications cannot be processed.so you may run a query to confirm the queue exists:

EXEC DBMS_AQADM.stop_queue('SYS.AQ_SRVNTFN_TABLE_Q_1', TRUE, TRUE); EXEC DBMS_AQADM.start_queue('SYS.AQ_SRVNTFN_TABLE_Q_1', TRUE, TRUE);

• If exists, try stopping and starting the queue

EXEC DBMS_AQADM.stop_queue('SYS.AQ_SRVNTFN_TABLE_Q_1', TRUE, TRUE); EXEC DBMS_AQADM.start_queue('SYS.AQ_SRVNTFN_TABLE_Q_1', TRUE, TRUE);

• Check the Status of the Queue :The queue should be enabled for enqueue and dequeue operations to allow messages to flow through.Both should return ‘YES’

SELECT enqueue_enabled, dequeue_enabled FROM dba_queues WHERE queue_name = 'AQ_SRVNTFN_TABLE_Q_1';

• If the queue is missing, that confirms the root cause.

• Recompile Oracle Internal Packages & Components
  Run as SYS user:(Better to keep backups as usual prior these executions)

  @$ORACLE_HOME/rdbms/admin/catproc.sql
  
  @$ORACLE_HOME/rdbms/admin/catalog.sql  
  
  @$ORACLE_HOME/rdbms/admin/utlrp.sql

   

• These altogether refreshes DBMS_AQ and scheduler components critical for queues.

• Ensure the IFS_AGENT exists and has DB access:

  SELECT * FROM dba_aq_agents WHERE agent_name = 'IFS_AGENT';

• If not, recreate the AQ Agent and enable DB Access

  BEGIN DBMS_AQADM.CREATE_AQ_AGENT('IFS_AGENT'); EXCEPTION WHEN OTHERS THEN NULL; END; / EXEC DBMS_AQADM.ENABLE_DB_ACCESS('IFS_AGENT');

• Restart IFS Notification & Background Jobs
  Ensure Event Action Handler and background jobs are running smoothly. (SELECT job_name, enabled, state FROM dba_scheduler_jobs WHERE job_name LIKE '%SUBSCRIPTION%';)  Notification processing relies on background jobs like Event Action Executors and Email Sender services within IFS. Make sure these jobs are running without errors and are not stuck or disabled.

• Test Email Notifications Using IFS APIs

• Notes:

  • If this does not work, you may check if IFS has released a patch that includes the Oracle-side fix. (may need to work with extended support to restore missing queues or apply patches)

  • Validate AQ status as part of your post-upgrade checklist.

  • If email notifications still don’t work after queue restoration, verify SMTP settings, IFS Notification background jobs, and AQ listeners.

• In post-upgrade validations, always ensure system queues like AQ_SRVNTFN_TABLE_Q_1 are present and valid. If not, confirm if any IFS patch aligns with the solution.

Oracle Docs related to this issue:

• Doc ID 742060.1 – How to Re-create Missing AQ Queues

• Doc ID 1361230.1 – Troubleshooting Oracle AQ Issues

• Doc ID 1370538.1 – How to Verify AQ Setup After Upgrade

Hope this helps!

Regards,

Binuri