RAC ORA-12545 when client side load balancing is on in Oracle 10g RAC

  • From: Harish Kalra <hkalra27@xxxxxxxxxxx>
  • To: RacDBA <racdba@xxxxxxxxxxxxx>
  • Date: Tue, 3 Jun 2008 01:00:06 +0100 (BST)

All, 
   
  I have a 2 node 10g RAC setup on linux boxes with NetApp storage. It is a 
shared infrastructure where different applications are accessing the database 
so I have created one service per application. 
   
  One cluster node is configured as preferred and the other as available for 
the services. Applications are using client side load balancing using 
LOAD_BALANCE=ON in their tnsnames.ora and receiving ORA-12545 occasionally. 
   
  I enabled the debug trace for listener and found that users are getting this 
error only when their database connection requests are being received by the 
listener running on the available node (Please note that the service user is 
intending to connect is not running on available node).
   
  If I modify the service and made both cluster node preferred then end users 
does not get any error. 
   
  To explain this further, consider the extract from a client's tnsnames.ora as 
shown below: (For security I have renamed the hostnames)

ORCL.S001 = 
(DESCRIPTION = (ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = host01-vip.domain.com)(PORT = 1521)) 
(ADDRESS = (PROTOCOL = TCP)(HOST = host02-vip.domain.com)(PORT = 1521)) 
(LOAD_BALANCE = ON) 
(FAILOVER = ON) ) 
(CONNECT_DATA = 
(SERVER = DEDICATED) 
(SERVICE_NAME = S001.domain.com) ) )

Client is trying to connect to the database ORCL using Oracle 10g Service 
"S001.domain.com". But when client connects to the database ORCL, occasionally 
receive error ORA-12545.

To analyze further, I enabled the debug trace in the sqlnet.ora at client 
machine by setting parameter TRACE_CLIENT_LEVEL = support and connected the 
database ORCL using the "S001.domain.com" service. Again I received ORA-12545 
and found following in the sqlnet.log on client machine:

Fatal NI connect error 12545, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host02-vip.domain.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S001.domain.com)(CID=(PROGRAM=C:\oraclexe\app\oracle\product\10.2.0\server\bin\sqlplus.exe)(HOST=PDUBAR-AU)(USER=pdubar))))
 
VERSION INFORMATION: TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - 
Production Time: 13-MAY-2008 11:27:34 
Tracing to file: C:\Documents and Settings\pdubar\cli_5308_1.trc 
Tns error struct: ns main err code: 12545 
TNS-12545: Connect failed because target host or object does not exist 
ns secondary err code: 12560 
nt main err code: 515 
TNS-00515: Connect failed because target host or object does not exist 
nt secondary err code: 1001 
nt OS err code: 0

So from the trace file, it clearly shows that client is receiving error 
ORA-12545 only if it is connecting to "S001.domain.com" on node 
host02-vip.domain.com.

But why client is not able to connect to S001.domain.com service on host02-vip. 
Should not listener running on host02 redirect the connection to host01?. 
  
Here is the output of lsnrctl services from host02:

Service "S001.domain.com"
has 1 instance(s). Instance "ORCL1", status READY, has 1 handler(s) for this 
service... Handler(s): "DEDICATED" established:7 refused:0 state:ready 
REMOTE SERVER (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01-vip)(PORT=1521)))

  Remote_listener parameter is also set on this cluster database.
   
  My question is :
   
  Is this the expected behavior? If yes then what is fun of enabling the load 
balancing?
   
  Thanks & Regards
  -Harish Kalra

       
---------------------------------
 Has your work life balance shifted? Find out.

Other related posts:

  • » RAC ORA-12545 when client side load balancing is on in Oracle 10g RAC