0

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!

sydney
  • 131
  • 8
  • 19

1 Answers1

0

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.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I do recall seeing 127.0.0.1 when I ran the lsnrctl status but when i checked my listener.ora, sqlnet.ora and tnsnames.ora, i didn't see this IP and didn't know where that was coming from. Sounds like you are right on target about my issue. I will post the result of lsnrctl status coming when I get home. I may need a bit of hand hold here. Thanks! – sydney Jun 03 '16 at 13:41
  • I think the listener defaults to localhost if no address is specified; or it may just refer to localhost rather than 127.0.0.1. You may also have an entry in your hosts file with a different name that still resolves to 127.0.0.1, but that's less likely. – Alex Poole Jun 03 '16 at 13:45
  • Can you post a sample of what the “listener.ora” should look like to resolve my issue? In your earlier reply, you indicated I need to configure my “listener.ora” so it listens on both localhost and external IP. What exactly do I need to do here? I already backed up the listener.ora, sqlnet.ora and tnsnamesora files from the initial oracle installation before I touched them when I was trying to fix my issues. So I will restore these and apply the changes you suggested. Thanks so much! – sydney Jun 03 '16 at 14:06
  • There's already a link to an earlier answer that shows setting two addresses in your `listener.ora`. TBH I'm wondering if this should be closed as a duplicate... – Alex Poole Jun 03 '16 at 14:10
  • Sorry didn't realize it was a url. I just checked. It looks all I have to do is to add a new ADDRESS line with my external IP for the oracle server. I will test this out when I get home. thanks again. – sydney Jun 03 '16 at 14:45
  • Alex, thanks for your suggestion. I got it to work now. – sydney Jun 06 '16 at 16:25