SQL PLUS 不能登录的异常处理

通过Sqlplus 访问数据同样hang死在登录界面，且不能通过Ctrl+C取消

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 30 10:31:33 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

alert 日志没任何报错

数据库cpu 无压力

连接数没有达到processes 的设置

处理

oracle$ sqlplus -prelim / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug hanganalyze 3

SQL> oradebug dump systemstate 266

<间隔一定时间，如20秒，执行下一次数据采样. >

SQL> oradebug hanganalyze 3

SQL> oradebug dump systemstate 266

SQL> oradebug tracefile_name

SQL> oradebug close_trace

生成的Trace 文件直接搜索blocker 关键字

     Dumping 1 direct blocker(s):

       inst: 1, sid: 2282, ser: 85

     Dumping final blocker:

       inst: 1, sid: 2282, ser: 85

final blocker sid:2282，再搜索2282，找到如下信息

SO: 0x552acf480, type: 4, owner: 0x551708ef0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

    proc=0x551708ef0, name=session, file=ksu.h LINE:12729, pg=0

  (session) sid: 2282 ser: 85 trans: (nil), creator: 0x551708ef0

             flags: (0x10041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

             flags2: (0x40009) -/-/INC

             DID: , short-term DID:

             txn branch: (nil)

             edition#: 100              oct: 3, prv: 0, sql: 0x557a67f20, psql: 0x557a692d8, user: 0/SYS

   ksuxds FALSE at location: 0

   service name: SYS$USERS

   client details:

     O/S info: user: oracle, term: UNKNOWN, ospid: 81552

     machine: dbh01 program: oracle@dbh01 (J000)

     application name: DBMS_SCHEDULER, hash value=2478762354

     action name: SM$CLEAN_AUTO_SPLIT_MERGE, hash value=1890521210

   Current Wait Stack:

    0: waiting for 'library cache: mutex X'

       idn=0x41bd76b6, value=0x2000000000, where=0x4f

       wait_id=71 seq_num=72 snap_id=1

       wait times: snap=3 min 13 sec, exc=3 min 13 sec, total=3 min 13 sec

       wait times: max=infinite, heur=872 min 45 sec

       wait counts: calls=17580 os=17579

       in_wait=1 iflags=0x15b2

   There are 91 sessions blocked by this session.

   Dumping one waiter:

     inst: 1, sid: 16, ser: 157

     wait event: 'latch: shared pool'

       p1: 'address'=0x6010f288

       p2: 'number'=0x150

       p3: 'tries'=0x0

     row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0

     min_blocked_time: 52361 secs, waiter_cache_ver: 60751

   Wait State:

     fixed_waits=0 flags=0x22 boundary=(nil)/-1

有如下重要信息：

(session) sid: 2282

ospid: 81552

machine: dbh01

program: oracle@dbh01 (J000)

application name: DBMS_SCHEDULER, hash value=2478762354

action name: SM$CLEAN_AUTO_SPLIT_MERGE, hash value=1890521210

0: waiting for 'library cache: mutex X'

There are 91 sessions blocked by this session.

杀掉OS 进程81552 , 故障恢复