How to Resolve TNS-03505: Failed to resolve name | PeopleSoft Tutorial
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)
       ))
Apurva Tripathi
 

  • ARNIK SARAIYA says:

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

    Arnik

  • swarnalatha says:

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

  • swarnalatha says:

    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

  • swarnalatha says:

    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

  • Vadz says:

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

    ——————-

  • Fabien says:

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

  • Dave Derrick says:

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

  • Leo says:

    I have spend hours trying to fix this issue, I follow your notes and fixed it in les than 2 minutes. Thanks.

  • Patrick says:

    I pondered this problem for days and after all it ended up beeing the “TNSNAMES.ora” in uppercase…
    In Unix environments it has to be all lowercase and it works.
    Sometimes the little things count.

  • Nitin Gupta says:

    I also struggle a lot to resolve this issue what i found it has been added space in tnsnames.ora file for new entry , Entry should be start with 0 column in a file.

  • >