Solved

Maximum number of processes exceeded

Posted on 2001-06-04
7
1,524 Views
Last Modified: 2011-10-03
Oralce 8.1.6 is running on a Sun Solaris 220R box.
The maximum number of processes is set to 200, yet a few days after running this limit is reached.

Doing a ps list or a top listing shows over 200 processes, and they almost all look like dead oracle connections.

The v$session table only shows about 25 connections.
The v$process table shows all 200 but not much more information than that.

What I need to know is what caused these 200 dead connections?

v$session has the information I want (MACHINE, TERMINAL, PROGRAM) but it only has that info on current connections. The dead ones don't show up there.

They must have been in v$session at one point, but that point has passed.

I was thinking maybe have oracle automatically copy anything that enters into v$session into another table where they won't be removed, so that I'll have sort of a transaction log. But I have no idea how to set up 'triggers' do this sort of thing.

Help!
0
Comment
Question by:Sembiance
7 Comments
 
LVL 9

Expert Comment

by:dbalaski
Comment Utility
Hi,

THere are a few parameters that can help with this:
1) Dead Connection Detection (DCD)
DCD is initiated on the server when a connection is established.  At The timer interval is set by providing a non-zero value in minutes for  the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.
 
  When the timer expires, SQL*Net on the server sends a "probe" packet to the client.  (In the case of a database link, the destination of the link constitutes the server side of the connection.)  The probe is essentially an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.
 
  If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset.  If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources.

In the SQLNET.ORA file on the host,  add the following line:
SQLNET.EXPIRE_TIME = 10

-------------------------------------------
2) Setup a generic profile in Oracle and set the idle time to expire the processes after a reasonable amount of time idle.
Taken from the Oracle8i SQL Reference Release 3 (8.1.7)
CREATE PROFILE
Purpose
Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, that user cannot exceed these limits.

resource_parameters
SESSIONS_PER_USER -- Specify the number of concurrent sessions to which you want to limit the user.
IDLE_TIME  -- Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.  
CONNECT_TIME  -- Specify the total elapsed time limit for a session, expressed in minutes.  
 
 
 
For example:   a profile to limit idle time to 1 hour  would be:

CREATE PROFILE  limit_time
    idle_time 60;

Then you need to alter the user accounts to have that profile as their default.

Also, you have to add the following parameter in you init.ora file to enable profiles and resource limits:
RESOURCE_LIMIT = TRUE

RESOURCE_LIMIT determines whether resource limits are enforced in database profiles:
TRUE: enables the enforcement of resource limits.
FALSE: disables the enforcement of resource limits.

This should be a start that will help clear up those dead connections.

sincerely,
dBalaski






0
 
LVL 9

Expert Comment

by:dbalaski
Comment Utility
Hi -- an addition.


Most of the time,  I find the above mentioned parameters helpful --   I have found that

1) DCD helps especially when users power off their desktops with active sqlnet connections.
2) Profiles help with the long inactive sessions.

One of the biggest reasons I have found has turned out to be the application logic!
I number of people forgetting to close or disconnect their session --  A very big problem in a lot of web applications.

Hope you find this and the above information helpful.
sincerely,
dB
0
 

Author Comment

by:Sembiance
Comment Utility
Ok, I will try those suggestions.
What I'm really after is trying to track down what is CAUSING these dead connections. I feel it is a third party software solution we are using here, but I need proof.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:misho2000
Comment Utility
Check v$process view. I think that you can join v$session, v$process and v$sesstat in order to have view on processes.
0
 

Expert Comment

by:mntndog
Comment Utility
You may also want to look at the sqlnet.log file for information on the connections.
0
 

Author Comment

by:Sembiance
Comment Utility
The network/admin/sqlnet.log says tracing is not on. How do I enable tracing? This will record all connections then correct?
0
 
LVL 9

Accepted Solution

by:
dbalaski earned 300 total points
Comment Utility
Hi,

You can enable tracing on the host through two methods:

1)Turn tracing on via NetMan.  Go to the object screen for the desired object and look for 'tracing'. You will then be asked to specify the trace level, directory and filename.  For level you can specify off, user or admin.  16 is the highest level of tracing.


2)Manual Method:
To enable tracing for the listener, set the following parameter in the LISTENER.ORA file:
TRACE_LEVEL_<listener-name>=OFF|USER|ADMIN

Where:  OFF disables tracing.
        USER enables limited tracing.
        ADMIN enables detailed tracing.

You are also going to need to set the following in your SQLNET.ORA file on the host for the log & trace file locations  (the defaults are a little obscure..):
LOG_DIRECTORY_SERVER,
LOG_FILE_SERVER,  
TRACE_DIRECTORY_SERVER,
TRACE_FILE_SERVER
--------------------------------------------

Pretty much that is it....

dB
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql to get orphans 7 50
Create index on View 27 52
Oracle SQL Select Statement 19 58
ORA-12560: TNS:protocol adapter error 8 41
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now