ORA-12547: TNS:lost contact in Oracle 19c

Today, we faced an issue with one of recently upgraded database where sqlplus “/ as sysdba” on node 1 was working and sqlplus “/ as sysdba” was not working on node 2. It was giving following error –

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 12 22:36:20 2021
Version 19.12.0.0.0

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

ERROR:
ORA-12547: TNS:lost contact

Problem Statement – ORA-12547: TNS:lost contact

On Node 2, sqlplus “/ as sysdba” has problem and end up as ORA-12547: TNS:lost contact locally. We have tried connecting remotely through application server, Node 2 were accepting my connection to node 2.

Investigation we did for ORA-12547: TNS:lost contact

Since it was a fresh installation from our side, we checked if our binaries had any problem?

ls -l $ORACLE_HOME/bin/oracle

We compared the permission on both the nodes, but it was same on both the nodes.

Correct Permission should be as follow –

-rwsr-s--x 1 ora12203 asmadmin 592407775 Oct 29 20:30 oracle

Please provide following permission if these are not the permission

$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle

$ORACLE_HOME/rdbms/lib/config.o

Next file we checked, to see if that’s of non-zero size. Both nodes had same size config.o
If size of the file is zero then you can follow following steps –

$ cd $ORACLE_HOME/rdbms/lib
$ mv config.o config.o_safe_12122021

Relink the binaries

relink oracle

Since remote connection were allowed I was sure that Listener configuration is not a problem for me. It’s environment variable / miss configuration which is causing the problem.

Environment Variable

I did check following on both the nodes –

echo $ORACLE_HOME
echo $PATH
echo $LD_LIBRARY_PATH

Unfortunately these parameters were same on both the nodes.

Database Registration in Cluster

I had no clue till now what is the issue, but just to check how database is registered in cluster I fired –

srvctl config database -d TEST

I got an output as follows ->

$srvctl config database -d TEST
Database unique name: TEST
Database name:
Oracle home: /DBHOME/app/product/19c/db1/
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oper
Database instances: TEST1,TEST2
Configured nodes: testhost1,testhost2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

With first look at the output, I didn’t notice the problem here, After going through the output once again, we noticed following

Oracle home: /DBHOME/app/product/19c/db1/      ===============> Additional "/" at the end of the path.
Oracle user: oracle
Spfile:

Just to Confirm if hunch is Correct – I did following Test Case – >

On Node 2 

$ export ORACLE_HOME=/DBHOME/app/product/19c/db1/
$ export PATH=$PATH:$ORACLE_HOME/bin
$ export ORACLE_SID=TEST2
$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 12 23:23:48 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
$

Voila!

Solution – To resolve ORA-12547: TNS:lost contact

We need to change the Oracle Home to resolve the issue –

srvctl stop database -d TEST
srvctl modify database -d TEST -o /DBHOME/app/product/19c/db1
srvctl config database -d TEST
srvctl start database -d TEST

After that we took the new session of the database on node 2 to crosscheck –

$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 13 00:47:26 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE

Reference –

Sqlplus Connection To ASM Fails With: ORA-12547: TNS:lost Contact (Doc ID 2620246.1)
Troubleshooting ORA-12547 TNS: Lost Contact (Doc ID 555565.1)