11

How to Resolve TNS-03505: Failed to resolve name

Every now and then when I created a new Oracle database in an existing Oracle base (be it 11g or 12c) – I get the error “TNS-03505: Failed to resolve name” whenever I try to do tnsping on a new database. You may receive this error even when not doing tnsping. For example, you may see this error when logging on the datamover in bootstrap mode (for example, using SYSADM).

99% of the time, this error turns out to be something that I overlooked and I could have resolved the error in a few minutes rather than wasting hours. As a result, I decided to document it here so everyone can benefit.

Additionally, if you know of any resolution to this error – let us know in the comments section below and we’ll include the solution for everyone’s benefit.

This error is documented on the Oracle website as:

TNS-03505: Failed to resolve name
Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.
Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.

I went through Net Manager and Net Configuration Manager about 1000 times. You know, instead of actually reading the error.

I thought I needed a Loopback Adapter. I didn’t.

I started to go through the files by hand, sqlnet.ora, listener.ora, tnsnames.ora (which I’m much more confident with surprisingly)…it had to be something small.

Appearing on its own, this essentially means the client has not even gotten to the point of attempting to make contact with a server.  The TNS-03505 is telling you that it simply cannot find any record of the database you are trying to establish a connection with.

Some things to check include the following:

• If you are using local naming (tnsnames.ora file) – make sure that “TNSNAMES” is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (sqlnet.ora file). SQLNET.ORA sample provided below.
• Make sure the host, port and service name specified are correct and typo free.
• In the tnsnames.ora, verify that the database name is present and is spelled correctly. Also, check for any potential errors like missing out on “(” or “)”
• Check the environment variable TNS_ADMIN. If you have TNS_ADMIN declared, check the tnsnames.ora file in the directory, which it points to. Many a times it has turned out that this variable was pointing to a different location than the one I was looking in (especially since the release of PUM, DPKs etc).
• Verify that there are not multiple tsnnames.ora files present in the same location.
• Verify that the same entries are found in Oracle client installs.
• Verify that there is no firewall issue in connecting to the database server.
• If you did edit the tnsnames.ora or sqlnet.ora file recently – make sure that the file got saved properly (I noticed that whenever I open the file in Notepad++, it does not save properly using the save shortcut, Control+S). Best thing is to close down you editing software and if the file was not saved, it will prompt you to save or discard.

 

SQLNET.ORA file

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

TNSNAMES.ORA

HR92U018 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = HR92U018)
       ))

PSHRDMO =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1521))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = PSHRDMO)
       ))
FSCM92 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = FSCM92)
       )
    )
EP92U019 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = EP92U019)
       )
    )
	
HCMDEV =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1521))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = HCMDEV)
       ))