There are about a jillion forum posts and official pages on the web that talk about ORA-12541, the infamous "TNS:No Listener" error. Somewhere around 70% of them appear to be link-farmers who just scrape all the Oracle forums and mailing lists. Virtually all of the pages just refer back to the official definition from Oracle, which says "there's no listener running on the server" and tells you to log in to the server as admin and start up the listener.
Not all that useful, especially if you're not the DBA.
I found a different way that you can get the same error code, even when the listener is running. Special thanks to blogger John Jacob, whose post didn't quite solve my problem, but did set me on the right track.
Here's my situation. My client is a laptop connecting to the destination network through a VPN client. I'm connecting to an Oracle 10g service with 2 nodes. Tnsping reported success, the connection assistant could connect successfully, but sqlplus always reported TNS-12541 TNS:No listener. The listener was fine.
Turning on client side tracing, I saw that the initial connection attempt to the service VIP was successful, but that the server than sends back a packet with the hostname of a specific node to use. Here's where the problem begins.
Thanks to some quirk in the VPN configuration, I can only resolve DNS names on the VPN if they're fully qualified. The default search domain just flat doesn't work. So, I can resolve proddb02.example.com but not proddb02. That's the catch, because the database sends back just the host portion of the node, not the FQDN. DNS resolution fails, but sqlplus reports it as "No listener", rather than saying "Host not found" or something useful like that.
Again, there are a jillion post and articles telling network admins how to fix the default domain search on a VPN concentrator. And, again, I'm not the network admin, either.
The best I can do as a user is work around this issue by adding the IPs of the physical DB nodes to the hosts file on my own client machine. Sure, some day it'll break when we re-address the DB nodes, and I will have long forgotten that I even put those addresses in C:\Windows\System32\Drivers\etc\hosts. Still, at least it works for now.