Wednesday, June 11, 2014

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.
  • 5 comments: