database tns error

[huqiao]

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
HPUX Error: 11: Resource temporarily unavailable

[Ross Zubritski]

I this a new install? You may want to recheck you kernel parameters, or look for a port conflict between TNS and another service registered in /etc/services.

Regards,

RZ

[Sridhar Bhaskarla]

Hi,

Error: 11 indicates that there are no more processes can be started.

It could be either nproc or maxuprc.

Look at sar -v 2 20 and observe proc-sz column. If it is near full, then you will need to increase that parameter.

If not, then it could be your maxuprc. As oracle, do a

ps -ef|grep oracle |wc -l.

Compare this number with

kmtune -l -q maxuprc

If they are near, then you will need to increase maxuprc kernel parameter.

-Sri

[huqiao]

this is new install.

[huqiao]

thanks Sridhar Bhaskarla
thanks Ross Zubritski

[Ross Zubritski]

Please refer to Sri's advice above, definately kernel(s) parameter(s).

Regards,

RZ

[T G Manikandan]

Sri is right

THe problem is with the
maxuprc - max number of processes per user
You need to increase this parameter in the kernel.

Before that Just do a

ps -ef|grep oracle

and check whether unwanted processes are running like any scripts that has gone into a loop spawning new processes.


Thanks

[Steven E. Protter]

attaching a general performance data collection script that will help you identify other issues.

SEP

[Jon Finley]

There is also a "processes" parameter in the init.ora file, try increasing that value.

Jon

[Jon Finley]

From Oracle:

Intermittent TNS-12500 errors are caused by a lack of resources on the server. Finding which resource is depleted may be difficult.

1. The TNS-12500 can be a result of the 'processes'
parameter in the init.ora file being too low.
2. If the init.ora 'processes' parameter seems ok then you
may need to increase the Unix kernel parameters for the
maximum number of processes or users (for example, nproc
or maxuprc). Check the manuals for the Unix operating
system for more information on these parameters.
3. Check that you have adequate swap space.
4. Disable OTRACE. OTRACE is a tracing feature that can cause
many problems. OTRACE is enabled by default. To disable
OTRACE:
a. Stop the Oracle database.
b. Go to the $ORACLE_HOME/otrace/admin directory.
c. Delete all files with a '.dat' extension.
d. Restart the Oracle database.
5. Use the Multi-Threaded Server (MTS) option. MTS will fix
the problem since the listener does not spawn dedicated
server processes in this environment. MTS also cuts down
on the amount of resources being utilized by having connections
threaded through dispatchers and by having shared server
processes read dispatcher requests from queues.

To invoke MTS:
a. Stop the database
b. Modify the init.ora file to include these
parameters: mts_dispatchers="," mts_max_dispatchers= mts_servers= mts_max_servers= mts_service= mts_listener_address=

An example of a working MTS configuration would be: mts_dispatchers="tcp,3" mts_max_dispatchers=10 mts_servers=2 mts_max_servers=4 mts_service=ORCL mts_listener_address= "(address=(protocol=tcp)(host=mars1)(port=1521))"
Please note for Oracle 8i the MTS configuration would be: mts_dispatchers = "(protocol=tcp)(dis=2)" mts_max_dispatchers = 4 mts_servers = 4 mts_max_servers = 6 local_listener = "(ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=mars1)(PORT=1521)))"
c. Restart the database.
For more information on the Multi-Threaded Server
option, please see NOTE 16656.1 and PR 1013209.6.

Jon