Tuesday, February 7, 2012

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

To resolve this error when a SCAN is in use ensure that all servers in the cluster are resolvable by all clients. These include the host VIPs and real IP of all the servers in the RAC configuration. For example, if you have a two node cluster and add a third node, all clients must be able to resolve the third node's VIP addresses and real IP not only the SCAN. The resolution should be in DNS from the client machine, if for some reason that cannot be achieved then try using the HOSTS file. Of course a SCAN cannot work using a HOSTS file.


It appears the SCAN is setup OK:

>tnsping mydb_cluster

Used TNSNAMES adapter to resolve the alias
OK (20 msec)

When connecting via SQL*plus:

>sqlplus someuser@mydb_cluster

SQL*Plus: Release Production on Tue Feb 7 10:59:24 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:
ORA-12502: TNS:listener received no CONNECT_DATA from client

To verify this is the issue eliminate the SCAN and try connect to the database using the VIP address:

>tnsping mydb

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost01-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost02-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
OK (30 msec)

>sqlplus someuser@mydb

ORA-12545: Connect failed because target host or object does not exist

In this case to resolve ensure the VIP and HOST addresses (host01-vip, host02-vip, host01 and host02) are resolvable via DNS on the client machine.

Even if we're using the SCAN and add a new host to the cluster, as true as it may be the TNSNAMES.ORA doesn't need updating when using a SCAN the new hosts must still be resolvable by all clients!

No comments:

Post a Comment