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.
Example:
It appears the SCAN is setup OK:
>tnsping mydb_cluster
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb-scan.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB_CLUSTER)))
OK (20 msec)
When connecting via SQL*plus:
>sqlplus someuser@mydb_cluster
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 7 10:59:24 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
ERROR:
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
ERROR:
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!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment