listener.ora parameters

Hi all,

We are getting ORA-12535: TNS:operation 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

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?

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: TNS:operation 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?

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.

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?

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.

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

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

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

Quote:

---

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

Quote:

---

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

---

Looks like it's both (http://metalink.oracle.com/metalink/...&p_id=125021.1)

Quote:

---

Oracle Multi-Threaded Server (MTS) on Unix platforms, (without specifying the address with the ports in the INIT.ORA file), Oracle Secure Sockets Layer (SSL) and Windows NT/2000 platforms will cause port redirection.

---

Quote:

---

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

---

OK, but it's a darn sight quicker to try that first than set up a CMAN!

There are more details here: http://metalink.oracle.com/metalink/...T&p_id=66382.1

You're right. We tried it first and it didn't work, which is why we went to CMAN for one of our apps. It worked.

If I remember correctly, the problem is more release level than intent. IE, it may work now, but not on the release we use/were using on that server config (which is Windows). The mix of OS release and Oracle release was our problem, I believe.

We are running into the same thing currently with allowing connections through a firewall. We are on win2k so we are getting the port redirection action happening.

With our firewall (checkpoint) you can set up a sql*net proxy that will allow connections to pass through. However, with our current version checkpoint does not support port redirection, however, with the latest version of checkpoint that is currently available you can do port redirection.

regards,

steve

You can set up shered server configuration (aka multithreaded server) of Oracle database, where you can easily prevent port redirection, that is you can "fix" the port where all the talking between the database and the client process will take place. So there's no need for CMAN or shared socket configuration if you don't want to use them.