ODBC connection limit in WinXP not in Win2K?

Question

We run a nightly process that generates a large number (~8000) of reports each night using Crystal Reports. This is really just about 14 different reports that get run once for each of our clients. The reports' data source is a windows ODBC connection (via the control panel) and connects to an Oracle 10g instance on our network.

Our current production machine runs Windows 2000 professional and we recently purchased a newer machine to replace it, with the intention of running it under Windows Server 2008. While preparing to make the switch, we noticed that test runs on our local development machines with Windows XP would run just fine until roughly 950-1100 reports had been generated. The number varies, but once it stops working, no connections can be made from the machine until you either wait about 30 minutes, restart the machine, or run it from someone else's machine. Once it stop working, we are unable to connect to the Oracle server using other applications as well, such as Toad, from that machine until waiting out the time or restarting the machine. Today we decided we would just try it out on the new machine in case it was just an anomaly with our development environment, but the new machine ran into the exact same problem. We have tried looking over the settings of the ODBC connection and every screen is exactly the same. We are certain the versions of all software installed match production exactly.

The error returned is a timeout error from Crystal: Database connector error. When we try to restart the application, we are immediately met with the error:

Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-12535: TNS:operation timed out

ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR [HY000] [Oracle][ODBC][Ora]ORA-12535: TNS:operation timed out

We are completely stuck on search terms for google, but we think it has something to do with the number of reports getting generated.

Can anyone help with this issue?

Answer 1

ORA-12535 is a network or firewall issue. PERIOD.

There is nothing to investigate at the application side, there is everything to investigate at the network side like - is there any firewall in play - is there any Network Address Translation into play - have the correct ports been opened in the firewall - is Oracle using random return ports (by default Oracle always uses 2 tcp/ip ports, the first one fixed, and configured in tnsnames.ora, the actual communication takes place on a radom port. If that is a problem at your site you need to fix that) - You need to run netstat -r to identify which ports are actually used.

• Maybe you should consider using a sniffer to identify further problems.

My experience is, that apart from stubborn network and/or firewall administrators involved, the issue can always be resolved. I have posted the usual methods to resolve this many times, and I am aware of the fact most current posters here do not make any attempt to research the archives at Google. Yet I am not going to post them again. They are easy to find, and they are also documented in the Net administrators manual. The small number of people responding to queries should stop rewarding laziness. If this will make this forum go extinct, so be it. Sybrand Bakker Senior Oracle DBA

**Taken from: http://database.itags.org/oracle/167383/