Abdul hafeez kalsekar -- OCS/OCE/OCP: March 2023

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.

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'></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 :

https://www.oracle.com/a/otn/docs/database/aq-db19c-technicalbrief.pdf

https://www.oracle.com/a/otn/docs/database/jmssq-db19c-technicalbrief.pdf

https://documentation.braintribe.com/Content/customer/oeamtc/oeamtcdocu/Troubleshooting/Oracle%20Advanced%20Queues%20(AQ).htm

https://docs.oracle.com/database/121/ADQUE/aq_intro.htm#ADQUE0100

https://docs.oracle.com/database/121/STRMS/strms_qpmon.htm#STRMS908

After import of queue: ORA-25226 dequeue failed queue not enabled for dequeue (Doc ID 1290221.1)

https://www.oracle.com/docs/tech/database/jmssq-db19c-technicalbrief.pdf

https://www.morganslibrary.org/reference/demos/aq_demo_rac.html

Abdul hafeez kalsekar -- OCS/OCE/OCP

Saturday, March 11, 2023

Troubleshooting advanced queue issues in Oracle 19c database