5 Ways To Fix ORA-12154: TNS: Could Not Resolve The Connect Identifier Specified Error

5 Ways To Fix ORA-12154: TNS: Could Not Resolve The Connect Identifier Specified Error

While using Oracle database, you may come across several errors due to some uncertain reasons and one of the error is the ORA-12154 error. If you are the one who has got this error then you don’t have to worry at all. I am saying so because here I am going to show you how to fix ora-12154 error with ease. Yes, you are so lucky that you have visited this webpage because after going through the solutions, you will be definitely able to resolve ora-12154 error in no time.

But, before we proceed to the solutions let us know something more about this ORA-12154 error that takes place while using Oracle database.

What Is ORA-12154: TNS: Could Not Resolve The Connect Identifier Specified?

If you receive ora-12154 error then it means that the Oracle database client has been failed while he/she tries to connect with the listener on the desired server.

Or, in Oracle language, this error is defined as ‘a connection to the service or the database was requested using a connect identifier, as well as the connect identifier that is specified could not be resolved into a connect descriptor with the use of one of the naming methods configured.’ As for example, if the type of connect identifier used was actually a net service name then the net service name is not found in a naming method repository or the repository is actually be reached or located.

There are several reasons that are responsible for causing this oracle error and due to the unknown reasons, it has become quite tricky to fix ora-12154 error. However, here I have tried to solve this error based on some reasons that may cause this error to occur.

What Causes ORA-12154 Error To Occur?

As I have already mentioned that there are several causes due to which ora-12154 error takes place. Here are some of the major known causes:

  • Missing tnsnames.ora File
  • Searching for Wrong Domain
  • Absent Local Naming Method
  • Missing Parenthesis
  • Connect Identifier Mismatch

How To Fix ORA-12154: TNS:could not resolve the connect identifier specified?

Here, you will get to know the best ways to fix ORA-12154: TNS:could not resolve the connect identifier specified error based on the error message you get. All these solutions are very easy and effective that will definitely fix ora-12154 error.

Error Message #1: ORA-12154 Due to Missing tnsnames.ora File

When you deliberately deleted tnsnames.ora file for testing the effect, you get the message:

C:UsersEd>dir /w %TNS_ADMIN%tnsnames.ora Volume in drive C has no label.

Volume Serial Number is C4BB-3A0E

Directory of C:appclientAdministratorproduct12.2.0client_1networkadmin

File Not Found

After getting this message, when you try to connect to the database, you get the below message:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:12:14 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL ERROR:

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

You get these message because of the sqlplus found no file to lookup the connect identifier.

Solution To Fix This Error:

In order to fix this error, you can create a new tnsnames.ora or you can also restore the original one.

C:UsersEd>dir /w %TNS_ADMIN%tnsnames.ora Volume in drive C has no label.

Volume Serial Number is C4BB-3A0E

Directory of C:appclientAdministratorproduct12.2.0client_1networkadmin

tnsnames.ora 1 File(s)            388 bytes

0 Dir(s)  179,702,697,984 bytes free

After doing so, you can again try to connect to the Oracle database:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:20:41 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
Connected.

Error Message #2: ORA-12154 Due to Absent Local Naming Method

When you set TNS_ADMIN environment variable explicitly, you try this:

C:UsersEd>set TNS_ADMIN=C:appclientAdministratorproduct12.2.0client_1networkadmin

Check the content of sqlnet.ora. There’s no TNSNAMES naming method.

C:UsersEd>type %TNS_ADMIN%sqlnet.ora … SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (EZCONNECT)

After this, you try to connect to the database:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:02:33 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL ERROR:

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

Here, you can get ora-12154 error in sqlplus because there is no TNSNAMES naming method available to support the connection.

#1: Solution To Fix This Error: Add TNSNAMES Method

You can fix ora-12154 error here by adding TNSNAMES method back to NAMES.DIRECTORY_PATH.

C:UsersEd>type %TNS_ADMIN%sqlnet.ora … SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Then we tried to connect to the database again.

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:05:24 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
Connected.

Using this way, you will be able to fix this ora-12154 error with ease.

#2: Solution To Fix This Error: Easy Connect Naming Method

You can also try connect naming method to fix ora-12154 error and connect to the database. For this, you have to make sure that you have EZCONNECT in the list of NAMES.DIRECTORY_PATH.

If you don’t have then you will get ora-12154 like this and it will solve your problem soon:

SQL> conn hr/[email protected]:1521/orcl ERROR:

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

Error Message #3: ORA-12154 Due to Connect Identifier Mismatch

If you changed the connect identifier while connecting the database using the method below then you can sometime get this error:

SQL> conn hr/hr@ORCL123 ERROR:

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

Solution To Fix This Error

If you get this error due to connect identifier mismatch then you can try the correct connect identifier so that you can connect to the database easily. To do so, you have to try the below query:

SQL> conn hr/hr@ORCL
Connected.

It is always advisable that you should always use a matched identifier which also exists in tnsnames.ora.

Error Message #4: ORA-12154 Due to Missing Parenthesis

Sometimes it happens that you may not notice that there is one parenthesis that is missing from the connect descriptor; generally it is the right side one. You can take an entry in tnsnames.ora, as for example:

ERPAPP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.21)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ERPAPP) (SERVER = DEDICATED)

)

Have you found any error in the above entry? No…..but I have noticed that there is a single parenthesis missing. Even if the connect identifier is correct but its connect descriptor is not correct then it will cause ORA-12154 error in oracle.

Solution To Fix This Error:

You can try the below codes to fix ORA-12154 due to missing parenthesis:

 ERPAPP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.21)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ERPAPP) (SERVER = DEDICATED) )

)

Using the above codes, the connect identifier is correct.

Error Message #5: ORA-12154 Due to Searching for Wrong Domain

There are some database environments that have been set to the default domain for search which may result ora-12154 error is there is some connect descriptor are configured wrong by these queries:

C:UsersEd>type %TNS_ADMIN%sqlnet.ora … SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN = example.com

Then we tried to connect to the database.

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:31:50 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL ERROR:

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

Solution To Fix This Error

If the ora-12154 error takes place due to wrong domain then you can turn off by connecting out NAMES.DEFAULT_DOMAIN by the following queries:

C:UsersEd>type %TNS_ADMIN%sqlnet.ora … SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

#NAMES.DEFAULT_DOMAIN = example.com

After this, you can again try to connect to the database by following these queries:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:33:55 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
Connected.

You will then see that it has been connected.

Ultimate Solution: Fix ORA-12154 Error Using Oracle File Repair Tool

Apart from all these fixes, if you are unable to fix ORA-12154: TNS:could not resolve the connect identifier specified you get due to any of the reasons mentioned above then just Oracle File Repair Tool. This tool has the capability to fix any kind of error you get while using Oracle database. Several reasons are there that may cause this error but now you can easily fix it by using this tool. It has great features that allow your oracle database to overcome any kind of error and get back the database easily as it was previously saved before. All you have to do is to download and install Oracle File Repair Tool and use the step by step guide and fix the Oracle database error easily.

downloadnow1-7675396 buynow1-8493106 learnmore-7174909

Steps To Fix ORA-12154 Error Using Oracle File Repair Tool

Step 1: Search the Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases on your computer.1-1062318

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

2-4661169

Step 3: Click an object to see its preview.

3-8942009

Step 4: Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

4-7298056

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects. 5-5721642 Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database. 6-8902077

If you ever get ORA-12154 error while connecting to the database then stop worrying and just try the solutions mentioned in the above section of this blog. Yes, all these solutions vary and are based on the causes due to which you get ORA-12154: TNS:could not resolve the connect identifier specified. Apart from this, if you find yourself unsuccessful in fixing this error then you can try Oracle File Repair Tool. This tool will help you get rid of ORA-12154 error easily. So, all the best to you….

downloadnow1-7675396 buynow1-8493106 learnmore-7174909

jacob355-4249226

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.