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

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

Alert log:

ORA-00020: maximum number of processes (1000) 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.
Mon Dec 29 22:48:29 2014
ORA-00020: maximum number of processes (1000) 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.
Mon Dec 29 22:52:32 2014
ORA-00020: maximum number of processes (1000) 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.

Solution:

If you cannot connect to the sqlplus traditinal method use prelim option. This is one of the bypass option to connect the sqlplus.

sqlplus -prelim is a backdoor method to connect the sqlplus where no othere method not supporting to create new session. It will connect only SGA and it will not create any new session.

$ sqlplus -prelim

oradb@CGABCDRADBOP01:/oracle_home/app $ sqlplus -prelim

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 30 13:04:12 2014

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

Enter user-name: / as sysdba
 
SQL>

Bounce the DB and  you can increase the processes with below options.


processes=x
sessions=x*1.1+5
transactions=sessions*1.1
sql> show parameter sessions

sql> show parameter processes
sql> show parameter transactions
sql> alter system set processes=500 SCOPE=SPFILE; 
sql> alter system set sessions=555 SCOPE=SPFILE; 
sql> alter system set transactions=610 SCOPE=SPFILE; 

and Restart the database.

Below parameters values also must be changed.....

sql>alter system set open_cursors=1500 scope=both;
sql>alter system set processes=3000 scope=spfile;
sql>alter system set job_queue_processes=4 scope=both;
sql>alter system set undo_retention=10800 scope=both;
sql>alter system set max_shared_servers=50 scope=both;
sql>alter system set db_files=2000 scope=spfile; 
sql>alter system set sessions=3305 SCOPE=SPFILE;  
sql>alter system set transactions=3635 SCOPE=SPFILE; 

Issue get fixed.

1. After you connect with “-prelim” mode, it’s also possible to use oradebug commands to examine the situation. 

2. It will not create new session and connecting only SGA. So you can performance all the oradebug utilities to analyse the issue.