9 Replies Latest reply: May 6, 2014 2:53 PM by spur230 RSS

    AQ procedure callback not working

    santoshkbh

      Hi,

      I am facing an issue wherein the callback procedure is not being called automatically when a new message in queued. At start this queue worked fine, But the call back package(P_AQ_TEST) was invalidated in between and the callback stopped. Then after making the package valid, i have cleared all the messages from the queue. Though new messages being queued are still not calling the callback procedure. It seems the AQ agent which listens for queue doesnt recognise the new status of package. Any suggestions will be helpful. Is there any parameter need to be set or DB restart? or some agent configuration?

       

      Details:

      Oracle Version : 11.2.0.2

      job queue processes : 25 (there are enough process as we dont have any jobs or schedulers)

       

      Queue Details:

       

      begin
      DBMS_AQADM.CREATE_QUEUE_TABLE (
      queue_table        => 'AQ.TEST_AQ_MSG',
      queue_payload_type => 'AQ.MESSAGE_TEST_TYPE',
      multiple_consumers => TRUE
      );
      end;

      BEGIN
      DBMS_AQADM.CREATE_QUEUE (
      queue_name         => 'AQ.TEST_MSG_QUEUE',
      queue_table        => 'AQ.TEST_AQ_MSG');
      end;

      begin
      DBMS_AQADM.START_QUEUE (
      queue_name         => 'AQ.TEST_MSG_QUEUE');
      end;


      --- To grant priviliges to MYSCHEMA

      begin
      DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
         privilege     =>     'ALL',
         queue_name    =>     'AQ.TEST_MSG_QUEUE',
         grantee       =>     'MYSCHEMA',
         grant_option  =>      TRUE);  
      end;

      -- To create agent to call our proc when message is queued

      BEGIN
          dbms_aqadm.add_subscriber
           ( queue_name => 'AQ.TEST_MSG_QUEUE',
             subscriber => sys.aq$_agent( 'RECEIVER', null, null ) );
      END;

      BEGIN
            dbms_aq.register
           ( sys.aq$_reg_info_list(
               sys.aq$_reg_info('AQ.TEST_MSG_QUEUE:RECEIVER',
                                  DBMS_AQ.NAMESPACE_AQ,
                                 'plsql://MYSCHEMA.P_AQ_TEST.SP_AQ_DEQUEUE_TEST_MSG',
                                  HEXTORAW('FF')) ) ,
              1 );
      END;

        • 1. Re: AQ procedure callback not working
          Renu-Oracle

          Hi,

           

          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;

           

           

          ref:

          AQ PL/SQL Notification: PL/SQL Callback and Email Notification (Doc ID 225749.1)

          Thanks

          • 2. Re: AQ procedure callback not working
            santoshkbh

            Thanks for the response.

            job_queue_processes: 25

            aq_tm_processes: 4

             

            I dont have DBA access to generate or verify trace.

             

             

            Thanks

            Santosh

            • 3. Re: AQ procedure callback not working
              Renu-Oracle

              Hi Santosh,

               

              Some issues get resolved by autotuning of aq_tm_processes.

               

              alter system reset aq_tm_processes scope=spfile sid='*';


              Thanks,

              Renu

              • 4. Re: AQ procedure callback not working
                User13260331-Oracle

                Hi Santosh,

                 

                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).

                Or

                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.

                 

                Thanks,

                Lalitha

                • 5. Re: AQ procedure callback not working
                  santoshkbh

                  Hi Renu,

                  We never touched this parameter aq_tm_processes parameter so its in autotuning mode.

                   

                  Hi Lalitha,

                  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?

                   

                  SIDSERVER_TYPESTATUSNUM_NTFNSNUM_EVENTS_PROCESSEDNUM_AQ_NTFNSTOTAL_EMON_LATENCY
                  154REGULAR IDLE5412543
                  230REGULAR IDLE3330
                  306REGULAR IDLE1111110
                  382REGULAR IDLE2220
                  458RELIABLEIDLE0000

                   

                  Please suggest.

                  Regards,

                  Santosh.

                  • 6. Re: AQ procedure callback not working
                    Renu-Oracle

                    Hi Santosh,

                     

                    EMNC

                    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.

                     

                    Ennn

                    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,

                    Renu

                    • 7. Re: AQ procedure callback not working
                      santoshkbh

                      Hi Renu,

                      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.

                       

                      Regards,

                      Santosh

                      • 8. Re: AQ procedure callback not working
                        Renu-Oracle

                        Hi,

                         

                        What are the state of messages in queue which are not getting dequeued?

                         

                        Thanks,

                        Renu

                        • 9. Re: AQ procedure callback not working
                          spur230

                          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