Seeing issues related to queuing below article is documented to have some handy steps for troubleshooting queuing issues
AQ Components
The four main components of AQ are:
1. Message - A message consists of message content, or payload, which can be specified using typed or raw data and message
attributes or control information.
2. Message Queue – Messages are stored in queues and these queues act as “postal boxes” where different applications can
look for “mail” in the form of messages. Thus, when one application wants to contact certain applications for certain tasks, it
can leave messages in these queues, and the receiving applications will be able to find these messages for processing.
3. Message Interfaces – AQ supports enqueue, dequeue, and propagation operations that integrate seamlessly with existing
applications by supporting popular standards. AQ messages can be created, queried, propagated and consumed using
popular application programming interfaces (API) such as PL/SQL, C/C++, Java, Visual Basic (through Oracle Objects for
OLE), Python, Node.js, and ODP.NET. AQ provides support for the Java Message Service 1.1 (JMS) API that allows Java
applications to utilize the message queuing functionality.
4. Message Handling – AQ supports rule-based routing of messages according to data in the message payload or attributes.
Additionally, message transformations can be applied to messages to re-format data before the messages are automatically
delivered to target applications or subscribers. Oracle Database 19c can also exchange AQ messages with IBM MQ and
TIBCO/Rendezvous through the Oracle Messaging Gateway.
Terms To Understand :
1) Queue
2) Queue tables
3) Propagations
4) Subscribers
Parameter Influencing Advanced queuing :
Main database parameter is aq_tm_processes . However below are other parameters that influence advanced queuing
aq_tm_processes
processes
job_queue_processe
timed_statistics
SGA_TARGET
streams_pool_size
undo_retention
_client_enable_auto_unregister
shared_pool_size
Troubleshooting AQ :
1) Restart queue process and queue monitoring
aq_tm_processes --> make it 0
kill existing q0 process .
oracle aq_tm_processes --> make it 1
ps -ef | grep -i e00 | grep -i dbname -- kill it
-- Perform the below to reset the qmon process:
alter system set "_aq_stop_backgrounds"=TRUE;
alter system set "_aq_stop_backgrounds"=FALSE;
2) Restart job queue processes
3) Check which jobs are being run by querying dba_jobs_running. It is possible that other jobs are starving the propagation jobs.
4) Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_QUEUE_TABLES. The queue sys.aq$_prop_notify_queue_instnomust also exist in DBA_QUEUES and must be enabled for enqueue and dequeue.
5) Turn on propagation tracing at the highest level using event 24040, level 10
6) . Debugging information is logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.
7) Generate TFA as per 1905796.1
$TFA_HOME/bin/tfactl diagcollect -srdc dbaqmon
8) Generate AQ & MGW Health Check using Configuration Script ( Doc ID 1193854.1 )
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/03/aq-mgw-health-check-and-configuration.html
9) Check *qm0*.trc in trace directory .
10) Restart Time Managers from root container
EXEC dbms_aqadm.stop_time_manager;
EXEC dbms_lock.sleep(120); -- sleep is must
EXEC dbms_aqadm.start_time_manager;
11) Restart queue process
Begin
dbms_aqadm.stop_queue ('<schema name>.<queue name>'); ;
END ;
/
Begin
dbms_aqadm.start_queue ( queue_name =>'QUEUENAME' ) ;
END ;
/
Views :
SELECT msg_state, MIN(enq_time) min_enq_time, MAX(deq_time) max_deq_time, count(*) FROM abdul_queue GROUP BY msg_state;
select TOTAL_NUMBER
from DBA_QUEUE_SCHEDULES
where QNAME=’<source_queue_name>’;
select* from QUEUE_PRIVILEGES ;
SELECT name, queue_type, waiting, ready, expired
FROM dba_queues q
JOIN v$aq v ON q.qid = v.qid
WHERE q.name = 'queue_name' ;
select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.QUEUE_TABLE group by q_name, state;
set line 100
col Parameter for a50
col "Session Value" for a20
col "Instance Value" for a20
SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm in ( '_client_enable_auto_unregister', '_emon_send_timeout' )
/
-- to check if dequeue is happening
select queue_id,shard_id,priority,ENQUEUED_MSGS,dequeued_msgs, enqueued_msgs-dequeued_msgs backlog from gv$aq_sharded_subscriber_stat order by queue_id, shard_id, priority;
Check Message State and Destination. Find the queue table for a given queue
select QUEUE_TABLE
from DBA_QUEUES
where NAME = &queue_name;
Check for messages in the source queue with
select count (*)
from AQ$<source_queue_table>
where q_name = 'source_queue_name';
Check for messages in the destination queue.
select count (*)
from AQ$<destination_queue_table>
where q_name = 'destination_queue_name';
select * from GV$AQ ;
select * from aq.aq$objmsgs80_qtab ;
select * from dba_queues where qid=14140;
SELECT * FROM SYS.ALL_QUEUES ;
select * from DBA_QUEUE_SCHEDULES ;
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN NAME HEADING 'Queue Name' FORMAT A28
COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22
COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15
SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT
FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND
q.QUEUE_TABLE = t.QUEUE_TABLE AND
q.OWNER = t.OWNER;
DBA_QUEUE_TABLES: All Queue Tables in Database
The DBA_QUEUE_TABLES view contains information about the owner instance for a queue table. A queue table can contain multiple queues. In this case, each queue in a queue table has the same owner instance as the queue table. Its columns are the same as those in ALL_QUEUE_TABLES.
Werbung
DBA_QUEUES: All Queues in Database The DBA_QUEUES view specifies operational characteristics for every queue in a database. Its columns are the same as those ALL_QUEUES.
DBA_QUEUE_SCHEDULES: All Propagation Schedules The DBA_QUEUE_SCHEDULES view describes all the current schedules in the database for propagating messages.
QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege The QUEUE_PRIVILEGES view describes queues for which the user is the grantor, grantee, or owner. It also shows queues for which an enabled role on the queue is granted to PUBLIC.
AQ$Queue_Table_Name: Messages in Queue Table
The AQ$Queue_Table_Name view describes the queue table in which message data is stored. This view is automatically created with each queue table and should be used for querying the queue data. The dequeue history data (time, user identification and transaction identification) is only valid for single-consumer queues.
***********************
.
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE> DIAG - INFO </title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO ON
spool AQ_Diag.html
set pagesize 120
set linesize 180
column global_name format a20
column current_scn format 99999999999999999999
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
alter session set nls_timestamp_format='dd-mon-yyyy hh24:mi:ss';
select global_name, sysdate from global_name;
select * from gv$version;
select inst_id, instance_number, instance_name, host_name, startup_time, status from gv$instance order by inst_id;
SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') "Current Container" FROM DUAL;
select con_id, name, cdb, current_scn from gv$database order by inst_id;
select * from gv$containers order by inst_id,con_id;
select * from gv$pdbs order by inst_id,con_id;
select owner, queue_table, type, RECIPIENTS from dba_queue_tables where
queue_table='ABDUL_QUEUE';
SELECT queue_table, owner_instance, primary_instance, secondary_instance
FROM dba_queue_tables
WHERE owner = 'ABDUL' AND queue_table = 'ABDUL_QUEUE';
select count(*), state, q_name from ABDUL.ABDUL_QUEUE
group by q_name, state;
select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.ABDUL_QUEUE
group by q_name, state;
select count(*), min(enq_time), max(deq_time), state, q_name from ABDUL.ABDUL_QUEUE
where deq_time < (sysdate - 1)
group by q_name, state;
select trunc(enq_time,'HH'), q_name, state, count(*), max(deq_time) from ABDUL.ABDUL_QUEUE
group by trunc(enq_time,'HH'),q_name, state;
select * from dba_queue_subscribers where queue_table='ABDUL_QUEUE';
spool off
SET MARKUP HTML OFF
SET ECHO ON
***********************
Possible message states in Oracle Advanced Queuing
| Value | Name | Meaning |
|---|
| 0 | READY | The message is ready to be processed, i.e., either the delay
time of the message has passed or the message did not have
a delay time specified |
| 1 | WAITING or WAIT | The delay specified by message_properties_t.delay while
executing dbms_aq.enqueue has not been reached. |
| 2 | RETAINED OR PROCESSED | The message has been successfully processed (dequeued) but
will remain in the queue until the retention_time specified
for the queue while executing dbms_aqadm.create_queue has
been reached. |
| 3 | EXPIRED | The message was not successfully processed (dequeued) in
either 1) the time specified by message_properties_t.expiration
while executing dbms_aq.enqueue or 2) the maximum number of
dequeue attempts (max_retries) specified for the queue while
executing dbms_aqadm.create_queue. |
| 4 | IN MEMORY | User-enqueued buffered message |
| 7 | SPILLED | User-enqueued buffered message spilled to disk |
| 8 | DEFERRED | Buffered messages enqueued by a Streams Capture process |
| 9 | DEFERRED SPILLED | Capture-enqueued buffered messages that have been spilled to disk |
| 10 | BUFFERED EXPIRED | User-enqueued expired buffered messages
|
Known Issues :
1) Issue 1
AQ Queue Monitor Does Not Change Message MSG_STATE from WAIT to READY on RAC after restart / patching :
Queue table "ABDUL_QUEUE" is AQ queue table owned by user "USER01" and view "AQ$ABDUL_QUEUE" is created when creating queue table "ABDUL_QUEUE"
and returns read only data from "ABDUL_QUEUE" with more meaningful/readable values for columns .
During restart queue process goes in hung state and primary_instance gets de-assigned .Check if any hung message detected in *qm0*.trc files
This can be fixed by altering queue
execute dbms_aqadm.alter_queue_table (queue_table => 'ABDU_QUEUE',primary_instance => 2,secondary_instance => 1);
select MSG_ID,MSG_STATE,DELAY,ENQ_TIME
from USER01.AQ$ABDUL_QUEUE
where MSG_STATE='WAIT'
order by ENQ_TIME;
select owner, queue_table, object_type, owner_instance, primary_instance, secondary_instance
from dba_queue_tables where queue_table='ABDUL_QUEUE';
AQ Messages Not Changing Status from DELAY to READY Or READY to PROCESSED In RAC Databases (Doc ID 2879519.1)
2) Issue 2 :
Sharded Queues : Sharded AQ:RAC: MESSAGES in READY when SHARD OWNERSHIP HAS CHANGED.
select distinct MSG_PRIORITY from owner.aq_table ;
select * from GV$AQ_CACHED_SUBSHARDS ;
select * from GV$AQ_REMOTE_DEQUEUE_AFFINITY;
select * from SYS.aq$_queue_shards;
select INST_ID,JOB_ID,QUEUE_NAME,JOB_STATE,MSGS_SENT,OWNER_INSTANCE_ID from GV$AQ_CROSS_INSTANCE_JOBS;
Purge Advanced Queue Table if auto purge not happening
1-)Identifiy how many data you will purge. If you want purge all off them you can easily purge without condition.
select owner,name,queue_table,retention
from dba_queues
where owner='OWNER_AQ'
and queue_table='QUEUE_TABLE_T'
and queue_type='NORMAL_QUEUE';
select count(1)
from OWNER_AQ.AQ$QUEUE_TABLE_T
where msg_state=’PROCESSED’ and enq_time<to_date(’16/10/2017 17:05:30′,’dd/mm/yyyy hh24:mi:ss’);
2.1-) Purge queue table with conditions.
DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := TRUE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'OWNER_AQ.QUEUE_TABLE_T',
purge_condition => 'qtview.queue = ''QUEUE_TABLE_Q''
and qtview.enq_timestamp < to_date(''16/10/2017 17:05:30'',''dd/mm/yyyy hh24:mi:ss'') and qtview.msg_state = ''PROCESSED''', purge_options => po);
END;
/
2.2-) Purge queue table without conditions. With this option you will gain space like truncate command on table .
DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
dbms_aqadm.purge_queue_table('Queue_Table_Name', NULL, po);
END;
2.3-) If your dataset is big it can be executed like that. Condition may change.
begin
FOR i IN 1..15
LOOP
DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := TRUE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => ‘OWNER_AQ.QUEUE_TABLE_T’,
purge_condition => ‘qtview.queue = ”QUEUE_TABLE_Q”
and qtview.enq_timestamp < to_date(”’||i*2||’/09/2017 05:05:30”,”dd/mm/yyyy hh24:mi:ss”) and qtview.msg_state = ”PROCESSED”’, purge_options => po);
END;
commit;
dbms_lock.sleep(20);
END LOOP;
end;
/
References :
1)
https://www.oracle.com/a/otn/docs/database/aq-db19c-technicalbrief.pdf
2)
https://www.oracle.com/a/otn/docs/database/jmssq-db19c-technicalbrief.pdf
3)
https://documentation.braintribe.com/Content/customer/oeamtc/oeamtcdocu/Troubleshooting/Oracle%20Advanced%20Queues%20(AQ).htm
4)
https://docs.oracle.com/database/121/ADQUE/aq_intro.htm#ADQUE0100
5)
https://docs.oracle.com/database/121/STRMS/strms_qpmon.htm#STRMS908
6)
After import of queue: ORA-25226 dequeue failed queue not enabled for dequeue (Doc ID 1290221.1)
7)
https://www.oracle.com/docs/tech/database/jmssq-db19c-technicalbrief.pdf
https://www.morganslibrary.org/reference/demos/aq_demo_rac.html
