SQLPLUS/SQLDeveloper Failed to connect to the database Instance in the Node1:ORA-12537: TNS:connection closed (Doc ID 2536583.1)

Last updated on JANUARY 30, 2020

Applies to:

Symptoms

There are total 6 nodes. All the six nodes VIP's are online.

Database dbname is only running on node-1 and node-2

After patching, when customer tried to connect using the SQLDEVELOPER using the SCAN name, intermittently its failing.

But when we connect directly node-1-vip its always failing with the below error. But when connect to node2-vip connection is always successful.

Using SCAN - SCAN-NAME:1621

Intermittent issue.

** Using Node1-vip - Always FAIL.

[Node-1[oracle]_dbname1> tnsping dbname1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2019 03:58:30

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Node1-vip)(PORT = 1724)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbname)))

OK (0 msec)

[Node-1[oracle]_dbname1> sqlplus system@dbname1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 29 03:58:46 2019

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

Enter password:

ERROR:

ORA-12537: TNS:connection closed <<<<<<<<<<<<<<<<<<<<

But when connect to Node2-vip connection is always successful.

[Node-2[oracle]_dbname1> tnsping dbname1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2019 03:46:46

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Node2-vip)(PORT = 1724)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbname)))

OK (140 msec)

[Node-2[oracle]_dbname1> sqlplus system@dbname1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 29 03:46:57 2019

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

Enter password:

Last Successful login time: Mon Apr 29 2019 03:36:39 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

dbname> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

[grid@Node-1]$ lsnrctl status lstndbname

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 29-APR-2019 03:30:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSTNdbname)))

STATUS of the LISTENER

------------------------

Alias LSTNdbname

Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date 27-APR-2019 20:25:09

Uptime 1 days 7 hr. 5 min. 4 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/18.1.0.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/NodeName/lstndbname/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LSTNdbname)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.nnn.nnn.128)(PORT=1724)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.nnn.nnn.18)(PORT=1724)))

Services Summary...

Service "dbname" has 1 instance(s).

Instance "dbname1", status READY, has 1 handler(s) for this service... <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Service "tsmitipx" has 1 instance(s).

Instance "tsmitipx1", status READY, has 1 handler(s) for this service...

Service "tsmitipxXDB" has 1 instance(s).

Instance "tsmitipx1", status READY, has 1 handler(s) for this service...

The command completed successfully

[grid@Node-1]$

[grid@Node-1]$ srvctl status vip -node Node-1

VIP 10.93.128.18 is enabled

VIP 10.93.128.18 is running on node: Node-1

$ crsctl stat res -t |grep vip

ora.NodeName.vip

1 ONLINE ONLINE NodeName01adm STABLE

ora.NodeName02adm.vip

1 ONLINE ONLINE NodeName02adm STABLE

ora.NodeName03adm.vip

1 ONLINE ONLINE NodeName03adm STABLE

ora.NodeName04adm.vip

1 ONLINE ONLINE NodeName04adm STABLE

ora.NodeName05adm.vip

1 ONLINE ONLINE NodeName05adm STABLE

ora.NodeName06adm.vip

1 ONLINE ONLINE NodeName06adm STABLE

dbname> sho parameter list

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

listener_networks string

local_listener string (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=10.nnn.nnn.18)(PORT=1724)))

remote_listener string agetedq1-scan:1621

Changes

 Apply YUM and BP patching on DB nodes

Cause