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

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.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Check for multiple oracle homes.

Oracle product installs can create new ORACLE_HOME's.  Each one of these needs it's own tnsnames.ora file.

In the first example using sqlplusw (Windows based sqlplus), what ORACLE_HOME did that run from?  Does that home have correct entries in the tnsnames.ora file?

>>But I can connect via Sql Plus in command window

That is a DIRECT connection, not a 'remote' connection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor 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

0
slightwv (䄆 Netminder) Commented:
That is your tnsnames.ora file from 'which' ORACLE_HOME?

My guess is you have several Oracle products installed in several ORACLE_HOMES?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gadsden ConsultingIT SpecialistAuthor 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 :-)
0
slightwv (䄆 Netminder) Commented:
>>-- are the client applications considered "remote"

Oracle has two types of connections:
bequeath and networked.

A bequeath is a direct pipe into the database and no networking is involved.

You can do either from the database server itself.  You cannot do a bequeath connection from a remote machine.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
ok, thanks for the expln.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.