5 Replies Latest reply: Jun 28, 2012 4:51 AM by 758358 RSS

    notifyCB notification procedure not being called.

    Greg Block
      Hi.

      I'm running on an 11g (11.2.0.2.0) database and trying to get my notification procedure called in advanced queues.

      I followed the example by asktom here:
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8760267539329

      I completed the same steps except for the creation of the table he inserts his de-queued message into (instead I redirected the insert to another pre-existing table).

      Everything works fine upto a point. The message is placed into the queue ( and I can query on it to confirm it's there)
      However the dequeue routine ins't being called.

      I've included logging code into it and confirmed it's not being called.

      Is there an extra step that needs to be performed to enable the de-queue routine to be called ?

      Regards,
      Greg.
        • 1. Re: notifyCB notification procedure not being called.
          758358
          Hi,

          The registration process is the bit that should initiate the async DQ, so this bit from your example:
          aq@ORA920> BEGIN
            2      dbms_aq.register
            3      ( sys.aq$_reg_info_list(
            4          sys.aq$_reg_info('AQ.MSG_QUEUE:RECIPIENT',
            5                            DBMS_AQ.NAMESPACE_AQ,
            6                           'plsql://AQ.notifyCB',
            7                            HEXTORAW('FF')) ) ,
            8        1 );
            9  end;
           10  /
          You can check in DBA_SUBSCR_REGISTRATIONS to see what has been registered:
          SELECT a.location_name,
                 a.subscription_name
          FROM   DBA_SUBSCR_REGISTRATIONS a;
          It is also worth going through the below note on My Oracle Support to check what might be causing issues with the background process which picks up notification calls:

          Event Monitor Process: Architecture and Known Issues (Doc ID 105067.1)

          Thanks
          Paul

          Edited by: pdtill2508 on Jun 25, 2012 9:10 AM
          • 2. Re: notifyCB notification procedure not being called.
            Greg Block
            Hi Paul.

            Thank you for your help so far, much appreciated.

            I have performed the register process and confirmed the entry in DBA_SUBSCR_REGISTRATIONS (and sys.reg$).

            The metalink doc id has some trouble shooting steps which I don't understand.
            The PLSQL troubleshooting section has a section on job_queue_process's which de-queue the messages (forgive my lack of info, but I thought my registered procedure would do the dequeueing?).

            It looks for a dba_jobs entry like '%register_driver%'. Not really sure what that is. I can't find an entry for anything like that. Would that be my issues as to why my registered procedure isn't being called ?

            I guess I'm a little lost, hoping I could be pointed in the right direction.

            Regards,
            Greg.
            • 3. Re: notifyCB notification procedure not being called.
              758358
              Hi,

              You are running 11.2 so you need to look at the scheduler not dba jobs - job_queue_processes is auto tuned from 11g:
              select p.spid, p.program, rj.job_name, sj.job_action from dba_scheduler_jobs sj, dba_scheduler_running_jobs rj, v$session s, v$process p where
              sj.job_name = rj.job_name and
              rj.session_id = s.sid and
              s.paddr = p.addr and
              lower(sj.job_action) like '%register_driver%';
              Did you also run this query (remember to change the APP_SCHEMANAME etc. to match your queue schema, table and queue name)?
              select
                msgid notify_msgid,
                to_char(n.enq_time,'DD-MON-YYYY hh24:mi:ss'),
                n.user_data.msg_id app_msgid,
                n.user_data.queue_name qname,
                nvl(utl_raw.cast_to_varchar2(n.user_data.payload),'null') payload
              from sys.aq_srvntfn_table_<N> n
              where n.user_data.msg_id in (
                select n.user_data.msg_id msgid
                from sys.aq_srvntfn_table_<N> n
                minus
                select msgid
                from <APP_SCHEMANAME>.<APP_QUEUE_TABLENAME>)
                and n.user_data.queue_name = '<APP_SCHEMANAME>.<APP_QUEUENAME>';
              Can you see the emon* process at the OS level? Are any trace files being generated or any messages in the database alert log?

              Thanks
              Paul
              • 4. Re: notifyCB notification procedure not being called.
                Greg Block
                Hi Paul.

                Thanks for staying with me on this.

                Currently, my job_queue_processes is set as:
                select value from v$parameter where name='job_queue_processes';
                14
                The sql queries you have are slightly different from the doc id, so hadn't run them before.

                The first returned no result, so I checked entries in dba_scheduler_jobs and found no lower(job_actions) records that were like '%register_driver%'.
                Should an entry be there at least? If so, is that part of a normal install of the database where AQ is concerned ?

                As for the second query, no results either.
                With that in mind, I checked entries in sys.aq_srvntfn_table_1 to look for the msgid of the message that was enqueued.

                I can confirm my message is listed in my app_queue_tablename (which in my code is AQ_MESSAGE_QTAB) however,
                it's not listed at all iin sys.aq_srvntfn_table_1 (I checked by outputing the msgid parameter of the dbms_aq.enqueue call and looked for that)

                Would the %register_driver% dba_scheduler_jobs entry have created that record in sys.aq_srvntfn_table_1 if it was there and running?

                Regards,
                Greg.

                EDIT: I can't see any trace files being generated nor any items in the alert log when a message is enqueued.
                As for the emon process, sorry I'm not sure what that is. I see it mentioned on the metalink site but wasn't sure what it was.

                EDIT2: I'll be away for the next 2 weeks so thank you for your help albeit I wont be able to respond until then.

                Edited by: Greg Block on 27/06/2012 14:55
                • 5. Re: notifyCB notification procedure not being called.
                  758358
                  Hi,

                  emon is a background process kind of like pmon - if you are running on unix/linux a quick check on the DB host should show it:

                  ps -ef | grep emon

                  When I get chance I'll do some of my own tests on 11.2, I've not touched notification since 10.2

                  Thanks
                  Paul