This content has been marked as final. Show 5 replies
Could you post some additional information?
Are any messages being posted to the database alert log or job queue trace files?
select SCHEMA, QNAME, DESTINATION, SCHEDULE_DISABLED, PROCESS_NAME, LAST_RUN_DATE, NEXT_RUN_DATE, FAILURES, LAST_ERROR_MSG from DBA_QUEUE_SCHEDULES; select p.SPID, p.PROGRAM, j.JOB_NAME, s.EVENT from v$PROCESS p, DBA_SCHEDULER_RUNNING_JOBS jr, V$SESSION s, DBA_SCHEDULER_JOBS j where s.SID=jr.SESSION_ID and s.PADDR=p.ADDR and jr.JOB_NAME=j.JOB_NAME and j.JOB_NAME like '%AQ_JOB$_%';
When you say the enq_time is 1 hour in front of sysdate, this seems odd if this issue persists after an instance restart, I have seen behavior like this through daylight savings changes if a process which has been running prior to the time change is not restarted but not repeated. What process enqueues the messages on to this queue?
Thanks for taking the time to have a look at this.
I have posted the results from your queries below.
NEXT_RUN_DATE 07-SEP-12 17.48.43.000000000 EUROPE/LONDON
SPID PROGRAM JOB_NAME EVENT
1000 ORACLE.EXE (J000) AQ_JOB$_65 Streams AQ: waiting for messages in the queue
3076 ORACLE.EXE (J003) AQ_JOB$_64 Streams AQ: waiting for messages in the queue
3652 ORACLE.EXE (J005) AQ_JOB$_63 Streams AQ: waiting for messages in the queue
4032 ORACLE.EXE (J004) AQ_JOB$_61 Streams AQ: waiting for messages in the queue
The following PL/SQL procedure enqueues the message
procedure ITEM_2_MSG_EQ -- scheduled job, maybe combine into single scheduled call
SELECT MESSAGE FROM aq_items_message_t WHERE status = 'AWAITING PROCESS'
-- this procedure gets a basket information stores it into an datatype
-- and the create a AQ message with the basket information stored as
-- the data type
-- open a cursor for the package header
FOR rcIte IN cIte
-- submit the message to the queue
nq_opt.visibility := dbms_aq.immediate;
nq_pro.expiration := dbms_aq.never;
SET status = 'QUEUED'
, enqueued_date = SYSDATE
WHERE CURRENT OF cIte;
WHEN OTHERS THEN
lErrm := SUBSTR(SQLERRM, 1, 80);
It just seems very odd that:
a: it works fine in another environment
b: all the messages propogate after a restart which is stand DBMS_JOB functionality when next date is < sysdate
Thanks for reading.
I reimported the schemas and purged all the table, and restarted the props.
This seemed to sort the issue
I don't know for sure, but I think the orphan data that came across when imported the schema must have been corrputing things.
Lesson learnt - AQ is a good tool, but you have to be 'bang on'.
If you are going to import AQ schemas, make sure your queue tables are purged.
However, hands up - my fault this one.
Yes if there is data in the underlying queue objects that doesn't properly hang together (i.e. no corresponding entry in the IOT's for instance) then this can break propagation - you should usually see errors in the job queue trace files when this sort of thing is a problem.
Glad you got to the bottom of it.