使用 resource_limit 及 profile 限制用户连接

      数据库性能是一个永恒的话题,那就是如何使用更少的资源以达到更高效的性能。Oracle系统参数RESOURCE_LIMIT是一个用于控制用户对于数据库资源使用的参数,当值为true的时候即为启用,否则禁用。该参数结合profile来可以控制多种资源的使用,如CPU_PER_SESSION, CONNECT_TIME,LOGICAL_READS_PER_SESSION, PRIVATE_SGA等等从而达到到节省资源来实现高效性能。本文描述了数据资源限制并演示了IDLE_TIME及SESSIONS_PER_USER的用法。

1、数据库资源限制的主要步骤 Implemented by      * Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)      * Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit      * Assigning a profile to a user whose resources are wished to be limited

It could happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers being set to 'sniped' and then getting 'cleaned up' via the shell script.

The removal of the dispatcher will result in other sessions 'dying' .In that case, If you are to implement resource limits, may be advisable to create new profiles that be assigned to users and not to change the characteristics of DEFAULT. Alternatively, if you do change DEFAULT, ensure that all the properties that you have affected have been fully tested in a development environment.

用户超出限制后的完成的动作 When a resource limit is exceeded (for example IDLE_TIME) ... PMON does the following      * Mark the V$SESSION as SNIPED      * Clean up the database resources for the session      * Remove the V$SESSION entry

2、资源限制的配置

--演示环境
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--查看参数resource_limit
SQL> show parameter resource_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE

--修改参数resource_limit为true
SQL> alter system set resource_limit=true;

System altered.

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE

--创建profile,其idle_time为3分钟
SQL> create profile app_user limit idle_time 3; 

Profile created.

--修改profile,限制每个用户只能开一个session
SQL> alter profile app_user limit sessions_per_user 1;

Profile altered.

--将用户指派给特定的profile
SQL> alter user scott profile app_user;

User altered.

--查看刚刚创建的profile,查询结果中的RESOURCE_NAME都可以作相应的设置或修改
SQL> select * from dba_profiles where profile='APP_USER';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
APP_USER                       COMPOSITE_LIMIT                  KERNEL   DEFAULT
APP_USER                       SESSIONS_PER_USER                KERNEL   1
APP_USER                       CPU_PER_SESSION                  KERNEL   DEFAULT
APP_USER                       CPU_PER_CALL                     KERNEL   DEFAULT
APP_USER                       LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
APP_USER                       LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
APP_USER                       IDLE_TIME                        KERNEL   3
APP_USER                       CONNECT_TIME                     KERNEL   DEFAULT
APP_USER                       PRIVATE_SGA                      KERNEL   DEFAULT
APP_USER                       FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
APP_USER                       PASSWORD_LIFE_TIME               PASSWORD DEFAULT
APP_USER                       PASSWORD_REUSE_TIME              PASSWORD DEFAULT
APP_USER                       PASSWORD_REUSE_MAX               PASSWORD DEFAULT
APP_USER                       PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
APP_USER                       PASSWORD_LOCK_TIME               PASSWORD DEFAULT
APP_USER                       PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16 rows selected.

3、演示资源被限制的情形

C:\Users\robinson.cheng>sqlplus scott/tiger@oradb1

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:10 2013

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

SQL> host             ----->开启一个session
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\robinson.cheng>sqlplus scott/tiger@oradb1   --->尝试开启另一个sessioin

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:21 2013

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

ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit   --->此时收到资源被限制的提示
Enter user-name:

--在服务器端查看session的情形,3分钟后用户scott 的session的状态被置为SNIPED
SQL> @comm_sess_users;

+----------------------------------------------------+
| User Sessions (All)                                |
+----------------------------------------------------+

Instance     SID Serial ID    Status Oracle User     O/S User  O/S PID Session Program         Terminal             Machine
--------- ------ --------- --------- ----------- ------------ -------- --------------------- ---------- -------------------
oradb          1         5  INACTIVE         SYS       oracle 10090    sqlplus@node1.szdb.co      pts/1      node1.szdb.com
              35         7    ACTIVE          HR        robin 10171    sqlplus@SZDB (TNS V1-      pts/2                SZDB
              40       237    SNIPED       SCOTT Robinson.Che 13282    sqlplus.exe                 PC39     2GOTRADESZ\PC39

--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612

--获得session的spid
SQL> @my_spid_from_sid
Enter value for input_sid: 40

   SID    SERIAL# SPID
------ ---------- -------------------------------------
    40        237 13282

--此时的时间为20:17:54
SQL> ho date
Wed Jun 26 20:17:54 CST 2013

--查看scott对应的server process,其进程的启动时间为18:12,过了1个多小时,进程依旧没有被释放    
SQL> ho ps -ef | grep 13282 | grep -v grep
oracle   13282     1  0 18:12 ?        00:00:00 oracleoradb (LOCAL=NO)

--下面调用shell脚本来杀掉对应的进程
SQL> host
[oracle@node1 ~]$ ./kill_sniped.sh oradb
13282
[oracle@node1 ~]$ ps -ef | grep 13282 | grep -v grep

--清除服务器进程的shell脚本
[oracle@node1 ~]$ more kill_sniped.sh 
#!/bin/sh
export ORACLE_SID=$1
tmpfile=/tmp/tmp.$$
sqlplus -S /nolog <<EOF
connect / as sysdba
set head off feedback off
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
                do
                kill -9 $x
done
rm $tmpfile

4、注意事项 NOTE:

      If you are running in a shared server environment, you need to be careful not to accidentally kill your dispatchers and/or shared servers. In Oracle 10.2 (or higher) a dedicated connections V$SESSION + V$PROCESS + OS Process can be cleaned up with       ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL>' IMMEDIATE At this point in versions prior to 10.2 and for shared server connections the only solution is to kill the session at the OS level (see Kill and ORAKILL above)      * Windows : use the orakill command .... orakill <ORACLE SID> <Thread ID> (see Note 69882.1 for details)

On occasions we see conditions where a database session has a V$SESSION.STATUS = SNIPED ... and the entry never goes away . This condition can be achieved by implementing Database Resource Limits + Profiles without DCD and allow the database session to exceed the limit in the profile

5、小结 a、参数RESOURCE_LIMIT = TRUE用于启用数据库资源配置限制 b、profile用于实现资源配置,创建profile或修改已存在的profile来调整各个具体资源配置 c、将profile指派给那些需要限制的用户 d、一旦被限制的用户超出所设定的阀值将收到资源配置相关的错误提示 e、被限制资源的session状态变成sniped f、被限制资源的session对应的server process并没有被释放,需要手动释放或结合sqlnet.expire_date来进行释放 g、Reference:[ID 601605.1]  Oracle 角色、配置文件 http://psoug.org/reference/profiles.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

ABAP Netweaver和Cloud Foundry上的环境变量Environment Variable

2112
来自专栏Hadoop实操

如何使用StreamSets实现Oracle中变化数据实时写入Kudu

9295
来自专栏乐沙弥的世界

启用用户进程跟踪

仅仅需要标识该会话并为该会话启用跟踪(专用模式为一对一模式,即一个用户进程对应一个服务器进程)

922
来自专栏Java学习123

powerdesigner 15 如何导出sql schema

3347
来自专栏乐沙弥的世界

Oracle 12c手工建库(非CDB及CDB创建)

对于Oracle数据库的创建,Oracle除了支持dbca(GUI界面),同时也支持手工方式创建数据库,即使用CREATE DATABASE语句创建数据库。使用...

1741
来自专栏乐沙弥的世界

收缩临时表空间

        当排序操作、重建索引等大型操作无法在内存中完成时,临时表空间将为排序提供便利。一般情况下临时表空间为多个用户,多个会话所共 享。不能为会话分...

1303
来自专栏乐沙弥的世界

使用优化器性能视图获取SQL语句执行环境

    Oracle SQL语句的运行环境分为多个不同的层次,主要包括实例级别,会话级别,语句级别,其优先级依次递增。即语句级别的执行环境具 有最高的优先权,...

882
来自专栏乐沙弥的世界

Oracle 实例恢复

Oracle实例失败多为实例非一致性关闭所致,通常称为崩溃(crash)。实例失败的结果等同于shutdown abort。

1315
来自专栏乐沙弥的世界

Oracle 审计失败的用户登陆(Oracle audit)

       对于在线交易系统,且Oracle用户在使用缺省的profile的情形下,多用户共享相同的数据库用户及密码,任意用户输入错误密码累计达到10次以上,...

1273
来自专栏杨建荣的学习笔记

使用shell生成orabbix自动化配置脚本(r6笔记第53天)

在使用Orabbix监控Oracle的时候,本身和zaabix agent最大的不同便是使用Orabbix不需要对每个数据库实例都安装单独的agent,而是一个...

3208

扫码关注云+社区

领取腾讯云代金券