处理Win2003下Oracle10.2.0.4.0的TNS-12518错误

32bit的Windows 2003系统，安装的是oracle 10.2.0.4.0版本，最近时常出现TNS-12518错误，而导致用户端连接失败。

Server是4GB的内存，SGA设置了1.6G，PGA 400MB。在50个process时就会连接失败。

目前暂时设置SGA 1.2G，PGA 400MB，尚未出现连接异常。

怀疑是由于32Bit限制Oracle只能使用1.7G内存，导致内存分配失败。

因此准备做如下两个动作：
1. 修改boot.ini文件，加入/3GB来开放Oracle使用内存限制。（如超过4GB可加入/PAE）
2. 因系统压力不大，继续缩小SGA及PGA大小，分别设置为：1G，200M。

后查询得知，可能是10G的Bug，需要打patch14。（如后续没有因session过多再出现问题，可能这步暂时不会做）

以下内容从Metalink    copy的，供参考：

主题:	10gR2 Dedicated Connections Intermittently Fail with TNS-12518
	文档 ID:	371983.1	类型:	PROBLEM
	上次修订日期:	02-APR-2009	状态:	PUBLISHED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

---

Applies to:

Oracle Net Services - Version: 10.2.0.1.0 to 10.2.0.4.13
Microsoft Windows 2000
Microsoft Windows Server 2003
Microsoft Windows (32-bit)
MS Windows 2000MS Windows 2003MS Windows NT
Connections to 10gR2 database
Checked for relevance on 22-JAN-2009.

Symptoms

Connection intermittent fail with TNS-12518: TNS:listener could not hand off client connection
Max Utilization of processes parameter is not being hit by connections and database background processes.

Listener log shows

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 233: Unknown error

Listener trace shows

snlpcss: Unable to spawn Oracle oracle (LOCAL=NO) , error 233.
sntpcall: exit
ntpcon: exit
nserror: entry
nserror: nsres: id=0, p=65, ns=12560, ns2=0; nt[0]= 530, nt[1]=233, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

ORA-4030 errors messages can also be reported

Changes

Upgrade to 10gR2 (10.2.0.1)

Cause

Lack of available memory on the server. For example Bug 5108234High Memory (Virtual Bytes) Consumption Per Connection in 10.2, was logged for the issue. It has been confirmed for release 10.2 on Windows, dedicated connections take 2.14MB of virtual memory, while 10.1 only takes 1.5MB.

In summary, as more functionality is added to new releases, more resources will be required

It was found that a code change was possible for release 10.2, to lower the resources taken per connection.

This change will not be available for release 11g onwards.

Solution

Code change for 5108234 has been included in Windows 10.2.0.3 Patch 1 Patch 5731535  and 10.2.0.4 Patch 14

Please see Note 161549.1 Oracle Database Server and Networking Patches for Microsoft Platforms for the lastest Patch available. 

Please note, this code change was marked as included in 10.2.0.4 patch set. This has now been confirmed as incorrect. 

Following steps are known to help increase memory available to Oracle or decreased memory utilization by Oracle. Allowing more connections to be established.

1.
Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory-intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts. . The only configuration change required is to ensure that the /3GB flag is used in Windows ’boot.ini file. See http://support.microsoft.com/?kbid=291988

2.
On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS

USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory.
It enables or disables the use of the extended buffer cache mechanism for 32- bit platforms that can support more than 4 GB of physical memory. See Note 225349.1

3.
Decrease the size of the database SGA (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. A good example is the sga_max_size parameter.  Oracle must 'reserve' this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA is never increases to the value of sga_max_size, then the memory is wasted. Reserved for no reason.

4.
Reduce oracle per thread/connection stack size from default. Oracle supplies the ORASTACK utility to allow customers to modify the default stack size of a thread / session when created in the Oracle executable.
See section c) Altering the way Oracle creates per session memory structures in Note 46001.1

5.
Change the connection model from dedicated to shared server. (formally known as MTS) Switching to Shared server should allow for more scalability. See Note 1005259.6 Shared Server (MTS) Diagnostic

References

Note 161549.1 - Oracle Database Server and Networking Patches for Microsoft Platforms
Note 342443.1 - 10.2.0.x Oracle Database and Networking Patches for Microsoft Platforms
Note 5108234.8 - Bug 5108234 - Win: High memory (virtual bytes) consumption per connection in 10.2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/110321/viewspace-618028/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/110321/viewspace-618028/