Connections to the Database lead to ORA-12518 or TNS-12547

ORA-12518, TNS-12547 and ORA-01031 are very bothering errors that we find when everything looks normal. There are numerous reason for this error such as server resource issue, memory limit, listener log file size and oracle executable permission etc.

The Linux error 32 broken pipe indicates that the listener process could not create a dedicated server process to hand over the client request. Recently when I was debugging a connection issue ORA-12537: TNS:connection closed, I found out another funny reason for this error. Usually when you receive ORA-12537 at client side, an error is logged in listener log file at server side. It may look like below. Usually listener log will show error like below.

21-SEP-2020 11:35:16 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DB19B1)(CID=(PROGRAM=sqlplus@snode1)(HOST=snode1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.161)(PORT=58842)) * establish * DB19B1 * 12518
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
Linux Error: 32: Broken pipe
2020-09-21T11:35:22.811678-07:00

This indicate that sever could not start a process and hook your connection to it. If you look at listener status, you may see it is ready and listening on the service you are connecting, some cases UNKNOWN status but that is not to worry. UNKNOWN status is usally because PMON could not register the DB with listener as local_listener was not set and the listener is not at default port(1528 in this case).

oracle$ lsnrctl status LISTENER19B
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-SEP-2020 11:30:02
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=snode2)(PORT=1528)))
STATUS of the LISTENER
Alias LISTENER19B
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 21-SEP-2020 11:29:56
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/193000/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/snode2/listener19b/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=snode2.localdomain)(PORT=1528)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1528)))
Services Summary…
Service "DB19B1" has 1 instance(s).
Instance "DB19B1", status UNKNOWN, has 1 handler(s) for this service…
Service "DB19B1_DGMGRL" has 1 instance(s).
Instance "DB19B1", status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

I configured a static entry in the listener.ora file so listener can find the service in the connect string with right database. My listener configuration looks like this

DB19B1:snode2:oracle$ cat listener.ora
listener.ora Network Configuration File: /u01/app/oracle/product/193000/network/admin/listener.ora
Generated by Oracle configuration tools.
LISTENER19B =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = snode2)(PORT = 1528))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
)
)
SID_LIST_LISTENER19B =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u02/app/oracle/product/193000/)
(SID_NAME = DB19B1)
)
(SID_DESC =
(GLOBAL_DBNAME = DB19B1_DGMGRL)
(ORACLE_HOME = /u02/app/oracle/product/193000/)
(SID_NAME = DB19B1)
)
)

Ideally everything looks good but still my connection from another host gives me ORA-12537: TNS:connection closed

oracle$ sqlplus sys/oracle@DB19B1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 21 11:35:16 2020
Version 19.6.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name: ^C

After doing a lot of trougbleshooting, I found that SQL Net did not like my listener.ora entry for ORACLE_HOME. It has trailing back slash! I removed trailing back slash from listener.ora and restartewd the listener, sure enough it worked!

LISTENER19B =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = snode2)(PORT = 1528))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
)
)
SID_LIST_LISTENER19B =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u02/app/oracle/product/193000) # ORACLE_HOME with no trailing "/"
(SID_NAME = DB19B1)
)
(SID_DESC =
(GLOBAL_DBNAME = DB19B1_DGMGRL)
(ORACLE_HOME = /u02/app/oracle/product/193000) # ORACLE_HOME with no trailing "/"
(SID_NAME = DB19B1)
)
)
DB19A1:snode1:oracle$ sqlplus sys/oracle@DB19B1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 21 11:42:14 2020
Version 19.6.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.1.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.1.0.0
DB19A1:snode1:oracle$

I thought this will be helpful if you run out of all other options while trouble shooting ORA-12537: TNS:connection closed.Something that is very silly to ignore. Keep in mind there are many other reasons for this error.