Skip to content

How to Resolve ORA-12547: TNS:lost contact

  • by

ORA-12547 and TNS-12547

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

ERROR:
ORA-12547: TNS:lost contact

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:\Users\ed>tnsping compdb 100

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:26:03

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

Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary0
1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
OK (20 msec)
OK (10 msec)
OK (20 msec)
OK (0 msec)
OK (10 msec)
...
TNS-12547: TNS:lost contact

C:\Users\ed>

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.

Leave a Reply

Your email address will not be published.