Wednesday 13 March 2013

ORA-00020: maximum number of processes (%s) exceeded


Ever received this error message in your alert log? I have on a couple of occasions. Perhaps an application server started spawning more database connections than it normally does in turn creating more processes on the database. The Oracle description of this error is:
Error: ORA 20 Text: maximum number of processes exceeded  
Cause: An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES. When this maximum is reached, no more requests are processed. Action: Try the operation again in a few minutes. If this message occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

But what if you cannot connect to your database to shut it down and increase the parameter?

oradba01t[labdb01]-/home/oracle/>sqlplus  /as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jun 6 11:25:05 2011
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
ERROR:
ORA-00020: maximum number of processes (%s) exceeded

I always like to look at the alert log to check for any additional details.

Using adrci I tailed the alert log and sure enough we had our ORA-00020 error all over.
2011-06-06 11:17:36.042000 -05:00
ORA-00020: No more process state objects available
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
2011-06-06 11:24:35.878000 -05:00
ORA-00020: maximum number of processes 0 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.
adrci> exit

How do I connect to the database to increase my processes parameter? Well I remember Tanel Poder blogged about "How to log on even when SYSDBA can't do so?"

oradba01t[labdb01]-/home/oracle/>sqlplus -prelim "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jun 6 11:29:54 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL>

I was able to log on to the instance without getting the error message. Now to see if I can shut it down and start it up to increase the parameter.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

It appears that the shutdown abort was successful but the mount command did not complete. Disconnect from the previously connected session and logon normally to start the instance.
SQL>exit
oradba01t[labdb01]-/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Jun 6 11:33:07 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>startup mount
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size                  2160352 bytes
Variable Size            3489663264 bytes
Database Buffers          671088640 bytes
Redo Buffers               12656640 bytes
Database mounted.
SQL>

Now we can go ahead and increase our processes parameter. Actually I opted not to increase the parameter since I knew the root cause of the processes being exceeded. This was a Enterprise Manager Grid Control repository and due to some OMS processes had caused the database processes to be exceeded.

No comments: