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