ORA-12154: TNS:could not resolve the connect identifier specified

Problem Description:
--------------------------------------------
Whenever you try to connect your database it returns error ORA-12154.
SQL> conn prod/prod@shaik
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Cause of The Problem:
------------------------------------
The cause of the problem is depends on the which naming method you are using. Suppose if you use tnsnames.ora for naming method then there might be problem in it. I look for oerr command on my unix machine and got,

-bash-3.00$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// - If you are using local naming (TNSNAMES.ORA file):
// - Make sure that "TNSNAMES" is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// - Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// - If you are using directory naming:
// - Verify that "LDAP" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Verify that the LDAP directory server is up and that it is
// accessible.
// - Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// - Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// - If you are using easy connect naming:
// - Verify that "EZCONNECT" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Make sure the host, port and service name specified
// are correct.
// - Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.

Solution of The Problem:
-----------------------------------------------
Actually above solution already depicted what to do if you get the problem. My suggest after getting ORA-12154 immediately go as the steps mentioned below.

Step 1: Look for tnsnames.ora and sqlnet.ora.
-----------------------------------------------------------------------
Look for your tnsnames.ora and sqlnet.ora file that you are using. On unix the default location is $ORACLE_HOME/network/admin. You better avoid to use tnsnames.ora. By using easy naming service you can easily avoid this error.

Step 2: Check both file
---------------------------------------------------------------------------
•After locating both file open it with any viewer like on windows with notepad and on unix with less or more or cat.

•Check within the files whether any illegal character or any unnecessary space exists. If have then remove that.

•Check whether the exact entry exist on the tnsnames.ora that you used in connection identifer. Like if you use conn a/a@shaik then within tnsnames.ora search for only shaik alias.

•You can check your whether there is error or not in the tnsnames alias inside tnsnames.ora by using tnsping. In order to check alias shaik issue,

bash-3.00$ tnsping shaik

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:25:58

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = ((ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KHALEEL)))
TNS-12533: TNS:illegal ADDRESS parameters
So there is illegal ADDRESS parameter which we see an extra ( before ADDRESS parameter.

If our tnsalias was good, then it would result below with how many miliseconds.
bash-3.00$ tnsping shaik

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:28:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = KHALEEL)))
OK (10 msec)

•Note that with name alias checking tnsnames also tell us which location parameter files it used. Here the location is /oracle/app/oracle/product/10.2.0/db_1/network/admin. So /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora location is used in order to resolve name. However if your tnsnames.ora is in other location then you have to set TNS_ADMIN variable.

•If you see tnsnames.ora is most likely accurate, echo the TNS_ADMIN environment variable.

% echo $TNS_ADMIN

•If nothing is returned, try to set the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_of_tnsnames.ora_file

In K Shell or bash, the syntax is:
% export TNS_ADMIN=full_path_of_tnsnames.ora_file

In windows the syntax is:
set TNS_ADMIN=full_path_of_tnsnames.ora_file

•Now try and see whether error remains. If still you get error then in the SQLNET.ORA file, add the parameter AUTOMATIC_IPC = OFF. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. And try to connect. If still you get error then check the permission of tnsnames.ora and sqlnet.ora. For workaround you can set chmod 777 of these files and try to connect. At this point it is expected you have solved your problem.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more