This content has been marked as final. Show 5 replies
The registration process is the bit that should initiate the async DQ, so this bit from your example:
You can check in DBA_SUBSCR_REGISTRATIONS to see what has been registered:
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 /
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:
SELECT a.location_name, a.subscription_name FROM DBA_SUBSCR_REGISTRATIONS a;
Event Monitor Process: Architecture and Known Issues (Doc ID 105067.1)
Edited by: pdtill2508 on Jun 25, 2012 9:10 AM
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.
You are running 11.2 so you need to look at the scheduler not dba jobs - job_queue_processes is auto tuned from 11g:
Did you also run this query (remember to change the APP_SCHEMANAME etc. to match your queue schema, table and queue name)?
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%';
Can you see the emon* process at the OS level? Are any trace files being generated or any messages in the database alert log?
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>';
Thanks for staying with me on this.
Currently, my job_queue_processes is set as:
The sql queries you have are slightly different from the doc id, so hadn't run them before.
select value from v$parameter where name='job_queue_processes'; 14
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?
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
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