10gR2 Dedicated Connections Intermittently Fail with TNS-12518

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

  Symptoms

  Changes

  Cause

  Solution

  References

--------------------------------------------------------------------------------

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.

Applies to:

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

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 11 onwards.

 

Solution

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.

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 342080.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 118322.1

 

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

转载于:http://blog.itpub.net/7419833/viewspace-493966/