5 Replies Latest reply on Apr 22, 2012 4:11 AM by Prashant Dixit

    Tnsping Errror - TNS-03505: Failed to resolve name

    Prashant Dixit
      Morning Everyone,

      Minute ago i was trying to check connectivity of my default instance using 'tnsping' OS command but it failed to resolve the instance name:

      Before i provide you the error codes below is the number of instances running:

      ================================================
      [root@localhost ~]# ps -ef | grep smon
      oracle 2561 1 0 09:29 ? 00:00:01 ora_smon_orcl
      oracle   13888     1  0 12:18 ?        00:00:00 ora_smon_doom /* Trying to ping this instance named 'Doom' */
      root 21039 21006 0 14:15 pts/1 00:00:00 grep smon
      ================================================


      Exact error messsage:

      =====================================================
      [oracle@localhost root]$ echo $ORACLE_SID
      orcl
      [oracle@localhost root]$ export ORACLE_SID=doom
      [oracle@localhost root]$ echo $ORACLE_SID
      doom
      [oracle@localhost root]$ sqlplus "/ as sysdba"

      SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 21 14:19:12 2012

      Copyright (c) 1982, 2005, Oracle. All rights reserved.


      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options

      SQL> select name, open_mode from v$database;

      NAME OPEN_MODE
      --------- ----------
      DOOM READ WRITE

      SQL> !tnsping doom_ /* Error in spite of 'Doom' instance is already running and open' */

      TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-APR-2012 14:19:21

      Copyright (c) 1997, 2005, Oracle. All rights reserved.

      Used parameter files:
      /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

      TNS-03505: Failed to resolve name

      SQL> !tnsping orcl

      SQL> !tnsping orcl /* No error when tried to ping even when 'orcl' instance is not running*/

      TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 21-APR-2012 14:19:40

      Copyright (c) 1997, 2005, Oracle. All rights reserved.

      Used parameter files:
      /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
      OK (0 msec)

      SQL>

      =================================================================================




      Thanks
      - Prashant Dixit
        • 1. Re: Tnsping Errror - TNS-03505: Failed to resolve name
          sb92075
          TNS-03505 is same as ORA-12154

          ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
          ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
          The lookup operation fails because the name provided can NOT be resolved to any remote DB.
          The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
          The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
          The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
          TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
          http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

          Also realize that on *NIX ORACLE_SID is CaseSensitive

          ORACLE_SID=DOOM is different from ORACLE_SID=doom
          • 2. Re: Tnsping Errror - TNS-03505: Failed to resolve name
            Prashant Dixit
            **** Guys this issue is fixed **** - Forgot to check tnsnames.ora file and discovered that instance 'doom' name was not registered in the network file.

            Sorry for this howler ... :)


            Thanks
            - Prashant Dixit
            • 3. Re: Tnsping Errror - TNS-03505: Failed to resolve name
              sb92075
              Prashant_Dixit wrote:
              **** Guys this issue is fixed **** - Forgot to check tnsnames.ora file and discovered that instance 'doom' name was not registered in the network file.

              Sorry for this howler ... :)


              Thanks
              - Prashant Dixit
              I expected this solution

              Edited by: sb92075 on Apr 21, 2012 9:11 PM
              • 4. Re: Tnsping Errror - TNS-03505: Failed to resolve name
                Prashant Dixit
                Below are the new contents of tnsnames.ora file -
                ======

                # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
                # Generated by Oracle configuration tools.

                TEST =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
                (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = test)
                )
                )

                ORCL =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
                (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = orcl)
                )
                )

                DOOM =
                *(DESCRIPTION =*
                *(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))*
                *(CONNECT_DATA =*
                *(SERVER = DEDICATED)*
                *(SERVICE_NAME = doom)*
                *)*
                *)*

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

                ==========================================


                Thanks
                Prashant Dixit