監聽日志listener.log裡面出現了 TNS-12518: TNS:listener could not hand off client connection與TNS-12540: TNS:internal limit restriction exceeded錯誤，如下所示，用戶連接不上ORACLE數據庫：
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
另外告警日志裡面並沒有發現任何錯誤信息。檢查內存，發現物理內存只剩下261M大小，但是Swap還有13556M剩余。
查看processes參數值為870， session值為10000，當時數據庫最大會話數為419，歷史最大值為720，應該可以排除processes與session參數值設置過小
在官方文檔查看TNS-12540: TNS:internal limit restriction exceeded錯誤的信息，如下所示：
TNS-12540: TNS:internal limit restriction exceeded
Cause: Too many TNS connections open simultaneously.
Action: Wait for connections to close and re-try.
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
查看當前系統所有的資源限制.
[orxxm@xxxx ~]$ 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
為了解決問題，立馬重啟監聽服務，問題解決了，但是是什麼原因導致了這個錯誤呢？搜索了大量的資料，沒有看到類似的案例，當然在檢查過程也發現一些應用程序是使用DEDICATED連接到數據庫，讓相關人員修改為共享服務器連接模式，最後依然不能找到一個確切的導致TSN-12540錯誤的原因。糾結、研究了很久，依然不能解決問題，留待以後解決。
參考資料：
http://www.dba-oracle.com/t_ora_12540_tns_internal_limit_restriction_exceeded.htm
http://database.ccidnet.com/art/1105/20060601/569461_1.html
http://blog.itpub.net/81018/viewspace-812609/
