Re: Problem using AQ on RAC

  • From: Yadavalli Aditya <aditya.yadavalli@xxxxxxxxx>
  • To: stbaldwin@xxxxxxxxxxxxxxxx
  • Date: Thu, 10 Jun 2010 12:39:01 -0400

This may be related to incorrect setting of LOCAL/REMOTE_LISTENER
parameters. Pl check note *472394.1.

Cheers/Aditya
*
On Thu, Jun 10, 2010 at 10:09 AM, Steve Baldwin
<stbaldwin@xxxxxxxxxxxxxxxx>wrote:

> Hi all,
>
> I'm hoping someone can give me a clue as to this error I'm seeing.
> Whilst I realise this isn't necessarily a good thing to do, the manual
> indicates you can do it and I'm trying to prove this.
>
> Basically, I create a queue table and queue on node A.  If I then try
> to enqueue or dequeue to the queue from node B I'm getting the
> following error :
>
> ERROR at line 1:
> ORA-25306: Cannot connect to buffered queue's owner instance
> ORA-06512: at "SYS.DBMS_AQ", line 6
> ORA-06512: at "SYS.DBMS_AQ", line 216
> :
>
> Here's what I see in the alert log of node B ...
>
> Wed Jun 09 13:26:00 2010
>
>
> ***********************************************************************
>
> Fatal NI connect error 12533, connecting to:
> (DESCRIPTION=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=
> opbld05-vip.multiservice.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=
> RAC01D.BUILD.MULTISERVICE.COM
> )(INSTANCE_NAME=RAC01D1)(CID=(PROGRAM=oracle)(HOST=
> opbld06.multiservice.com)(USER=oracle))))
>
> VERSION INFORMATION:
> TNS for Linux: Version 11.1.0.7.0 - Production
> Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version
> 11.1.0.7.0 - Production
> TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
> Time: 09-JUN-2010 13:26:00
> Tracing not turned on.
> Tns error struct:
> ns main err code: 12533
>
> TNS-12533: TNS:illegal ADDRESS parameters
> ns secondary err code: 12560
> nt main err code: 503
>
> TNS-00503: Illegal ADDRESS parameters
> nt secondary err code: 0
> nt OS err code: 0
>
>
> ***********************************************************************
>
> Fatal NI connect error 12533, connecting to:
> (DESCRIPTION=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=
> opbld06-vip.multiservice.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=
> RAC01D.BUILD.MULTISERVICE.COM
> )(INSTANCE_NAME=RAC01D1)(CID=(PROGRAM=oracle)(HOST=
> opbld06.multiservice.com)(USER=oracle))))
>
> VERSION INFORMATION:
> TNS for Linux: Version 11.1.0.7.0 - Production
> Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version
> 11.1.0.7.0 - Production
> TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
> Time: 09-JUN-2010 13:26:00
> Tracing not turned on.
> Tns error struct:
> ns main err code: 12533
>
> TNS-12533: TNS:illegal ADDRESS parameters
> ns secondary err code: 12560
> nt main err code: 503
>
> TNS-00503: Illegal ADDRESS parameters
> nt secondary err code: 0
> nt OS err code: 0
>
> Note the "(DESCRIPTION=(DESCRIPTION=..." bit.  It looks like that is
> what is causing the error.  I'm pretty sure that connect string is
> being built by the AQ code somewhere but I'm not sure how to influence
> it.
>
> Here's our tnsnames.ora - which works just fine for 'regular' connections
> ...
>
> [stbaldwin@opbld06 ~]$ cat $TNS_ADMIN/tnsnames.ora
> #
> #tnsnames.ora - Oracle database service definition file.
> #
> #$Id: tnsnames.ora 10 2009-06-18 13:09:12Z cakirke $
> #
> rac01d =
>        (DESCRIPTION =
>                (ADDRESS_LIST =
>                        (FAILOVER = on)
>                        (LOAD_BALANCE = on)
>                        (ADDRESS =
>                                (PROTOCOL = tcp)
>                                (HOST = opbld05-vip.multiservice.com)
>                                (PORT = 1521)
>                        )
>                        (ADDRESS =
>                                (PROTOCOL = tcp)
>                                (HOST = opbld06-vip.multiservice.com)
>                                (PORT = 1521)
>                        )
>                )
>                (CONNECT_DATA =
>                        (SERVER = shared)
>                        (SERVICE_NAME = rac01d.build.multiservice.com)
>                        (FAILOVER_MODE =
>                                (TYPE = select)
>                                (METHOD = basic)
>                                (RETRIES = 10)
>                                (DELAY = 1)
>                        )
>                )
>        )
>
> rac01d_asm1 =
>        (description =
>                (address =
>                        (protocol = tcp)
>                        (host = opbld05.multiservice.com)
>                        (port = 1521)
>                )
>                (connect_data =
>                        (service_name = +ASM)
>                )
>        )
>
> rac01d1 =
>        (description =
>                (address =
>                        (protocol = tcp)
>                        (host = opbld05-vip.multiservice.com)
>                        (port = 1521)
>                )
>                (connect_data =
>                        (server = shared)
>                        (service_name = rac01d.build.multiservice.com)
>                        (instance_name = rac01d1)
>                )
>        )
>
> rac01d2 =
>        (description =
>                (address =
>                        (protocol = tcp)
>                        (host = opbld06-vip.multiservice.com)
>                        (port = 1521)
>                )
>                (connect_data =
>                        (server = shared)
>                        (service_name = rac01d.build.multiservice.com)
>                        (instance_name = rac01d2)
>                )
>        )
>
> local_listeners_rac01d =
>        (DESCRIPTION =
>                (ADDRESS_LIST =
>                        (ADDRESS =
>                                (PROTOCOL = tcp)
>                                (HOST = opbld06-vip.multiservice.com)
>                                (PORT = 1521)
>                        )
>                )
>        )
>
> remote_listeners_rac01d =
>        (DESCRIPTION =
>                (ADDRESS_LIST =
>                        (ADDRESS =
>                                (PROTOCOL = tcp)
>                                (HOST = opbld05-vip.multiservice.com)
>                                (PORT = 1521)
>                        )
>                )
>        )
>
> I have attached a small self-contained test script.  If you want to
> run it, it takes 2 parameters.  The first is 1 (create queue table and
> queue) or 0 (don't create queue table or queue).  The second is 1
> (enqueue 'BB' message then dequeue 'AA' message) or 0 (enqueue 'BB'
> message then dequeue 'AA' message).
>
> Here's what it looks like when I run it ...
>
> [from node A]
> SQL> @sb1 1 0
> old 2: l_p1 pls_integer := &1;
> new 2: l_p1 pls_integer := 1;
> old 3: l_p2 pls_integer := &2;
> new 3: l_p2 pls_integer := 0;
> Timeout waiting for BB message
>
> PL/SQL procedure successfully completed.
>
> [from node B]
> SQL> @sb1 0 1
> old 2: l_p1 pls_integer := &1;
> new 2: l_p1 pls_integer := 0;
> old 3: l_p2 pls_integer := &2;
> new 3: l_p2 pls_integer := 1;
> declare
> *
> ERROR at line 1:
> ORA-25306: Cannot connect to buffered queue's owner instance
> ORA-06512: at "SYS.DBMS_AQ", line 6
> ORA-06512: at "SYS.DBMS_AQ", line 216
> ORA-06512: at line 66
> ORA-06512: at line 102
>
> Any help would be much appreciated.
>
> Thanks and regards,
>
> Steve
>
>
>
> ---------------------------------------------------------------------------------------
> This email is intended solely for the use of the addressee and may
> contain information that is confidential, proprietary, or both.
> If you receive this email in error please immediately notify the
> sender and delete the email.
>
> ---------------------------------------------------------------------------------------
>

Other related posts: