ORA-12154: TNS:could not resolve the connect identifier specified | How to resolve ORA-12154 error?

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

While working with different  functionalities ; the oracle developers are facing different types of errors.The most common error coming while connecting database is ORA-12154: TNS:could not resolve the connect identifier specified .In this article i will explain how ORA-12154 error will solve and why this error will come.This is the most common error and most searched error by developers and DBAs.This error will come while trying to establish connection first time.So this is most common error in oracle.

“ORA-12154 error has been searched on google approximately 1 lakh times per month”

The error is displayed in the above images.

CLICK HERE FOR CHECKING DIFFERENCE BETWEEN SQL AND NOSQL

Why ORA-12154 error will come?

ORA-12154 error is very common error related to ‘tnsnames.ora’ file.This error will come when oracle is unable to locate the service name specified in ODBC data source.ORA-12154 error will come at local service when application tries to connect with remote server.This error will come in following situations :

Situation 1 :

When Application tries to connect with database.

Situation 2 :

When one database will try to  connect with other database via database link.

Situation 3 :

Just after installing the oracle database and tries to connect system user.

In above situations this error will occur.There should be multiple reasons for this error.The following should be the reasons of this error. So If this error will come then DBA or developer needs to do first check i.e. tnsnames.ora.

Following needs to check to resolve this error :

1.The Service name entry is missing from tnsnames.ora

Make sure that the service name we are using is the same for which the listener is listening for.

2.The Entry is wrong in tnsnames.ora file

3.tnsnames.ora is in wrong ORACLE_HOME location

4.Your Service name might have an alias.Check that in global entries as well as local entries.

Location for tnsnames.ora :

$ORACLE_HOME/network/admin/tnsnames.ora

5.Check for global_name settings with SQL.

Select *  from global_name; 

This query value should match the init.ora parameters db_name and db_value. If the value is not matching you can change that value using alter command of database.

Alter Database Rename global_name to ‘New Name’;

Resolution of Error :

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

For Local naming : (tnsnames.ora file issue)

1. Kindly check the tnsnames.ora file is present in specified location or not.
2. Check that tnsnames.ora is accessible to database
3. The Service name which are using should present as connect identifier in tnsnames.ora
4. Make sure that tnsnames is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile.
5. Check for the syntax errors in tnsnames.ora file.If there is error in this file then this file is unusable.

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

TNSNAMES.ORA file sample :

Database_name =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Database_name)
)
)

If you are using instant client method rather than tnsnames.ora file then database attribute name needs an connect URL rather than service name.

Syntax :

//host:port/service_name

 

Where host is the host_name or ip address and port will be the oracle port and service name is the service name specified.

Example:

10.98.186.22:ODSDB/PDWHS

CLICK HERE TO GET THE SAMPLE TNSNAMES.ORA FILE

For Directory Naming :

1.LDAP must be there in names.directory_path parameter of sqlnet.ora file.

2.Verify LDAP directory server is up and accessible.

3.Verify that service name and database name is configured in directory or not.

I have specified some ways to resolve the error ‘ORA-12154: TNS:could not resolve the connect identifier specified’ which is frequent error on Oracle. Hope you like this article.If you like this article don’t forget to share it.