Re: Oracle AQ Question

I went to MySupport (what a horrible site -- it totally hosed my computer
for half an hour printing the two pages of this note).

There are basically six points to this note:

1. One of the recipients (destination address for propagation) does not
exist.  --DESTINATION ADDRESS FOR PROPAGATION DOES EXIST.
2. The destination queue is not enabled for enqueue. -- QUEUES ARE STARTED
3. There is no recipient for the message at destination queue. --
4. Message was enqueued into a multiconsumer queue without a recipient list
and none of the
subscription rules are satisfied.  -- RECIPIENT LIST AND SUBSCRIPTION RULES
EXIST.
5. The destination queue for propagation is an exception queue. -- NO
6. There is a mismatch between the payload of the source and destination
queues. -- IDENTICAL
My responses are in the all caps.  I'm not sure that I understand the
distinction between #1 and #3 so I'm not sure what to say there.  #2 is
especially amusing since dbms_aqadm does not have an enable_enqueue proc.
In fact, what they mean is, have you run start_queue?  Typical of the
clarity of Oracle's documentation.

Trying to find out what #3 meant, I ended up running
ALTER SESSION SET EVENTS '24040 trace name context forever, level 10';

All is finally revealed.  The start queue is failing because of lack of OS
resources. The
Thu Mar 11 08:09:19 2010
MMNL absent for 61854 secs; Foregrounds taking over
MMNL absent for 61854 secs; Foregrounds taking over
MMNL absent for 61854 secs; Foregrounds taking over

in the alert log could have been a clue but it and the UNDELIVERABLE message
are about the only hints that Oracle gives.

With the trace on I see:
Thu Mar 11 08:09:19 2010

Thu Mar 11 11:11:54 2010

Errors in file c:\oracle\product\10.2.0.4\admin\tsi\bdump\tsi_j004_4448.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04030: out of process memory when trying to allocate 68 bytes
(Typecheck,optdef: qcopCreateOptInternal)

ORA-12012: error on auto execute of job 591

ORA-04030: out of process memory when trying to allocate 16396 bytes (QERHJ
hash-joi,QERHJ Bit vector)

ORA-00604: error occurred at recursive SQL level 2

ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ
hash-joi,kllcqas:kllsltba)

ORA-06512: at "SYS.DBMS_IJOB", line 49

ORA-06512: at "SYS.DBMS_JOB", line 124

ORA-06512: at "SYS.DBMS_PRVTAQIP", line 2166

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_IJOB", line 49

ORA-06512: at "SYS.DBMS_JOB", line 124

ORA-06512: at "SYS.DBMS_PRVTAQIP", line 2166

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7224

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7502

ORA-06512: at "SYS.DBMS_AQADM", line 978

ORA-06512: at line 1

Thu Mar 11 11:12:48 2010

Process startup failed, error stack:

Thu Mar 11 11:12:48 2010

Errors in file c:\oracle\product\10.2.0.4\admin\tsi\bdump\tsi_psp0_716.trc:

ORA-27300: OS system dependent operation:CreateThread failed with status: 8

ORA-27301: OS failure message: Not enough storage is available to process
this command.

ORA-27302: failure occurred at: ssthrddcr

AQ queuing is a bit of a memory hog.  I have 2G of physical RAM and can go
to 4G virtual.  Anyway, now I know why it appears to be not running.  It
isn't.  Now I have to figure how configure it to run within the OS (WIndows
XP) limits.  Thank you to everyone for your help.

Other related posts: