ORA-00020: maximum number of processes exceeded

Hi All,

Hope you might have seen this error in your Oracle Database sometimes.

ORA-00020: maximum number of processes exceeded

What this problem is?

It’s self explanatory; Oracle can’t create a new process for a new session because the value specified by the parameter ‘processes’ has already been crossed.

What could be the exact reason for this problem?

Well, It might be the poor application that keeps on creating  sessions in the Oracle Database, of course processes for those sessions in the Database server, and not closing them but just retaining them or Really that many sessions are being created by the application because of the increased load

If first option is the culprit, You have to ask the Application administrators/Developers to monitor/provide a fix. Otherwise, it’s a pure mistake of the Architects/DBAs or whomever they’re fixed the number of processes to that value which is insufficient to that environment.

Anyway, Who ever do the mistake, DBAs are the one who have the responsibility to restore the services 🙂

So, As a DBA you’ll try to login to the Database and bounce it so that all the sessions/processes will be gone away and everything starts fresh.

What if you’re unable to login to the Database even with the SYS user, which is a must for the DBA to bounce the Database???

The clean/best possible solution is,

Try to find out all the processes with name like ‘oracle*’ by giving ‘ps -ef | grep oracle*’ at the OS level

After that kill some of the processes like ‘oracle<ORACLE_SID>’ by giving a simple kill -9 <PID1> <PID2>….<PIDn>; Don’t kill any oracle background processes like PMON/SMON etc…

This will release some resources and will give a chance to establish new sessions (new processes on the OS)

Then try to connect to the Database and if you’re able to connect, that’s good. You can shut down the instance.

If not try to kill some more processes of that kind I’ve mentioned above and try again.

Remember this just to restore the services but you’re still pending on the permanent fix. Otherwise be ready to visit this page again 🙂

I appreciate your feedback/comments.

CSM

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: