Re: The dreaded TNS-12500 error.

darrens_69_at_hotmail.com (Darren) wrote in message news:<3514a44c.0307100611.125ff48_at_posting.google.com>...
> Hi Oracle experts,
>
> We are getting this error on our Oracle system. I've searched Google,
> MSDN, Oracle tech support and newsgroups to death, but our DBAs seem
> to have already applied all the solutions suggested. Here's the
> relevant part from the Listener log:
>
> 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
>
> Oracle is running on Windows 2000 - dual processor machine with
> several gigs of RAM. The base error seems to be a Windows error 8 -
> "not enough storage is available to complete this operation" - if I'm
> reading the log correctly. Here are some facts:
>
> - the error occurs when the DB is under heavy load, and at that point
> is intermittent - some connection requests get rejected, whether they
> come from SQLPlus or from the rich VB client over ODBC, but others are
> honored.
> - the server has plenty of spare RAM even when connections are being
> refused.
> - the server CPUs are at around 50% utilization when the problem
> happens.
> - we have already increased the PROCESSES parameter in the ini file to
> way more than we need (I think it's currently at 700 and we only have
> a couple hundred connections according to Spotlight - in any case we
> are nowhere near the max connections)
> - we used the 3GB process patch for Windows 2000 to make sure the
> Oracle process has enough memory.
> - we have reduced the "SGA" size as mentioned in several articles
> - nothing is logged in the Oracle Alerts
> - nothing is logged in any of the Windows 2000 Event Logs
> - the Listener responds fine to tnsping (albeit with somewhat
> increased ping times)
>
> The only thing I can think is that the Listener is using some Windows
> OS kernel object to communicate with the Oracle process (Semaphores
> probably?) and there is some system-wide limit on whatever those
> objects are, which the Listener is somehow exceeding.
>
> Oracle technical support of course blames it on the O/S - "Oracle
> consumes O/S resources just like any other app."
>
> We are going to reengineer the app to use connections more
> efficiently, but for now we need to put this fire out.
>
> Any help much appreciated!
>
> -Darren.

Oracle is implemented on NT as a multithreaded application. Each connection (assuming you are not running MTS) will use a thread. There is a fixed limit of threads per processes. So -whether you like it or not- OTS is correct.

You could do either one of two things
- configure Oracle to use MTS
- switch to a real operating system. You will never ever be capable to manage 700 active connections on any Winblows O/S

Regads

Sybrand Bakker
Senior Oracle DBA Received on Fri Jul 11 2003 - 11:55:44 CEST