解决连接被防火墙强制断开，引起PL/SQL 和 Navicat Premium 客户端频繁连接卡机等一下问题
本地IDE Console控制台报错：java.net.SocketException: Connection reset by peer: socket write error 浏览器请求返回：Status Code:504 Gateway Time-outX-Swift-Error:orig response 5xx errorX-Swift-Error:forward...
本地IDE Console控制台报错：
java.net.SocketException: Connection reset by peer: socket write error
浏览器请求返回：
Status Code:504 Gateway Time-out
X-Swift-Error:orig response 5xx error
X-Swift-Error:forward peer connect close
PL/SQL 和 Navicat Premium 客户端返回：
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 18-APR-2014 11:05:46
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
如果应该经常出现这样的错误，主要问题可能出现在内网的防火墙设置，如果应用保持连接而长时间没有操作，一些网络设备就会断开连接，解决方法就是通过设置服务端的sqlnet.ora文件的sqlnet.expire_time参数，来主动向客户端发送检测请求，如果客户端还活着，则不做操 作，如果检测发现客户端的连接已经不存在或没有反映，则回收这个session的资源。这样，如果DCD的检测时间小于防火墙设置的空闲连接 最大存活时间，那么由于DCD检测客户端存活性需要从服务端发送一个空包到客户端，防火墙就会重新计算这个连接的空闲时间，就不会中断这个会话了。设置DCD需要在服务端的sqlnet.ora文件中添加以下信息：
---------------------
作者：风流搞技术
来源：CSDN
原文：https://blog.csdn.net/qq_34556414/article/details/81330604
版权声明：本文为博主原创文章，转载请附上博文链接！
F:\app\yzb\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora :
# sqlnet.ora Network Configuration File: F:\app\yzb\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
sqlnet.expire_time=1 //增加这个配置，单位为分钟
通过设定参数为非零值(分钟)来发送探测包以检查客户端的异常断开。一旦探测包找到了异常的连接将返回错误，清除对应的server process
下面是参数使用的一些限制。(缺省值为0，最小值0，建议值10。SQLNET.EXPIRE_TIME=10)
参考文章：
Oracle 监听SQLNET.EXPIRE_TIME
2018年08月01日 14:55:59 风流搞技术 阅读数：471更多
个人分类： Oracle 监听
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/qq_34556414/article/details/81330604
在这边数据库加固有如下一个加固项，使用SQLNET.EXPIRE_TIME可以来断开在session里面超时的状态为inactive的连接。
检查是否设置超时时间
注意事项及影响：
- 作用：非活动会话超过10分钟，连接断开
- 该项需要与业务侧确认是否可以操作
- 对于11g （如果有grid）只加固ORACLE用户下的sqlnet.ora ，如果没有则创建
- 该项加固后，可能在alert 文件中存在ORA-07445: exception encountered: core dump [snstimsane()+43] 报错，(文档 ID 3934729.8)，影响版本如下
| 序号 | 操作内容 | 操作步骤 | 责任人 | 时间 |
| 1 | 登陆主机 | su - oracle |
|
|
| 2 | 检查监听和数据库状态 | lsnrctl status sqlplus ‘/as sysdba’ select open_mode from v$database; |
|
|
| 3 | 进入oracle_home | cd $ORACLE_HOME/network/admin |
|
|
| 4 | 备份sqlnet.ora | cp sqlnet.ora sqlnet.ora_bak |
|
|
| 5 | 编辑sqlnet.ora | 增加下面的内容 SQLNET.EXPIRE_TIME = 10 |
|
|
| 6 | 检查数据库状态 | Select open_mode from v$database; |
|
|
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
目的
使用参数SQLNET.EXPIRE_TIME指定发送探针以验证客户端/服务器连接是否处于活动状态的时间间隔（以分钟为单位）。设置大于0的值可确保由于客户端终止异常，连接无法无限期保持打开状态。如果探测发现终止连接或不再使用的连接，则会返回错误，导致服务器进程退出。此参数主要用于数据库服务器，该服务器通常一次处理多个连接。
使用此终止连接检测功能的限制是：
遗留连接不允许这样做。
虽然非常小，但探测数据包会产生额外的流量，可能会降低网络性能。
根据正在使用的操作系统，服务器可能需要执行额外的处理以将连接探测事件与发生的其他事件区分开。这也可能导致网络性能下降。
默认
0
最低值
0
推荐值
10
例
SQLNET.EXPIRE_TIME = 10
DCD: Dead Connection Detection ，可以用于检测、标记僵死而没有断开会session，再由PMON进行清理，释放资源。开启DCD，只需要在服务端的sqlnet.ora文件中SQLNET.EXPIRE_TIME参数，单位为分钟。
如果时间达到这个值，server端就是发出一个”probe” packet 给客户端，如要客户断是正常的，这个packet就被忽略，timer重新计时；如果客户端异常中断，则server端就会收到一个消息，用以释放连接。
SQLNET.EXPIRE_TIME设置客户端连接会话超时时间（单位分钟）
定期检测客户端是否还是活动的，设置为0不检测
SQLNET.EXPIRE_TIME = 10
这里是别人博客的一个案例
关于sqlnet.expire_time.txt
***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxx.com)(CID=(PROGRAM=oracle)(HOST=xxx)(USER=oracle11g))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
Time: 18-APR-2014 11:05:46
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0
如果应该经常出现这样的错误，主要问题可能出现在内网的防火墙设置，如果应用保持连接而长时间没有操作，一些网络设备就会断开连接，解决方法就是通过设置服务端的sqlnet.ora文件的sqlnet.expire_time参数，来主动向客户端发送检测请求，如果客户端还活着，则不做操 作，如果检测发现客户端的连接已经不存在或没有反映，则回收这个session的资源。这样，如果DCD的检测时间小于防火墙设置的空闲连接 最大存活时间，那么由于DCD检测客户端存活性需要从服务端发送一个空包到客户端，防火墙就会重新计算这个连接的空闲时间，就不会中断这个会话了。设置DCD需要在服务端的sqlnet.ora文件中添加以下信息：
sqlnet.expire_time = 5
这个值的单位是分钟，这里设置的是每五分钟服务端会向已连接数据库的session所在的客户端发送一个空包，来检测客户端的存活性， 如果防火墙限制的空闲连接时间大于5分钟，那么连接到数据库的会话就不会因为大于5分钟的空闲时间而被中断。这种方案完全可以解决这个问题，但这种方法需要重新注册监听。
--简单做一个测试：
1.修改 sqlnet.ora文件，加入:
SQLNET.EXPIRE_TIME=1
重启监听。
2.远端打开连接数据库，不做任何操作。
3.在服务端执行如下命令：
# tcpdump -i eth0 -nnn host 192.168.xxx.xxx and port 1521
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
11:13:11.436128 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 2119621137:2119621147(10) ack 2682317414 win 16060
11:13:11.636552 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 10 win 63492
11:14:11.437488 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 10:20(10) ack 1 win 16060
11:14:11.637790 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 20 win 63482
11:15:11.437914 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 20:30(10) ack 1 win 16060
11:15:11.637900 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 30 win 63472
11:16:11.438691 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 30:40(10) ack 1 win 16060
11:16:11.637143 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 40 win 63462
11:17:11.439824 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 40:50(10) ack 1 win 16060
11:17:11.639376 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 50 win 63452
11:18:11.441028 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 50:60(10) ack 1 win 16060
11:18:11.640484 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 60 win 63442
11:19:11.441949 IP 192.168.yyy.yyy.1521 > 192.168.xxx.xxx.64247: P 60:70(10) ack 1 win 16060
11:19:11.641719 IP 192.168.xxx.xxx.64247 > 192.168.yyy.yyy.1521: . ack 70 win 63432
--可以看到每隔1分钟，服务端向客户端发起连接，检测客户端是否存在。
下面再整合一个案例
DCD: Dead Connection Detection ，可以用于检测、标记僵死而没有断开会session，再由PMON进行清理，释放资源。开启DCD，只需要在服务端的sqlnet.ora文件中添加SQLNET.EXPIRE_TIME参数，单位为分钟。 如果时间达到这个值，server端就是发出一个”probe” packet 给客户端，如要客户断是正常的，这个packet就被忽略，timer重新计时；如果客户端异常中断，则server端就会收到一个消息，用以释放连接。
DCD还可以用于防止防火墙的timeout，例如：
某个系统RMAN备份，在结束时，这里是结束了，服务端和客户端不存在数据的交互，报如下信息：
released channel: dev_0
released channel: dev_1
released channel: dev_2
released channel: dev_3
released channel: dev_4
released channel: dev_5
released channel: dev_6
released channel: dev_7
released channel: dev_8
released channel: dev_9
released channel: dev_10
released channel: dev_11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database:
ORA-03135: connection lost contact
ORACLE error from recovery catalog database:
ORA-03114: not connected to ORACLE
Is the rman catalog Database running on a different system than the Target Database?
If so, verify with the System Administrators what the TCP/IP timeout is set to.
If there is any firewall between two systems, set the value of SQLNET.EXPIRE_TIME to less than the TCP timeout value of the firewall.
Increase the value of SQLNET.EXPIRE_TIME in the sqlnet.ora file on both target servers if no firewall exists between two systems.
Increase the value of keepalive interval.
以上这个RMAN报错例子就是由于防火墙设置timeout原因，当client和server在timeout时间内没有数据传输的时候，会话就会被防火墙断开。而设置SQLNET.EXPIRE_TIME参数，使其小于防火墙的timeout时间，就可以避免这一情况的发生
——————————————————————————————
ORA-03135: connection lost contact.
——————————————————————————————
某B/S架构的应用程序在测试过程中每隔1到2小时出现“错误信息：ORA-03135: 连接失去联系的报错”，详细报错信息如下：
ORA-03135出现的原因较多，问题有可能出在网络设备、操作系统、数据库上，问题最有可能是由于网络闪段和防火墙配置所导致。
解决：经与网工确认，当前防火前未开启长连接设置，开启后问题解决。
补充一下长连接和短连接的概念：
①长连接的概念
长连接功能用于设置特定数据流的超长保持时间，让数据流的会话连接保持时间不受全局老化时间限制。其实这项特殊业务与目前业界的状态防火墙的实现机制是存在矛盾的。
为保证内部网络的安全，防火墙上的各会话缺省保持时间都相对较短，例如：缺省情况下，TCP的保持时间为1200s，UDP的保持时间为120s。
正常情况下，当一个TCP会话的两个连续报文到达防火墙的时间间隔大于该会话的保持时间时，为保证网络的安全性，防火墙将从会话表中删除相应会话信息。后续报文到达防火墙后，防火墙根据自身的转发机制，丢弃该报文，导致连接中断。在实际应用中，用户需要查询服务器上的数据，这些查询时间间隔远大于TCP/UDP默认的会话保持时间。此时需要在防火墙上保持TCP连接一段相对较长的时间。当某会话的报文长时间没有到达防火墙后再次到达时，仍然能够通过防火墙，这种技术就是长连接。
②短连接的概念
某些应用频繁发起连接，如果不缩短其会话保持时间，则会使防火墙的会话数爆涨，进而拖垮防火墙。保持太多的会话对防火墙没有必要，相反，当系统资源过多地用在会话保持的话，会相应损害每秒生成会话的能力，这是一个同样重要的性能指标。设定过高的会话数量，却降低了每秒生成会话的能力，其结果，只能是保留一些永远用不到的会话虚数而已。
因此，我们可以根据网络应用环境的实际需求，缩短某些会话的保持时间，从而减少防火墙的工作负荷，提高网络性能。
https://blog.csdn.net/qq_34556414/article/details/81330604
使用 SQLNET.EXPIRE_TIME 清除僵死连接
数据库连接的客户端异常断开后，其占有的相应并没有被释放，如从v$session视图中依旧可以看到对应的session处于inactive，且对应的服务器进程也没有释放，导致资源长时间地被占用，对于这种情形开该如何处理呢？SQLNET.EXPIRE_TIME对于这个问题我们提供了解决方案，专门用于清理那些异常断开的情形，如网络异常中断，客户端异常掉电，异常重启等。本文描述了设置SQLNET.EXPIRE_TIME参数以及演示死连接以及资源被释放的情形。
1、理解SQLNET.EXPIRE_TIME参数
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.
通过设定参数为非零值(分钟)来发送探测包以检查客户端的异常断开。一旦探测包找到了异常的连接将返回错误，清除对应的server process
下面是参数使用的一些限制。(缺省值为0，最小值0，建议值10。SQLNET.EXPIRE_TIME=10)
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.
2、Dead Connection Detection (DCD)与Inactive Sessions
Dead connections:
These are previously valid connections with the database but the connection between the client and server processes has
terminated abnormally.
Examples of a dead connection:
- A user reboots/turns-off their machine without logging off or disconnecting from the database.
- A network problem prevents communication between the client and the server.
In these cases, the shadow process running on the server and the session in the database may not terminate.
Implemented by
* adding SQLNET.EXPIRE_TIME = <MINUTES> to the sqlnet.ora file
With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration of
the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter.
If the client side connection is still connected and responsive, the client sends a response packet back to the database
server, resetting the timer..and another packet will be sent when next interval expires (assuming no other activity on
the connection).
If the client fails to respond to the DCD probe packet
* the Server side process is marked as a dead connection and
* PMON performs the clean up of the database processes / resources
* The client OS processes are terminated
NOTE: SQLNET.RECV_TIMEOUT can be set on the SERVER side sqlnet.ora file. This will set a timeout for the server process
to wait for data from the client process.
Inactive Sessions:
These are sessions that remain connected to the database with a status in v$session of INACTIVE.
Example of an INACTIVE session:
- A user starts a program/session, then leaves it running and idle for an extended period of time.
3、配置SQLNET.EXPIRE_TIME
#对于SQLNET.EXPIRE_TIME的配置，需要修改sqlnet.ora，然后添加SQLNET.EXPIRE_TIME项 [oracle@orasrv admin]$ more sqlnet.ora sqlnet.expire_time = 1 #仅仅需要配置此项，后面的各项仅仅是为了生成跟踪日志，可省略 TRACE_LEVEL_SERVER = 16 TRACE_FILE_SERVER = SERVER TRACE_DIRECTORY_SERVER= /u01/app/oracle/network/trace TRACE_TIMESTAMP_ SERVER = ON TRACE_UNIQUE_SERVER = ON DIAG_ADR_ENABLED=OFF
4、模拟及测试DCD连接
C:\Users\robinson.cheng>sqlplus scott/tiger@ora11g --->从windows客户端发起连接
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 25 09:57:59 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
---Issued the sql to hold a lock
SQL> update emp set sal=sal*1.1 where deptno=20;
5 rows updated.
--disabled the network adapter in VM setting
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29522
Session ID: 15 Serial number: 447
--服务器端环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--在服务器端查看session的情况，SCOTT用户的session状态为INACTIVE
SQL> @comm_sess_users;
+----------------------------------------------------+
| User Sessions (All) |
+----------------------------------------------------+
Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine
--------- ------ --------- --------- ----------- ------------ ------- -------------------------- ---------- -------------
ora11g 15 447 INACTIVE SCOTT Robinson.Che 29522 sqlplus.exe PC39 TRADESZ\PC39
125 5 INACTIVE SYS oracle 4734 sqlplus@orasrv.com (TNS V1 pts/0 orasrv.com
139 9 ACTIVE SYS oracle 29447 sqlplus@orasrv.com (TNS V1 pts/4 orasrv.com
--Get the spid for user scott by SID
SQL> @my_spid_from_sid
Enter value for input_sid: 15
old 4: AND s.sid = &input_sid
new 4: AND s.sid = 15
SID SERIAL# SPID
------ ---------- ------------------------
15 447 29522
--To find the locked object
SQL> @lock_obj
OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL#
------------------------------------------------------------------
EMP 3 14 15 447
EMP 3 83 15 447
--The trace file exists
SQL> ho ls -hltr /u01/app/oracle/network/trace/s*29522*
-rw-r----- 1 oracle oinstall 241K Jun 25 09:59 /u01/app/oracle/network/trace/server_29522.trc
--->try to issue another sql. the sql is blocked
SQL> set time on;
10:03:46 SQL> delete scott.emp where deptno=20;
delete scott.emp where deptno=20
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
--Check the server process for scott
10:04:37 SQL> ho ps -ef | grep 29522 | grep -v grep
oracle 29522 1 0 09:58 ? 00:00:00 oracleora11g (LOCAL=NO)
--Could not reach to client from server.
10:06:51 SQL> ho ping 192.168.7.133
PING 192.168.7.133 (192.168.7.133) 56(84) bytes of data.
From 192.168.7.40 icmp_seq=2 Destination Host Unreachable
From 192.168.7.40 icmp_seq=3 Destination Host Unreachable
From 192.168.7.40 icmp_seq=4 Destination Host Unreachable
From 192.168.7.40 icmp_seq=6 Destination Host Unreachable
From 192.168.7.40 icmp_seq=7 Destination Host Unreachable
From 192.168.7.40 icmp_seq=8 Destination Host Unreachable
--此时总进程数为27个
10:15:08 SQL> select count(*) from v$process;
COUNT(*)
----------
27
--从09:58进程启动开始到10:17:59进程依旧没有被释放
10:17:59 SQL> ho ps -ef | grep 29522 | grep -v grep
oracle 29522 1 0 09:58 ? 00:00:00 oracleora11g (LOCAL=NO)
-->At this time the server process was released
10:18:08 SQL> ho ps -ef | grep 29522 | grep -v grep
--进程释放后此时进程总数变为26个
10:19:45 SQL> select count(*) from v$process;
COUNT(*)
----------
26
-->the lock was released
10:19:54 SQL> @lock_obj
no rows selected
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612
--scott用户的session已经从v$session中被移除
10:20:03 SQL> @comm_sess_users;
+----------------------------------------------------+
| User Sessions (All) |
+----------------------------------------------------+
Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine
--------- ------ --------- --------- -------------- ------------ ------- -------------------------- -------- ----------
ora11g 125 5 INACTIVE SYS oracle 4734 sqlplus@orasrv.com (TNS V1 pts/0 orasrv.com
139 9 ACTIVE SYS oracle 29447 sqlplus@orasrv.com (TNS V1 pts/4 orasrv.com
5、查看SQLNET.EXPIRE_TIME是否启用
#下面对跟踪日志过滤，可以看到09:58:02:853中提示开启dead connection detection
[oracle@orasrv trace]$ cat -n server_29522.trc |grep dead
78 [25-JUN-2013 09:58:02:853] niotns: Enabling dead connection detection (1 min)
#下面的查询中，在09:58:03 timer被启动，10:18:26后，连接被彻底关闭(包括server process)
[oracle@orasrv trace]$ cat -n server_29522.trc |grep timer
447 [25-JUN-2013 09:58:03:050] nstimstart: starting timer at 25-JUN-2013 09:58:03
451 [25-JUN-2013 09:58:03:051] nsconbrok: timer created for connection
4092 [25-JUN-2013 10:18:26:173] nstimarmed: timer is armed, with value 3833
#下面是starting timer的详细信息
[oracle@orasrv trace]$ head -451 server_29522.trc | tail -5
[25-JUN-2013 09:58:03:050] nstimstart: starting timer at 25-JUN-2013 09:58:03
[25-JUN-2013 09:58:03:051] nstimset: entry
[25-JUN-2013 09:58:03:051] nstimset: normal exit
[25-JUN-2013 09:58:03:051] nstimstart: normal exit
[25-JUN-2013 09:58:03:051] nsconbrok: timer created for connection
#下面是timer被清除后的详细信息nstimclear: normal exit
[oracle@orasrv trace]$ head -4097 server_29522.trc | tail -7
[25-JUN-2013 10:18:26:173] nstimarmed: entry
[25-JUN-2013 10:18:26:173] nstimarmed: timer is armed, with value 3833
[25-JUN-2013 10:18:26:173] nstimarmed: normal exit
[25-JUN-2013 10:18:26:173] nstimclear: entry
[25-JUN-2013 10:18:26:173] nstimclear: normal exit
[25-JUN-2013 10:18:26:173] nttctl: entry
[25-JUN-2013 10:18:26:173] nttctl: entry
6、小结
a、DCD连接通常指用户没有正常断开连接而重启客户端，关机以及网络问题导致客户端无法与服务器正常通信所致的连接
b、相对于DCD连接，INACTIVE session则是用户建立连接之后，尚未执行任何操作或操作已经完成但没有断开，等同于与处于idle状态
c、无论是DCD连接，还是出于idle状态的INACTIVE session，在v$session视图呈现的都是INACTIVE状态
d、对于使用resource_limit及profile配置后用户session超出idle_time的情形，在v$session视图呈现sniped状态
e、当在sqlnet.ora配置文件中设置了SQLNET.EXPIRE_TIME参数为非零值时，僵死连接在EXPIRE_TIME指定的时间后被清除
f、演示中仅仅设定EXPIRE_TIME为1分钟，而实际的释放时间接近20分钟左右，什么原因尚不清楚，有待进一步测试
g、设定SQLNET.EXPIRE_TIME为非零值之后，系统需要产生而外的开销以及带来网络性能的下降
h、对于需要及时释放OS及DB资源的情形，Oracle建议使用resource_limit 及 profile 限制用户连接的同时并设定SQLNET.EXPIRE_TIME为非零值
i、Reference: [ID 206007.1] [ID 395505.1] [ID 601605.1] [ID 151972.1]
https://www.cnblogs.com/dyllove98/archive/2013/06/27/3159734.html
https://blog.csdn.net/leshami/article/details/9188379
权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)