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

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.

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

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

On one occasion, after my IP changed, I had to use this tip from https://logic.edchen.org/how-to-resolve-the-listener-supports-no-services

I changed the listener to localhost from an invalid IP

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))';

alter system register;