oracle错误TNS-12518解决
+++
系统环境：
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V5.2 Service Pack 2
CPU : 16 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:7678M/8181M, Ph+PgF:9751M/10032M, VA:1938M/2047M
+++
症状：
系统设置processes=350 (sessions=390)，但当会话数到250左右，新连接就会出现错误：TNS-12518: 监听程序无法分发客户机连接(CS客户端程序提示：未连接到oracle)……
相关讨论：
http://www.itpub.net/thread-1286246-1-1.html
http://www.itpub.net/thread-1285545-1-1.html
+++
联系oracle support，提供解决方案如下：
=== ODM Proposed Solution ===
1. Enable 4GB RAM Tuning (4GT)
OR
2. On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS
OR
3. Decrease the size of the database SGA
OR
4. Reduce oracle per thread/connection stack size from default.
OR
5. Change the connection model from dedicated to shared server.
=== ODM Proposed Solution Justif ===
According to Note 371983.1 solution,
32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT).
USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory.
(Decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) giving memory back to the O/S, allowing more connections to be established.
Oracle supplies the ORASTACK utility to allow customers to modify the default stack size of a thread / session when created in the Oracle executable.
Switching to Shared server should allow for more scalability.
+++
最终决定使用4. Reduce oracle per thread/connection stack size from default.
To implement the solution, please execute the following steps:
1. orastack executable_name new_stack_size_in_bytes
(Below are examples of setting the stack to 500K for the main executables :
orastack oracle.exe 500000
orastack tnslsnr.exe 500000
orastack svrmgrl.exe 500000
orastack sqlplus.exe 500000)
2. Retest the issue.
3. Migrate the solution as appropriate to other environments.
+++
修改前测试：
昨天修改processes=350,测试连接可以达到349，之前未报错，
怀疑单纯测试占用内存较低，无法重现错误
今天修改processes=450,测试连接
SE_Q PR_Q
356 362
报错：ORA-12518: TNS: 监听程序无法分发客户机连接
修改线程
关闭数据库及服务
修改系统线程
E:\oracle\product\10.2.0\db_1\BIN>orastack oracle.exe 500000
Dump of file oracle.exe
Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096
New Reserved Memory per Thread = 500000
E:\oracle\product\10.2.0\db_1\BIN>orastack tnslsnr.exe 500000
Dump of file tnslsnr.exe
Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096
New Reserved Memory per Thread = 500000
测试连接
SQL> select * from
2 (select count(*) as se_q from v$session) a,
3 (select count(*) as pr_q from v$process) b;
SE_Q PR_Q
---------- ----------
447 449
可以达到449，再连接则提示：ORA-12516: TNS: 监听程序无法找到匹配协议栈的可用句柄
(当前oracle.exe进程占用内存为1108928K)
增加线程，再测试连接
关闭数据库及服务
修改线程：
E:\oracle\product\10.2.0\db_1\BIN>orastack oracle.exe 2000000
Dump of file oracle.exe
Current Reserved Memory per Thread = 500000
Current Committed Memory per Thread = 4096
New Reserved Memory per Thread = 2000000
E:\oracle\product\10.2.0\db_1\BIN>orastack tnslsnr.exe 2000000
Dump of file tnslsnr.exe
Current Reserved Memory per Thread = 500000
Current Committed Memory per Thread = 4096
New Reserved Memory per Thread = 2000000
启动数据库及服务
测试连接
SQL> select * from
2 (select count(*) as se_q from v$session) a,
3 (select count(*) as pr_q from v$process) b;
SE_Q PR_Q
---------- ----------
237 239
可以达到239，再连接则提示：ORA-12518: TNS: 监听程序无法分发客户机连接
(当前oracle.exe进程占用内存为916620K)
+++
总结：
查看和修改oracle线程
1，查看oracle各线程方法：
主要线程如下：
oracle.exe
tnslsnr.exe
svrmgrl.ex
sqlplus.exe
修改方法：orastack executable_name
如：orastack oracle.exe
2，修改线程大小
各线程默认大小为1M，修改线程，不能小于300k.
orastack executable_name new_stack_size_in_bytes
如：
orastack oracle.exe 500000
orastack tnslsnr.exe 500000
orastack svrmgrl.exe 500000
orastack sqlplus.exe 500000
3，修改线程时必须要在%oracle_home%\bin目录下执行。
4，修改时，必须关闭相关服务
5，以下进程未修改成功，不论是关闭服务，还是打开服务时，原因不明。
E:\oracle\product\10.2.0\db_1\BIN>orastack svrmgrl.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 2
E:\oracle\product\10.2.0\db_1\BIN>orastack sqlplus.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 32
以上
转帖请注明：
本文源自TTT BLOG，原文地址：http://blog.chinaunix.net/u3/107265/showart_2201600.html
