Listener

TNSPING – 8 Aspects You Should Know

tnsping

tnsping is a utility provided by Oracle to test the listener is available or not from clients. If there’s any problem in the path to the destination, it will fail with different aspects. Here I list several possible error types below for your reference.

  1. “tnsping is not recognized as an internal or external command” or “tnsping command not found”
  2. tnsping message 3511 not found
  3. TNS-03505: Failed to resolve name
  4. TNS-12545: Connect failed because target host or object does not exist
  5. TNS-12535: TNS:operation timed out
  6. TNS-12541: TNS:no listener
  7. TNS-12547: TNS:lost contact
  8. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Now, let’s see the details.

1. “tnsping is not recognized as an internal or external command” or “tnsping command not found”.

  • Phenomenon:
  • In Windows:

    C:\Users\ed>tnsping compdb
    'tnsping' is not recognized as an internal or external command,
    operable program or batch file.

    In Linux:

    [oracle@test ~]$ tnsping compdb
    -bash: tnsping: command not found

    The shell returned tnsping command not found.

  • Cause:
  • Actually, tnsping is not recognized as an internal or external command is a DOS error. DOS shell cannot recognize your command in any directories in %PATH% variable. That’s because you have not append bin directory of Oracle Client to the environment variable %PATH%. You may check the variable like this:

    C:\Users\ed>echo %PATH%

  • Solution:
  • You have to append the bin location of Oracle Client to the environment variable %PATH%

2. tnsping message 3511 not found

  • Phenomenon:
  • C:\Users\ed>tnsping compdb

    TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 21-JUL-2014 19:02:08

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

    Message 3511 not found; No message file for product=NETWORK, facility=TNSTNS-03505: Message 3505 not found; No message file for product=NETWORK, facility=TNS

  • Cause:
  • You have no or incorrect ORACLE_HOME registry.

  • Solution:
  • Make sure ORACLE_HOME is set correctly by regedit.exe. Please refer to this post for more information:

    How to Resolve TNS-03511 “Message 3511 not found”

3. TNS-03505: Failed to resolve name

  • Phenomenon:
  • 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

  • Cause:
  • The utility cannot find “comdb” in tnsnames.ora. The correct name is “compdb”, I deliberately missed one letter in the alias name.

    Another possible cause is that you have an entry NAMES.DEFAULT_DOMAIN in your sqlnet.ora. It may erroneously resolve your external TNS names.

  • Solution:
  • Make sure (1). The name you typed matches the name in tnsnames.ora. (2). sqlnet.ora does support TNSNAMES method like this.

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    If you have NAMES.DEFAULT_DOMAIN in sqlnet.ora, just comment it out by prefixing a pound sign (#).

    [oracle@test ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
    ...
    #NAMES.DEFAULT_DOMAIN = example.com

4. TNS-12545: Connect failed because target host or object does not exist

  • Phenomenon:
  • C:\Users\ed>tnsping compdb

    TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 21-JUL-014 19:10:59

    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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
    TNS-12545: Connect failed because target host or object does not exist

  • Cause:
  • The utility cannot find the host “primary01“.

  • Solution:
  • Add an IP-Hostname mapping record in /etc/hosts

    C:\Users\ed>notepad %SystemRoot%\System32\drivers\etc\hosts
    ...
    10.1.2.123    primary01 primary01.example.com
    C:\Users\ed>ping primary01

    Pinging primary01 [10.1.2.123] with 32 bytes of data:
    Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
    Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
    Reply from 10.1.2.123: bytes=32 time<1ms TTL=64
    Reply from 10.1.2.123: bytes=32 time<1ms TTL=64

    Ping statistics for 10.1.2.123:
        Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
        Minimum = 0ms, Maximum = 0ms, Average = 0ms

5. TNS-12535: TNS:operation timed out

  • Phenomenon:
  • C:\Users\ed>tnsping compdb

    TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:03:21

    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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
    TNS-12535: TNS:operation timed out

  • Cause:
  • (1). The hostname primary01 points to a wrong IP address in etc-hosts file. (2). The firewall of database server blocks all the incoming connections to port 1521.

  • Solution:
  • (1) Check your /etc/hosts or C:\Windows\System32\drivers\etc\hosts file. (2). Open the port 1521. You may refer to the following post for more steps:

6. TNS-12541: TNS:no listener

  • Phenomenon:
  • C:\Users\ed>tnsping compdb

    TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:20:06

    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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
    TNS-12541: TNS:no listener

  • Cause:
  • The connection cannot find the listener, it could be not started or at another port, e.g. 1522.

  • Solution:
  • Check the status and port of LISTENER on server side and start it if necessary.

    [oracle@primary01 ~]$ lsnrctl status

    LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:22:34

    Copyright (c) 1991, 2013, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       Linux Error: 2: No such file or directory
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01)(PORT=1521)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       Linux Error: 111: Connection refused

    As you can see, the database port is 1521 without questions. But the listener is not started. So let’s start it up.

    [oracle@primary01 ~]$ lsnrctl start

    LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:23:59

    Copyright (c) 1991, 2013, Oracle.  All rights reserved.

    Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 12.1.0.1.0 - Production
    System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/primary01/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1521)))

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
    Start Date                22-JUL-2014 19:23:59
    Uptime                    0 days 0 hr. 0 min. 2 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/primary01/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1521)))
    The listener supports no services
    The command completed successfully

7. TNS-12547: TNS:lost contact

  • Phenomenon:
  • C:\Users\ed>tnsping compdb 100

    TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:26:03

    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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary0
    1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
    OK (20 msec)
    OK (10 msec)
    OK (20 msec)
    OK (0 msec)
    OK (10 msec)
    ...
    TNS-12547: TNS:lost contact

    C:\Users\ed>

  • Cause:
  • The listener could be hang or not responsive.


  • Solution:
  • Try to restart (stop + start) the listener. Or you can connect to another backup listeners.

8. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

  • Phenomenon:
  • The listener is up.

    C:\Users\ed>tnsping compdb

    TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:24:04

    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


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
    OK (60 msec)

    But the database is down or not open.

    C:\Users\ed>sqlplus hr/hr@compdb

    SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 22 20:11:20 2014

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

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor


    Enter user-name:

  • Cause:
  • (1) The database is not available or you had a mismatched service name in tnsnames.ora. (2) The database service is not registered with the listener.

  • Solution:
  • Make sure (1). The database is open. (2). The service name in your TNS name does match the service registered in the listener. (3). The service name registered in listener does match the service of the database.

tnsping can only far reach the listener on the connection path, no more further. That is, it do care everything about the listener, but it don’t care about the database is alive or not. You have to use sqlplus to do more checks.

An important note: tnsping cares about the listener can be reached or not, it does not care the service name in connection descriptor is right or wrong in your tnsnames.ora. Don’t be fooled by the success of tnsping results. That’s why we use sqlplus, a simple connect to test deeper configurations.

Leave a Reply

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