Thursday, December 12, 2013

Oracle ORA-00020: maximum number of processes (xxx) exceeded

If you get following 'ORA-00020' errors in database alert log:
--
ORA-00020: maximum number of processes (xxx) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process m000 submission failed with error = 20
--

Then you probably wont get into the database via sqlplus or any other tool. This is because you got same error when you are trying to connect into the database. Error means that you need to increase processes parameter value or find out what is using too many processes and fix the problem. Best way to handle this kind of situation is to stop application/applications that are using this database and then you can again connect into the database and increase needed parameter values (alter system set processes=500 scope=spfile;) and restart the database.

If you cannot stop the applications to free processes then you can try to stop database with following way:
Run following as oracle user into database server:
export ORACLE_SID=<database_name>
sqlplus -prelim / as sysdba

and then run 'shutdown immediate' or 'shutdown abort' . Then 'startup' or 'startup mount' and do the parameter change (after parameter change you need to do restart the database). But remember that this can damage your applications data (especially if you use abort which just stops the database right away.) So it is better to just stop the application/applications even it means little downtime.

NOTE! When you connect into database via 'sqlplus -prelim' then you can also try to check what is causing these errors with oradebug (hanganalyze). And if you find some problematic sessions/SQLs you can kill (via OS) just those without need to restart the whole database. But this can take some time so more quickly fix use the above instructions. More info about oradebug and hanganalyze can be find from My Oracle Support (MOS) documents:
215858.1
and
310830.1