This content has been marked as final. Show 10 replies
What version of the database are you running? Is this RAC or single instance?
Are there any messages being output to the Qmon trace files? Can you actually see the q00n background processes on the DB host? If you trace their processes back to the database what are they waiting on?
Did you review:
Master Note for AQ Queue Monitor Process (QMON) (Doc ID 305662.1)
Section: PROCESSED Messages not being removed
---What version of the database are you running? Is this RAC or single instance?
18.104.22.168 single instance
---Can you actually see the q00n background processes on the DB host?
select pname,tracefile from v$process where pname like 'Q0%'
---Are there any messages being output to the Qmon trace files? ? If you trace their processes back to the database what are they waiting on?
os cmd: pwd
same as: SELECT value
WHERE name = 'Diag Trace';
os cmd: ls -l ....G_q0<TAB>
I didn't notice this in your first post:
Retention_time: See doc for this value: http://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_admin.htm#i1006091
DBMS_AQADM.CREATE_QUEUE( Queue_name => 'my_AQ', Queue_table => 'AQT', Queue_type => 0, Max_retries => 65535, Retry_delay => 0, Retention_time => 2592000, <<<<<<<<<< why are you specifying such a high value for this? dependency_tracking => FALSE );
This parameter specifies the number of seconds a message is retained in the queue table after being dequeued from the queue. When retention_time expires, messages are removed by the time manager process. INFINITE means the message is retained forever. The default is 0, no retention.
You are asking for messages to be retained for 30 days, now looking at the min/max enq_time you have messages which are much older than this, but did you check when they were actually DQ'd (DEQ_TIME)?
Also, from my previous post, did you check out that MOS note ? Did you check what the Q00 processes are doing in the database?
Thanks for prompt response.
I want the messages to be retained for 30 days.
DEQ_TIME is null for all records and msg_state is 'PROCESSED'! it's ok?
I checked the document on MOS:
Note 251737.1 PROCESSED Messages remain in Queue Table after a Successful Dequeue
AQ_TM_PROCESSES parameter is set to 10
Note 378247.1 PROCESSED Messages not removed from Queue Table in a RAC database after Reconfiguration
I don't have RAC
Note 752708.1 Intermittently PROCESSED Messages are not removed from Queue Tables by the QMON Processes.
The problem is only observed on the HP-UX Itanium port. I have AIX
Could you run these queries and post the output?
Substitute in your queue table name for <queue_table_name>
select count(*) from AQ$_<queue_table_name>_I i where not exists (select t.msgid FROM <queue_table_name> t where i.msgid = t.msgid); select count(*) from AQ$_<queue_table_name>_T i where not exists (select t.msgid FROM <queue_table_name> t where i.msgid = t.msgid); select count(*) from AQ$_<queue_table_name>_H i where not exists (select t.msgid FROM <queue_table_name> t where i.msgid = t.msgid);
You say count(*)=0 for all 3 but then the output shows differently? I'll assume the output is correct.
You have orphaned data in the underlying queue structures which will probably be preventing the QMON processes from removing the data as it is incomplete.
Have you at any point used the purge_queue_table procedure in dbms_aqadm? There is a known issue with this which can leave the queue in this state - I think it is fixed in 22.214.171.124 but if this database was upgraded from a previous version it could have happened then.
Also, do you happen to know if anyone has been manually updating the queue table structures?
I'd recommend raising an SR with support for advise on cleaning this up - I think you can just safely delete those orphaned entries but best to get their view, having cleaned them up you might also want to re-org the queue objects to get some space back:
How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1)