Ошибка 12154 oracle

Arrhhh!! I RAN INTO THIS AGAIN!!!

Just install ToadForOracle in C:\ or any directory without parenthesis in the path.

---

In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers!

I am using SQL Reporting Services with an Oracle Database. The problem is the brackets in the path to Visual Studio (BIDS). Oracle doesn’t like apps that start in a path with brackets:

RDBMS 10g XE problem with parenthesis in path

So I made a BAT file to open Visual Studio with Progra~2 as the short path name for «Program Files (x86)».

Here is the contents of the BAT file:

rem Progra~2 is short path name for "Program Files (x86)" and works around an Oracle client bug that doesn't like the ()'s in the path
start /B "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

I name this BAT file StartBIDS.BAT and put it in the directory:

«C:\Program Files\Microsoft SQL Server\Start BIDS.bat»

Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon. This allows me to open TOAD, Visual Studio, BIDS and etc apps that I use to work with Oracle.

Update:

Alternatively make a Junction:

mklink /J "C:\Program Files (x86)\" "C:\Program Files x86\"

Then remove the brackets in the shortcut:

Arrhhh!! I RAN INTO THIS AGAIN!!!

Just install ToadForOracle in C:\ or any directory without parenthesis in the path.

---

In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers!

I am using SQL Reporting Services with an Oracle Database. The problem is the brackets in the path to Visual Studio (BIDS). Oracle doesn’t like apps that start in a path with brackets:

RDBMS 10g XE problem with parenthesis in path

So I made a BAT file to open Visual Studio with Progra~2 as the short path name for «Program Files (x86)».

Here is the contents of the BAT file:

rem Progra~2 is short path name for "Program Files (x86)" and works around an Oracle client bug that doesn't like the ()'s in the path
start /B "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

I name this BAT file StartBIDS.BAT and put it in the directory:

«C:\Program Files\Microsoft SQL Server\Start BIDS.bat»

Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon. This allows me to open TOAD, Visual Studio, BIDS and etc apps that I use to work with Oracle.

Update:

Alternatively make a Junction:

mklink /J "C:\Program Files (x86)\" "C:\Program Files x86\"

Then remove the brackets in the shortcut:

ORA-12154: TNS could not resolve the connect identifier specified error comes when you try to connect Oracle database using clients/tools like SQL Plus, Toad, Oracle Forms, Oracle Reports, and SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapters.

Now, let’s reproduce the error and find out solution for ora 12154. We are going to us Oracle Forms 11G on the Oracle Web logic server for demonstration.

Reproduce ORA-12154

Open the Oracle form builder and connect it to the database. Create a custom form. Compile the form and then start the web logic server. Run the form.

Internet Explorer (IE) opens and launches the form service, but you get the error as shown below,

What is wrong here?

The form builder is able to connect to the database during design time what happens when you run the form.

Let me explains,

ORA 12154 Solution

Form builder uses default.env file during run time and TNS_ADMIN variable to locate the tnsnames.ora file. Below is the value of this variable.

TNS_ADMIN – C:\Oracle\Middleware\asinst_1\config

So copy the tnsnames.ora file to the location specified by the TNS_ADMIN variable to resolve ORA 12154 error in Oracle forms. If this does not work, just comment the TNS_ADMIN variable entry in default.env file as shown below and copy tnsnames.ora file to ORACLE_HOME path.

ORACLE_HOME=C:\Oracle\Middleware\Oracle_FRHome1

Then run the forms, your forms should work fine.

Still facing the issue, it means something wrong with TNS entries.

Verify TNSNAMES.ORA and SQLNET.ORA files

First, verify tnsnames.ora and sqlnet.ora file exists in ORACLE_HOME and there is a proper entry of the database which you are trying to connect.

Below is the default location for tnsnames.ora and sqlnet.ora files for Windows and Linux /Unix Operating System.

Windows Client

ORACLE_HOME\network\admin

UNIX Client

$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle

Sample TNS entry,

TEST =
     (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (Host = xxx.yy.zz.56)
             (Port = 1521)
           )
       )
       (CONNECT_DATA = (SERVICE_NAME = TEST)
       )
     )

Sample SQLNET.ora entry,

TRACE_LEVEL_CLIENT = OFF
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH = (TNSNAMES)

Summary

I am sure solution mentioned in this article should help you to resolve this TNS 12154 error. If you still facing issue and not able to resolve, I would recommend to read below articles.

• ORA-12154-TNS Could not Resolve the Connect identifier Specificed when Connecting from Forms Builder (Doc ID 1604843.1)
• TNS Issues when connecting to database with Forms Builder 12.2.1.2.0

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.

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-3A0E

Directory 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-3A0E

Directory 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.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Have you gotten an “ORA-12154: TNS:could not resolve the connect identifier specified” error? Learn what causes it and how to resolve it in this article.

ORA-12154 Cause

If you attempt to access or log on to an Oracle database, you might get this error:

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

This means that the tnsnames.ora file was not found or has an error within it.

There are a few steps you can take to resolve this ORA-12154 error.

Check that the tnsnames.ora file exists

There is a tnsnames.ora on both the client and server systems. It’s located in the ORACLE_HOME/network/admin directory. I’ve written a guide to the TNSNAMES file here which has more information.

ORACLE_HOME is where your Oracle database is installed on the server, or on your own computer if you’re using Oracle Express.

For example, in my installed version of Oracle Express, my ORACLE_HOME is:

C:\oraclexe\app\oracle\product\11.2.0\server

If I open the network then admin folders, I will see a tnsnames.ora file.

If it exists in this folder, then you need to check that it has no errors (see the next step).

If it doesn’t exist, then you can create one.

To do this:

1. Create a new file in this folder and call it tnsnames.ora.
2. Open the file in a text editor and add the information in this format:

The syntax of the tnsnames.ora file is:

<addressname> =
(DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
  )
(CONNECT_DATA =
  (SERVICE_NAME = <service_name>)
)
)

The example in my Oracle Express instance is:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

So, just copy and paste this into your new tnsnames.ora file, make changes as necessary, and save it.

Try your connection again (the one where you got the error) and see if it works.

Check that TNSNAMES.ORA has no syntax errors

If the file exists, open it and see that there are no syntax errors.

Using the example above:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Check that the brackets are all in the right place, there are no quotes in there, no missing lines or anything unexpected.

Check that TNSNAMES.ORA has your service name in it

To be able to connect to your database, the tnsnames.ora file needs to have your service name in it.

Open the tnsnames.ora file and add it in there if it does not exist, using the examples above.

Check that TNSNAMES.ORA has read permission

Sometimes, the file can exist and be syntactically correct, but doesn’t have any permissions.

If other users or processes cannot read the file, you’ll get the ORA-12154 error.

So, check that the file can be read by other users by applying read permissions to it.

Run the TNSPING Utility

Oracle includes a tnsping utility for checking that the TNSNAMES is OK.

You can find this by going to ORACLE_HOME/bin/tnsping.exe

For example:

C:\oraclexe\app\oracle\product\11.2.0\server\bin

If you’re on Windows, you can open the Command Prompt and CD to this directory.

Then, run tnsping xe (or your service name you want to check)

This should show if it is OK or not.

TNSADMIN Environment Variable is Missing

If you’re connecting on Windows, this error can sometimes happen if the TNSADMIN environment variable is missing.

To check this:

1. Go to Start > Control Panel
2. Open System
3. Click “Advanced system settings”
4. Click Environment Variables
5. Add a new system variable called TNSADMIN with a value of ORACLE HOME\network\admin

This is often not needed, but if you’ve tried everything else, and are still getting the ORA-12154 error, you can try adding the TNSADMIN environment variable.

So, there are a few solutions to the “ORA-12154: TNS:could not resolve the connect identifier specified” error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!