Ora 12547 tns lost contact error

Issue / Oracle error

sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
ERROR:
ORA-12547: TNS :lost contact when try to connect to Oracle.

Issue description

I saw that TNS connection issue along with ORA-12547 Oracle error several times, usually when trying to connect to Oracle database server on Unix / Linux host with an OS user that does not belong to oinstall group (Oracle binaries owner group).  In this case, interesting enough that local TNS connection to database (when using tnsnames alias) works fine:

sqlplus scott/tiger@orcl

and only local BEQ protocol connection through an Oracle error ORA-12547:

sqlplus scott/tiger
ERROR:
ORA-12547: TNS:lost contact

There can be different reasons of this issue, but usually the problem is in wrong privileges/ownership of some Oracle binaries located in $ORACLE_HOME/bin directory. Perhaps, Oracle database binaries were installed / linked not correctly or something or somebody has changed the files’ ownership.

Solution

1. Ensure the DB is up and running and you can connect locally AS SYSDBA to the database using Oracle binaries owner (usually oracle:oinstall Unix / Linux user). If it does not work, probably you encounter a different problem.
2. Check privileges of an Oracle file on Unix / Linux host where database is running:

cd $ORACLE_HOME/bin
ls -ltr oracle
-rwxr-xr-x    1 oracle   oinstall       136803483 Mar 16 20:32 oracle

1. Change permissions as below:

chmod 6751 oracle
ls -ltr oracle
-rwsr-s--x    1 oracle   oinstall       136803483 Mar 16 20:32 oracle


1. Usually the above operation should fix the issue but I suggest continue investigating privileges of other files to avoid further possible problems.  As the matter of fact these special rights of Oracle binaries are set by $ORACLE_HOME/root.sh script on Unix / Linux after the Oracle installation. You can run the script again as root user, if you see more files have wrong permissions or ownership (make backup before, just in case). For that I provide as an example 2 lists of oracle binary files in $ORACLE_HOME/bin directory with correct rights below.

Oracle 11gR2 (11.2.0.3) on Linux Redhat 6.x

-rwsr-s--x.  1 oramcelt oinstall 221227204 Aug 14 12:12 oracle
-rwsr-s--x.  1 oramcelt oinstall 221189362 Aug 14 11:03 oracleO
-rwsr-x---.  1 root     oinstall     71758 Sep 17  2011 oradism
-rws--x---.  1 root     oinstall     44437 Aug 14 11:02 nmo
-rws--x---.  1 root     oinstall     66324 Aug 14 11:02 nmhs
-rws--x---.  1 root     oinstall     34166 Aug 14 11:03 nmb
-rwsr-x---.  1 root     oinstall     43402 Aug 14 11:03 jssu
-rwsr-x---.  1 root     oinstall     43402 Aug 14 11:03 jssu
-rwsr-x---.  1 root     oinstall   1249349 Aug 14 11:03 extjob
-rwsr-s--x.  1 oramcelt oinstall     65799 Aug 14 11:02 emtgtctl2


Oracle 11gR2 (10.2.0.3) on Linux Redhat 4.x

-rwsr-x---  1 root   oinstall    64850 Nov  6  2007 extjob
-rwsr-s---  1 root   oinstall    18376 Mar  1  2006 nmb
-rwsr-s---  1 root   oinstall    19566 Mar  1  2006 nmo
-rwsr-s--x  1 oracle oinstall 95118102 Nov  6  2007 oracle
-r-sr-s---  1 root   oinstall    14456 Nov 15  2006 oradism

Good luck and feel free to add solutions of the same issue in your comments.

Enjoyed this article? Please share it with others using the social site of your choice:

ORA-12547 is the same as TNS-12547, they all indicate that the ability of communication between the client and the listener is interrupted and lost by something, mostly, it’s because of poor network condition.

SQL*Plus

SQL> conn hr/hr@orcl

Solutions

There’re several possible causes when you connect to the database.

Binaries Permission

It might be caused by wrong permission when executing Oracle binaries.

[oracle@test ~]$ cd $ORACLE_HOME/bin
[oracle@test bin]$ ll oracle
-rwxr-x--x 1 oracle oinstall 443586008 Dec 16  2020 oracle

As you can see, the permission set is 0751 which is not proper to use. To make it back to work, we should add SUID and SGID bit to it.

[oracle@test bin]$ chmod 6751 oracle

Or this:

[oracle@test bin]$ chmod ug+s oracle

Let’s see the result.

[oracle@test bin]$ ll oracle
-rwsr-s--x 1 oracle oinstall 443586008 Dec 16  2020 oracle

Relink OS

Oracle binaries may not have linked correctly in this platform, we should relink them back in order to stick with OS.

[oracle@test ~]$ relink all

This could happen after a patching or upgrading.

TNSPING

Let’s see how we reproduce TNS-12547.

C:Usersed>tnsping compdb 100

We consecutively tnsping a remote listener 100 times to see its status change.

Solution

The listener could be hang or not responsive so that caused TNS-12547. You should restart (stop + start) current listener or try to fix it. Otherwise you should go for anyone of backup listeners to keep the continuity of database service.

For more connection troubleshooting, you may refer to Oracle 19c Net Services Administrator’s Guide: 15 Testing Connections.

In general, when logged in as an Oracle installation user, by setting two environment variables ORACLE_SID and ORACLE_HOME, local bequeath connection «sqlplus / as sysdba» works absolutely fine.

But in this specific case it was throwing ERROR: ORA-12547: TNS:lost contact.

There are many useful links available but none were helpful in my particular scenario.

Also, once, ORACLE_BASE was set, it worked like a charm.

It took me reading through 2000 lines of strace and a whole day of debugging to figure out the issue. 

So, here I am sharing my findings, maybe it will save someone else some time

ISSUE MANIFESTATION:

When attempted to connect by setting basic environment variables, connect request failed with ORA-12547

[dev01] : /home/abhimanyusuri> export ORACLE_SID=DEV01
[dev01] : /home/abhimanyusuri> export ORACLE_HOME=/db/oracle/product/12.2/DBHOME
[dev01] : /home/abhimanyusuri> export PATH=${PATH}:${ORACLE_HOME}/bin:.
[dev01] : /home/abhimanyusuri> which sqlplus
/db/oracle/product/12.2/DBHOME/bin/sqlplus
[dev01] : /home/abhimanyusuri> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 11 16:15:11 2018

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

ERROR:
ORA-12547: TNS:lost contact


Enter user-name: ^C
[dev01] : /home/abhimanyusuri>

But, it was known that, when a custom «.env» is used to set environment, everything works fine.

So, rather than, setting up the environment by sourcing an «env» file, I decided to go with setting variables defined in it, one by one.

WORKAROUND :

Upon setting the very first one i.e. export ORACLE_BASE=/db/oracle, along with the variables mentioned in the above snippet, the connection was successfully established. But why not, without it?

[dev01] : /home/abhimanyusuri> export ORACLE_BASE=/db/oracle
[dev01] : /home/abhimanyusuri> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 12 14:02:42 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

It is also established from observed behavior that, binaries are in place, intact with desired permissions, otherwise, it would have never worked.

DEBUGGING ROUND1: 

I looked through the following areas and confirmed everything is as required/suggested in metanotes

• Checked SQLNET.EXPIRE_TIME.
• Checked permissions on file ${ORACLE_HOME}/bin/sqlplus —>  6751
• Checked permissions on file ${ORACLE_HOME}/bin/oracle  —>  6751
• Performed relinking as suggested in metalink : ${ORACLE_HOME}/bin/relink all
• Checked /proc/<<PID_PMON>>/environ to rule out if instance was started with wrong env settings.
• Restarted DB instance
• Collected «STRACE» and stared at the o/p for few hours
• Compared straces for successful and unsuccessful connection

NO HELP

At this point, being completely clueless about what is happening, decided to start from scratch. 

DEBUGGING ROUND2:

1. Login to the server via Oracle installation user (same user as before).

2. Run oraenv

Upon running oranev, I discovered something strange «The Oracle base has been set to /db/oracle/product/12.2/DBHOME».

Rather than setting $ORACLE_BASE to /db/oracle, it was being set to $ORACLE_HOME.

But connection did work fine post this.

Change approach: Need to shift focus to $ORACLE_BASE.

To further investigate, executed oraenv in debug mode i.e. «sh -x /usr/local/bin/oraenv», which lead to below mentioned code piece

+ ORABASE_EXEC=/db/oracle/product/12.2/DBHOME/bin/orabase
+ '[' x '!=' x ']'
+ OLD_ORACLE_BASE=
+ '[' -w /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml ']'  
+ '[' -f /db/oracle/product/12.2/DBHOME/bin/orabase ']'
+ '[' -x /db/oracle/product/12.2/DBHOME/bin/orabase ']'
++ /db/oracle/product/12.2/DBHOME/bin/orabase
+ ORACLE_BASE=
+ '[' x '!=' x ']'
+ '[' '' '!=' true ']'
+ echo 'The Oracle base has been set to '
The Oracle base has been set to 
+ export ORACLE_BASE
+ '[' x = x ']'
+ '[' '' '!=' true ']'
+ echo 'Resetting ORACLE_BASE to its previous value or ORACLE_HOME'
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
+ '[' '' '!=' '' ']'
+ ORACLE_BASE=/db/oracle/product/12.2/DBHOME
+ '[' '' '!=' true ']'
+ echo 'The Oracle base has been set to /db/oracle/product/12.2/DBHOME'

Checked content and permissions of file «oraclehomeproperties.xml», everything seems in place, here is an excerpt 

[dev01] : /home/abhimanyusuri> ls -ltr /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml
-rw-r-----. 1 abhimanyusuri dba 545 Feb 19  2018 /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml

cat /db/oracle/product/12.2/DBHOME/inventory/ContentsXML/oraclehomeproperties.xml
..
<PROPERTY NAME="ORACLE_BASE" VAL="/db/oracle"/>
..

Still, execution of «orabase» was not returning anything

++ /db/oracle/product/12.2/DBHOME/bin/orabase
+ ORACLE_BASE=
+ '[' x '!=' x ']'
+ '[' '' '!=' true ']'
+ echo 'The Oracle base has been set to '
The Oracle base has been set to

Thought, to peek into STRACE o/p one more time. One big difference between strace o/p from successful and unsuccessful connection was logging in «sqlnet.log» and «log.xml»

Here is an excerpt from both log files at the time of failure

SQLNET.LOG was filled with below error

Fatal NI connect error 12547, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/db/oracle/product/12.2/DBHOME/bin/oracle)(ARGV0=oracleDEV01)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DETACH=NO))(CONNECT_DATA=(CID=(PROGRAM=sqlplus)(HOST=DEV01)(USER=abhimanyusuri))))

VERSION INFORMATION:
TNS for Linux: Version 12.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
Time: 11-SEP-2018 23:08:25
Tracing not turned on.
Tns error struct:
ns main err code: 12547

TNS-12547: TNS:lost contact
ns secondary err code: 12560
nt main err code: 517

TNS-00517: Lost contact
nt secondary err code: 32
nt OS err code: 0

diag/rdbms/alert/log.xml had below message

</msg>
<msg time='2018-09-11T23:08:25.738-07:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='dev01'
host_addr='xxxxxxxxxxx' pid='220600'>
<txt>Oracle Clusterware infrastructure fatal error in oracle (OS PID 220600_139933757714208): Internal error (ID (:CLSB00107:)) - Error -1 (ORA-08275) determining Oracle base
</txt>
</msg>

And again, error related to oracle base.

Please note, this is a single instance database with no ASM/grid, so error is little misleading.

At this point, I had an intuition, that whatever it is, had to be related with $ORACLE_BASE.

So, decided to hammer «oraenv», using ${ORACLE_TRACE} 

[dev01] : /home/abhimanyusuri> export ORACLE_TRACE=T
[dev01] : /home/abhimanyusuri> . oraenv
++ N=
++ C=
++ grep c
++ echo 'c'
++ N=-n
++ '[' 0 = 0 ']'
..
..
ORACLE_SID = [abhimanyusuri] ? ++ read NEWSID
DEV01
++ case "$NEWSID" in
++ ORACLE_SID=DEV01
..
++ ORACLE_BASE=/db/oracle/product/12.2/DBHOME
++ '[' '' '!=' true ']'
++ echo 'The Oracle base has been set to /db/oracle/product/12.2/DBHOME'
The Oracle base has been set to /db/oracle/product/12.2/DBHOME
++ export ORACLE_BASE

Since, all environment variables had been set, decided to go for another iteration, without passing any argument value at prompt and let oracle decipher.

BULLS-EYE, «orabasetab»

[dev01] : /home/abhimanyusuri> . oraenv
+ . oraenv
++ SILENT=
++ '[' 0 -gt 0 ']'
++ case ${ORACLE_TRACE:-""} in
++ set -x
++ N=
..
..
++ ORASID=DEV01
++ echo -n 'ORACLE_SID = [DEV01] ? '
ORACLE_SID = [DEV01] ? ++ read NEWSID

++ case "$NEWSID" in
++ ORACLE_SID=DEV01
++ export ORACLE_SID
+++ dbhome DEV01
..
..
++ case "$OLDHOME" in
++ case "$PATH" in
+++ echo /usr/local/symlinks:/usr/local/scripts:---
#orabasetab file is used to track Oracle Home associated with Oracle Base
..
..
..
..

«ORABASETAB», I had never heard of it before. 

Circled back to o/p of strace and found the reference in there as well. 

Below is an excerpt from strace log

lstat("/etc/orabasetab", 0x------) = -1 ENOENT (No such file or directory)
open("/etc/orabasetab", O_RDONLY) = -1 ENOENT (No such file or directory)
lstat("/db/oracle/product/12.2/DBHOME/install/orabasetab", {st_mode=S_IFREG|0660, st_size=129, ...}) = 0
open("/db/oracle/product/12.2/DBHOME/install/orabasetab", O_RDONLY) = 9
fstat(9, {st_mode=S_IFREG|0660, st_size=129, ...}) = 0
fstat(9, {st_mode=S_IFREG|0660, st_size=129, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x-----
read(9, "#orabasetab file is used to trac"..., 4096) = 129
read(9, "", 4096)                = 0
close(9)                         = 0

ROOT CAUSE IDENTIFICATION AND FIX:

Upon, checking the content of «orabasetab» file, it was discovered that file had a wrong mapping of ORACLE_HOME and ORACLE_BASE.

[dev01] : /home/abhimanyusuri> cat /db/oracle/product/12.2/DBHOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/db/oracle/product/12.2/DB_HOME_DOESNT_EXIST:/db/oracle:OraDB12Home1:N:

Commented old entry and entered a new line with the right mapping.

Back to business. 

[dev01] : /home/abhimanyusuri> export ORACLE_HOME=/db/oracle/product/12.2/DBHOME
[dev01] : /home/abhimanyusuri> export ORACLE_SID=DEV01
[dev01] : /home/abhimanyusuri> env|egrep -i "ora|tns"
ORACLE_SID=DEV01
ORACLE_HOME=/db/oracle/product/12.2/DBHOME
[dev01] : /home/abhimanyusuri> PATH=${ORACLE_HOME}/bin:${PATH}:
[dev01] : /home/abhimanyusuri> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 12 22:50:51 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Reference to Oracle metanote for ORABASETAB:

https://docs.oracle.com/en/database/oracle/oracle-database/18/ntdbi/win-orabasetab.html

Thanks,

Suri