Re: Oracle AQ Question
- From: Thomas Day <tomdaytwo@xxxxxxxxx>
- To: "Wang, Tao" <twang@xxxxxxxxxx>
- Date: Thu, 11 Mar 2010 13:38:29 -0500
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:
