We help IT Professionals succeed at work.

TNS listener not working for client tools ("no listener"), but Sql Plus command window is ok

1,963 Views
Last Modified: 2014-08-07
I can log on to sql plus successfully from the command line, but when attempting to connect with various client tools, I keep getting "No listener".
no listener Sql Plus windowsOracle Forms no listener
But I can connect via Sql Plus in command window
Sql Plus command window connect
The Listener is started in services, and here is the status (points to 192.168.1.9, Orcl shows ready)
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-AUG-2014 14:35:56

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                06-AUG-2014 15:12:27
Uptime                    0 days 23 hr. 23 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\app\Stephen\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         e:\app\stephen\diag\tnslsnr\Stephen-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.9)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Open in new window


so it's registered ok.

Notes:
- this is a follow-on from How to connect to PC #1 (running Oracle) from PC #2 (running Oracle client), where I was trying to connect from PC #2  to PC #1. The solution was to specify the local IP address (192.168.1.9) in the tnsnames file and in listener.ora, and to register. All was good, thus allowing me to connect from remote PC's

- just today I set up Oracle client on two different PC's, and I can connect via Sql Plus to scott/tiger@orcl

- so in circling back to regression testing on PC #1, something's wrong with the Listener when trying to connect with client tools :-(. Although I can connect from Sql Developer . . .

Also, I checked my PATH and it's good.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gadsden ConsultingIT Specialist

Author

Commented:
here's my tnsnames file
# tnsnames.ora Network Configuration File: E:\app\Stephen\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Open in new window


Here's my listener.ora file
# listener.ora Network Configuration File: E:\app\Stephen\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Stephen\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Stephen\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\Stephen

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
That is your tnsnames.ora file from 'which' ORACLE_HOME?

My guess is you have several Oracle products installed in several ORACLE_HOMES?
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv, thanks . . .

>>Check for multiple oracle homes.
-- bingo ! I didn't change the tnsnames in my "DevSuiteHome_1" location. Now I can connect, yea . . .

>>But I can connect via Sql Plus in command window. That is a DIRECT connection, not a 'remote' connection.
-- are the client applications considered "remote"

but I got it :-)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gadsden ConsultingIT Specialist

Author

Commented:
ok, thanks for the expln.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.