Frequent disconnection between client and database [message #447226] |
Fri, 12 March 2010 09:22 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #448667 is a reply to message #448663] |
Wed, 24 March 2010 10:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ThomasG
Messages: 3212 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 #448687 is a reply to message #448685] |
Wed, 24 March 2010 12:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ThomasG
Messages: 3212 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.
|
|
|
|
|