加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

Oracle连接数过多释放机制

发布时间:2020-12-12 14:11:27 所属栏目:百科 来源:网络整理
导读:一、设置回话和连接时间 Oracle服务器连接数过多会当掉,把连接数过多的客户机网线拔出后,在远程Oracle上依然还会保留此用户的连接数,久久不能释放,上网查了下可以以下面方法解决。 通过profile可以对用户会话进行一定的限制,比如IDLE时间。 将IDLE超过

一、设置回话和连接时间

Oracle服务器连接数过多会当掉,把连接数过多的客户机网线拔出后,在远程Oracle上依然还会保留此用户的连接数,久久不能释放,上网查了下可以以下面方法解决。

通过profile可以对用户会话进行一定的限制,比如IDLE时间。


将IDLE超过一定时间的会话断开,可以减少数据库端的会话数量,减少资源耗用。
使用这些资源限制特性,需要设置resource_limit为TRUE:

  [oracle@test126 udump]$ sqlplus "/ as sysdba"

  SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 07:58:21 2006

  Copyright (c) 1982,2005,Oracle. All rights reserved.

  Connected to:

  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

  With the Partitioning and Data Mining options

  SQL> show parameter resource

  NAME TYPE VALUE

  ------------------------------------ ----------- ------------------------------

  resource_limit boolean TRUE

  resource_manager_plan string

  该参数可以动态修改:

  SQL> alter system set resource_limit=true;

  System altered.

  数据库缺省的PROFILE设置为:

  SQL> SELECT * FROM DBA_PROFILES;

  PROFILE RESOURCE_NAME RESOURCE LIMIT

  -------------------- -------------------------------- -------- ---------------

  DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

  DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

  DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

  DEFAULT CPU_PER_CALL KERNEL UNLIMITED

  DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

  DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

  DEFAULT IDLE_TIME KERNEL UNLIMITED

  DEFAULT CONNECT_TIME KERNEL UNLIMITED

  DEFAULT PRIVATE_SGA KERNEL UNLIMITED

  DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

  DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

  PROFILE RESOURCE_NAME RESOURCE LIMIT

  -------------------- -------------------------------- -------- ---------------

  DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

  DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

  DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

  DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED

  DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

  16 rows selected.

  创建一个允许3分钟IDLE时间的PROFILE:

  SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;

  Profile created.
新创建PROFILE的内容:

  SQL> col limit for a10

  SQL> select * from dba_profiles where profile='KILLIDLE';

  PROFILE RESOURCE_NAME RESOURCE LIMIT

  ------------------------------ -------------------------------- -------- ----------

  KILLIDLE COMPOSITE_LIMIT KERNEL DEFAULT

  KILLIDLE SESSIONS_PER_USER KERNEL DEFAULT

  KILLIDLE CPU_PER_SESSION KERNEL DEFAULT

  KILLIDLE CPU_PER_CALL KERNEL DEFAULT

  KILLIDLE LOGICAL_READS_PER_SESSION KERNEL DEFAULT

  KILLIDLE LOGICAL_READS_PER_CALL KERNEL DEFAULT

  KILLIDLE IDLE_TIME KERNEL 3

  KILLIDLE CONNECT_TIME KERNEL DEFAULT

  KILLIDLE PRIVATE_SGA KERNEL DEFAULT

  KILLIDLE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT

  KILLIDLE PASSWORD_LIFE_TIME PASSWORD DEFAULT

  PROFILE RESOURCE_NAME RESOURCE LIMIT

  ------------------------------ -------------------------------- -------- ----------

  KILLIDLE PASSWORD_REUSE_TIME PASSWORD DEFAULT

  KILLIDLE PASSWORD_REUSE_MAX PASSWORD DEFAULT

  KILLIDLE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

  KILLIDLE PASSWORD_LOCK_TIME PASSWORD DEFAULT

  KILLIDLE PASSWORD_GRACE_TIME PASSWORD DEFAULT

  16 rows selected.

  测试用户:

  SQL> select username,profile from dba_users where username='EYGLE';

  USERNAME PROFILE

  ------------------------------ --------------------

  EYGLE DEFAULT

  修改eygle用户的PROFILE使用新建的PROFILE:

  SQL> alter user eygle profile killidle;

  User altered.

  SQL> select username,profile from dba_users where username='EYGLE';

  USERNAME PROFILE

  ------------------------------ --------------------

  EYGLE KILLIDLE

  进行连接测试:

  [oracle@test126 admin]$ sqlpluseygle/eygle@eygle

  SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 08:07:13 2006

  Copyright (c) 1982,Oracle. All rights reserved.

  Connected to:

  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

  With the Partitioning and Data Mining options

  SQL> select username,profile from dba_users where username='EYGLE';

  USERNAME PROFILE

  ------------------------------ ------------------------------

  EYGLE KILLIDLE

  当IDLE超过限制时间时,连接会被断开:

  SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  TO_CHAR(SYSDATE,'YY

  -------------------

  2006-10-13 08:08:41

  SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

  *

  ERROR at line 1:

  ORA-02396: exceeded maximum idle time,please connect again

二、创建新的profile

sqlplus /nolog
打开sqlplus

conn sys/orcl@orcl as sysdba
使用具有dba权限得用户登陆oracle

show parameter resource_limit
显示资源限定是否开启,value为true是开启,为false是关闭

alter system set resource_limit=true
如果未开启,则使用此命令开启资源限定功能


create profile profileName limit connect_time 60 idle_time 30
创建profile文件,profileName任意起,connect_time设置连接超过多少分钟后强制释放,idle_time设置连续不活动的会话超过多少分钟后强制释放

alter user oracleUser profile profileName
将profile文件作用于指定用户

Oracle session连接数和inactive的问题记录oracle学习 2009-03-10 15:42:37 阅读317 评论0 字号:大中小 订阅 .

从上周起,服务器Oracle数据库出现问题,用不到半天,就会报maxsession(150)的问题,肯定是数据库的会话超过最大数了。

由于服务器跑的是文件传输应用,占用的请求和会话肯定很大,因此用户数不大就已经让oracle的会话数达到最大值。

处理方式不外乎两种:扩大oracle最大session数以及清除inactive会话,当然还有,就是从数据库连接池和程序bug上面下手。

从各处收集了一些查看当前会话的语句,记录一下:

1.select count(*) from v$session;

select count(*) from v$process;

查看当前总会话数和进程数,这两个视图就是跟会话及进程有关的重要视图啦,信息都是从这里面取的。

2.查询那些应用的连接数此时是多少

select b.MACHINE,b.PROGRAM,count(*) from v$process a,v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE,b.PROGRAM order by count(*) desc;

3.查询是否有死锁

select * from v$locked_object;

如果查询结果为no rows selected,说明数据库中没有死锁。否则说明数据库中存在死锁。

接下来说明一下会话的状态:

1.active 处于此状态的会话,表示正在执行,处于活动状态。

2.killed 处于此状态的会话,表示出现了错误,正在回滚,当然,也是占用系统资源的。还有一点就是,killed的状态一般会持续较长时间,而且用windows下的工具pl/sql developer来kill掉,是不管用的,要用命令:alter system kill session 'sid,serial#' ;

3.inactive 处于此状态的会话表示不是正在执行的,比如select语句已经完成。我一开始以为,只要是inactive状态的会话,就是该杀,为什么不释放呢。其实,inactive对数据库本身没有什么影响,但是如果程序没有及时commit,那么就会造成占用过多会话。解决inactive的方法最好的就是在oracle中直接设置超时时间,也是有两种方法,区别暂时还不清楚:

1.修改sqlnet.ora文件,新增expire_time=x(单位是分钟)

我的sqlnet.ora位置在D:/oracle/ora92/network/admin

2.通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10; 命令修改,记得重启下oracle。

三、修改ORACLE 中的SESSION和PROCESS

会话sessions和进程pocesses的关系
一个process可以有0个、1个或者多个session,一个session也可以存在若干个process中,并行同样是一个session对应一个process,主session是coordinator session,每个parallel process同样会对应数据库里一个单独的session。可以从v$px_session和v$session中验证这点。
连接connects,会话sessions和进程pocesses的关系

每个sql login称为一个连接(connection),而每个连接,可以产生一个或多个会话,如果数据库运行在专用服务器方式,一个会话对应一个服务器进程(process),如果数据库运行在共享服务器方式,一个服务器进程可以为多个会话服务。

Oracle的sessions和processes的数量关系是:sessions=1.1 * processes + 5

下面我们用两种方法修改PROCESS的最大值
一、通过Oracle Enterprise Manager Console在图形化管理器中修改
以系统管理员的身份登入,进入界面 数据库的例程 - 配置 - 一般信息 - 所有初始化参数,修改processes的值

二、在SQLPLUS中修改
以DBA权限登录,修改PROCESS的值(SESSION的值会跟着改);创建pfile;重新启动数据库。输入的SQL命令如下,回显信息省略了
SQL> connect sys/sys as sysdba
SQL> alter system set processes=400 scope = spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup

四、kill session的方式来终止一个进程

我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:

alter system kill session 'sid,SimSun;font-size:16px;"> 被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.

我们发现当一个session被kill掉以后,该session的paddr被修改,如果有多个session被kill,那么多个session
的paddr都被更改为相同的进程地址:

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542B70E8 EYGLE INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE


SQL> alter system kill session '11,314';

System altered.

SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E5044 18 662 542B6D38 SYS ACTIVE


SQL> select saddr,SimSun;font-size:16px;"> SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E2AA4 14 397 542B7498 EQSP INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE

SQL> alter system kill session '14,397';

SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E2AA4 14 397 542D6BD4 EQSP KILLED
542E5044 18 662 542B6D38 SYS ACTIVE


在这种情况下,很多时候,资源是无法释放的,我们需要查询spid,在操作系统级来kill这些进程.

但是由于此时v$session.paddr已经改变,我们无法通过v$session和v$process关联来获得spid

那还可以怎么办呢?

我们来看一下下面的查询:

SQL> SELECT s.username,s.status,
2 x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
3 decode(bitand (x.ksuprflg,2),null,1)
4 FROM x$ksupr x,v$session s
5 WHERE s.paddr(+)=x.addr
6 and bitand(ksspaflg,1)!=0;


USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -
542B44A8 0 0 0
ACTIVE 542B4858 1 14 24069 0 1
ACTIVE 542B4C08 26 16 15901 0 1
ACTIVE 542B4FB8 7 46 24083 0 1
ACTIVE 542B5368 12 15 24081 0 1
ACTIVE 542B5718 15 46 24083 0 1
ACTIVE 542B5AC8 79 4 15923 0 1
ACTIVE 542B5E78 50 16 24085 0 1
ACTIVE 542B6228 754 15 24081 0 1
ACTIVE 542B65D8 1 14 24069 0 1
ACTIVE 542B6988 2 30 14571 0 1

USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -
SYS ACTIVE 542B6D38 2 8 24071 0
542B70E8 1 15 24081 195 EV
542B7498 1 15 24081 195 EV
SYS INACTIVE 542B7848 0 0 0
SYS INACTIVE 542B7BF8 1 15 24081 195 EV

16 rows selected.

我们注意,红字标出的部分就是被Kill掉的进程的进程地址.


简化一点,其实就是如下概念:

SQL> select p.addr from v$process p where pid <> 1 2 minus 3 select s.paddr from v$session s;ADDR
--------
542B70E8
542B7498


Ok,现在我们获得了进程地址,就可以在v$process中找到spid,然后可以使用Kill或者orakill在系统级来杀掉这些进程.

实际上,我猜测:

当在Oracle中kill session以后,Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.

此时v$process和v$session失去关联,进程就此中断.

然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间.

如果此时被Kill的process,重新尝试执行任务,那么马上会收到进程中断的提示,process退出,此时Oracle会立即启动PMON
来清除该session.这被作为一次异常中断处理

五、oracle profile详解

数据库创建后,系统则存在名为DEFAULT的默认PROFILE,若不做特殊指定,创建用户时用户默认使用的PROFILE就是DEFAULT。

使用profile

一:创建profile

create profile文件名limit failed_login_attempts 3 password_lock_time 2 password_life_time 10 password_grace _time 2;
alter user用户名 profile 文件名;

注意:

用户所有拥有的PROFILE中有关资源的限制与resource_limit参数的设置有关,当为TRUE时生效,当为FALSE时(默认值)设置任何值都无效。

resource_limit默认为false.创建profile需要相应的权限,show parameter resource_limit同样需要权限.

SQL> show parameter resource_limit
NAME TYPE VALUE
-------------------- ----------- -------

resource_limit boolean FALSE

SQL> alter system set resource_limit=true;


二:修改profile

修改profile:alter profile [资源文件名] limit [资源名] unlimited;
如:alter profile default limit failed_login_attempts 100;

三:删除profile

删除PROFILE:drop profile [资源文件名] [CASCADE] ;
若创建的PROFILE已经授权给了某个用户,使用CASCADE级联收回相应的限制,收回限制信息后将以系统默认的PROFILE对该用户进行限制。

已分配的profile,删除时必须加cascade选项。

如果不删除profile,只是取消单个用户的profile:

SQL>alter user dinya profile default;

查询profile

一:查看视图dba_profiles可找出数据库中有哪些PROFILE。

SQL> select distinct profile from dba_profiles;
PROFILE
--------------------
MONITORING_PROFILE
DEFAULT

二:查看所有的PROFILE

SQL> select * from dba_profiles order by PROFILE;
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------------------- ------------------------------ -------- ----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

三.参数解释 1、对数据库资源做限制
{ { SESSIONS_PER_USER 每个用户名所允许的并行会话数
| CPU_PER_SESSION 一个会话一共可以使用的CPU时间,单位是百分之一秒
| CPU_PER_CALL 一次SQL调用(解析、执行和获取)允许使用的CPU时间
| CONNECT_TIME 限制会话连接时间,单位是分钟
| IDLE_TIME 允许空闲会话的时间,单位是分钟
| LOGICAL_READS_PER_SESSION 限制会话对数据块的读取,单位是块
| LOGICAL_READS_PER_CALL 限制SQL调用对数据块的读取,单位是块
| COMPOSITE_LIMIT “组合打法”
} { integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA 限制会话在SGA中Shared Pool中私有空间的分配 { size_clause | UNLIMITED | DEFAULT}
}
2、对密码做限制
{ { FAILED_LOGIN_ATTEMPTS 帐户被锁定之前可以错误尝试的次数
| PASSWORD_LIFE_TIME 密码可以被使用的天数,单位是天,默认值180天
| PASSWORD_REUSE_TIME 密码可重用的间隔时间(结合PASSWORD_REUSE_MAX)
| PASSWORD_REUSE_MAX 密码的最大改变次数(结合PASSWORD_REUSE_TIME)
| PASSWORD_LOCK_TIME 超过错误尝试次数后,用户被锁定的天数,默认1天
| PASSWORD_GRACE_TIME 当密码过期之后还有多少天可以使用原密码
} { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
}

1.如果用户超过了connect_time或idle_time的会话资源限制,数据库就回滚当前事务,并结束会话。用户再次执行命令,数据库则返回一个错误,
2.如果用户试图执行超过其他的会话资源限制的操作,数据库放弃操作,回滚当前事务并立即返回错误。用户之后可以提交或回滚当前事务,必须结束会话。
提示:可以将一条分成多个段,如1小时(1/24天)来限制时间,可以为用户指定资源限制,但是数据库只有在参数生效后才会执行限制。

六、常用命令

sqlplus /nolog
conn sys/oracle@orcl as sysdba

su - oracle -c "sqlplus / as sysdba"

show parameter processes;

alter system set processes = 1000 scope=spfile;


select username,PROFILE FROM dba_users;
create profile DEFAULT limit connect_time 60 idle_time 30

alter profile DEFAULT limit idle_time 30;
alter profile DEFAULT limit connect_time 60;
select count(*) from v$process;
select count(*) from v$session;

select *from dba_profiles

select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited; shutdown immediate; startup

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读