Use resource_limit and profile to restrict user connections

Using resource_limit and profile to limit user connection to database performance is an eternal topic, that is, how to use fewer resources to achieve more efficient performance. The Oracle system parameter RESOURCE_LIMIT is a parameter used to control the user's usage of database resources. It is enabled when the value is true. Otherwise, it is disabled. This parameter can be used with the profile to control the use of a variety of resources, such as CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA to save resources and achieve efficient performance. This article describes data resource restrictions and demonstrates IDLE_TIME and SESSIONS_PER_USER usage. 1. Main steps for database resource restrictions: 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 limitedIt cocould happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers be Ing 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 affe Cted have been fully tested in a development environment. when the user exceeds the limit, the completed action 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. Resource Restriction configuration [SQL] -- demo Environment SQL> select * from v $ version where rownum <2; BANNER ------------------------------------------------------------ Export Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production -- View resource_limit SQL> show parameter resource_limit name type value parameter ----------- modify resource_limit boolean FALSE -- modify the parameter resource_limit to true SQL> alter system set resource_limit = true; system altered. SQL> show parameter resource_limit NAME TY Pe value Creation ------------- ---------------------------- resource_limit boolean TRUE -- create a profile whose idle_time is 3 minutes SQL> create profile app_user limit idle_time 3; Profile created. -- modify the profile to limit that each user can open only one session SQL> alter profile app_user limit sessions_per_user 1; Profile altered. -- assign the user to a specific profile SQL> alter User scott profile app_user; user altered. -- view the created profil E. You can set or modify RESOURCE_NAME in the query result. SQL> select * from dba_profiles where profile = 'app _ user '; PROFILE RESOURCE_NAME resource limit extends -------- extends 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 Login kernel default APP_USER login kernel default APP_USER IDLE_TIME KERNEL 3 APP_USER CONNECT_TIME kernel default APP_USER login password default APP_USER password_ver FY_FUNCTION password default APP_USER PASSWORD_LOCK_TIME password default APP_USER PASSWORD_GRACE_TIME password default 16 rows selected. 3. demonstrate resource restrictions [SQL] 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,201 0, Oracle. all rights reserved. SQL> host -----> enable a session Microsoft Windows [Version 6.1.7600] Copyr Ight (c) 2009 Microsoft Corporation. all rights reserved. c: \ Users \ robinson. cheng> sqlplus scott/tiger @ oradb1 ---> try to enable another sessioin SQL * Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:21 2013 Copyright (c) 1982,201 0, Oracle. all rights reserved. ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit ---> at this time, the message "Enter user-name: -- view the session on the server" is received, after 3 minutes, scott's session status is set to SNIP. Ed SQL> @ comm_sess_users; + ---------------------------------------------------- + | User Sessions (All) | + region + Instance SID Serial ID Status Oracle User O/s pid Session Program Terminal Machine --------- ------ --------- ------------- ------------ region ---------- --------------------- oradb 1 5 inactive sys o Racle 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 -- Obtain the spid SQL OF THE session> @ my_spid_from_sid Enter value for input_sid: 40 sid serial # SPID ------ ---------- --------------------------------------- 40 237 13282 -- the time is 20:17:54 SQL> ho date Wed Jun 26 20:17:54 CST 201 3 -- check scott's corresponding server process. The start time of the process is. After more than one hour, the process is still not released SQL> ho ps-ef | grep 13282 | grep-v grep oracle 13282 1 0? 00:00:00 oracleoradb (LOCAL = NO) -- call the shell script below to kill the corresponding process SQL> host [oracle @ node1 ~] $./Kill_sniped.sh oradb 13282 [oracle @ node1 ~] $ Ps-ef | grep 13282 | grep-v grep -- clear the shell script of the server process [oracle @ node1 ~] $ More kill_sniped.sh #! /Bin/sh export ORACLE_SID = $1 tmpfile =/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> 'immediateat 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. Summary a. the parameter RESOURCE_LIMIT = TRUE is used to enable Database Resource configuration. restrict B and profile for resource configuration, create a profile or modify an existing profile to adjust the configuration of each specific resource c. Assign the profile to the user d to be restricted. Once the restricted user exceeds the set threshold, the user will receive error message related to resource configuration: e. The session Status of the restricted resource changes to snipedf. The server process corresponding to the session of the restricted resource is not released, must be manually released or combined with sqlnet. expire_date to release