ORA-12516: TNS:listener could not find available handler with matching protocol stack解决方法

某天，业务部门反应前端肯定业务端无法访问数据库，在数据库上尝试连接数据库：

sqlplus a/b@192.168.0.160:1521/orcl

ORA-12516: TNS:listener could not find available handler with matching protocol stack

查看监听日志

11-MAY-2021 09:18:47 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=D:\MEDIINFO\EMRCS\emr_yzbjq.exe)(HOST=NEK12)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.72.23)(PORT=1864)) * establish * orcl * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
11-MAY-2021 09:18:47 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=D:\MEDIINFO\EMRCS\emr_yzbjq.exe)(HOST=NEK12)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.72.23)(PORT=1865)) * establish * orcl * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
11-MAY-2021 09:18:47 * service_update * orcl * 0

该报错是由于连接进程满了的原因，查看数据库进程数

查询得知，数据库最大进程数1500已经达到了，而且当前的连接数一直是1490+，随时都会抛出此类报错

select username,program,count(*) from v$session group by username,program order by 3;

业务部门反映，ssgl.exe这个应用不应该有这么多连接，抽丝剥茧，再查看ssgl.exe这个应用的连接情况。

select username,machine,program from v$session;

查询得知，这个应用程序几乎是由这一台客户端发起的连接，通过监听日志或者命令可以定位到这台机器的IP

命令：
随便找个会话的SID，找到这个会话的操作系统的PID，再通过netstat定位IP

通过SID找PID
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=2247 and ses.paddr=pro.addr;

SPID
------------------------
6135

通过netstat查找IP
netstat -anp | grep 6135
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:192.168.0.250:1521   ::ffff:192.168.23.29:51745  ESTABLISHED 6135/oracleorcl

监听日志：

11-MAY-2021 09:23:18 * (CONNECT_DATA=(SERVICE_NAME=orcl)***(HOST=1GWS***064)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.23.29)(PORT=49468)) * establish * orcl * 0

IP地址和主机名已经定位到了，提交给客户，让客户去找这台机器，为了保障业务，批量kill session

SELECT 'alter system kill session '''||SID || ',' || SERIAL#||''';'   FROM V$SESSION  where program='ssgl.exe' and machine like '%1GWS%;

通过此方法kill session后，需要等待资源释放，若想快速kill session可以通过sid找到pid，在操作系统层面通过kill -9 pid的方法。