oracle10g连接自动断开，报ORA-03135错误

问题描述：

 oracle使用过一段时间，连接断开，报ORA-03135错误。

 

问题挖掘：

用pl/sql和sqlplus连接oracle，也存在该问题，确定该问题与连接方式无关。

查看服务器，发现没有防火墙，防火墙因素排除。

ping -t 服务器地址，发现没有丢包，都100%收到，网络通畅。

基本可以肯定问题出在oracle参数配置上。但也不排除其他因素。

 

解决过程：

 

根据ora-03135查询到oracle官方的解决方案：

ORA-03135: connection lost contact

Cause: 1) Server unexpectedly terminated or was forced to terminate. 2) Server timed out the connection.

Action: 1) Check if the server session was terminated. 2) Check if the timeout parameters are set properly in sqlnet.ora.

 

查询相关资料，发现该问题可能与sqlnet.ora设置参数SQLNET.EXPIRE_TIME 有关。因此在server上面的sqlnet.ora设置参数SQLNET.EXPIRE_TIME = 5（需在服务器监听reload一下使参数生效：lsnrctl reload），而在client不设置该参数，。等待一段时间后，没有出现该问题了，问题解决。

 

知识扩展：

在server端的sqlnet.ora文件中设置SQLNET.EXPIRE_TIME这一参数可以启用DCD功能，DCD是Dead Connection Detection的缩写，用于检查死掉但没有断开的session。

例如：
SQLNET.EXPIRE_TIME ＝ 20
可以设置为任意数值，单位分钟。

重启listener

bash-2.05$ lsnrctl reload

……
The command completed successfully

 

DCD可以用于防止防火墙的timeout。

 

当新连接建立的时候，会读取sqlnet.ora中的这一设定，当20分钟间隔到达时，对inactive的session发送”probe” SQL*NET packet来确认client是否活着。如果已经死掉，那么相对应的resource会被自动清除。
如果client和server中间有防火墙，防火墙设有timeout参数，例如设置为一个小时，当一个小时中client和server间没有数据传输的时候，防火墙会断开连接。开启DCD后，通过设置SQLNET.EXPIRE_TIME的值小于防火墙的timeout参数可以避免这一情况。 “probe” package确保防火墙不会断开idle的session。

 

 大多数情况下，这样操作就好了，要是还是不行就进行停止oracle实例和重启操作

1、启动数据库：
oracle@suse92:~> sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 20 02:29:37 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 135352820 bytes
Fixed Size 455156 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 679936 bytes
Database mounted.

 

2、关闭数据库：
oracle@suse92:~> sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 20 02:29:37 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> shutdwon abort;

 

3、启动监听器
oracle@suse92:~> lsnrctl start

 

4、停止监听器
oracle@suse92:~> lsnrctl stop

 

5、查看监听器状态
　　lsnrctl">oracle@suse92:~>lsnrctl
　　LSNRCTL> status
　　LSNRCTL> exit

 

 

 

 

 

参考资料：

sqlnet.ora http://www.orafaq.com/wiki/Sqlnet.ora

oracle DCD http://www.dbafan.com/blog/?p=174

sqlnet.expire_time and IDLE_TIME http://space.itpub.net/10687595/viewspace-420407

 

 

SQLNET.EXPIRE_TIME 的官方说明

Purpose

Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

 

Limitations on using this terminated connection detection feature are:

·         It is not allowed on bequeathed connections.

·         Though very small, a probe packet generates additional traffic that may downgrade network performance.

·         Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Default

0

Minimum Value

0

Recommended Value

10

Example

SQLNET.EXPIRE_TIME=10