`

sqlnet.expire_time and IDLE_TIME

 
阅读更多

当设置了resource_limit=true 。通过idle_time限制session idle 时间。session idle超过设置时间,状态为sniped (v$session).,然而OS下的process并不会释放,当session(user process) 再次与server process 通讯,将关闭相应的server process.

sqlnet.expire_time 的原理不一样,Oracle Server 发送包探测dead connection ,如果连接关闭,或者不再用,则关闭相应的server process.

以上两者组合使用,减少server process,防止process超过init$ORACLE_SID极限值。


#查找长时间不用的session.
SELECT s.username,s.status,s.machine,osuser,spid,
'kill -9 '||spid UNIX_level_kill,
'alter system kill session ' ||''''||s.sid||','||s.serial# || ''';' Oracle_level_kill,
TO_CHAR (logon_time, 'dd/mm/yyyy hh24:mi:ss') logon_time,
last_call_et idle_time,
TO_CHAR (TRUNC (last_call_et / 36000))||' '||' HRS '||TO_CHAR (TRUNC ((last_call_et - TRUNC(last_call_et / 36000* 3600/ 600)) ||' MINS' idle_time_hour_minute,
module 
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status = 'SNIPED' 
-- AND SUBSTR (machine, 1, 19) NOT IN ('machine')
AND last_call_et > 60 * 60 * 2
-- session idle time more than 1 hour
ORDER BY last_call_et desc;

##写了一个脚本,kill sniped session
##kill_sniped_session.sh
#! /bin/bash
ORACLE_SID=xxxxprod
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
SQLPATH=/apps/oracle/sql
export SQLPATH
#
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<!
@sniped_session.sql 
exit
!
if [ -s /apps/oracle/sql/kill_sniped_session.lst ] 
then
echo "have a list of sniped_session"
grep kill /apps/oracle/sql/kill_sniped_session.lst
grep kill /apps/oracle/sql/kill_sniped_session.lst | awk '{ print $3 }' | xargs kill -9 2>/backup/oracle/kill_sniped_session.log
fi
if [ $? -ne 0 ]
then
cat /backup/oracle/kill_sniped_session.log | mailx -s "xxxxprod kill sniped session failed" xx@@ss.com
else
sessions_count=`grep kill /apps/oracle/sql/kill_sniped_session.lst | wc -l`
echo "sessions:${sessions_count}" | mailx -s "xxxxprod kill sniped session successful" xx@@ss.com
touch /backup/oracle/kill_sniped_session.sh
fi


oracle@xxxxprod$ more sniped_session.sql 
rem sniped_session.sql 
rem DESCRIPTION
rem kill sniped session
rem MODIFIED
set pagesize 1000
set heads off
set verify off
set heading off 
set termout off 
set echo off
set feedback off 
spool on
spool /apps/oracle/sql/kill_sniped_session.lst
select 'kill -9 '||spid UNIX_level_kill
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status = 'SNIPED' 
AND last_call_et > 60 * 60 * 3
ORDER BY last_call_et desc;
spool off

 

##btw

What does 'SNIPED' status in v$session mean?

When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.

This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session

 

sqlnet.expire_time

sqlnet.expire_time actually works on a different principle and is used to detect dead connections as opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes. 

Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. 
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.

分享到:
评论

相关推荐

    mysql-8.0.18-1.el7.x86_64.zip

    ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #更改加密方式 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '自己的密码'; #更新用户密码 (我这里为root )

    Microsoft.UI.Xaml.2.7-7.2208.15002.0-x64

    Microsoft.UI.Xaml.2.7_7.2208.15002.0_x64__8wekyb3d8bbwe

    友盟推送DLL umeng.zip

    po.expire_time = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss:ffff"); p.policy = po; p.type = "unicast"; p.production_mode = true; p.device_tokens = "xxxxxxxxx"; PayLoad pay = new ...

    ESSP_nt64_14.2.19.0_Expire.7z

    杀毒软件直装版

    easy-time:时间转换正确

    minutesexpire_time == 5 //trueexpire_time === 5 //false+ expire_time === 5 //truetoken = jwt . sign ( user , secret , { expiresInMinutes : expire_time } )key = user . email + '_token'redis . set ( ...

    php.ini-development

    and you cannot use both "ob_gzhandler" and "zlib.output_compression". ; Note: output_handler must be empty if this is set 'On' !!!! ; Instead you must use zlib.output_handler. ; ...

    AF-Scalper.Ltd-2019.zip_Free!_LTD_free_scalper

    Scalper Free Expire

    TokenGenerator_cpp:生成令牌的代码

    token.expire_time = 0; std::string strToken = token.Build(); 欢迎提交你的token生成代码 如果您对现有代码有任何改进建议,欢迎提交pull request,我们将非常感谢。 联系我们 完整的 API 文档见

    Wrox.Press.-.Professional..Programming.pdf

    specifying the expire time 18-4. Deleting the cookie username 18-5. deleting and setting a new cookie 18-6. Identifying a visitor 21-1. addslashes() 21-2. output 21-3. output with enabled magic_...

    Beego+Vue实现毕业设计校园生活服务平台gcmiss.zip

    Beego+Vue实现毕业设计校园生活服务平台gcmiss.zip 项目介绍 毕业设计校园生活服务网站,集校园交流、失物招领、寻物启事、求人办事、二手... res , err = cache:expire("bind_"..ngx.var.remote_addr,ip_bind_time)

    php_memcache 服务扩展

    配置如下: 1、开启memcached 服务,注意路径 memcached.exe -d install...echo "Store data in the cache (data will expire in 10 seconds)\n"; echo "Data from the cache:\n"; var_dump($memcache-&gt;get("key"));

    memcache.dll(5.2/5.3/5.4/5.5)

    我找到一个能用的,现在共享给大家。 配置如下: ...echo "Store data in the cache (data will expire in 10 seconds)\n"; echo "Data from the cache:\n"; var_dump($memcache-&gt;get("key"));

    Javascript读取cookie函数代码

    expire_time.setFullYear(expire_time.getFullYear() + 1); cookie .SetCookie(“key2″,”val2”,expire_time); // 设置域及路径,带过期时间 cookie .SetCookie(“key3″,”val3″,expire_time,”.cn

    ap6212a0_a33_sc3817r_服务器验证通过_bt已经通了_wifi需要修改配置_需要再次验证_20170626_1549.7z

    persist.sys.timezone=Asia/Shanghai \ persist.sys.language=zh \ persist.sys.country=CN PRODUCT_PACKAGES += Bluetooth #PRODUCT_PROPERTY_OVERRIDES += \ # ro.product.8723b_bt.used=true #GPS Feature ...

    JavaScript操作cookie类实例

    本文实例讲述了JavaScript操作cookie类。分享给大家供大家参考。...expire_time.setFullYear(expire_time.getFullYear() + 1); cookie .SetCookie("key2","val2",expire_time); // 设置域及路径,带过期时间 cookie .S

    IntraWeb 15.0.21

    Use the menu to the left for more options or choose one of these common options: I want to buy IntraWeb, download the latest IntraWeb 14 for FREE for Delphi and C++ (including Rio 10.3), or learn ...

    Oracle事例

    sql&gt; temporary tablespace temp quota 10m on data password expire sql&gt; [account lock|unlock] [profile profilename|default]; &lt;1&gt;.查看当前用户的缺省表空间 SQL&gt;select username,default_tablespace ...

    ap6212a0_a33_sc3817r_神舟验证版本_借用nvram_ap6210这个配置文件_20170626_1834没有外层目录.7z

    persist.sys.timezone=Asia/Shanghai \ persist.sys.language=zh \ persist.sys.country=CN PRODUCT_PACKAGES += Bluetooth #PRODUCT_PROPERTY_OVERRIDES += \ # ro.product.8723b_bt.used=true #GPS Feature ...

    !!!!ap6212a0_a33_sc3817r_验证通过_修正wifi的配置文件为nvram_ap6212.txt

    persist.sys.timezone=Asia/Shanghai \ persist.sys.language=zh \ persist.sys.country=CN PRODUCT_PACKAGES += Bluetooth #PRODUCT_PROPERTY_OVERRIDES += \ # ro.product.8723b_bt.used=true #GPS Feature ...

    在Django的视图(View)外使用Session的方法

    从内部来看,每个session都只是一个普通的Django model(在 django.contrib.sessions.models 中定义)。每个session都由一个随机的32字节哈希串来标识,并存储于cookie中。 因为它是一个标准的...&gt;&gt;&gt; s.expire_date da

Global site tag (gtag.js) - Google Analytics