Friday, June 03, 2011

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 could have increased the value of the processes parameter. 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 >

No comments: