ORA-00020: maximum number of processes
(string) exceeded.
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.
Example:
The following is an example scenario for ORA-00020 and this scenario was obtained from web and provide here for informational purpose:
I have a big problem here on my ORACLE 10.2.0.2 P14 on Windows 2003
32-bit.
32-bit.
Something happened at the weekend (and I have no idea what because
nobody was working) that lead to the following:
nobody was working) that lead to the following:
Maximum number of processes (200) exceeded within a couple of minutes
after DB start. No further connections possible (ORA-00020).
after DB start. No further connections possible (ORA-00020).
Now, what I researched so far is that there is apparantly a record in
V$LOCK with lmode=6 without username, always with the same SID and serial# but different thread numbers after each DB startup. The thread numbers do not exist in Windows Manager and there is no locked record, latch, no deadlock or whatever out there.
V$LOCK with lmode=6 without username, always with the same SID and serial# but different thread numbers after each DB startup. The thread numbers do not exist in Windows Manager and there is no locked record, latch, no deadlock or whatever out there.
I tried to use "ALTER SYSTEM KILL SESSION 'xxxx,x';" but I receive the
ORA-00029 "session is not a user session".So I tried to use "orakill ". I receive the expected correct (!) message but all what happens is that the database is shutdown. After new startup the record still stays active and blocks everything.Not only can no further connection be established (e.g. SAP, BRGUI, OEM,SQLPLUS) but also my filesystem is filling up with trace files about ORA-00020 and of course the alert log with messages about process M000 died and new creation failed (4 lines each second!)...
ORA-00029 "session is not a user session".So I tried to use "orakill ". I receive the expected correct (!) message but all what happens is that the database is shutdown. After new startup the record still stays active and blocks everything.Not only can no further connection be established (e.g. SAP, BRGUI, OEM,SQLPLUS) but also my filesystem is filling up with trace files about ORA-00020 and of course the alert log with messages about process M000 died and new creation failed (4 lines each second!)...
Can anybody help me with that? I have spent now 3 days on this and don't
know wherelese to research further options...And of course I tried to increase the number of processes and sessions,up to 600 proc. with 1200 sessions...no change in behavor...just took longer to reach the limit...
know wherelese to research further options...And of course I tried to increase the number of processes and sessions,up to 600 proc. with 1200 sessions...no change in behavor...just took longer to reach the limit...
My SGA total size is 1.3GB while windows is booted with 3GB option.
PGA-target is 300M.
PGA-target is 300M.
------------
Verify PMON is running in the background...
select *
from v$session
where type ='BACKGROUND'
AND program like '%PMON%';
Verify PMON is running in the background...
select *
from v$session
where type ='BACKGROUND'
AND program like '%PMON%';
-----------------
Resolution :
I just fixed it! Since I could not get rid of the locked record I tried
this:
First I did a reset on all system user passwords. Then deleted
the remote-login-password file and recreated it. Processes are now
perfectly in the normal range!
No comments:
Post a Comment