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.