Oracle Database Hang While Loading 3rd party SBT Library And After This Nobody Can Access The Database (windows login 登陆hang )

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Microsoft Windows x64 (64-bit)
Goal

This issue is Windows 2012 platform specific affecting 3rd party media managers when allocating SBT channels with RMAN.

We are seeing an issue where the rman sessions hangs and after which we cannot access the database through
sqlplus or rman.

We are seeing this issue only on Windows 2012.
Other Windows editions work fine.

This is causing service outage and Oracle service need to be restarted. When we analyze the wait-chain all the
Oracle DB service's threads are waiting for load to complete.

We are not able to reproduce this hang on any other Windows release only the Windows2012 edition.

 
Solution

Bug 18846036 - RMAN: WINDOWS HANDLE LOCKED BY ORACLE THREAD CAUSING HANG CONDITION
Fixed in Product Version: 12.2


Rediscovery:
Any process any in Console input handle while ReadConsole() in another thread context.

ERRORSTACK
==========

ntdll!NtDeviceIoControlFile+0xa
KERNELBASE!GetSystemDefaultLangID+0x18c
KERNELBASE!ReadConsoleA+0x175
KERNELBASE!ReadConsoleA+0x16
oracle!sksasmothrd+0xbc [sksa.c @ 309]
KERNEL32!BaseThreadInitThunk+0x1a
ntdll!RtlUserThreadStart+0x21

select * from v$session where username='SYS' and program='rman.exe'
    SYS    0    2147483644            INACTIVE    DEDICATED    0    SYS    aackupadmin    55292:54940    aEAISSVR    0    EAISSVR    rman.exe    2017/2/13 11:08:18    16802949    rman.exe    

8008

1.
10:52:44 SQL> @check.sql

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24-MI-SS'
--------------------------------------
2017-02-13 10-54-06

Connected.

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24-MI-SS'
--------------------------------------
2017-02-13 10-54-46


2.
check.sql

set time on
select to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual;
conn oper/oper123@eaisprod
select to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual;
set time off

3.in windows 任务管理器。

kill name as process name "rman.exe'

4. manaule kill oracle session

select 'alter system kill session ',''''||trim(t2.sid)||','||trim(t2.serial#)||''';'
  from v$locked_object t1,
       v$session t2
 where t1.session_id=t2.sid
 order by t2.logon_time;

5.still session "rman" is killed status.

 发现rman 进程无论是killed 状态，或者inactive 状态，都是会存在v$session 列表里，无法清理。

6.NO way try restarrt db