How can I set the timeout on OCILogon2?

Question

When the Oracle 10 databases are up and running fine, OCILogon2() will connect immediately. When the databases are turned off or inaccessible due to network issues - it will fail immediately. However when our DBAs go into emergency maintenance and block incomming connections, it can take 5 to 10 minutes to timeout. This is problematic for me since I've found that OCILogin2 isn't thread safe and we can only use it serially - and I connect to quite a few Oracle DBs. 3 blocked servers X 5-10 minutes = 15 to 30 minutes of lockup time

Does anyone know how to set the OCILogon2 connection timeout?

Thanks.

Answer 1

Hi 1001010,

I'm currenty playing with OCI and it seems to me that it's impossible. The only way I can think of is to use non-blocking mode. You'll need OCIServerAttach() and OCISessionBegin() instead of OCILogon() in this case. But when I tried this, OCISessionBegin() constantly returns OCI_ERROR with the following error code:

• ORA-03123 operation would block
• Cause: The attempted operation cannot complete now.
• Action: Retry the operation later.

It looks strange and I don't yet know how to deal with it.

Possible workaround is to run your logon in another process, which you can kill after timeout...

Answer 2

We think we found the right file setting - but it's one of those problems where we have to wait until something rare and horrible occurs before we can verify it :-/

[sqlnet.ora]
SQLNET.OUTBOUND_CONNECT_TIMEOUT=60

From the Oracle docs..

http://download.oracle.com/docs/cd/B28359_01/network.111/b28317/sqlnet.htm#BIIFGFHI

5.2.35 SQLNET.OUTBOUND_ CONNECT _TIMEOUT

Purpose

Use the SQLNET.OUTBOUND_ CONNECT _TIMEOUT parameter to specify the time, in seconds, for a client to establish an Oracle Net connection to the database instance.

If an Oracle Net connection is not established in the time specified, the connect attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error.

The outbound connect timeout interval is a superset of the TCP connect timeout interval, which specifies a limit on the time taken to establish a TCP connection. Additionally, the outbound connect timeout interval includes the time taken to be connected to an Oracle instance providing the requested service.

Without this parameter, a client connection request to the database server may block for the default TCP connect timeout duration (approximately 8 minutes on Linux) when the database server host system is unreachable.

The outbound connect timeout interval is only applicable for TCP, TCP with SSL, and IPC transport connections.

Default

None

Example

SQLNET.OUTBOUND_ CONNECT _TIMEOUT=10