Thursday, January 31, 2013

"ORA-12502: TNS:listener received no CONNECT_DATA" when connecting to a SCAN address

This is a client side error. Nothing to do with the database. Usually, if the hostname used in tnsnames.ora are not resolvable by the client then the subject lined error appears.

For my case, I was facing this error for a 2 node RAC cluster 11gR2 database. We are using SCAN address in the tnsnames.ora. Even though the client can resolve scan still it's failing. 
Because, not only the SCAN but all server in the cluster needs to be resolvable by application clients. This includes the host VIPs and real IP of all the servers in the RAC configuration.

Look at the demonstration of this error:
 I can ping the DB alias from client pc.

#tnsping DB1

TNS Ping Utility for Linux: Version - Production on 31-JAN-2013 14:13:09
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=db1-scan)(PORT=1521)) (CONNECT_DATA = (SERVER=dedicated) (SERVICE_NAME=
OK (0 msec)

But, Can't connect to the database using SQLPLUS.

sqlplus ***/****@db1

SQL*Plus: Release Production on Thu Jan 31 14:13:35 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ORA-12502: TNS:listener received no CONNECT_DATA from client

It's because, the client firewall rule is blocking it from communicating either.
VIP/Real IPS of all the servers associated with RAC configuration.

The problem was fixed after fixing firewall in accessing VIP.

No comments: