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

If you ever had to establish a connection to an Oracle Server, you probably noticed that sometimes it is not an easy task. The same notice the users of our Oracle related products, they often have some problems during connections to their servers. We analyzed these problems and found that the most common error, often the very first one when you are trying to connect to your database, is

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

Below I will discuss the reason for this error and possible resolutions.

Oracle server may return this error if you specified incorrect name of database, or Port number or Hostname. So, first of all check all your Oracle credentials.

If everything is correct, make sure that:

· you have the TNSNAMES.ORA file and it is located in %OracleClientDirectory%\ network\admin\

· TNS is described correctly in tnsnames.ora file. Here is our example:

# tnsnames.ora Network Configuration File: C:\app\John\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.77)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Additionally look through the example of listener.ora

# listener.ora Network Configuration File: C:\app\John\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\John\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = “EXTPROC_DLLS=ONLY:C:\app\John\product\11.2.0\dbhome_1\bin\oraclr11.dll”)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.77)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.77)(PORT = 1527))
)
)

ADR_BASE_LISTENER = C:\app\John

· you added ORACLE_HOME to the Windows Environment Variables. On the screen below you can see the example of the path:

NOTE:

-сheck that there are no ‘’ at the end of the path.

- the path should end with a folder followed by a BIN or bin folder.

This concludes the solution to the most frequently encountered error when connecting to Oracle server. Write comments whether this information helped you or not!