Oracle "ORA-12154: TNS:could not resolve the connect identifier specified": how to fix it

1. Skip the file: try Easy Connect

Name the server and service directly. It’s the quickest test:

sqlplus app@//db01.example.com:1521/PROD

The format is //host:port/service_name. If it works, keep reading to fix the alias, or just use this form in your application. Note that PROD is the service name, not necessarily the SID.

A different error here means you got past ORA-12154, and the rest of the path is what’s wrong (see the end of this article).

2. Which tnsnames.ora does the client read?

tnsping PRODDB

Used parameter files:
/opt/oracle/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

The client looks for tnsnames.ora in this order:

1. the folder in the TNS_ADMIN environment variable;
2. network/admin in the Oracle home;
3. and for Instant Client, its own network/admin subfolder.

So the file you’re editing may simply not be the one being read. Set it explicitly:

export TNS_ADMIN=/opt/oracle/network/admin

On Windows, set it in System Properties → Environment Variables, and restart the application. tnsping comes with the full client; with Instant Client you may not have it, and Easy Connect from section 1 is the quickest test.

3. Is the entry right?

A correct entry:

PRODDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db01.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

Things that break it:

• The alias differs from what you typed. PRODDB in the file and PROD_DB in the connection string don’t match.
• A domain is added. With NAMES.DEFAULT_DOMAIN = example.com in sqlnet.ora, the client looks for PRODDB.example.com. Remove that line, or add the domain to the alias.
• Unbalanced brackets, or a stray character from a copy and paste. One missing ) can invalidate the entries after it.
• The alias doesn’t start at the beginning of the line. Leading spaces can make the parser treat it as a continuation of the previous entry.
• Wrong encoding or a wrong line ending from an editor, more rarely.

4. sqlnet.ora may turn the file off

sqlnet.ora decides where names are looked up:

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

If TNSNAMES isn’t in that list, your file is ignored. And if EZCONNECT is missing, the //host:port/service form fails with this same error.

5. An @ in the password

A classic one. The client splits the connection string at @:

sqlplus app/P@ssw0rd@PRODDB        # the client reads "ssw0rd@PRODDB" as the identifier: ORA-12154

Quote the password, and let your shell pass the quotes through:

sqlplus 'app/"P@ssw0rd"@PRODDB'

Better still, leave the password out and let the tool ask for it: sqlplus app@PRODDB. It also won’t end up in your shell history. In a JDBC or ODBC URL, the password goes in its own field.

6. On Windows: several Oracle homes, 32 and 64 bits

A machine with more than one Oracle client may use a different one from what you think. where sqlplus shows which is first in PATH. A 32-bit application needs the 32-bit client, with its own tnsnames.ora, even on 64-bit Windows; this catches a lot of Excel, Power BI and older tools. Setting TNS_ADMIN makes them all read the same file.