CODEWITHSUNDEEP
oracle11goracle-sqldeveloperoracle12ctnsnamestnsping
sydney
sydney

Reputation: 131

Unable to connect to Oracle 12c from client machine

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:

  1. Win7 x64 with Oracle 12c installed.
  2. Windows Server 2012 R2 (client, yes this setup isn't ideal. will explain why later).
  3. Windows Server 2012 R2 (domain controller)
  4. All machines are VM's and part of a domain.
  5. All VM's are running under ESXI 6.0

I can do the following without any issues:

  1. 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:

  1. 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

Answers (1)

Alex Poole
Alex Poole

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

Related Questions