10gR2 Dedicated Connections Intermittently Fail with TNS-12518
Subject: 10gR2 Dedicated Connections Intermittently Fail with TNS-12518
Doc ID: Note:371983.1 Type: PROBLEM
Last Revision Date: 05-FEB-2008 Status: MODERATED
In this Document
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Oracle Net Services - Version: 10.2 to 10.2.0.3.1
Microsoft Windows 2000
Microsoft Windows Server 2003
Microsoft Windows (32-bit)
Connections to 10gR2 database
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.
nserror: nsres: id=0, p=65, ns=12560, ns2=0; nt= 530, nt=233, nt=0; ora=0, ora=0, ora=0
ORA-4030 errors messages can also be reported
Upgrade to 10gR2 (10.2.0.1)
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 11 onwards.
Code change for 5108234 has been included in Windows 10.2.0.3 patch 1 Patch 5731535 and 10.2.0.4. (10.2.0.4 still to be released)
Following steps are known to help increase memory available to Oracle or decreased memory utilization by Oracle. Allowing more connections to be established.
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
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 342080.1
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.
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
Change the connection model from dedicated to shared server. (formally known as MTS)Switching to Shared server should allow for more scalability. See Note 118322.1
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7419833/viewspace-493966/，如需转载，请注明出处，否则将追究法律责任。