pat98's always & forever

주로 10g에서 발생한다고 하는데..아무 로그도 없고 , 이유없이 listener 가 Hang 이 걸릴때 조치방법입니다. listener 를 하나 띄워도 OS에서 계속 spawn 시켜서 listener가 4개가 생성되는 증상이 있을때 해보면 됨.
 
10.2.0.3 에서 fix 되었다고 나와있네요.

==================================================================================


Oracle Net Services - Version: 10.1.0.2.0 to 10.2.0.3.0
This problem can occur on any platform.
All new connections via TNS listener hang, no errors reported

Symptoms

Intermittently the TNS listener hangs and new connections to the database are not possible.

Listener process can also consume high amount of CPU

Child TNS listener process is seen when doing a ps on the listener process, eg.:

Killing the child process allows new connections to work until the problem reoccurs

Cause

This is a known problem addressed via non-published bug:4518443 (Abstract: Listener Gets Hung Up)

The issue is that the TNS listener can hang under load while spawning a process

Solution

Bug 4518443 is fixed in 10.2.0.3

 - OR -

Apply Patch 4518443  for the problem (if a patch is available)

 - OR -

As a workaround, the following parameter can be added to listener.ora

Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file.

For example, if the listener name is LISTENER (default), the parameter would be:

This will prevent the listener from registering against ONS (Oracle Notification Services), which is the area affected by bug:4518443. For more information on ONS, please refer to eg. the Oracle10g Release 2 documentation ("Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide").

Please note, adding SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> to listener.ora file on RAC, will mean that FAN (fast application notification) will not be possible. See Note 220970.1 RAC: Frequently Asked Questions for further information on FAN

PURPOSE
-------

The purpose of this article is to explain why the 12500 error is raised
and mainly how the tuning of resource in the platform is very important
to solve this issue.
This a short and quick checklist to understand and solve this error.
An ORA/TNS-12540 could be related to 12500 error.

SCOPE & APPLICATION
-------------------

Microsoft platforms ( windows machines)


Understanding and Diagnostics for 12500 error on Windows Platform
-----------------------------------------------------------------
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded

These errors are related to physical usage of Oracle and OS resources :
Memory, Swap, Open files, Open sockets ...ect

The client receive this message error from the listener

The following actions may help to solve the problem when the error appear
after a longtime running:
- Increase memory and swap in the system
- Configure MTS and DCD
- Tune SGA / shared_pool_size parameter
Reduce the SGA size to a reasonable figure which allows the user
process to have enough memory to run.
- Increase processes parameter in the init.ora file
- Restart Listener/DB
- Check for any memory leak
- Tune microsoft TCP parameters in :
HKEY_LOCAL_MACHINE\System\CurrectControlSet\services\Tcpip\Parameters
TcpTimedWaitDelay, MaxUserPortData and TcpMaxDataRetransmissions
- Try to set the /3GB switch in the BOOT.INI file to enable the 4GB feature.
Allowing a process to address 3GB and reserving 1GB for the kernel.
- Install the latest Windows Service Pack

If the error is persistent :
- Check the user who start the listener privileges.
- Check Oracle_Home and Oracle_SID
- The Oracle Service may be corrupt : Recreate The Oracle service by oradim command


RELATED DOCUMENTS
-----------------
Note 46001.1 Oracle Database and the Windows NT memory architecture
Note 171636.1 TNS-12500 TNS-12540 TNS-12560 TNS 510 Windows Error 8: Exec Format Error
Note 108180.1 Intermitent TNS-12540 Errors When Trying to Connect to oracle
Note 2064864.102 Troubleshooting TNS-12500 On Microsoft Windows NT
Note 118322.1 Basic MTS setup
Note 151972.1 Dead Connection Detection (DCD)