SQLNETTNS Name

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
  2. Connect Identifier Mismatch
  3. Search for Wrong Domain

1. Absent Local Naming

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?

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

Solution

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

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 – 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.

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.

Leave a Reply

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