Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1663
  • Last Modified:

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.
0
Gadsden Consulting
Asked:
Gadsden Consulting
  • 3
  • 3
2 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now