Intermittent Oracle client errors

I have a 9.2.0.5 DB running on a sun Server, and Web Servers running Win2K/IIS5.0 with Oracle client 8.1.7 connecting to the DB. this system has been configured and running for a while.

Everything's working fine, except that at some points, usually during high load, lots of current connections per web server, the web servers seem not to be able to access the DB.
sqlnet.log on a web server will show an error like the one below. The server's listener log file shows no errors, I actually notice that during these periods the server has a light load.
I had network people checking the network layer and there doesn't seem to be any TCP/IP level error, all ports full at 100Mbs full duplex

all machines are behind a firewall. again, there has been no change to the network layout or configuration.


---sample errors from sqlnet.log on the client ----
Fatal NI connect error 12535, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbprodv440)(PORT=1521))(CONNECT_DATA=(SID=FPROD)(CID=(PROG RAM=D:\CFusion\Bin\cfserver.exe)(HOST=WEBSRV02)(USER=SYSTEM))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 8.1.7.0.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 8.1.7.0.0 - Production
Time: 12-OCT-2005 22:15:58
Tracing not turned on.
Tns error struct:
nr err code: 0
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 60
nt OS err code: 0
-----------------------------------------------------

Looks like your listeners can't handle the load. Is your listener.log really getting a lot of entries during this time? If so (as I suspect), I'd configure multiple listeners and load balance between the two (or three, or four). Also, you might want to look at shared servers (MTS) on the database if you are looking for a large number (1000) of simultaneous connect/disconnect requests.

Jeff,

thanks for the answer,

First , how do I know it is a listener problem? yes, there are lots of connections showing on listener.log. Is there any log file that would show connections are being dropped to the floor by the server? would tracing on the client help to prove or disprove this?

>>I'd configure multiple listeners and load balance between the two (or three, or four).

how is that done? is this what is refered in the net guide manual as "Connection Load Balancing"? their examples use 2 servers; do you have en example using one DB server?

Regarding "shared servers", I have received advice in the past that it can actually lower performance than the standard configuarion. do you have any data/stats on shared server agaisnt dedicated server?

the DB server is set to handle only 450 processes now. it is getting close to this limit and I was thinking of incremanting it anyway

Quote:

---

Originally Posted by structuredweb

First , how do I know it is a listener problem?

---

I don't know, I only suspect it. I used to get these timeout errors when connection load testing with connection manager.

Quote:

---

yes, there are lots of connections showing on listener.log. Is there any log file that would show connections are being dropped to the floor by the server?

---

Don't know, but I don't think the listener will tell you the client timed out since it wouldn't know.

Quote:

---

would tracing on the client help to prove or disprove this?

---

prove. The problem you might have is to get a meaningfull trace it has to be very detailed. Those traces typically slow down the client process which might not exhibit your problem.

Quote:

---

>>I'd configure multiple listeners and load balance between the two (or three, or four).

---

each listener would listen on one port. On the client you would give multiple addresses and tell sql*net to load balance between the two. If you need a more specific example (with tnsnames.ora and listener.ora files) I can blog it tonight.

Quote:

---

how is that done? is this what is refered in the net guide manual as "Connection Load Balancing"? their examples use 2 servers; do you have en example using one DB server?

---

sure, it can be done with one server. Do you have a ref for that document? You might be on the wrong path...

Quote:

---

Regarding "shared servers", I have received advice in the past that it can actually lower performance than the standard configuarion. do you have any data/stats on shared server agaisnt dedicated server?

---

as always, it depends. Sometimes they're faster, sometimes not. If you're app does a ton of connect/disconnects and fast little queries, I suspect it would be faster with MTS because you would take away the overhead of creating a process on the Solaris side. However, if your app is on a middle-tier that always has a connection to the database, MTS may not be appropriate. Test, test, test.

Quote:

---

Originally Posted by marist89

each listener would listen on one port. On the client you would give multiple addresses and tell sql*net to load balance between the two. If you need a more specific example (with tnsnames.ora and listener.ora files) I can blog it tonight.

sure, it can be done with one server. Do you have a ref for that document? You might be on the wrong path...

---

If you can upload examples it would be terrific for me.

the documents I am refering to are the set of manuals for Oracle 9.2 from otn.oracle.com

i might try with a simple addtion of QUEUESIZE to the listener and see if this solves the issue but that might be too easy :)

thanks again,

Yoram Ayalon

http://marist89.blogspot.com/2005/10...listeners.html

Jeff,

grabbed your blog, will play with it over the weekend. lots of thanks, will post results on Monday

Yoram