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-00923: FROM keyword not found where expected

Problem Symptom While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below. SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected Cause of the problem The ORA-00923 can be caused by two different reasons. 1)Oracle itself expect FROM keyword in the position but it could not find any. Like below, SQL> select 48*1024 om dual; select 48*1024 om dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected As no from keyword found so error arises. Oracle expected from in place of keyword dual. 2)The secong cause can sometimes mislead you. Like in case of SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where exp
Read more

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur
Read more

Copy files between Unix and Windows with rcp

There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task. The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service. Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer. you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service. The following steps will help you. Step 01: Turn on RCP service on UNIX Host Turn on the RCP service on unix machine by using inetd: 1. Log on as root. 2. Edit the file /etc/Inetd.conf. 3. Uncomment the lines that start with shell and that start with exec. 4. Save the file. 5. Use ps -ef |grep inetd and determine inetd service p
Read more