關於ora-02391問題的總結

關於ORA問題的分析和解決其實是一個很好的學習思路，抓住一個每一個ORA錯誤，然後進一步分析一些原因，總結，總會有不一樣的收穫，還是那句話，任何問題背後都是有原因的。
今天早上，開發的同事反饋說客戶端中丟擲了ora錯誤。
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
希望我們能夠幫忙看看是什麼原因，怎麼修復一下。
這個問題其實還是比較清晰的，就是在我們設定的profile中會定義對應session數限制，比如存在使用者test，sessions_per_user為50，則test使用者最多使用50個session.
預設的profile是DEFAULT ,在建立資料庫之後會做基本的初始化，比如密碼的過期時間等等
SQL> select *from DBA_PROFILES WHERE PROFILE='DEFAULT'
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 UNLIMITED
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
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
預設的profile DEFAULT中sessions_per_user是Unlimited,但是實際中我們為了限制資源的使用，還是會自定義profile，其實還是基於profile DEFAULT 
sql> select username,profile from dba_users where username=’ USER_TEST’;
USERNAME                       PROFILE
------------------------------ ------------------------------
USER_TEST                   APP_TEST
這個時候可以看到對應的最大session數就是20了。
SQL> select * from dba_profiles where profile='APP_TEST';
PROFILE            RESOURCE_NAME                    RESOURCE LIMIT
------------------ -------------------------------- -------- ----------------------------------------
APP_TEST    COMPOSITE_LIMIT                  KERNEL   DEFAULT
APP_TEST    SESSIONS_PER_USER                KERNEL   20
APP_TEST    CPU_PER_SESSION                  KERNEL   DEFAULT
APP_TEST    CPU_PER_CALL                     KERNEL   DEFAULT
APP_TEST    LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
APP_TEST    LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
APP_TEST    IDLE_TIME                        KERNEL   DEFAULT
APP_TEST    CONNECT_TIME                     KERNEL   DEFAULT
APP_TEST    PRIVATE_SGA                      KERNEL   DEFAULT
APP_TEST    FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
APP_TEST    PASSWORD_LIFE_TIME               PASSWORD DEFAULT
APP_TEST    PASSWORD_REUSE_TIME              PASSWORD DEFAULT
APP_TEST    PASSWORD_REUSE_MAX               PASSWORD DEFAULT
APP_TEST    PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
APP_TEST    PASSWORD_LOCK_TIME               PASSWORD DEFAULT
APP_TEST    PASSWORD_GRACE_TIME              PASSWORD DEFAULT
基本知識介紹完畢。

如果丟擲了ora-02391，我們可以透過alter  profile xxxx limit sessions_per_user  xxxx來進行解決。
問題是這種錯誤在資料庫日誌中不會顯示，所以DBA也無從知曉，只能等待反饋，得到反饋後再解決問題。
我們可以變被動為主動。
使用下面的監控語句來進行檢查。
比如我們設定閥值為90%,即每個user的對應的session超過profile中設定值的90%,就會返回結果。
select u.username||' with related profile '||p.profile||' has potential issue on '||p.resource_name ||' current value is '||s.cnt||' of '||p.limit
from dba_profiles p,
(select username,count(*)cnt from v$session where username is not null group by username) s,
dba_users u
where  p.RESOURCE_NAME = 'SESSIONS_PER_USER'
and p.profile=u.profile
and s.username=u.username
and p.profile !='DEFAULT'
and s.cnt*100/decode(p.limit,'DEFAULT',999,p.limit)>=90;

返回結果類似下面的形式。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP_TEST with related profile PF_APP_TEST1 has potential issue on SESSIONS_PER_USER current value is 29 of 31
APP_TEST2 with related profile PF_APP_TEST2 has potential issue on SESSIONS_PER_USER current value is 60 of 6

進一步改進，我們可以加入orabbix中，這樣我們可能比開發還早收到報警郵件，問題處理也更有效