New session to database are refused by the listener TNS-00510 and TNS-12500

Since your db is using much memory it might be better to use MTS (Multi Threaded Server) to enhance memory usage of server processes. Oracle then opens assinged number of connections and users connects and shares them. Oracle uses ~2M of server process area for each connection.

Hi rateesh,
>>  TNS-12540: TNS:internal limit restriction exceeded
I have seen this on Windows NT and Win2k already several times, and it needed an Oracle patch to solve it.
Are you running 8.1.7.0.0 or did you put already some patch (for example 8.1.7.4)?

CHeers    

Hi angel & iozturk ,


I have not yet patched Oracle it is still running 8.1.7.0.0 . Do you know what is number of the bug we are hitting . So that i can find out the exact patch.

And now about  using MTS  i am not sure because we have been using oracle with the same server configuration with no resource problem for past 1 year. anyway Will shifting to MTS help in an enviornment where there is hardly 200 users. Although there are 180 session only around 20 sessions  active at any point of time. I am going to test it first with test database  but it's going to be hard to replicate the error on the test db.

This error message is a little misleading. The error implies we have hit a limit of some form. This is usually an OS limit that has been reached.  
Diagnosis:
Locate the full SQL*Net V2 error stack.
For client processes see the 'sqlnet.log' file produced by the client. For the listener look at the listener.log file.
If an error stack cannot be locate turn on client or server
tracing and extract the error stack from the trace file.
From the error stack the bottom error should identify an Operating System error which may give a clue as to which resource is being depleted.  
Typical problems are:
        - Out of system memory / swap
        - Out of process slots in the process table
        - Streams resources depleted
        - Out of File Handles
 
It is useful to monitor the system with any OS monitoring tools (Eg: sar on Unix) to see if any resource is low when the error occurs. Also note that there is a hard coded limit to the number of dispatchers that a listener can keep track of.

Active sessions are not important each session uses 2M of memory.

Active sessions are not important each session uses 2M of memory.

hai ,

we are using Windows 2000 Server with 2 GB RAM so
the article mentioned by schwertner  cannot be applied. i will try to turn on tracing for the client and server once
we get the error but it is difficult to know when.



The server side listener log file shows the following

===========================================================
31-MAR-2003 15:57:25 * (CONNECT_DATA=(SERVICE_NAME=pg2)(CID=(PROGRAM=D:\Program Files\Paris Gallery\Inventory\INVENTORY.exe)(HOST=FAISAL)(USER=faisal))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.240)(PORT=2566)) * establish * pg2 * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12540: TNS:internal limit restriction exceeded
  TNS-12560: TNS:protocol adapter error
   TNS-00510: Internal limit restriction exceeded
    32-bit Windows Error: 8: Exec format error
31-MAR-2003 15:57:38 * (CONNECT_DATA=(SERVICE_NAME=pg2)(CID=(PROGRAM=D:\Program Files\Paris Gallery\Inventory\INVENTORY.exe)(HOST=FAISAL)(USER=faisal))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.240)(PORT=2567)) * establish * pg2 * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12540: TNS:internal limit restriction exceeded
  TNS-12560: TNS:protocol adapter error
   TNS-00510: Internal limit restriction exceeded
    32-bit Windows Error: 8: Exec format error

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


The client side log file shows the following

Fatal NI connect error 12500, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.98)(PORT=1541))(CONNECT_DATA=(SERVICE_NAME=pg2)(CID=(PROGRAM=C:\Program Files\Inventory\INVENTORY.exe)(HOST=VISWAN)(USER=viswan))))

  VERSION INFORMATION:
     TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
     Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 8.1.6.0.0 - Production
  Time: 31-MAR-2003 15:57:46
  Tracing not turned on.
  Tns error struct:
    nr err code: 0
    ns main err code: 12564
    TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
===========================================================

1. Increase the init.ora PROCESSES parameter.  
2. If after increasing the PROCESSES parameter does not help, can set the following  parameter in init.ora in addition to point 1.        _attach_count_slack = 2000

hi


I have decreased the SGA size and Increased the processes parameter and the error has not occured till now . schwertner, could u explain what the _attach_count_slack  parameter does and how it is usefull in this context. I could not locate it in the hidden parameters

hi


I have decreased the SGA size and Increased the processes parameter and the error has not occured till now . schwertner, could u explain what the _attach_count_slack  parameter does and how it is usefull in this context. I could not locate it in the hidden parameters.

Also if somebody could tell me how to configure the current db setup to go into MTS and if it would be helpfull in an enviornment with 200 sessions. I dont have any java objects installed in the db.

hi schwertner ,

After i reduced the sga size i have not yet got the error again so i will select this as my accepted answer