Notifications
Clear all

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

   RSS

2
Topic starter

A developer is having trouble connecting to his Oracle database.

When trying to connect from the command line with "sqlplus", he gets the following error message: "ORA-12154: TNS:could not resolve the connect identifier specified"

This developer is using a computer which was cloned from another computer.

What is the best way to resolve this issue?

 

Topic Tags
3 Answers
1

This is a challenging issue with multiple possible root causes.  Here is a systematic way to validate this.

First, before trying to connect through java, connect on the command line using sqlplus.  For a local dev env with Oracle 18 XE, you should use:

sqlplus system/password@localhost:1521/XEPDB1

 

You *must* use XEPDB1.  If that doesn't connect, please run:

lsnrctl status

 

If it's working properly you'll see something like this among the output:

Services Summary...
Service "733cd371b3424e998e46c97f691b550b" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...

 

Note the "xepdb1" entry.  If you don't see the xepdb1 or other services, it means the Oracle database is having trouble talking with the oracle listener.

 

We have found a couple root causes for this.  First, check your dbhomeXE\network\admin\listener.ora file and make sure the hostname is correct in the LISTENER entry:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wks-gmerrill1.elogex.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

We've observed that in some envs, this has to be a true hostname, not an IP.  If this value is an IP, or the hostname doesn't match your hostname, please modify this.

Also - check your tnsnames.ora in the same dir.  It should look something like this:

 

# tnsnames.ora Network Configuration File: C:\oraclexe\18\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wks-gmerrill1.elogex.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

LISTENER_XE =
(ADDRESS = (PROTOCOL = TCP)(HOST = wks-gmerrill1.elogex.com)(PORT = 1521))


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

After changing these, restart the OracleXE service Windows Services, then restart the listener Oracle Listener service in Windows Services.  Finally,  retry the lsnrctl command.

If this still doesn't help, it might be because the database isn't able to register itself to the listener.  This seems to happen sometimes if your IP changes.  We found this link useful in this regard: https://logic.edchen.org/how-to-resolve-the-listener-supports-no-services/

The steps for this are:

1) Connect to sqlplus via "sqlplus system/password"  (don't give host port or sid)

2) Run: ALTER SYSTEM REGISTER;

3) exit sqlplus and re-run your lsnrctl status command, you should see it available.

 

It's not clear why they aren't getting automatically registered, but calling that command "kicks" oracle so that they get registered.  It's not clear if this will outlive restarts of listeners/dbs/etc.

During a recent call we found that a developer's issue was that they had the wrong name listed in the listener.ora and tnsnames.ora files.

Here are the steps to find out what name goes in those files (when using Windows 10):
Click on the Windows search field in the bottom left of Windows.
Search for and open Control Panel.
Click on System and Security
Click on System
The value to use will be shown next to "Full computer name".

screenshot:

image

screenshot 2:

image

screenshot 3:

image

Note: that not all "Full computer name" values have the domain. Some just use the Computer name. Removing extra text here, such as ".attlocal.net" (and then restarting the services) fixed this issue for some users.

In Oracle databases 0.0.0.0 is a wild card for any host. You can use this in the listener.ora and tnsnames.ora files if you have trouble getting other host values to connect.

0

TNS is usually resolved by a file called Tnsnames.ora.

I believe it's usually found under ORACLE_HOME\network\admin directory on windows and the $ORACLE_HOME/network/admin directory on Linux/UNIX

 

This post was modified 11 months ago by Marco Gouveia

@mgouveia
We don't use tnsnames.ora in most environments (definitely not in local). We use fully-qualified host/port/sid like localhost:1521/XEPDB1

0

A file called Tnsnames.ora is typically used to resolve TNS.

It's typically found in the basement, I believe ORACLE_HOME\network\admin directory on windows and the $ORACLE_HOME/network/admin directory on Linux/UNIX

© 2020 One Network Enterprises. All rights reserved. Privacy Policy