SQLNETTNS Name

How to Resolve TNS-03505: Failed to resolve name

TNS-03505: Failed to resolve name

This type of error could involve any one of the network configuration files, such as tnsnames.ora or sqlnet.ora. Two possible causes may throw TNS-03505:

  1. Absent Local Naming
  2. Connect Identifier Mismatch
  3. Search for Wrong Domain

1. Absent Local Naming

There’re several naming methods supported by Oracle in SQLNET. Only local naming, also called TNSNAMES supports alias-fashioned connect identifiers to represent lengthy connect descriptors locally.

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)
    )
  )

Take the first connect identifier for an example, we can use ORA12C to delegates its lengthy connect descriptor in programs, which shortens our codes a lot.

Moreover, any programs that use connect identifiers won’t be affected by any changes on connect descriptors, because the connection will dynamically map the connect descriptor at run-time. This is a very outstanding advantage to use local naming.

In contrary, if you didn’t put TNSNAMES in the order of the naming methods used for client name resolution lookups, then you can’t use local naming method like this:

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

The solution to TNS-03505 is obvious. Just 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)

2. Connect Identifier Mismatch

A connect identifier, also called TNS name is an identifier that maps a user-defined name to a connect descriptor.

Sometimes, users may misspell the name corresponding to their own identifier in tnsnames.ora, so that tools like sqlplus, tnsping or Oracle JDBC driver fails to match a correct entry. 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.

How do we handle this error? Just make sure the name you typed matches the connect identifier in tnsnames.ora.

Further reading: TNSPING – 8 Aspects You Should Know

3. Search 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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *