sqlplus -prelim

If all the available processes in a database are used up, you will get an ORA-00020 when you try to login. The best way round this is to get some people to log out. However, this may not be possible e.g. if people have logged in from some application then killed it, leaving their sessions alive in the database but with no way to get back to them and end them tidily.

 

Oracle 11: sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:40 2013

 

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

 

ERROR:

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

 

Enter user-name: ^C

Oracle 11:

 

An alternative is to login with the –prelim option. This does not allow you to run any SQL but it does let you close the database:

 

Oracle 11: sqlplus -prelim / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:59 2013

 

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

 

SQL> select sysdate from dual;

select sysdate from dual

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 0

Session ID: 0 Serial number: 0

 

SQL> shutdown abort

ORACLE instance shut down.

SQL>

Before opening the database again, you might consider increasing the processes parameter to reduce the chance of this happening again.

One Possible Cause of ORA-12537

Some testers had a problem connecting to a database remotely. I tried it myself and saw the following message:

 

SQL> conn system@test11

Enter password: ******

ERROR:

ORA-12537: TNS:connection closed

 

Warning: You are no longer connected to ORACLE.

SQL>

I logged on to the server hosting the database and tried to connect from there. This showed a different error message:

 

SOLARIS > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 26 12:10:41 2011

 

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

 

ERROR:

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

I guessed that this message indicated the true cause of the problem so I looked in the database's server parameter file. The processes initialisation parameter specifies the maximum number of operating system processes allowed for the database and it was set to 50:

 

*.processes=50

 

Another way to check this value is to wait for another session to complete, connect to the database and look in v$parameter as follows:

 

SOLARIS > sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 26 12:39:28 2011

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> col value format a10

SQL> select value from v$parameter

  2  where name = 'processes';

 

VALUE

----------

50
 

SQL>
 

Once I had a session running in SQL*Plus, I tried to increase the value of the processes parameter but it's not one that you can change dynamically:

SQL> alter system set processes = 100;

alter system set processes = 100

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

SQL>

...  so you have to do it in the server parameter file then bounce the database (I will cover this in another post).

Another option, particularly if the problem is only affecting a test database, is to suggest to the users that they work together to make better use of the resources available to them.

I double checked the diagnosis by counting the number of processes the database had in the operating system:

 

SOLARIS > ps -ef|grep TEST11|wc -l

      47

SOLARIS >

 

At this point the number of processes had gone down so I could connect to the database:

 

SQL> conn system@test11

Enter password: ******

Connected.

SQL>

 

And (in a separate server session) I saw the number of processes go up again:

 

SOLARIS > ps -ef|grep TEST11|wc -l

      48

SOLARIS >