MESSAGES FOR SYS$RLB_GEN_SUB CONSUMER ON SYS$SERVICE_METRICS ARE NOT BEING DEQUEUED ON PDB (Doc ID 2930539.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

Oracle DB 19.14 on RAC with a single tenant database.

At CDB level we have 5 services started that which produces 5 messages to be enqueued on SYS$SERVICE_METRICS at CDB$ROOT level every 30 secs.
These messages are processed properly by CDB$ROOT

At PDB level we have 3 services started that which produces 3 messages to be enqueued on SYS$SERVICE_METRICS at PDB level every 30 secs.
These messages are not being dequeued, and they are pilling up continuously, so there are thousands of messages after a few days.

The problem persists even after recreating queue and queue table according to MOS Doc ID 1574889.1.

No errors appear in the alert.log files.

The following reveals the problems:

At CDB$ROOT

SQL> select CONSUMER_NAME, MSG_STATE, QUEUE, count(*) from AQ$SYS$SERVICE_METRICS_TAB group by CONSUMER_NAME, MSG_STATE, QUEUE

CONSUMER_NAME MSG_STATE QUEUE COUNT(*)
------------------------- ---------------- --------------------------------------------- ----------
SYS$RLB_GEN_SUB PROCESSED SYS$SERVICE_METRICS 120

At PDB

SQL> select count(1), msg_state, queue, consumer_name
2 from AQ$SYS$SERVICE_METRICS_TAB group by msg_state, queue, consumer_name;

COUNT(1) MSG_STATE QUEUE CONSUMER_NAME
130332 READY SYS$SERVICE_METRICS SYS$RLB_GEN_SUB

SQL> select CONSUMER_NAME, COUNT(*), max(enq_time), max(deq_time), MSG_STATE, trunc(enq_time) Day FROM sys.aq$SYS$SERVICE_METRICS_TAB
2 GROUP BY CONSUMER_NAME,MSG_STATE, trunc(enq_time);

CONSUMER_NAME COUNT(*) MAX(ENQ_TIME) MAX(DEQ_TIME) MSG_STATE DAY
SYS$RLB_GEN_SUB 8628 14-jan-2023 23:59:31 READY 14-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 15-jan-2023 23:59:32 READY 15-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 24-jan-2023 23:59:47 READY 24-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 12-jan-2023 23:59:57 READY 12-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 13-jan-2023 23:59:58 READY 13-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 23-jan-2023 23:59:45 READY 23-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 16-jan-2023 23:59:34 READY 16-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 18-jan-2023 23:59:38 READY 18-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 20-jan-2023 23:59:41 READY 20-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 22-jan-2023 23:59:44 READY 22-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 19-jan-2023 23:59:39 READY 19-jan-2023 00:00:00
SYS$RLB_GEN_SUB 2994 11-jan-2023 23:59:54 READY 11-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 17-jan-2023 23:59:36 READY 17-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 21-jan-2023 23:59:42 READY 21-jan-2023 00:00:00
SYS$RLB_GEN_SUB 6522 26-jan-2023 18:07:58 READY 26-jan-2023 00:00:00
SYS$RLB_GEN_SUB 8631 25-jan-2023 23:59:48 READY 25-jan-2023 00:00:00

16 rows selected.

In the PDB messages are not being dequeued for consumer SYS$RLB_GEN_SUB.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.