Home » Server Options » Streams & AQ » Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680679] Sun, 31 May 2020 17:37 Go to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hello Team,
In our envirnoment oracle streams for oneside replication only for just 2 schemas, it was working fine in 11GR2, after upgrading into oracle 12C with RAC one configuration, in order to avoid issue for

" ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance" error message which leads to abort propagation Hence as per oracle document advice recreated queues means (capture,propagation and queue tables) then mentioned issue gone, also these already proven into couple of critical envirnoment, which is working fine for few months as well.

now we are upgrading oracle 12C with RAC one for main production there even after recreation of queues above error encounted. still propagation / streams broken with mentioned message.
what could be the reason, and how do I trouble shoot and fix for the same.

FYI: all the above envirnoment after recreation of the queue we too got the following queue service published in the listener status as well, still facing the issue.
in production envirnoment.But in other critical envirnoment we do not had any issue after recreation of the queues.


Service "SYS$STRMADMIN.CCUSER_QUEUE.IGW11P.WORLD" has 1 instance(s).
Instance "IGW11P_1", status READY, has 1 handler(s) for this service...
Service "SYS$STRMADMIN.PC2USER_QUEUE.IGW11P.WORLD" has 1 instance(s).
Instance "IGW11P_1", status READY, has 1 handler(s) for this service...

Herewith enclosed respective tracefiles which is generated against for ORA-25333 in oracle alert log.

since it is production, expecting expert update ASAP.

Note :

Thanks in Advance,
Siva..
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680683 is a reply to message #680679] Mon, 01 June 2020 01:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

What service are you using, and how is it registered wih the SCAN listeners and the node listeners?

Incidentally, thisQuote:
expecting expert update ASAP
suggests that you shold raise a TAR with Oracle Support (whom you pay to provide help) rather than asking here. Of course, they may tell you that you should be using Xstreams or GoldenGate instead.
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680689 is a reply to message #680683] Mon, 01 June 2020 08:56 Go to previous messageGo to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hi John,

Thanks for your update.using one of the active service(exist in dba_Services), the same service name been used in propagation, db_link as well.Also scan listener been used
in the connection string of db_link.

14:18:01 01-JUN-20 at IGW11P> select sysdate from dual@IGW13P.WORLD;

SYSDATE
---------
01-JUN-20

could you guys support further, how to avoid "ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance".
FYI: Earliear 3 of our critical envirnomrnt above error been avoided by recreating queue, so that queues will be published in lsnrctl status as follows

Service "SYS$STRMADMIN.PC2USER_QUEUE.IGW13P.WORLD" has 1 instance(s).
Service "SYS$STRMADMIN.CCUSER_QUEUE.IGW11P.WORLD" has 1 instance(s).
Service "SYS$STRMADMIN.CCUSER_QUEUE.IGW13P.WORLD" has 1 instance(s).
Service "SYS$STRMADMIN.PC2USER_QUEUE.IGW11P.WORLD" has 1 instance(s).

all the steps carried out these prod as well, also we too above published queue services as well, but still we are having mention issue 0RA-25333.

Please review attached trace file which was generating for ora -25333 in DB alert log, also attached latest Health check report of our streams envirnoment for your review.


Note: In oracle support now every one is supporting Golden Gate etc.,could not found any expert who support streams yet. But we are going to live with streams may another a year or so.
Hope forum will support how where could we trouble shoot.

Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680690 is a reply to message #680689] Mon, 01 June 2020 08:58 Go to previous messageGo to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hi Hohn,

Herewith attaching HC for earliear/Latest reply.

Siva..
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680691 is a reply to message #680689] Mon, 01 June 2020 09:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to show how the service is registered: What is the output of lsnrctl status for the SCAN listener and the node listener?
Also, how is your queue configured: which is the primary instance and which is the secondary instance?

Lastly, if you use [code] tags, you are more likely to get assistance.

Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680705 is a reply to message #680691] Tue, 02 June 2020 04:34 Go to previous messageGo to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hi John,

Thanks for your update.

Herewith enclosed output for SCAN_LISTENER1,2,3 and node listener status in listener_Status.txt for your review.

our source DB is IGW11P.world and target db is IGW13P.world , we have just schema to schema one way replication.

FYI. queue & propagation was restabilished as follows after RAC one configuration.

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'CCUSER_QUEUE',
queue_table =>'CCUSER_QUEUE_TABLE',
queue_user => 'STRMADMIN');
end;
/

Pause ' Claim Queue Table Created '

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name => 'CCUSER',
streams_name => 'PRP4CCUSER',
source_queue_name => 'STRMADMIN.CCUSER_QUEUE',
destination_queue_name => 'STRMADMIN.CCUSER_QUEUE@IGW13P.WORLD',
include_dml => true,
include_ddl => true,
source_database => 'IGW11P.WORLD',
inclusion_rule =>true,
queue_to_queue => true);
END;
/


FYI:error message seen in dba_queue_schedules tables and source DB alert log files.also observed that every 1 hours these error comes and broke propagation.

[Updated on: Tue, 02 June 2020 05:17] by Moderator

Report message to a moderator

Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680707 is a reply to message #680705] Tue, 02 June 2020 05:24 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You forgot to use [code] tags in your earlier post. On this occasion, I have added them. Please do so yourself in future. You also forgot to show which is the primary and secondary instance, as shown in dba_queue_tables.

Apart from all that, do you actually have a cluster? There seems to be only one node running.
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680708 is a reply to message #680707] Tue, 02 June 2020 06:06 Go to previous messageGo to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hi Team,

Thanks for your update.

select queue_table, primary_instance, secondary_instance, owner_instance from dba_queue_tables where owner='STRMADMIN';

QUEUE_TABLE PRIMARY_INSTANCE SECONDARY_INSTANCE OWNER_INSTANCE
-------------------- ---------------- ------------------ --------------
CCUSER_QUEUE_TABLE 0 0 1
PC2USER_QUEUE_TABLE 0 0 1

Also RAC one running on cluster with two nodes

FYI [kuttys1@AVIVAGROUP.COM@auplud1prapp09 ~]$ srvctl status SCAN_LISTENER
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node auplud1prapp10 (Passite node)
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node auplud1prapp09 (active node)
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node auplud1prapp09 (active node).

Also you mentioned in latest update "forgot to use [code] tags" what is these ???. Please clarify.


Thanks,
Siva..
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680711 is a reply to message #680708] Tue, 02 June 2020 10:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
How to use code tags and make your code easier to read
It looks as though you may need to run dbms_aqadm.alter_queue_table to configure it correctly.
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680712 is a reply to message #680711] Tue, 02 June 2020 11:16 Go to previous messageGo to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hi Team,

Thanks for your update.

asyou mentioned "dbms_aqadm.alter_queue_table to configure it correctly" what exactly we need to do further.

we have only two queues ccuser_queue & pc2user_queue, what exactly need to be done.

could you update me in details, steps for the same.

Thanks,
Siva..
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680714 is a reply to message #680712] Tue, 02 June 2020 12:39 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'm sure you can work it out (or you can ask my boss to send you a quote for consulting services Smile )
Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680715 is a reply to message #680714] Tue, 02 June 2020 12:50 Go to previous messageGo to next message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hello Team,

In our envirnoment queue services was working fine only, so kindly advice what will do for to fix the same.

for contact you can call me on +44 7459016351.


Siva..

[Updated on: Tue, 02 June 2020 13:13]

Report message to a moderator

Re: Streams - ORA-25333: Buffered Queue to Queue propagation did not connect to the correct instance [message #680724 is a reply to message #680715] Wed, 03 June 2020 12:41 Go to previous message
siva_faq
Messages: 8
Registered: May 2020
Junior Member
Hello Team,

Hope you are requesting to do the following with bounce DB, already done it as follows and bounce the DB as well. still issue is not resolved.

begin
dbms_aqadm.alter_queue_table (
queue_table => 'STRMADMIN.CCUSER_QUEUE_TABLE',
primary_instance => 1,
secondary_instance => 2);
end;
/
begin
dbms_aqadm.alter_queue_table (
queue_table => 'STRMADMIN.PC2USER_QUEUE_TABLE',
primary_instance => 1,
secondary_instance => 2);
end;
/
could you update what shall we do to sort out these issue.


Thanks,
Siva..
Previous Topic: Remote JMS queue read
Goto Forum:
  


Current Time: Thu Mar 28 18:28:54 CDT 2024