RE: Oracle AQ Question

From: Teehan, Mark <mark.teehan_at_credit-suisse.com>
Date: Thu, 11 Mar 2010 09:11:54 +0800
Message-ID: <82A20AE725E34B43B3F6C9DF5DBF1E3409D0A62C_at_ESNG17P32003A.csfb.cs-group.com>

Thomas,
Is aq_tm_processes > 0; make sure you are issuing a commit after the enqueue; and check your alert logs for any errors.    

Rgds
Mark Teehan
Singapore

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day Sent: 11 March 2010 03:20
To: Oracle-L_at_freelists.org
Subject: Oracle AQ Question

I'm pretty new to AQ and, dispite reading the manuals and Tom K's blog, I'm pretty sure that I'm missing something fundamental.  

I've got Oracle 10.2.0.4 on a Windows box with two databases, A & B. Database A is my source. I have a multi-consumer queue that I'm enqueuing into. On database B, my target, I have 5 schemata, each with their own queue.  

On database A I've added each of the B schemata as a subscriber, created a database link to each of the database B schemata, and added a propagation schedule for each destination.  

I have a working proc to enqueue the data in database A and a proc to dequeue it in database B that compiles with no errors. Enqueuing the data causes rows to appear in the queue table. Dequeuing them in database B does not cause any rows to be removed from the queue table in A or any rows to appear in the queue tables in B.  

Querying DBA-JOBS in database A shows five jobs, corresponding to the five queue schedules. While the TOTAL_TIME keeps incrementing, the THIS_DATE and NEXT_DATE data remains unchanged and the LAST_DATE is blank. E.g.,
LAST_DATE TO_CHAR(THIS_DATE, TO_CHAR(NEXT_DATE, TOTAL_TIME B FAILURES

--------- ------------------ ------------------ ---------- - ----------
          10-MAR-10 13:36:35 10-MAR-10 13:36:35       1798 N
          10-MAR-10 13:36:35 10-MAR-10 13:36:35       1798 N
          10-MAR-10 13:36:35 10-MAR-10 13:36:35       1798 N
          10-MAR-10 13:36:35 10-MAR-10 13:36:35       1798 N
          10-MAR-10 13:36:35 10-MAR-10 13:36:35       1798 N
 

They say that they're not broken and have no failures, but they don't appear to be doing anything.  

Querying DBA_QUEUE_SCHEDULES shows 5 schedules with start_date and current_start_date matching the data in DBA_JOBS. NEXT_TIME, NEXT_RUN_DATE, and LAST_RUN_DATE are blank. It reports 0 FAILURES and nothing in LAST_ERROR_MSG.  

Is this running at all? What piece am I missing?

---

 Please access the attached hyperlink for an important electronic communications disclaimer:  http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

---

 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 10 2010 - 19:11:54 CST