Reputation: 131
I have spent 3 days now trying to figure out why I can't connect to my Oracle 12 database from a client machine. I have read lots of articles and Googling around but haven't been able to find a solution yet. I have tried everything possible and know of already. So I am hoping someone maybe able to point me in the right direction.
Here are some details:
I can do the following without any issues:
Logon to oracle server.
ORCL is my oracle isntance (global database identifier).
6.1 run tnsping orcl
6.2 lsnrctl status (up and running)
6.3 stop and restart the listner service from the Windows Services snap-in.
6.4 sqlplus system/xyz@orcl
Connects w/o any problems.
6.5 Oracle SQL Developer can connect to ORCL
6.6 can ping client machine.
However I CAN NOT do the following:
Logon to client machine
7.1 copied tnsnames.ora from oracle server to this client machine and placed under [ORACLE_HOME]\network\admin where it should be. replaced "localhost" with the oracle sserver IP.
7.2 Using sqlplus system/xyz@orcl or Oracle SQL Developer to connect
I get TNS: no listener found.
7.3 can ping oracle server.
7.4 tnsping orcl (failed)
7.4 already disabled all firewalls (domain, private and public) on oracle server. There shouldn't be any issues with firewalls or ports.
No matter what I do, I just can't connect from the client machine. Can someone tell me what I'm doing wrong? As a side note, I was not able to install Oracle 12c on Win2012R2. So I installed on a Win7x64. But eventually all the VM's will run on Windows Server 2012R2. This setup is my home lab.
Thank you!
Upvotes: 0
Views: 7450
Reputation: 191520
replaced "localhost" with the oracle sserver IP
It looks like the listener is only listening on localhost (127.0.0.1); you can confirm that with lsnrctl status
, or with netstat -an | find "1521"
(or your actual port number if you aren't using the default).
It isn't listening on the server's external IP address, so when you try to connect to post 1521 (or whatever you have configured) on that IP there is nothing there listening - which is why you get "no listener found".
You need to modify your listener.ora
to either listen to both localhost and the server IP address, or to only listen to the external address. But the latter has side-effects - your existing connections and tnsnames.ora
entries would need to change to refer to that address (or, even if it is static, a DNS name that resolves to that address), and your database may need to be modified so it knows the listener address to register against, via the LOCAL_LISTENER
initialisation parameter. After changing the listener.ora
you will need to bounce the listener, and you can then check netstat
again.
Upvotes: 0