listener.ora parameters

[kburrows]

Hi all,

We are getting ORA-12535: TNSperation timed
when trying to connect.

I have the parameter CONNECT_TIMEOUT_LISTENER = 10 set.

Thinking I need to change this to be 0 or higher than 10.
Any Advice?

Also, 8.1.5 database, will I have to bounce the listener for the value to be reset or does this happen with automatic instance registration.

If I have to bounce, will the currently connected users get kicked out. I am thinking not, since listener spawns a separate dedicated process. So listener has nothing to do with the currently connected users, right? Only the new users trying to connect will be affected, for a few minutes.

I am a little nervous about changing this on prod database, any advice helpful.

Thanks,
Kathy

[jrpm]

I would not be so quick to modify the listener.

In my experience there are two other areas that have a higher probability of being the problem.

The first is the network. Make sure the users are not seeing a sort of venturi effect where their 100MB NICs are on a 10MB line or switch port. This can cause packet drops which appear as timeouts.

The second is to make sure you are not approaching a system limit on the number of connections/sessions/processes. This is a performance limit, not a parameter one. I have one system that is supposed to handle 200+ connections (enough memory, etc) but bogged down miserably at about 180. This can also cause timeout problems.


You did not say what your OS is. If it is one of the Unixes, what is the box stress level while this is happening?

[kburrows]

The details get more confusing.

Oracle DB, NT Server on Novell Network.

Connecting from Web Application to the Database gives,
500 Servlet Exception
java.sql.SQLException: [Oracle][ODBC][Ora]ORA-12535: TNSperation timed out
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6879)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7036)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3028)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)

Response from Network/Firewall Techs:
The 1521 ports are open, no trouble here.

Respone from the Web Hosting Techs:
We can't get connected to TCP port 1521 on the database server
hosting this instance of oracle. Packets destined to that
server pass through our firewall, but we never complete
the three-way handshake that initiates a TCP transaction.

This was recently working, last Friday. Of course, everyone contends there have been no changes, what so ever.

No way do I claim to be a network guru, but it has been 3 days, I think thats long enough. We need an answer. I definately want to make sure its not an Oracle DB problem.

I will check the NT server resources. What else can I do?

[jrpm]

The problem is not your listener it is your firewall. You are probably doing NAT (Network Address Translation). The Connect packet gets to the listener, but with a response to talk to a port (and maybe address) different from the x.x.x.x:1521 that initiates the transaction(s). The port change is part of SQL*NET, with the address translation a standard part of firewalling (with a proxy server maybe?)

You need to look at ConnectionManager (part of the Oracle software suite) to sit in your DMZ with the firewall and have the Oracle connections pass through it. Oracle 8i required CMAN to handle NAT.

[kburrows]

This sound interesting enough. But why was it working a few days ago and now it is not? If I needed this additional setup, wouldn't I have required it from the start?

[jrpm]

Have your network people been 'cleaning up' their routing tables? Or have parts of the network been reset/restarted (due to scheduled maintainence, etc?). The control tables may have just now been updated for port access, etc.

[kburrows]

Thanks for the insite. I will check into the items you mentioned.

[DaPi]

I don't recognise the symptoms . . . but if it is a firewall prob, then you need to be aware of Oracle's dynamic port re-allocation that takes place as well as the NAT.

USE_SHARED_SOCKET=TRUE is simpler than setting up a CMAN.
http://forums.dbasupport.com/forums/...threadid=38541

[jrpm]

Shared socket doesn't always work on all platforms and releases.

[stmontgo]

Originally posted by DaPi
I don't recognise the symptoms . . . but if it is a firewall prob, then you need to be aware of Oracle's dynamic port re-allocation that takes place as well as the NAT.

USE_SHARED_SOCKET=TRUE is simpler than setting up a CMAN.
http://forums.dbasupport.com/forums/...threadid=38541

actually that's a windooze port redirection, not an oracle redirect as I understand it