在测试环境中经常会遇到ORA-12516错误，登录数据库报错如下：
[oracle@D2-PISIT22 ~]$ sqlplus lissit/lissit@10.163.91.22:1521/pocsit02

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 11 08:51:49 2017

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

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

查看监听服务：
[oracle@D2-PISIT22 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2017 08:50:53

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "pocsit02" has 1 instance(s).
Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:8095 refused:0 state:blocked
         LOCAL SERVER
Service "pocsit02XDB" has 1 instance(s).
Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: D2-PISIT22, pid: 1690>
         (ADDRESS=(PROTOCOL=tcp)(HOST=D2-PISIT22)(PORT=54841))
The command completed successfully

可以看到对于实例pocsit02的服务pocsit02，已经创建了8095个连接，状态为blocked，处于封锁状态。
这个问题对于测试环境，直接重启监听和数据库ok了，但生产环境就不能使用这么重的方式了，可以执行如下语句重新让pmon注册一下监听：

SQL> alter system register;

System altered.

[oracle@D2-PISIT22 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2017 08:54:42

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "pocsit02" has 1 instance(s).
Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:8100 refused:0 state:ready
         LOCAL SERVER
Service "pocsit02XDB" has 1 instance(s).
Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: D2-PISIT22, pid: 1690>
         (ADDRESS=(PROTOCOL=tcp)(HOST=D2-PISIT22)(PORT=54841))
The command completed successfully

可以看到状态变为ready了。也可以对监听开一下跟踪，然后关掉的方式来解决这个问题并重置连接数：

SQL> alter system set events='immediate trace name listener_registration level 3';

System altered.

SQL> alter system set events='immediate trace name listener_registration level 0';

System altered.

[oracle@D2-PISIT22 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2017 08:55:58

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "pocsit02" has 1 instance(s).
Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "pocsit02XDB" has 1 instance(s).
Instance "pocsit02", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: D2-PISIT22, pid: 1690>
         (ADDRESS=(PROTOCOL=tcp)(HOST=D2-PISIT22)(PORT=54841))
The command completed successfully

ITPub博客

ORA-12516问题处理

最新文章