Problem using AQ on RAC
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Jun 2010 09:09:39 -0500
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:
