Re: ORA-12535: TNS:operation timed out errors for over a year now...

Sybrand Bakker wrote:
> On 8 Jul 2006 17:04:49 -0700, "Charles Hooper" <hooperc2000_at_yahoo.com>
> wrote:
>
> >* There is a VPN connection or some type of WAN link between the server
> >and the client. The VPN or WAN link connection go down temporarily,
> >and the client tries to connect to the database (or display a report).
> >After a 40 second delay, or whatever the timeout is, the client reports
> >ORA-12535: TNS:operation timed out.
> >
> >* Something on the server is preventing the listener from responding.
> >It could be another database on the server is having a bad day -
> >listener tries to hand off a connection to the database that is having
> >a bad day, and never returns from the handoff. If this happens, you
> >will likely see many entries in the alert logs for the databases, and
> >possibly several trace files.
>
>
> Sqlnet uses *two* ports, one to get in touch, one for the actual
> communications.
> The second port is determined *randomly*.
> If that port isn't opened in the firewall (and why should it), you get
> 12535.
> Listeners don't have a 'bad day'.
>
> There are three workarounds
> - set up connection manager on the database server
> - set up mts on the database server to a fixed port
> - set use_shared_sockets = true
>
> --
> Sybrand Bakker, Senior Oracle DBA

Regarding the "bad day" reference, this was an attempt to over-simplify for the sake of understanding. I stated that there is a possibility that another database instance on the server is having a "bad day" that causes the listener on the server that is handling multiple databases to stop responding.

One of the unfortunate common traits of people who are experts in their field, it that they tend to communicate using terminology that only other experts in their field understand. The person who originally posted the request for assistance is a network administrator, not an Oracle DBA. That is a little bit like a user of Microsoft Word asking a C programmer on Windows how to make a dot on the screen a little more green. And the C programmer replying to just use getpixel, perform a binary OR with 00FF00, and then setpixelv to update the screen. The C programmer may be right, but does it help the user of Microsoft Word?

With a properly configured firewall, the three work arounds that you posted are unnecessary to allow the connection to pass through a stateful firewall to a VPN connection.

I am aware of how the connection between the client and server jumps from one set of IP ports to another. I will, however, diagree with your assertion that "Listeners don't have a 'bad day'". Perhaps the best description of what could cause a listener to have a bad day can be found on page 61 of Tom Kyte's "Expert Oracle Database Architecture":

"Now that the client software knows where to connect to, it will open a TCP/IP socket connection to the server with the hostname localhost.localdomain on port 1521. If the DBA for our server has installed and configured Oracle Net, and has the listener listening on port 1521 for connection requests, this connection may be accepted. In a network environment, we will be running a process called the TNS listener on our server... When it receives the inbound connection request... [to a] dedicated server connection, the listener will create a dedicated server for us. On UNIX, this is achieved via a fork() and exec() system call... The new dedicated server process inherits the connection established by the listener, and we are now physically connected to the database. On Windows, the listener process requests the database process to create a new thread for a connection. Once the thread is created, the client is "redirected" to it, and we are physically connected."

There are various other examples in the "Oracle Database Concepts 10g Release 2" manual, "Expert Oracle Database 10g Administration", and several other sources. What happens if a physical or artificial limit is encountered during the fork/exec call or the request to create a new thread. Hitting a physical limit on a 32 bit operating system is not out of the question.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Jul 09 2006 - 12:45:02 CDT