The dreaded TNS-12500 error.

[Darren]

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.

[sybrandb]

da********@hotmail.com (Darren) wrote in message news:<35*************************@posting.google.c om>...

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

[lycovian]

Had the same problem. In our case the oracle.exe process was crossing
3GB boundry. Try this, monitor the Virtual Bytes of your application
with PERFMON. Watch the VB count rise as users log in. You may see
that as you cross the 3GB Virtual Bytes boundry that you get this
error. If that is the case then your user count (~2.7MB/user) is slowly
driving you over the max process size that Win2K allows with the /3GB
boot.ini switch enabled. If that is the case recommend a) switching to
MTS and b) resizing your SGA such that it fits completely with in the
3GB boundry.
--
Posted via http://dbforums.com

[hmreyes]

Hello,

I would to know how to modify the MTS because I'm getting error 12540.

Thank you in advance
--
Posted via http://dbforums.com