How to Resolve TNS-03505: Failed to resolve name

by Apurva T. on August 1, 2016

in Database

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.

Also Read:  Understanding Oracle Database and PeopleSoft Database

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)

 

Also Read:  ORA-20005: object statistics are locked (stattype = ALL)

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

{ 8 comments… read them below or add one }

ARNIK SARAIYA March 19, 2017 at 11:59 am

Thanks it worked like a charm. Wasted my half a day and finally got your blog and it solved the issue. Thanks.

Arnik

Reply

swarnalatha March 29, 2017 at 1:25 am

The above same issue i have but i am not able to resolve it . can anyone help me plzz

Reply

swarnalatha March 29, 2017 at 1:29 am

Actually i am trying to connect both mysql and oracle. when i create a database link at oracle it has been created successfully. but when i select a table called ss which is present in mysql
SELECT * FROM “SS”@OM2MYSQL
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
i am getting this error. Can you help me plzz
Thanks in Advance

Reply

swarnalatha March 29, 2017 at 1:31 am

my listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = tst
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = dg4odbc)
)
(SID_DESC =
(SID_NAME = tst)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = dg4odbc
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = SWARNALATHA)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

my tnsnames.ora

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SWARNALATHA)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
TSTLINK=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=SWARNALATHA)
(port=1521)
)
(CONNECT_DATA=
(SID=tst)
)
(HS=OK)
)

my sqlnet.ora

n# 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)
NAMES.DEFAULT_DOMAIN=tst

Reply

Vadz June 3, 2017 at 7:42 am

Hi..I am using oracle 12c and i got this same error when i run it on my command prompt
“TNS-03505- Failed to resolve name
————————————————

I have this files on my Tnsnames.ora
—————————————————-
# tnsnames.ora Network Configuration File: C:\OracleDB12c\app\ts\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
—————————
on sqlnet.ra. i have this

# sqlnet.ora Network Configuration File: C:\OracleDB12c\app\ts\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

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

—————————————————————————–

my listener.ora i have this
————————————————————————–
# listener.ora Network Configuration File: C:\OracleDB12c\app\ts\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\OracleDB12c\app\ts\product\12.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = “EXTPROC_DLLS=ONLY:C:\OracleDB12c\app\ts\product\12.2.0\dbhome_1\bin\oraclr12.dll”)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

——————-

Reply

Fabien November 22, 2017 at 3:47 am

I also noticed that the “TNS-03505: Failed to resolve name” error can occur whenever the database names in the tnsnames.ora file are preceded with spaces.
As soon as I removed the leading spaces, my tnsping worked and some more sensitive applications than others started to connect fine.

Before:
EDWPDEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EDWPDEV)
)
)

After

EDWPDEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EDWPDEV)
)
)

Reply

Michael December 4, 2017 at 12:44 pm

Spaces ! Seriously, its 2017. Thanks for your suggestion because it did the trick.

Reply

Dave Derrick December 12, 2017 at 6:54 am

I just realised I had manually created my TNS Names file & called it tnsnnames.ora, took me nearly 30 minutes to spot the double N !!

Reply

Leave a Comment

Previous post:

Next post: