Hi All ,
I am regularly getting the eror ORA-00020: maximum number of
processes (2000) exceeded on one of the RAC nodes .
The process limit is set to 2000 .
I found lot of INACTIVE sessions around 1805 on one node on which
the service(preffered/available) was active .
Therefore implemented :
show parameter resource_limit
###should show true value i.e resource_limit=TRUE
select * from dba_profiles
where profile='DEFAULT'
and resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 240;
Now the session are getting killed , however the number of process doesnot
come down and I still get into ORA-00020: maximum number of processes .
I checked this document : A Discussion of Dead Connection Detection,
Resource Limits using V$SESSION, V$PROCESS and OS processes (Doc ID
601605.1)
it says IDLE / ABANDONED / INACTIVE sessions OS processes will not be
cleaned up even if DCD and Database Resource Limits + user Profiles are
used in combination and these must be cleaned up manually.
So I am taking a step back and need you guidance on this issue how to
resolve it :
1) How to know why this apps is creating so many session , is there a way
know more details about it why it has somany INVALID session ? Just to
gather some data for apps to check and tune .
2) How processes and Inactive sessions related what is the math behind it
i.e how the number of session translate to number of processes .
3) I want to get rid of Cleaning the process manually is there a better
automated fix which can be enabled on database side to get rid of it .
Thanks,
--
Asad Hasan
+91 9582111698
