合 Oracle告警日志里记录了“KILL SOFT -/-/-”会话被杀掉的信息

2018-08-10T11:27:20.711173-04:00

KILL SESSION for sid=(291, 41855):

Reason = alter system kill session

Mode = KILL HARD SAFE -/-/-

Requestor = USER (orapid = 419, ospid = 6236, inst = 1)

Owner = N/A

Result = ORA-27

2018-08-10T11:28:23.410778-04:00

 

KILL SESSION for sid=(382, 30780):

Reason = profile limit idle_time

Mode = KILL SOFT -/-/-

Requestor = PMON (orapid = 2, ospid = 2961, inst = 1)

Owner = Process: USER (orapid = 164, ospid = 26147)

Result = ORA-0

 

SQL> col tracefile format a300

SQL> select sid,a.serial#,b.pid,b.spid,b.TRACEFILE

  2    from   v$session a,v$process b

  3   where a.PADDR=b.ADDR

  4   and  a.sid='36'  ;

 

       SID    SERIAL#        PID SPID                                             TRACEFILE

---------- ---------- ---------- ------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        36      11571        161 4309                                             /opt/oracle/diag/rdbms/lhrsdb/lhrsdb/trace/lhrsdb_ora_4309.trc

 

 

  select sid,a.serial#,b.pid,b.spid,b.TRACEFILE

  from   v$session a,v$process b

   where a.PADDR=b.ADDR

   and  a.sid='68'  ;

 

SID SERIAL# PID SPID    TRACEFILE

68  62973   162 4557    /opt/oracle/diag/rdbms/lhrsdb/lhrsdb/trace/lhrsdb_ora_4557.trc

 

select * from v$active_session_history a where a.SESSION_ID=68 and a.SESSION_SERIAL#=62973;  

select * from dba_hist_active_sess_history a where a.SESSION_ID=68 and a.SESSION_SERIAL#=62973;

 

alter system kill session '36,11571';

 

select d.os_user,

       d.host_name,

       d.terminal,

       d.authentication_type,

       d.userid,

       d.client_program_name,

       d.event_timestamp,

       d.return_code,

       d.os_process,

       to_char(d.sql_text) sql_text,

       d.client_identifier,

       d.current_user,

       d.unified_audit_policies

  from AUDSYS.AUD$UNIFIED d

where d.event_timestamp >= sysdate - 1

   and d.system_privilege_used = 'ALTER SYSTEM';

 

OS_USER HOST_NAME   TERMINAL    AUTHENTICATION_TYPE USERID  CLIENT_PROGRAM_NAME EVENT_TIMESTAMP RETURN_CODE OS_PROCESS  SQL_TEXT    CLIENT_IDENTIFIER   CURRENT_USER    UNIFIED_AUDIT_POLICIES

lhr WORKGROUP\LHRXXT    LHRXXT  (TYPE=(DATABASE));(CLIENT ADDRESS=((ADDRESS=(PROTOCOL=tcp)(HOST=192.168.26.245)(PORT=63840)))); LHR plsqldev.exe    08-AUG-23 03.17.42.627167 AM    0   4557    "   alter system kill session '36,11571'"   WORKGROUP\LHRXXT    LHR ORA_SECURECONFIG

 

[oracle@lhrora19c trace]$ tailf alert_lhrsdb.log

2023-08-08T11:17:42.626761+08:00

KILL SESSION for sid=(36, 11571):

  Reason = alter system kill session

  Mode = KILL SOFT -/-/-

  Requestor = USER (orapid = 162, ospid = 4557, inst = 1)

  Owner = Process: USER (orapid = 161, ospid = 4309)

  Result = ORA-0

[oracle@lhrora19c trace]$ more /opt/oracle/diag/rdbms/lhrsdb/lhrsdb/trace/lhrsdb_ora_4557.trc

Trace file /opt/oracle/diag/rdbms/lhrsdb/lhrsdb/trace/lhrsdb_ora_4557.trc

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417

ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1

System name:    Linux

Node name:      lhrora19c

Release:        3.10.0-1160.80.1.el7.x86_64

Version:        #1 SMP Tue Nov 8 15:48:59 UTC 2022

Machine:        x86_64

Instance name: lhrsdb

Redo thread mounted by this instance: 1

Oracle process number: 162

Unix process pid: 4557, image: oracle@lhrora19c

 

*** 2023-08-08T11:17:42.626523+08:00

*** SESSION ID:(68.62973) 2023-08-08T11:17:42.626562+08:00    # 表示主动执行kill的会话信息

*** CLIENT ID:(WORKGROUP\LHRXXT) 2023-08-08T11:17:42.626569+08:00

*** SERVICE NAME:(lhrsdb) 2023-08-08T11:17:42.626576+08:00

*** MODULE NAME:(PL/SQL Developer) 2023-08-08T11:17:42.626581+08:00

*** ACTION NAME:(SQL 窗口) 2023-08-08T11:17:42.626586+08:00

*** CLIENT DRIVER:(OCI) 2023-08-08T11:17:42.626591+08:00

 

KILL SESSION for sid=(36, 11571):    # 表示被杀掉的会话信息

  Reason = alter system kill session

  Mode = KILL SOFT -/-/-

  Requestor = USER (orapid = 162, ospid = 4557, inst = 1)   # 表示主动执行kill的进程信息

  Owner = Process: USER (orapid = 161, ospid = 4309)    # 表示被杀掉的进程信息

  Result = ORA-0

[oracle@lhrora19c trace]$ more /opt/oracle/diag/rdbms/lhrsdb/lhrsdb/trace/lhrsdb_ora_4309.trc

/opt/oracle/diag/rdbms/lhrsdb/lhrsdb/trace/lhrsdb_ora_4309.trc: No such file or directory

[oracle@lhrora19c trace]$

SQL> conn / as sysdba

Connected.

SQL> alter system kill session '45,48410';

*

ERROR at line 1:

ORA-00030: User session ID does not exist.

 

sys_context('USERENV','SID')

from dual; 2 3

 

SYS_CONTEXT('USERENV','SID')

--------------------------------------------------------------------------------

45

 

SQL> alter system kill session '45,58610';

*

ERROR at line 1:

ORA-00027: cannot kill current session

 

2018-08-13T13:50:06.971193+00:00

KILL SESSION for sid=(45, 48410):

Reason = alter system kill session

Mode = KILL SOFT -/-/-

Requestor = USER (orapid = 24, ospid = 18655, inst = 1)

Owner = N/A

Result = ORA-30

2018-08-13T13:51:47.858998+00:00

KILL SESSION for sid=(45, 58610):

Reason = alter system kill session

Mode = KILL SOFT -/-/-

Requestor = USER (orapid = 24, ospid = 18655, inst = 1)

Owner = N/A

Result = ORA-27