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

What if lost both the PFILE and SPFILE???

It’s been two months I didn’t write any articles. It’s been a continuous festive season, here in India, and a job change for me 🙂

Hope I’ll continue my interest with the same pace as earlier.

Loosing SPFILE and the PFILE might be a typical scenario faced by lot of DBAs (mostly the junior DBAs): Loosing PFILE and SPFILE both.

If the Database is already running and if you lost these files, you won’t face any problems until you come across a situation to bounce the Database 😉

If the Database is down and your task is to bring it up, you’re already dead 🙂

Don’t be panic in this kind of situations, as I do 🙂

I’m going to tell you a trick by following which you can make your instance alive by having at least some amount of settings of your running instance recovered.

You can find the details about the various parameters with which the instance is started in the alert log. Yes, the alert log collects all the parameters before the instance get started. So you can use them to construct a pfile and start your instance with that pfile like below

start pfile='<path to PFILE>’

after that you can generate the SPFILE as well using

create spfile from pfile

One caveat to this is, you couldn’t recover the setting you made ‘on the fly’. i.e. the settings you made to the spfile directly while the instance is running.

If you’re smart and already configured SPFILE auto backup, you don’t need all the things mentioned above. You could just restore the SPFILE from the auto backup.

See the instructions below to do that (courtesy Oracle Documentation )

To restore the server parameter file:

  1. If the database is up at the time of the loss of the SPFILE, connect to the target database. For example, run:
    % rman TARGET /

    If the database is not up when the SPFILE is lost, and you are not using a recovery catalog, then you must set the DBID of the target database. See“Determining your DBID” for details on determining your DBID.

  2. Shut down the instance and restart it without mounting. When the SPFILE is not available, RMAN starts the instance with a dummy parameter file. For example:
    RMAN> STARTUP FORCE NOMOUNT;
  3. Restore the server parameter file. If restoring to the default location, then run:
      RMAN> RESTORE SPFILE FROM AUTOBACKUP;

If restoring to a nondefault location, then you could run commands as in the following example:

      RMAN> RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM AUTOBACKUP;

Hope you enjoyed it.

I always welcome your feedback and suggestions.

Thanks,

CSM