[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等待事件，应该可以在测试环境模拟看看。