0

I am new to Oracle, attempting to learn about connecting remote clients to an Oracle 12c Database server instance.

SUMMARY: I cannot get TNSPING to work from a client machine to a known good database on a host machine.

DETAILS:

I am hosting the Oracle 12c database on my Windows 7 x64 development machine. I've done numerous tests of connecting to the database from that machine - TNSPING, SQL*Plus, ASP.Net web page, and they all connect to the database no problem. Great!

So I figured a next logical step is to try to connect from another client inside my network. I created a Win7 client using VirtualBox running on the same host (RM08) as the Oracle Database. I completely disabled Windows Firewall on that client machine. I then installed the Oracle Client on that virtual machine. That gives me SQL*Plus and various other tools, and led me through a configuration wizard to create a tnsnames.ora.

When I tnsping from that virtual/client machine to the Oracle host I get "ORA-12541: TNS: no listener". But I am confident the listener is in fact running, because the database works fine when I access from the host machine itself.

Further information:

-- The host machine has IP address 192.168.1.6

-- The virtual/client machine has IP address 10.0.2.15

-- From the client machine I get success when I ping the host

-- From the client machine I can successfully open up various ASP.Net web pages at http://192.168.1.6

-- Windows firewall is completely disabled on BOTH the client and host machines.

On the client machine, the tnsnames.ora file has contents:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

On the client machine, when I issue:

tnsping orcl

I get:

C:\Users\rmvirt>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:11:37

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.1.6)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
TNS-12541: TNS:no listener

If I ping by IP address, I get a similar message, but it mentions EZCONNECT, I assume because I including that option when installing Oracle client:

C:\Users\rmvirt>tnsping 192.168.1.6

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:13:26

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=192.168.1.6)(PORT=1521)))
TNS-12541: TNS:no listener

Finally, if I issue

tnsping badname, I get:

C:\Users\rmvirt>tnsping badname

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 20-APR-2
016 14:15:15

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\rmvirt\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

That at least gives me confidence that my tnsnames.ora file is syntactically correct and being referenced.

I have read this somewhat similar question, with no luck: Using Oracle client to connect to remote database

I have also read the "Testing Connections" section from the "Database Net Services Administrators Guide" at Using Oracle client to connect to remote database.

But I am still stumped.

Can anyone suggest other troubleshooting steps?

Thanks!

Improve this question
4
  • 1
    TNS-12541: TNS:no listener indicates that your listener isn't running on your host (where the oracle DB resides) or the listener has a problem. Check that the OracleOra...TNSListener Windows service is running. If not, start it, if it is running, then c:\> lsnrctl status. Also check the listener log for issues... you may also need to modify %oracle_home%\network\admin\listener.ora. If you modify the listener.ora file, you have to reload it via c:\> lsnrctl reload
    – Kris Johnston
    Apr 20, 2016 at 18:37
  • 1
    What does listener.ora look like? You're not using localhost for the host name, are you?
    – mustaccio
    Apr 20, 2016 at 19:18
  • Thanks to both KrisJohnston and mustaccio, both of you suggested to look at the listener.ora file (which, being new to Oracle, I had no idea even existed - I had been focused on tnsnames.ora). Particular thanks to @mustaccio, who asked "You're not using localhost... are you?". Indeed, I was. When I changed "localhost" to "192.168.1.6" (the IP address of the host machine), bingo, success! But... now on to the next problem. TNSPING is now working, but next I want to try connecting with SQL*Plus. Issuing "sqlplus myuser/mypassword@orcl" gives "TNS:listener does not know of service requested"
    – Rob3C
    Apr 21, 2016 at 10:09
  • Hmm... perhaps not quite as much success as I thought. I notice now that the "tnsping 192.168.1.6" (which works) says "Used EZCONNECT adapter to resolve the alias". So I wonder if that has any bearing on why SQL*Plus will not connect.
    – Rob3C
    Apr 21, 2016 at 10:21

1 Answer 1

Reset to default
0

On the database server, issue the command 'lsnrctl status'. You should get a list of database services known to the listener. There should be one for your database. It is the listed 'service name' that should be the subject of the 'SERVICE_NAME parameter in your tnsnames.ora. There is a more complete explanation of your error at http://edstevensdba.com/oracle-tns/ora-12514-tnslistener-does-not-currently-know-of-service/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.