How to Resolve TNS-03505: Failed to resolve name

TNS-03505

Failing to resolve TNS names could involve any one of the network configuration files, such as tnsnames.ora or sqlnet.ora. There're several possible causes may throw TNS-03505:

1. Absent Local Naming Method
2. Missing tnsnames.ora File
3. Connect Identifier Mismatch
4. Searching for Wrong Domain

TNS-03505 due to Absent Local Naming Method

There're several naming methods supported by Oracle in sqlnet.ora. Only local naming can be used for resolving TNS identifiers in tnsnames.ora.

What is Local Naming Method?

Local naming is a naming method (TNSNAMES) that supports alias-fashioned connect identifiers to represent lengthy connect descriptors locally. That is to say, each client should have its own tnsnames.ora to resolve TNS names. If we miss TNSNAMES in the list of naming method, we will see TNS-03505 once we use connect identifiers.

Additionally, local naming instructs clients to use the configuration file tnsnames.ora which may contain several connect identifiers like this:

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
ORA12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA12C)
    )
  )

ORACLE9I =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle9i)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORACLE9I)
    )
  )

SMALLDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle9i)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SMALLDB)
    )
  )

Advantages of Local Naming

Take the first connect identifier for an example, connect identifier ORA12C will be resolved as its lengthy connect descriptor defined in the file at run-time.

By applying local naming, we can shorten our code if we use the identifier instead of its lengthy connect string in our application. Moreover, any programs that use aliases (connect identifier) won't be affected by any changes on their own definitions (connect descriptors).

Reproduce TNS-03505

In contrast, if TNSNAMES is not in the order of the naming methods used for client name resolution lookup, then you can't use local naming and receive TNS-03505:

C:\Users\ed>tnsping ora12c

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 21-JUL-2014 18:47:09

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

Solutions

The solution to TNS-03505 is obvious. Just make sure that tnsnames.ora is existing and then add TNSNAMES to parameter NAMES.DIRECTORY_PATH in sqlnet.ora in order to support local naming.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
...
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNS-03505 due to Missing tnsnames.ora File

Sometimes, you might not notice that your tnsnames.ora is missing. Here I deliberately deleted the file from %TNS_ADMIN%.

C:\Users\ed>tnsping ora12c

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 21-JUL-2014 18:52:09

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

No need to say, the solution is either to get one tnsnames.ora for yourself or to restore tnsnames.ora from the recycle bin.

In some rare situations, your "real" %TNS_ADMIN% might point to another location where has no tnsnames.ora file and you don't know about it. I think you have to know where is your current %TNS_ADMIN% first. Perhaps, reconfiguring the environment variable if necessary.

TNS-03505 due to Connect Identifier Mismatch

Sometimes, users misspell the name of connect identifier to connect the database. So that tools like sqlplus, tnsping or Oracle JDBC driver fails to match a correct entry in tnsnames.ora so as to fail to resolve the given name.

Reproduce TNS-03505

For example, we can reproduce TNS-03505 by tnsping a deliberately misspelled the connect identifier like this:

C:\Users\ed>tnsping comdb

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 21-JUL-2014 19:07:50

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

The utility can match no one named "comdb" in tnsnames.ora. The correct name is "compdb", I missed one letter on purpose.

Solution

How do we handle TNS-03505? Just make sure the name you typed matches the connect identifier in tnsnames.ora.

Further reading: TNSPING Errors Collections

TNS-03505 due to Searching for Wrong Domain

Another possible cause of TNS-03505 is that you have an entry NAMES.DEFAULT_DOMAIN in your sqlnet.ora. It may erroneously resolve your external TNS names.

Solution

The solution is easy, just comment it out by prefixing a pound sign (#).

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
...
#NAMES.DEFAULT_DOMAIN = example.com

DBA can rarely find out the root cause of this type of error, because TNS-03505 itself is a very broad error message in applicable range.

For more connection troubleshooting, you may refer to Oracle 18c Net Services Administrator's Guide: 15 Testing Connections.

Please note that, all symptoms found in TNS-03505 will also be found in ORA-12154: TNS:could not resolve the connect identifier specified.