are there any traces generated when call back procedure called?
Also get the output of the following:
-Show parameter job_queue_processes;
-Show parameter aq_tm_processes;
AQ PL/SQL Notification: PL/SQL Callback and Email Notification (Doc ID 225749.1)
Thanks for the response.
I dont have DBA access to generate or verify trace.
Some issues get resolved by autotuning of aq_tm_processes.
alter system reset aq_tm_processes scope=spfile sid='*';
1 person found this helpful
Check if the Emon process is running at the OS level.
Kill of those Emon process (emn*, e00*) from OS level, they will start up automatically as and when the the notification is fired again (enqueue a new message).
try to restart the database so that the emon process is restarted and then enqueue a new message and see if helps.
If the still persists, then raise an SR with Oracle Support for further analysis.
We never touched this parameter aq_tm_processes parameter so its in autotuning mode.
For now we dont have option to restart the database as users are using the database. I am manually dequeueing the messages for now. I have requested DBA to check the EMON processes .
Thank you both for you suggestions. Will keep you posted.
Below is the result set from v$emon process. Which process i should be killing for AQ processes to get affected? Killing all of the processes will affect any other processes?
SID SERVER_TYPE STATUS NUM_NTFNS NUM_EVENTS_PROCESSED NUM_AQ_NTFNS TOTAL_EMON_LATENCY 154 REGULAR IDLE 54 12 54 3 230 REGULAR IDLE 3 3 3 0 306 REGULAR IDLE 11 11 11 0 382 REGULAR IDLE 2 2 2 0 458 RELIABLE IDLE 0 0 0 0
1 person found this helpful
EMON Coordinator Process
Coordinates database event management and notifications
EMNC coordinates event management and notification activity in the database, including Streams Event Notifications, Continuous Query Notifications, and Fast Application Notifications.
EMON Slave Process
Performs database event management and notifications
The database event management and notification load is distributed among the EMON slave processes. These processes work on the system notifications in parallel, offering a capability to process a larger volume of notifications, a faster response time, and a lower shared memory use for staging notifications.
The background process responsible is called EMNC, and this process spawns other process labelled E000,…
First of all, we need the PID of the process running on the server
[oracle@xxxxxx ~]$ ps -ef | grep -i emn
[oracle@xxxxx ~]$ kill -9 <PID>
and we check it re-spawns:
[oracle@xxxxxxx ~]$ ps -ef | grep -i emn
Note.- Sometimes killing emnc process is not enough and spawned process will also need to be terminated:
The rule of thumb is to check emnc process has restarted, if after 1 min it has not,the proceed to terminal e00? processes:
[oracle@ellison ~]$ ps -ef | grep -i e00
Thanks for response. I got the emon processes restarted through v$emon view but dint resolve. Finally we had to restart the database and it resolved.
Thank you all for your help.
Thoughh I am facing new issue now. Few messages are getting are not getting dequeued but rest all are.
What are the state of messages in queue which are not getting dequeued?
What does your callback procedure do? Handle exception if you are not already handling it.
See if notification table has any entry
Select * from aq_srvntfn_table_1
See if callback if actually registered and enabled
select * from user_subscr_registrations