Home » RDBMS Server » Networking and Gateways » Frequent disconnection between client and database (Oracle 10g 10.2.0.3 on linux 64 bit)
Frequent disconnection between client and database [message #447226] Fri, 12 March 2010 09:22 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
We are using Oracle 10g on linux 64 bit, client is on XP, using Oracle sql developer to connect to database through vpn, we have only allowed access to 1521 port of database

Client is frequently disconnecting

Initially client connected, then start disconnecting after 20 minutes.

Client side sqlnet.ora has following entry
SQLNET.AUTHENTICATION_SERVICES = (NTS)

Client side Tnsnames.ora has following entry

dd12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.51)(PORT = 1521))
)
(CONNECT_DATA =
(Sid = dd12)
)
)

Following is the entry in sqlnet.ora in Database server

NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.INBOUND_CONNECT_TIMEOUT=0
sqlnet.expire_time = 10


Following is the entry in tnsnames.ora file of database server

# tnsnames.ora Network Configuration File: /home/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

dd12=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ph1.abc.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = dd12)
)
)


dd13=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ph2.abc.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dd13)
)
)


EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



Following entry in Listener.ora

# listener.ora Network Configuration File: /home/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ph1.abc.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
INBOUND_CONNECT_TIMEOUT_LISTENER = 0

lsnrctl status command is giving following

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= ph1.abc.com)(PORT=1521)))
STATUS of the LISTENER



--------------------------------------------------------------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date 07-MAR-2010 20:53:47
Uptime 4 days 15 hr. 38 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= ph1.abc.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dd12" has 1 instance(s).
Instance "dd12", status READY, has 1 handler(s) for this service...
Service "dd12XDB" has 1 instance(s).
Instance "dd12", status READY, has 1 handler(s) for this service...
Service "dd12_XPT" has 1 instance(s).
Instance "dd12", status READY, has 1 handler(s) for this service...
The command completed successfully

No error in listener.log in database.

Receive following error in sqlnet.log in database

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Linux: Version 10.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.3.0 - Production
Time: 11-MAR-2010 16:25:16
Tracing not turned on.
Tns error struct:
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: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.14.0.31)(PORT=3934))


Listener is working fine and it is up
Re: Frequent disconnection between client and database [message #448663 is a reply to message #447226] Wed, 24 March 2010 09:59 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Is there any one who can help
Re: Frequent disconnection between client and database [message #448667 is a reply to message #448663] Wed, 24 March 2010 10:09 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have you checked all the standard actions for the errors.

oracle ~ $ oerr TNS 12170
12170, 00000, "TNS:Connect timeout occurred"
// *Cause:  The server shut down because connection establishment or
// communication with a client failed to complete within the allotted time
// interval. This may be a result of network or system delays; or this may
// indicate that a malicious client is trying to cause a Denial of Service
// attack on the server.
// *Action: If the error occurred because of a slow network or system,
// reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,
// SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.
// If a malicious client is suspected, use the address in sqlnet.log to
// identify the source and restrict access. Note that logged addresses may
// not be reliable as they can be forged (e.g. in TCP/IP).

oracle ~ $ oerr TNS 12535
12535, 00000, "TNS:operation timed out"
// *Cause: The requested operation could not be completed within the time out
// period.
// *Action: Look at the documentation on the secondary errors for possible
// remedy. See SQLNET.LOG to find secondary error if not provided explicitly.
// Turn on tracing to gather more information.

oracle ~ $ oerr TNS 12560
12560, 00000, "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors. For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.

oracle ~ $ oerr TNS 00505
00505, 00000, "Operation timed out"
// *Cause: The requested operation could not be completed within the timeout
// period.
// *Action: For further details, turn on tracing and reexecute the operation.

oracle ~ $ oerr TNS 110
00110, 00000, "Could not initialize network from file TNSNET.ORA"
// *Cause: Poorly formed network information in network file.
// *Action: Fix network configuration data to assure that at least one
// Interchange is defined in TNSNET.ORA.
Re: Frequent disconnection between client and database [message #448675 is a reply to message #448667] Wed, 24 March 2010 10:42 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
I have set parameter SQLNET.INBOUND_CONNECT_TIMEOUT=0 and sqlnet.expire_time = 10 in sqlnet.ora on server side
and INBOUND_CONNECT_TIMEOUT_LISTENER=0 in listener.ora

[Updated on: Wed, 24 March 2010 10:43]

Report message to a moderator

Re: Frequent disconnection between client and database [message #448682 is a reply to message #447226] Wed, 24 March 2010 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 21950
Registered: January 2009
Senior Member
>Client is frequently disconnecting
>Initially client connected, then start disconnecting after 20 minutes.

Problem & solution are external to Oracle.
You need to work with local Network Administrator to resolve this.
Re: Frequent disconnection between client and database [message #448683 is a reply to message #448682] Wed, 24 March 2010 12:18 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
How do i convince them that it is not Oracle. What should be the correct way
Re: Frequent disconnection between client and database [message #448685 is a reply to message #448683] Wed, 24 March 2010 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 21950
Registered: January 2009
Senior Member
As a test start a SQL*NET connection from the DB Server to the DB Server bypassing the VPN.

If this connection does not error out & stays up, it points to the VPN at least being involved with errors.

Besides nobody else has these problems; only your environment.

You can't fix network errors by tweaking Oracle.
Re: Frequent disconnection between client and database [message #448687 is a reply to message #448685] Wed, 24 March 2010 12:42 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, a laggy network *could* be compensated up to a point by increasing SQLNET.SEND_TIMEOUT and/or SQLNET.RECV_TIMEOUT.

But when the VPN connection is lost and then re-connected then oracle would have to re-connect, too.

You could perhaps convince them by running a PING in another window, and then have a look if the ping is also lost when the Oracle connection is lost.
Re: Frequent disconnection between client and database [message #448896 is a reply to message #448687] Thu, 25 March 2010 09:48 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
They are connected with test database, and not disconnected in the same vpn.
Re: Frequent disconnection between client and database [message #467601 is a reply to message #447226] Mon, 26 July 2010 03:39 Go to previous message
hanifikr
Messages: 1
Registered: December 2006
Junior Member
May I know has this issue been resolved, and how?
Previous Topic: delay in oracle
Next Topic: Re: listener could not find available handler for requested type of server
Goto Forum:
  


Current Time: Sun Apr 20 09:04:46 CDT 2014

Total time taken to generate the page: 0.03889 seconds