TNS-12540: TNS:internal limit restriction exceeded

   应用程序以及客户端工具(Toad、PL/SQL Developer等)出现突然连接不上数据库服务器的情况,监听日志listener.log里面出现了TSN-12518与TSN-12540错误,如下所示,

27-JAN-2015
10:10:19 *
(CONNECT_DATA=(SERVICE_NAME=scm2)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=POS)(USER=SYSTEM)))

* (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.xxx.xxx)(PORT=1667)) * establish * scm2 * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12540: TNS:internal limit restriction exceeded

27-JAN-2015
10:10:19 *
(CONNECT_DATA=(SERVICE_NAME=scm2)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=CEGWEB1)(USER=NETWO

RK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.xxx.xxx)(PORT=1284)) * establish * scm2 * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12540: TNS:internal limit restriction exceeded

 

经过这几天的努力,终于解决了这个问题,所以修改、补充了这篇先前的文章,希望能对其他人有所借鉴。下面整理了一下解决问题的步骤。如下所示:

 

1:首先查看监听服务的进程是否正常,如下所示,监听服务完全正常

ps -ef | grep tnslsnr

oraescm 6669 5979 0 14:03 pts/5 00:00:00 grep tnslsnr

oraescm 29877 1 0 08:12 ? 00:00:11 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

 

2:检查查看监听服务的状态(lsnrctl status)

 

3: 检查lsnrctl services后, 发现一个奇怪的情况Dispatcher没有拒绝监听的记录,如下所示

 

4:检查Dispatcher进程的是否繁忙,结果查询时一直查询不出结果,立即取消了当前查询

 

像平时查询时(如下所示)的速度是非常快的。如果”busy rate%“比例超过50%一般建议加SHARED SERVER OR DISPATCHER

SQL> select name,(busy/(busy+idle))*100 "busy rate%" from v$dispatcher;
 
NAME busy rate%
---- ----------
D000 .194393707
D001 .124595175
D002 .102238233
D003 .097416229
D004 .140182022
D005 .114123279
D006 .137454634
D007 .148511476
D008 .148901287
D009 .111103697
 
10 rows selected.

 

5:检查会话、进程数量。发现当前会话数为483,历史最大值为720. sessions最大值为1000 ,processes参数最大值为870, 应该可以排除processes与session参数值设置过小导致。

select * from v$resource_limit;
 
select count(1) from v$session;
 
SELECT SESSIONS_MAX,SESSIONS_WARNING,SESSIONS_CURRENT,SESSIONS_HIGHWATER    
 
FROM v$license;
 
select count(1) from v$process;
 
select count(1) from v$session;
 
select busy/(busy+idle) from v$shared_server;
 
select name,busy/(busy+idle)*100 "Dispatcher Busy Rate" from v$dispatcher;
 

 

6:检查服务器CPU、RAM资源。如下所示,CPU利用率非常低。检查内存,发现物理内存只剩下204M大小,但是Swap还有13729M剩余。另外告警日志里面并没有发现任何错误信息。

 

在官方文档查看TSN-12540错误信息,如下所示:

 

TNS-12540: TNS:internal limit restriction exceeded

Cause: Too many TNS connections open simultaneously.

Action: Wait for connections to close and re-try.

 

http://docs.oracle.com/cd/B28359_01/network.111/b28316/troublestng.htm#CEGDGHBJ

TNS-12540/ORA-12540: TNS:internal limit restriction exceeded and TNS-00510: Internal limit restriction exceeded

Cause: An internal limit has been exceeded. Possible limits include:

      Number of open connection that Oracle Net can process simultaneously

      Number of memory buffers which can be used simultaneously

      Number of processes a particular database instance is allowed

The first two are examples of
hard limits. The third is an example of a limit which can be increased
by setting PROCESSES parameter in the database initialization file to a
larger value. In this case, a TNS-12500/ORA-12500 error is also
returned. In some cases, these errors can be caused by the same
conditions which cause TNS-12549/ORA-12549 and TNS-00519 errors.

Action: Perform these steps:

Wait for the open connections to
close and retry. If the error persists, then check the sqlnet.log or
listener.log file for detailed error stack information.

 

ORA-12540: TNS:internal limit restriction exceeded

Cause: Too many TNS connections open simultaneously.

Action: Wait for connections to close and re-try.

 

This
error is not an indication of a network problem unless it occurs on all
connections (in other words you have never been able to connect). If so
it is a .ora configuration problem.

If you can make connections up to
a certain point but then fails it is an indication of a resource
limitation at the os level (this sounds like your scenario)

Typical problems are:

       Out of system memory / swap

       Out of process slots in the process table

       Streams resources depleted

       Physical connections allows by the kernal.

       Out of File Handles

但是如下所示,检查了Linux系统的一些内核参数设置,也没有发现有设置不当的地方

 
[[email protected] ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 193217
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 4194302
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

 

check to count the number of existing socket handles on your db server (as root):

[[email protected] bdump]$ lsof -i 4 -a | wc -l
473
 
[[email protected] bdump]$ cat /proc/sys/fs/file-max
2414114

最后,我们决定增加Dispatcher的数量,从8改为10,尝试解决问题,结果问题不再重现。问题解决了。

SQL>alter system set dispatchers='(protocol=TCP)(disp=10)(serv=SCM2)' scope=both;
 
 
 
SQL> show parameter dispatchers
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (protocol=TCP)(disp=10)(serv=S
                                                 CM2)
max_dispatchers                      integer
SQL> 

 

205-02-03 添加:

       几天过去了,悲催的是今天下午又出现了TSN-12540错误,纠结啊,这个问题已经把我折腾惨了!

时间: 2016-04-26

TNS-12540: TNS:internal limit restriction exceeded的相关文章

TNS - 12516 TNS : 解决

报错 TNS - 12516 TNS : listener could not find instance with matching protocol stack   根据官方的文档说是processes和sessions参数设置的问题.已经达到了最大值.索引监听拒绝连接,这个时候监听例程是blocked的,确实如此如下: 服务 "libaodb" 包含 1 个例程.   例程 "libaodb", 状态 READY, 包含此服务的 1 个处理程序...    

[Oracle]Oracle错误案例:TNS-00510

TNS-00510 Internal limit restriction exceeded Cause Too many files or sockets open simultaneously (or some other resource has been depleted). Action For further details, trace the operation for protocol details. TNS-12500 TNS:listener failed to start

Oracle错误案例:TNS-00510

TNS-00510 Internal limit restriction exceeded Cause Too many files or sockets open simultaneously (or some other resource has been depleted). Action For further details, trace the operation for protocol details. TNS-12500 TNS:listener failed to start

ORACLE 博客文章目录(2015-05-27更新)

从接触ORACLE到深入学习,已有好几年了,虽然写的博客不多,质量也参差不齐,但是,它却是成长的历程的点点滴滴的一个见证,见证了我在这条路上的寻寻觅觅,朝圣的心路历程,现在将ORACLE方面的博客整理.归纳分类,方便自己和大家查看.翻阅.  ORACLE数据类型 ORACLE基本数据类型总结 ORACLE VARCHAR2最大长度问题 ORACLE数据库汉字占几个字节问题 ORACLE NUMBER类型Scale为0引发的问题   PL/SQL语法学习   PLSQL 调试触发器 PL/SQL重

TNS-12535: TNS:operation timed out案例解析

一数据库突然连接不上,在自己电脑上使用SQL Developer也连接不上.立即使用SecureCRT连接上了这台服务器,从下面几个方面检查.   1:检查了数据库的状态是否正常 $ sqlplus / as sysdba   SQL*Plus: Release 10.2.0.5.0 - Production on Mon May 16 11:07:55 2016   Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.     Conn

使用SQLTools工具怎么访问我本地的Access数据库,怎么配置TNS

问题描述 使用SQLTools工具怎么访问我本地的Access数据库,怎么配置TNS 学习数据库ing,想用SQLTool访问本地的Access文件,应该怎么连接,如果用TNS,TNS应该配置在哪个地方,怎么配置. 解决方案 是dsn吧http://zhidao.baidu.com/link?url=5IhNdNkdgqNiii-cL8A3hQADwEaaI2ntylzuYnJCxUeN_9fJV4ZuaJZXqHn40K8JU3c2bG5RzDhxbrEwOLjAEq 解决方案二: http:

ORACLE错误一览表 方便查询_oracle

ORACLE错误一览表,方便大家查询! ORA-00001: 违反唯一约束条件 (.) ORA-00017: 请求会话以设置跟踪事件 ORA-00018: 超出最大会话数 ORA-00019: 超出最大会话许可数 ORA-00020: 超出最大进程数 () ORA-00021: 会话附属于其它某些进程:无法转换会话 ORA-00022: 无效的会话 ID:访问被拒绝 ORA-00023: 会话引用进程私用内存:无法分离会话 ORA-00024: 单一进程模式下不允许从多个进程注册 ORA-000

使用 resource_limit 及 profile 限制用户连接

      数据库性能是一个永恒的话题,那就是如何使用更少的资源以达到更高效的性能.Oracle系统参数RESOURCE_LIMIT是一个用于控制用户对于数据库资源使用的参数,当值为true的时候即为启用,否则禁用.该参数结合profile来可以控制多种资源的使用,如CPU_PER_SESSION, CONNECT_TIME,LOGICAL_READS_PER_SESSION,PRIVATE_SGA等等从而达到到节省资源来实现高效性能.本文描述了数据资源限制并演示了IDLE_TIME及SESSI

Cookie 规范

cookie|规范 PERSISTENT CLIENT STATEHTTP COOKIES Preliminary Specification - Use with caution -------------------------------------------------------------------------------- INTRODUCTION Cookies are a general mechanism which server side connections (su