Oracle Database Discussions

Hi gurus,
Please help me here and than you. How do I solve this ORA-12805: parallel query server died unexpectedly problem? I have posted this on the streams forum but have not got any answer. Sorry to post it here but I am desperate.

I followed the samples and created two queue tables of sys.mgw_basic_msg_t and two queues (Source_Q and Dest_Q) on them. I started both of them. Then I added two subscribers of the Dest_Q to the Source_Q. Both Q's are in the same database. I am in 10gR2 enterprise edition.

DECLARE
subscriber1 sys.aq$_agent;
BEGIN
subscriber1 := sys.aq$_agent('JW1', 'ben.Dest_Q', NULL);
dbms_aqadm.add_subscriber(
queue_name => 'ben.Source_Q',
subscriber => subscriber1,
queue_to_queue => true
);
END;
/

DECLARE
subscriber1 sys.aq$_agent;
BEGIN
subscriber1 := sys.aq$_agent('JW2', 'ben.Dest_Q', NULL);
dbms_aqadm.add_subscriber(
queue_name => 'ben.Source_Q',
subscriber => subscriber1,
queue_to_queue => true
);
END;
/

Then I set up the schedule,

BEGIN
dbms_aqadm.schedule_propagation(
queue_name => 'ben.Source_Q',
start_time => sysdate,
duration => 30,
next_time => 'sysdate + 30/86400',
latency => 10
);
END;
/

And then create and start the apply

begin
dbms_apply_adm.create_apply(
queue_name => 'ben.Dest_Q',
apply_name => 'mncis_apply',
message_handler => 'ben.sprocMessageHandler',
apply_user => 'ben'
);
exception
when others then
dbms_output.put_line(SQLErrM);
end;
/

begin
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'mncis_apply'
);
end;
/

Then I manually enqueued messages into the Source_Q. It is always successful, but then nothing happens. The propagation from Source_Q to Dest_Q never happens. Querying the dba_jobs shows this,

select job, last_date, next_date, what from dba_jobs;

1 09/21/2006 14:36 09/21/2006 14:37
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();

4001 09/21/2006 08:45 09/21/2006 16:45
wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);

4002 09/21/2006 14:27 09/21/2006 14:37
wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get

444 09/21/2006 14:37
next_date := sys.dbms_aqadm.aq$_propaq(job);

I noticed that there is never a last_date value for job 444 (this number changes all the time), sys.dbms_aqadm.aq$_propaq(job).

Query the dba_apply view

select apply_name, queue_name, status from dba_apply;

Apply Process Name QUEUE_NAME STATUS
---------------------- ------------------------------ -------
MNCIS_APPLY DEST_Q ENABLED

Manually dequeuing is always succssful.

When I created an APPLY on the source_q with a user-defined message handler, it tries to do the apply, but the operation was aborted with the ORA-12805: parallel query server died unexpectedly error.

Please tell me what I did wrong. Thank you!

Ben