?
Solved

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

Posted on 2003-03-31
12
Medium Priority
?
4,377 Views
Last Modified: 2008-08-29


Hai guys,


I am facing some problem with my database Oracle 8.1.7 running on windows 2000 service pack 2 .The server has Physical Memory of 2 Gb Ram . Now to the problem, new connections to the database are someties rejected giving the following error

  TNS-12500: TNS:listener failed to start a dedicated server process


the listener log file gives the following error

==============================================================================
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
==============================================================================


this is happening randomly and at time of high CPU activity. I went through some oracle docs and it said that it could be due to lack of resources or memory .I guess that new session could not be started by the listener. Task manager shows that around 1.9 gb of ram is being used.


Around 180 sessions access the database through out. sharedpool is set to 385 MB Buffer cache is 400 Mb Sort_area_size is set to 2 Mb.
Processes parameter is set to 220.


Thanks in advance

0
Comment
Question by:rateesh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 4

Expert Comment

by:iozturk
ID: 8238134
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8238366
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    
0
 

Author Comment

by:rateesh
ID: 8238437
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 48

Expert Comment

by:schwertner
ID: 8238637
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.

0
 
LVL 48

Accepted Solution

by:
schwertner earned 750 total points
ID: 8238663
You are receiving an ora TNS-12540 error intermitently
when trying to make connections to the database. OPS
and MTS are not being used in this configuration.
The error might be:
TNS-12540: TNS:internal limit restriction exceeded
The alert log has an entry that looks like:
skgpspawn failed category 27143
The skgpspawn failed message refers to a failure to spawn
a new session.
Solution Description
--------------------
Performance monitor shows that the instance that that has
the TNS-12540 error occurring is using right around 2gb of
memory. The SGA for the instance is about 1.8gb of memory.
Reconfiguring the SGA to take less memory allows for connections
to be made sucessfully.
The most significant init.ora settings that are a candidate to
to reduce the size of the SGA are:
db_block_buffers
shared_pool_size
java_pool_size
sort_area_size
sort_area_retained_size
Explanation
-----------
Because non Enterprise Windows NT Server limits the amount of memory
used per process to 2gb, the process for the instance could not
allocate more memory for new connections.
References
----------
A Microsoft document that explains what is termed 4GT RAM Tuning can
be found at:
http://support.microsoft.com/support/kb/articles/Q171/7/93.ASP 
Oracle Server Reference A67790-01
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8238832
Active sessions are not important each session uses 2M of memory.
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8238836
Active sessions are not important each session uses 2M of memory.
0
 

Author Comment

by:rateesh
ID: 8243234
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
===========================================================
0
 
LVL 48

Expert Comment

by:schwertner
ID: 8243900
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
0
 

Author Comment

by:rateesh
ID: 8244904


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
0
 

Author Comment

by:rateesh
ID: 8244941


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.
0
 

Author Comment

by:rateesh
ID: 8258957
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
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question