Sunday, June 09, 2013

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 are several possible reasons 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> 

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.

No comments: