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

Gadsden Consulting
Gadsden Consulting used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
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.
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

Most Valuable Expert 2012
Distinguished Expert 2018

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

My guess is you have several Oracle products installed in several ORACLE_HOMES?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 :-)
Most Valuable Expert 2012
Distinguished Expert 2018
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.
Gadsden ConsultingIT Specialist

Author

Commented:
ok, thanks for the expln.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial