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.

Tuesday, January 29, 2013

ORA-12162 TNS:net service name is incorrectly specified

After creating a new environment and database. I was facing this error. Even trying to logging in sys as sysdba was failing.

[oracle@lab ~]$ sqlplus / as sysdba
SQL*Plus: Release Production on Tue Jan 29 15:15:46 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ORA-12162: TNS:net service name is incorrectly specified

Looking into the error doesn't exactly tell what the issue is. This was actually environmental variable related issue. Nothing to do with tns, listener or even with the database.

I'm running oracle on linux. So needs to set ORACLE_HOME and ORACLE_SID variable correctly.

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME

That's it! Now I'm able to login

[oracle@lab~]$ sqlplus / as sysdba
Connected to:
Oracle Database 11g Release - 64bit Production