Another Possible Cause of ORA-12537

I have changed the names of the database and server in this example.

I had a new Oracle 11.1 database, which I was trying to access remotely using an Oracle 11.2 listener:

 

Solaris > sqlplus /@XXXXXX

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 6 18:25:48 2012

 

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

 

ERROR:

ORA-12537: TNS:connection closed

 

The following errors appeared in the listener log:

 

<msg time='2012-11-06T18:25:48.133+00:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='xxx-xxxxxx-xxxx'

host_addr='99.99.9.999'>

<txt>TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    Solaris Error: 32: Broken pipe

</txt>

</msg>

 

There is more than one possible reason for this. On this occasion the entry for the new database in the listener parameter file was as follows:

 

(SID_DESC =

 (SID_NAME = XXXXXX)

 (ORACLE_HOME = /oracle/app/oracle/product/11.1.0)

)

 

I added an extra line like this:

 

(SID_DESC =

  (SID_NAME = XXXXXX)

  (ORACLE_HOME = /oracle/app/oracle/product/11.1.0)

  (ENVS="LD_LIBRARY_PATH=/oracle/app/oracle/product/11.1.0/lib")

)

 

I restarted the listener:

 

Solaris > lsnrctl reload listener_11

 

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 06-NOV-2012 18:40:10

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-xxxxxx-xxxx)(PORT=1529)))

The command completed successfully

Solaris >

 

Then I was able to connect to the database remotely:

 

Solaris > sqlplus /@XXXXXX

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 6 18:42:27 2012

 

Copyright (c) 1982, 2009, 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>

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 >