Allen
2010-04-07
14:21:10
34311
0
0
0
0
oracle错误TNS-12518解决+++系统环境：ORACLE V10.2.0.1.0 - Production vsnsta=0vsnsql=14 vsnxtr=3Windows NT Version V5.2 Service Pack 2CPU : 16 - type 586, 2 Physical CoresProcess Affinity : 0x00000000Memory (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.htmlhttp://www.itpub.net/thread-1285545-1-1.html+++联系oracle support，提供解决方案如下：=== ODM Proposed Solution ===1. Enable 4GB RAM Tuning (4GT)OR2. On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERSOR3. Decrease the size of the database SGAOR4. Reduce oracle per thread/connection stack size from default.OR5. 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 500000orastack tnslsnr.exe 500000orastack svrmgrl.exe 500000orastack 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:oracleproduct10.2.0db_1BIN>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:oracleproduct10.2.0db_1BIN>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:oracleproduct10.2.0db_1BIN>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:oracleproduct10.2.0db_1BIN>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.exetnslsnr.exesvrmgrl.exsqlplus.exe修改方法：orastack executable_name如：orastack oracle.exe2，修改线程大小各线程默认大小为1M，修改线程，不能小于300k.orastack executable_name new_stack_size_in_bytes如：orastack oracle.exe 500000orastack tnslsnr.exe 500000orastack svrmgrl.exe 500000orastack sqlplus.exe 5000003，修改线程时必须要在%oracle_home%in目录下执行。4，修改时，必须关闭相关服务5，以下进程未修改成功，不论是关闭服务，还是打开服务时，原因不明。E:oracleproduct10.2.0db_1BIN>orastack svrmgrl.exe 500000Couldn't open file with CreateFile()GetLastError() == 2E:oracleproduct10.2.0db_1BIN>orastack sqlplus.exe 500000Couldn't open file with CreateFile()GetLastError() == 32 以上
评论
