ORA-12541 TNS :no listener on 1 specific remote database, but not on others
Some context: I'm setting up an environment to run some Ruby code our Lead wrote to connect to some remote Databases. I'm using Win10 and have WSL installed. I have installed SQL*Plus, docker, and Ruby, and am sure I have installed all the dependent gems (actually at this point I know for a fact its not a ruby problem). I'm using Docker to use a pre-built image of an Oracle Database, and I have populated it with data.
Obfuscated company DB information:
'Broken' DB (TNS no listener)
- Username: W-WORK-DEV
- Password: password
- Host: host1
- Port: 999
- SID: W-WORK-DB.tech.company
'Working' DB (can connect just fine)
- Username: C-OLD-DEV
- Password: password
- Host: host2
- Port: 999
- SID: C-OLD-DB.tech.company
Please note the names I have provided here are purely for obfuscation, they do not represent anything about age or even config. They are essentially the same, just different databases on different hosts. Also note this is a corporate company and would have a lot of base infrastructure for their internal network.
The issue I am having is that with the specific database I am working on, I cannot connect to it. I connect to the company intranet via a VPN (I work offsite) and have issues connecting to WORK-DB.tech.company, though I can connect to C-OLD-DB.tech.company. Methods I have tried to connect with:
- Oracle SQL Developer
I have inputted all the correct jdbc strings and username / password. Have even asked other people to attempt to connect (at least 3 others). They can all access C-OLD-DB as well as W-WORK-DB. When using this method, I get The network adapter could not establish the connection.
Side note: I'm fairly sure I have connected to the DB just fine in the past, though I can't be certain because I'm fairly new to this team and have only accessed it once, if that.
- SQL*Plus via WSL
I installed SQL*Plus because I think Ruby was accessing it in some capacity when running the script. I got to the point where the script was able to connect to my local docker Database, but when it tried to connect to the remote database, it would return TNS: no listener. I attempted this using sqlplus as well and got the same error.
Example:
- sqlplus C-OLD-DEV/password@host2:999/C-OLD-DB.tech.company
- This command works and connected to the DB jsut fine.
- sqlplus W-WORK-DEV/password@host1:999/W-WORK-DB.tech.company
- This command gave me TNS: no listener.
During my research I had found mentions about tnsnames.ora, sqlnet.ora, and listener.ora. I have found relevant files around the company intranet and attempted to use them in my own personal environment to no avail (one of the sqlnet.ora files actually stopped me from being able to connect to any) I found mention about a config/database.yml file related to ruby, but our code already has the database information set up and I seem to be getting to the same solution as the script when trying to manually connect via sqlplus.
Here are some relevant environment variables I have set up when doing these installs. I'm thinking that at some point when trying to set up my environment, I messed up some config that changed the way sqldeveloper or sql plus would connect to that database.
$ echo $ORACLE_HOME: /usr/lib/oracle/12.2/client64/
$ echo $TNS_ADMIN: /usr/lib/oracle/12.2/client64/network/admin
$ echo $LD_LIBRARY_PATH: /usr/lib/oracle/12.2/client64/lib
I hope I have provided all the information I have come across and have explained clearly what my issue is, if you have any questions feel free to comment about it so I can clear it up. Thank you for any and all your help.
Answer
So my problem ended up being a 'firewall/port' issue. In the end i found out that specific port I was connecting to was blocked, but I thought it was only on my system. Turns out the VPN I'm using was the issue blocking that port. Company has a Primary Data Center and a Secondary one, and apparently the SDC VPN was blocking that port from me. I switched to using the PDC VPN and it worked.