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
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.
$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’;
For Local naming : (tnsnames.ora file issue)
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
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.
Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…
In my previous article I have given Top 20 technical support interview questions with its…
In my previous articles I have given 15 most asked desktop support interview questions with…
A business analyst is someone who is versed in processes of data analysis used for…
In my previous article I have already given top questions and answers for Desktop support…
In my previous article I have given unix production support interview questions and answers. In…