Re: Advanced Queues -- problem (Urgent!)

alex_at_sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0.0208090937.627754b5_at_posting.google.com>...
> Dear netters!
>
> We have 2 Oracle 8.1.7 servers set up. One server (call it P) is
> working as the publisher, another one (S) is the subscriber. There is
> a DB-link defined from P to S, using a user id P_TO_S_USER.
>
> A user on P (P_USER) owns a queue/queue table, which were defined as
> compatibility=8.1, multiple consumers=TRUE, and is granted the
> AQ_ADMINISTRATOR_ROLE.
>
> A user on S (S_USER) owns a queue/queue table, which were defined as
> compatibility=8.1, multiple consumers=TRUE, and is granted the
> AQ_ADMINISTRATOR_ROLE.
>
> A subscriber is added on P, with agent defined as follows:
> agnt := sys.aq$_agent(NULL, sub_queue_name || '@' || db_link, NULL);
> DBMS_AQADM.ADD_SUBSCRIBER(queue_name => pub_queue_name, subscriber
> => agnt );
>
> The queues are started on both servers, and propagation is scheduled
> and enabled. The enqueued messages, however, sit on the P server in
> the state of 0...
>
> In the initial tests we ran, the queue owner names on both sides were
> the same. And it worked fine...
>
> Are we missing something on the permissions? Is there anything else
> that needs to be granted to: P_USER, S_USER and P_TO_S_USER IDs?
>
> Thank you very much for your timely response!
>
> --Alex Vilner

Follow up: Another difference between the initial tests and the currenct set up is in that the intitial tests were running on two Oracle instances located on the same server. The "problem" set-up goes across two different physical servers.

I did some additional tests by using the same set-up scripts that were used for successful tests (same queue owner names, same queue names, same queue table names) and it still failed with the Oracle internal error. So it seems as though there is some problem with the DB-link or the timeouts (although, I can query through DB link and it works pretty fast -- milliseconds' response time).

The Oracle error I receive is in the USER_QUEUE_SCHEDULES. I do not remember the specifics, but it is the one that has many [], [], []'s in it and no meaning :) I can provide details once I see it again...

Any help will be greatly appreciated.
TIA --Alex Received on Sun Aug 11 2002 - 22:00:12 CDT