[20220531]驗證inactive session出現的問題.txt

[20220531]驗證inactive session出現的問題.txt

--//http://blog.itpub.net/267265/viewspace-2898061/ => [20220531]inactive session等待事件2.txt
--//在alert.log記錄出現大量類似的資訊如下：
ORA-06512: at line 5
opiodr aborting process unknown ospid (25783) as a result of ORA-28
Tue May 17 09:11:27 2022
Errors in file /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_ora_15721.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00028: your session has been killed
ORA-06512: at "SYS.DBMS_LOCK", line 205
ORA-06512: at "HZMCASSET.TLOGON", line 1552
ORA-06512: at "HZMCASSET.TLOGON", line 1566
ORA-06512: at "HZMCASSET.TLOGON", line 1620
ORA-06512: at "HZMCASSET.TLOGON", line 2523
--//1566-1552 = 14
--//1620-1552 = 68
--//2523-1552 = 971
ORA-06512: at line 1
ORA-06512: at line 5
opiodr aborting process unknown ospid (15721) as a result of ORA-28
Tue May 17 09:11:30 2022

--//做一個例子驗證：

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試1：
--//session 1:
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       358          5 47454                    DEDICATED 47455       21          3 alter system kill session '358,5' immediate;

SCOTT@book> exec dbms_lock.sleep(100);
--//sleep 100秒。

--//開啟session 2，執行：
SYS@book> select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,status,state,wait_time_micro,seconds_in_wait,wait_class,client_info from v$session where sid=358
  2  @pr
==============================
P1RAW                         : 00
P2RAW                         : 00
P3RAW                         : 00
P1                            : 0
P2                            : 0
P3                            : 0
SID                           : 358
SERIAL#                       : 5
SEQ#                          : 211
EVENT                         : PL/SQL lock timer
STATUS                        : ACTIVE
STATE                         : WAITING
WAIT_TIME_MICRO               : 26799981
SECONDS_IN_WAIT               : 27
WAIT_CLASS                    : Idle
CLIENT_INFO                   :
PL/SQL procedure successfully completed.
--//注意該等待事件是PL/SQL lock timer，狀態是ACTIVE，WAIT_CLASS=Idle.

SYS@book> alter system kill session '358,5' immediate;
System altered.

--//session 1:
SCOTT@book> exec dbms_lock.sleep(100);
BEGIN dbms_lock.sleep(100); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 47455
Session ID: 358 Serial number: 5

SCOTT@book> exec dbms_lock.sleep(100);
ERROR:
ORA-03114: not connected to ORACLE

--//連線已經斷開。
$ ps -fp 47455
UID        PID  PPID  C STIME TTY          TIME CMD

3.測試2：
--//session 1,再次啟動會話，執行基本同上，不使用immediate;。
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       358          7 47479                    DEDICATED 47480       21          4 alter system kill session '358,7' immediate;

SCOTT@book> exec dbms_lock.sleep(100);


--//session 2:
SYS@book> alter system kill session '358,7';
System altered.

--//session 1:
SCOTT@book> exec dbms_lock.sleep(100);
BEGIN dbms_lock.sleep(100); END;

*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
ORA-06512: at "SYS.DBMS_LOCK", line 205
ORA-06512: at line 1


--//注意對比前面的提示，可以發現出現如下資訊：
ERROR at line 1:
..
ORA-06512: at "SYS.DBMS_LOCK", line 205

--//注意與前面alert看到的行號都是205一致。
--//也就是驗證了連結為什麼 GRANT EXECUTE ON SYS.DBMS_LOCK TO SCOTT; 還是報錯的原因。
--//當然因為出現inactive session等待，job再次呼叫，這樣在前面的job正好執行到SYS.DBMS_LOCK.sleep(3),這樣會話已經不存在了
--//，alert出現大量的這類錯誤。

--//關於出現inactive session等待事件，應該可以在測試環境模擬看看。