Troubleshooting ORA-12535: TNS: operation timed out or kponepms: encountered ora-12535 for location net8 or tns 12535

by

Troubleshooting ORA-12535: TNS: operation timed out or kponepms: encountered ora-12535 for location net8 or tns 12535

I received a complaint from one of my clients that they are not able to connect to the database from their sqlplus from client machine.When trying to connect through sqlplus from the client machine the below error was occurring.

ORA-12535I checked the following from both database server and client machine:

  1. tnsname.ora : found the service name was configured correctly.
  2. tnsping: it was working fine.
  3. Telent the ip address and port no:- To see if the port is open on the given ip address from client machine. Found it was working fine.
  4. Netstat –an: – It should show all Client connections paired with the port the Listener is listening on.

From the above steps found that every thing was configured correctly and was working. Hence I enabled tracing to further investigate.

To enable tracing set trace level in sqlnet.ora as given below:

  • trace_level_client = 16
  • trace_file_client = client
  • trace_directory_client = D:\oracleproducttrace

Below is the output from the trace log file of sqlnet.ora

  • [31-AUG-2012 11:49:59:890] niotns: Not trying to enable dead connection detection.
    [31-AUG-2012 11:49:59:890] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.3.45)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=FCRTEST2)(CID=(PROGRAM=D:oracleora92binsqlplus.exe)(HOST=MYTEST)(USER=test))))
    [31-AUG-2012 11:49:59:890] nscall: entry
    [31-AUG-2012 11:49:59:890] nsmal: entry
    [31-AUG-2012 11:49:59:890] nsmal: 164 bytes at 0xfb0f60
    [31-AUG-2012 11:49:59:890] nsmal: normal exit
    [31-AUG-2012 11:49:59:890] nscall: connecting…
    [31-AUG-2012 11:49:59:890] nladini: entry
    [31-AUG-2012 11:49:59:890] nladini: exit
    [31-AUG-2012 11:49:59:890] nladget: entry
    [31-AUG-2012 11:49:59:890] nladget: exit
    [31-AUG-2012 11:49:59:890] nsc2addr: entry
    [31-AUG-2012 11:49:59:921] nttbnd2addr: entry
    [31-AUG-2012 11:49:59:921] nttgetport: entry
    [31-AUG-2012 11:49:59:921] nttgetport: port resolved to 1521 –> The initial packets sent to the listener is on port 1521.
    …………………..
    [31-AUG-2012 11:49:59:968] nsprecv: packet dump
    [31-AUG-2012 11:49:59:968] nsprecv: 00 3E 00 00 05 00 00 00 |.>……|
    [31-AUG-2012 11:49:59:968] nsprecv: 00 34 28 41 44 44 52 45 |.4(ADDRE|
    [31-AUG-2012 11:49:59:968] nsprecv: 53 53 3D 28 50 52 4F 54 |SS=(PROT|
    [31-AUG-2012 11:49:59:968] nsprecv: 4F 43 4F 4C 3D 74 63 70 |OCOL=tcp|
    [31-AUG-2012 11:49:59:968] nsprecv: 29 28 48 4F 53 54 3D 31 |)(HOST=1|
    [31-AUG-2012 11:49:59:968] nsprecv: 30 2E 31 30 2E 33 2E 34 |0.10.3.4|
    [31-AUG-2012 11:49:59:968] nsprecv: 35 29 28 50 4F 52 54 3D |5)(PORT=|
    [31-AUG-2012 11:49:59:968] nsprecv: 31 36 39 36 29 29 |1696)) | –>The received packet from the listener telling the client to use 1696 instead of 1521
    …………………………………….
    [31-AUG-2012 11:49:59:968] nscall: connecting…
    [31-AUG-2012 11:49:59:968] nsc2addr: entry
    [31-AUG-2012 11:49:59:968] nttbnd2addr: entry
    [31-AUG-2012 11:49:59:968] nttgetport: entry
    [31-AUG-2012 11:49:59:968] nttgetport: port resolved to 1696 –> The client resolving the connection to port 1696

From the above trace output I found that packets sent by the listener is on port 1521  where as the received packet is on port 1696. My database service has been configured with 1521 port however, when client is connecting to the database it is resolving to port 1696 due to which I am getting TNS operation time out error. Which concludes that the Database is listening on different port.

How does my Database listen on different port?

  • When the Oracle client makes a connection to the database (e.g. sqlplus abc/abc@test) it compares the alias name supplied in the sqlplus line and looks for a match in the TNSNAMES.ORA file or Names server. Once it obtains the address for the database server, a connection attempt is made to the server from the client.
  • The Listener is contacted on the database server and port redirection can take place depending on the platform, configuration of the INIT.ORA file and/or the Oracle product being used. The underlying network layer on the server will obtain a free port from the Operating System (OS) and send back to the client via the Listener the new port assignment. The client will then try to connect to the database on a new port. This is where connection failure normally occurs.
  • A remote Oracle client making a connection to an Oracle database can fail if there is a firewall installed between the client and the server and if port redirection is taking place. The firewall will block the connection to the new port when the Oracle client connects to the database – the client typically fails with Oracle errors ORA-12203 or ORA-12535.
  • The client connection failure is due to port redirection from the Database Server’s operating system. Port redirection requires the client to connect to the database using a different port than originally configured in the configuration files. Oracle Multi-Threaded Server (MTS) on UNIX platforms, (without specifying the address with the ports in the INIT.ORA file), Oracle Secure Sockets Layer (SSL) and Windows NT/2000 platforms will cause port redirection.

Solution for database listening on different port

  1. Firewall Vendor
    The first solution is to contact the firewall vendor and see if they have an upgrade to allow for Oracle connectivity with OS port redirection. If the firewall software can be upgraded, it is the best solution to follow.
  2. Configure Oracle Connection Manager
    Configure of Oracle Connection Manager (CMAN) to allow the clients to connect through a firewall. CMAN is an executable that allows clients to connect despite a firewall being in place between the client and server. CMAN is similar to the Listener that it reads a CMAN.ORA configuration file, which contains an address that Oracle Connection Manager listens for incoming connections, usually default ports of 1610 or 1630. CMAN starts similar to the Listener and will enter a LISTEN state (see netstat). The Oracle client must be Net8 or above and have the following in the TNSNAMES.ORA file.
    cmantest =
    (description =
    (address_list =
    (address =                     <- first address is to CMAN
    (protocol=tcp)
    (host=hostname or ip of cman)
    (port=1610)
    )
    (address=                       <- second address is to Listener
    (protocol=tcp)
    (host=hostname or ip of listener)
    (port=1521)
    )
    )
    (connect_data = (sid = sidname))
    (source_route = yes)            <- This tells the client that it is using CMAN and it must take the first two addresses listed.
    )
    When the client contacts Oracle Connection Manager, CMAN completes the connection with the second address the client brought with it. The second address will point to the host machine where the listener is running. CMAN then uses that address to ‘pass’ the connection to the Listener and the connection to the database is established. All redirection takes place between CMAN and the database, therefore the Oracle Connection Manager Installation should be placed after the firewall but before the database.
  3. USE_SHARED_SOCKET
    Set USE_SHARED_SOCKET to TRUE in the Windows registry. This allows the OS to share the listening port (e.g. 1521) and clients then remain using that same port when connecting to the database – no port redirected takes place.
    Note: A downfall of this solution is all connetions will stay on the listener port. If the listener is stopped or restarted all the connections will be severed from the database. Furthermore, USE_SHARED_SOCKET could be a performance bottleneck with multiple connections to the database. Please use it deliverately if many simultaneous connections to the database are involved.
  4. Setting MTS ports
    A workaround for this is to specify the port in the MTS parameters of the INIT.ORA file. This allows the Dispatcher to use the port specified and will not select a random port. Ensure the user-defined MTS port/s are opened on the firewall.
    The following example shows the ports set to 2450 and 3125. Set these parameters according to your individual systems.
    dispatchers=”(address=(protocol=tcp)(host=hostname)(port=2450))(dispatchers=1)”
    dispatchers=”(address=(protocol=tcp)(host=hostname)(port=3125))(dispatchers=1)”
    SSL
    Using SSL will cause Port redirection. The workaround is to select and set the ports using MTS in the INIT.ORA .

Refer metalink note: Oracle Connections and Firewalls (Doc ID 125021.1)

I hope the above steps will help you in resolving this issue.

Leave a Comment