Monday, April 25, 2016

ORA-00020: maximum number of processes (5000) exceeded

Symptom
After an ORA-00020 "maximum number of processes (%s) exceeded" occurs ... no new connections (even SYSDBA or SYSOPER) may be made to the instance.

Solution
An ORA-20 "maximum number of processes (%s) exceeded" occurs when the number of OS processes for the instance exceeds the PROCESSES parameter 
There are a couple of 'workarounds' for this issue
WORKAROUNDS:
UNIX: kill -9 one or more of the client connections 
WINDOWS : using ORAKILL ... kill one or more of the client connections 
* Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and 
         alter system kill session 'SID, SERAL#';
In my case I don't have any active sessions with sufficient privileges
As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to generate traces.
You can shut abort and then restart your database, instead of rebooting your server.
This is the ultimate chance before reboot. Before doing this, consider disconnecting or killing some user session to get a regular sqlplus / as sysdba.

No comments:

Post a Comment

How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g/12c

1. Create Temporary Tablespace Temp create temporary tablespace temp2 tempfile '/mnt/mnt04/oradata/temp01.dbf'size 2000M;  2. Move D...