This discussion is archived
9 Replies Latest reply: Jan 11, 2013 11:04 AM by damorgan RSS

Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2

user10393748 Newbie
Currently Being Moderated
A few days ago I started seeing ORA-12170 errors in applications running on two different servers (both servers running Windows Server 2003). Both of these machines are connecting to the same Oracle 11GR2 database (running on Windows Server 2008R2).

The errors do not happen all the time, but they do happen frequently. I can see the issue by running tnsping from one of the affected machines -- it will often return ORA-12535:

C:\>tnsping orcl 10

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2
013 09:50:43

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

Used parameter files:
C:\oracle\product\11.2.0\client_1\Network\Admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.199.199)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (30 msec)
OK (30 msec)
OK (30 msec)
OK (50 msec)
OK (30 msec)
OK (30 msec)
OK (30 msec)
OK (50 msec)
TNS-12535: TNS:operation timed out


I turned on tracing for the client, and the following seems to be a portion of trace relevant to my issue:

(1948) [09-JAN-2013 22:04:39:253] nttcnp: creating a socket.
(1948) [09-JAN-2013 22:04:39:269] nttcnp: exit
(1948) [09-JAN-2013 22:04:39:269] nttcni: entry
(1948) [09-JAN-2013 22:04:39:269] nttcni: Tcp conn timeout = 60000 (ms)
(1948) [09-JAN-2013 22:04:39:269] nttcni: TCP Connect TO enabled. Switching to NB
(1948) [09-JAN-2013 22:04:39:269] nttctl: entry
(1948) [09-JAN-2013 22:04:39:269] nttctl: Setting connection into non-blocking mode
(1948) [09-JAN-2013 22:04:39:269] nttcni: trying to connect to socket 1380.
(1948) [09-JAN-2013 22:04:39:269] ntt2err: entry
(1948) [09-JAN-2013 22:04:39:269] ntt2err: exit
(1948) [09-JAN-2013 22:04:39:269] ntctst: size of NTTEST list is 1 - not calling poll
(1948) [09-JAN-2013 22:04:39:269] sntseltst: Testing for WRITE on socket 1380
(1948) [09-JAN-2013 22:05:00:286] sntseltst: FOUND: write request on socket 1380
(1948) [09-JAN-2013 22:05:00:301] ntt2err: entry
(1948) [09-JAN-2013 22:05:00:301] ntt2err: soc 1380 error - operation=1, ntresnt[0]=505, ntresnt[1]=60, ntresnt[2]=0
(1948) [09-JAN-2013 22:05:00:301] ntt2err: exit
(1948) [09-JAN-2013 22:05:00:410] nttcni: exit
(1948) [09-JAN-2013 22:05:00:410] nttcon: exit
(1948) [09-JAN-2013 22:05:00:410] nserror: entry
(1948) [09-JAN-2013 22:05:00:410] nserror: nsres: id=0, op=65, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(1948) [09-JAN-2013 22:05:00:410] nsopen: unable to open transport


I'm at a loss as to what to look for. Other machines on the same network can connect without a problem. One of the affected servers is virtual; moving it to a different physical host machine made no difference. I checked to make sure no Windows/software updates were applied to either server that may have caused this. I uninstalled/reinstalled the Oracle client software, uninstalled/reinstalled network adapters. Any suggestions would be appreciated.

Edited by: user10393748 on Jan 10, 2013 2:06 PM
  • 1. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    sb92075 Guru
    Currently Being Moderated
    what clues exist within listener.log file?

    Is connection pooling used to connect to this DB?
  • 2. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    damorgan Oracle ACE Director
    Currently Being Moderated
    A couple of thoughts.

    1. Your ping times are far too high. Has someone implemented or changed rules on a firewall?

    2. Have you opened an SR with Oracle Support?

    3. Do you have antivirus software running on your Windows server? Has an exception been carved out for Oracle files and traffic?

    4. Have you asked your network people to provide you a log of all changes during the 24 hours preceding the problem first showing up? If not do so. And if they don't have a log then they should be taken to the woodshed by corporate management and given some serious re-education on how to do the job they are being paid to perform.

    5. Post your LISTENER.ORA, SQLNET.ORA, and TNSNAMES.ORA files or check them yourself ... are you looking things up by IP address or is their name resolution taking place on a separate DNS server?
  • 3. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to
    Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors [ID 119706.1]
  • 4. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    user10393748 Newbie
    Currently Being Moderated
    No firewalls are up, and no antivirus software is running. The network people tell me no changes were made. Connections are done via IP so DNS should not be an issue.

    Further testing revealed that tnsping would occasionally fail with ORA-12535 even when run locally from the database server itself (using the server IP -- tnsping through localhost worked fine).

    After having run completely out of ideas, we tried a reboot of the database server, which has resolved the issue for now. But since I still don't know the cause, I'm afraid the problem could reappear.

    One thing I noticed before rebooting that seemed unusual was an extremely excessive amount (~60k) of TIME_WAIT connections to the database server. They appeared to be lingering long after they should have closed, and were of course cleared out after reboot. I tried researching this but was unable to find much information. Could this have caused the connection issues to Oracle?
  • 5. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    sb92075 Guru
    Currently Being Moderated
    Since you don't answer my questions, I won't waste more time on yours.
  • 6. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    John Spencer Oracle ACE
    Currently Being Moderated
    It is very likely that the large number of sockets in time_wait status had something to do with the proble, There is an excellent article in [url http://www.serverframework.com/asynchronousevents/2011/01/time-wait-and-its-design-implications-for-protocols-and-scalable-servers.html]this blog post.

    John
  • 7. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    user10393748 Newbie
    Currently Being Moderated
    I'm sorry -- I do sincerely appreciate any attempt to help. This problem has been causing me a lot of grief; I was interrupted last night in the middle of replying to your questions by a need to help users with workarounds and I didn't realize I never actually posted my reply.

    I looked through listener.log, but I didn't see anything that looked troublesome. Connection pooling is used.
  • 8. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    you could go thru oracle support document that i post and try to troubleshoot
  • 9. Re: Intermittent ORA-12170 / ORA-12535 errors connecting to 11GR2
    damorgan Oracle ACE Director
    Currently Being Moderated
    My best guess is that your network people aren't telling you the truth, the whole truth, and nothing but the truth. They may believe what they are saying but it doesn't ring true from my experience and it is possible that a switch or cable is in the initial process of failing and they haven't figured it out yet because the link light is still blinking green.

    1. Run a traceroute from your client to the database server. Then go to the server and run a traceroute back to your client. Post the results here.
    Sample traceroute from my laptop to the University of Washington which at the moment is only about 
    5 miles away.
    
    C:\Users\oracle>tracert www.washington.edu
    
    Tracing route to www.washington.edu [128.95.155.198]
    over a maximum of 30 hops:
    
      1     8 ms     7 ms    23 ms  73.105.112.1
      2     7 ms     7 ms     9 ms  te-0-0-0-14-ur05.bellevue.wa.seattle.comcast.net [68.85.144.209]
      3     8 ms    10 ms    11 ms  ae-18-0-ar03.burien.wa.seattle.comcast.net [69.139.164.113]
      4     9 ms    14 ms     8 ms  ae-1-0-ar03.seattle.wa.seattle.comcast.net [68.85.240.94]
      5    19 ms    17 ms    11 ms  68.86.94.237
      6    37 ms    26 ms    27 ms  4.59.234.25
      7    27 ms    27 ms    29 ms  ae-12-51.car2.Seattle1.Level3.net [4.69.147.132]
      8    10 ms    11 ms     9 ms  PACIFIC-NOR.car2.Seattle1.Level3.net [4.53.146.142]
      9     9 ms    21 ms     9 ms  ae0--4000.iccr-sttlwa01-02.infra.pnw-gigapop.net [209.124.188.132]
     10    11 ms    13 ms    11 ms  vl4000.uwbr-ads-01.infra.washington.edu [209.124.188.133]
     11     *        *        *     Request timed out.
     12    30 ms    10 ms    11 ms  ae3--836.uwar-uwtc-1.infra.washington.edu [128.95.155.195]
     13    17 ms    11 ms    13 ms  www4.cac.washington.edu [128.95.155.198]
    
    Trace complete.
    
    C:\Users\oracle>
    
    Note the "Request timed out." which is part of it of the trace.
    2. Ask the network people to give you a copy of the log. Don't take their verbal word for it. I have heard the same nonsense from network admins for years and all too often they are wrong. And just so you know ... I'll bet a beer they don't have a log book.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points