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 >