I've read similar posted issues, but I haven't found one that is exactly like mine, There was one other intermittent issue, but not similar circumstances.
We have an Oracle database on a server that holds our Maintenance records. I have a Linked server setup in SQL 2016 on another machine to connect to the Oracle database. 90% or so of the time, its fine. There are times when the Linked server can't connect. Logging on to that machine, and running TNSPING, I get the No Listener error, when that situation occurs. If I wait a short time, TNSPING gets an OK from the server. I can telnet to the Oracle machine but I just get a blank screen. If I hit ENTER, i get back to the DOS prompt. If I run TNSPING after getting back to the DOS prompt, I get the No listener. When I check the Oracle machine at these times, it seems the Listener is running fine and nothing in the listener.log file. On my PC, I have SQLDeveloper installed and it never has an issue connecting, but I'm not sure if it uses the same method or not. I also use Power Bi and I can connect to the Oracle database with the connection tcl-cmms.sps.sps.ourcompanydomain.local.local:1521/orcl.sps.ourcompanydomain.local . We had the Oracle PC upgraded last year and we had to use the orcl.sps.ourcompanydomain.local since then. Before we only needed the orcl. I"m not sure if thats related or not. It seems that if certain calls/operations are done, the Oracle machine does not listen to the machine that is making the calls, until a period of time goes by.
Below is the listener status at the time of a No Listener Error. I have a good TNSping and a bad TNSPing.
Listener Status:
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 08-JUN-2017 10:13:30
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 23-JAN-2017 21:13:20
Uptime 135 days 12 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File D:\app\Administrator\diag\tnslsnr\TCL-CMMS\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TCL-CMMS.sps.companydomain.local)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.sps.companydomain.local" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.sps.companydomain.local" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
Bad ping:
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 08-JUN-2017 10:09:50
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
c:\app\client\Administrator\product\12.2.0\client_2\network\admin\sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.1.109)(PORT=1521)))
TNS-12541: TNS:no listener
Good Ping:
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 08-JUN-2017 10:15:20
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
c:\app\client\Administrator\product\12.2.0\client_2\network\admin\sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.1.109)(PORT=1521)))
OK (530 msec)