How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to deal with ORA-00020: maximum number of processes (%s) exceeded

I recently had a situation where access to the database was completely blocked because of the infamous error message

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

Facts:

·  The database had processes set to 1000.

·  The Cloud Control agent was spawning hundreds of processes (obviously an error to troubleshoot as a separate action)

·  Connecting through sqlplus with os authentication (sqlplus / as sysdba) didn't work due to the same reason

At that time, the database had to become available to the users again ASAP.

When I have encountered these situations in the past, I have had to kill all the operating system processes and restart the instance. A brut-force method that is not particularly pretty, but sometimes necessary:

for a in $(ps -ef |grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do

>kill -9 $a;

>done

It normally does the job when you really have no other option.
This time however, after having killed all the processes, Oracle still rejected connections to the database using sqlplus:

sqlplus /nolog

 

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014

 

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

 @ SQL> connect /

 ERROR:

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

I then found the page by tech.e2sn.com that showed how to use sqlplus with the "preliminary connection".

Simply by using

sqlplus -prelim "/as sysdba"

I was able to connect and shutdown the database with the abort option.

sqlplus -prelim "/ as sysdba"

 

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014

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

 

 SQL> shutdown abort

 ORACLE instance shut down.

 SQL> exit

 Disconnected from ORACLE

After this point the database could once again be restarted:

sqlplus / as sysdba

 

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:10:38 2014

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

 Connected to an idle instance.

SQL> startup

 ORACLE instance started.

 

 Total System Global Area 2137886720 bytes

 Fixed Size                  2248080 bytes

 Variable Size            1258291824 bytes

 Database Buffers          855638016 bytes

 Redo Buffers               21708800 bytes

 Database mounted.

 Databasen opened.

The article referred to above is worth reading, but in short, the -prelim option will not try to create private session structures in the SGA. This allows you to connect to perform debugging or shutdown operations.