Oracle
Allen 2010-04-07 14:21:10 34263 0 0 0 0

 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: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.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%in目录下执行。

4,修改时,必须关闭相关服务

5,以下进程未修改成功,不论是关闭服务,还是打开服务时,原因不明。
E:oracleproduct10.2.0db_1BIN>orastack svrmgrl.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 2
E:oracleproduct10.2.0db_1BIN>orastack sqlplus.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 32
 
以上
 


【版權聲明】
本文爲原創,遵循CC 4.0 BY-SA版權協議!轉載時請附上原文鏈接及本聲明。
原文鏈接:https://tdlib.com/am.php?t=tBEqKYrc8N19
Tag: Oracle Oracle优化 Oracle错误 TTTBLOG
我也要發一個   ·   返回首頁   ·   返回[Oracle]   ·   前一個   ·   下一個
歡迎評論
未登錄,
請先 [ 註冊 ] or [ 登錄 ]
(一分鍾即可完成註冊!)
返回首頁     ·   返回[Oracle]   ·   返回頂部