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:\Users\Ed>dir /w %TNS_ADMIN%\tnsnames.ora
Volume in drive C has no label.
Volume Serial Number is C4BB-3A0EDirectory of C:\app\client\Administrator\product\12.2.0\client_1\network\admin
File Not Found
After getting this message, when you try to connect to the database, you get the below message:
C:\Users\Ed>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:\Users\Ed>dir /w %TNS_ADMIN%\tnsnames.ora
Volume in drive C has no label.
Volume Serial Number is C4BB-3A0EDirectory of C:\app\client\Administrator\product\12.2.0\client_1\network\admin
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:\Users\Ed>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:\Users\Ed>set TNS_ADMIN=C:\app\client\Administrator\product\12.2.0\client_1\network\admin
Check the content of sqlnet.ora. There’s no TNSNAMES naming method.
C:\Users\Ed>type %TNS_ADMIN%\sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (EZCONNECT)
After this, you try to connect to the database:
C:\Users\Ed>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:\Users\Ed>type %TNS_ADMIN%\sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Then we tried to connect to the database again.
C:\Users\Ed>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/hr@ora11g.example.com: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:\Users\Ed>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:\Users\Ed>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:\Users\Ed>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:\Users\Ed>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.
 |
 |
 |
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.
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.
Step 3: Click an object to see its preview.
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.
Final Words
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….
|
|
|
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.