2

I have Oracle 11g (11.2.0.1.0) running on Windows 7 (PC #1), using Sql Developer, Sql Plus, everything seems fine. I've also connected via Oracle JDeveloper, Forms, Reports, and Designer, but all from PC #1.

I have installed Oracle Client on PC #2 and want to connect to PC #1 via Sql Plus (to test) and then via Oracle JDeveloper and some other tools. I can remote desktop into PC #1 so that's worked out already

I need to know how to set up "localhost" with a proper name and the IP address on PC #1 so PC #2 can connect via Oracle tools.

(at the end of a previous post on Oracle, I was told that no remote client can connect to the listener using 127.0.0.1 [i.e., "localhost"]) as the hostname. I was also told this is a networking issue and has nothing to do with Oracle, and that the IP address needs to be routeable, e.g., 10.10.10.10 or 192.168.0.13

So how do I fix ?

Here is my tnsnames on PC #1, followed by listener status

tnsnames

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(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 = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Listener status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-AUG-2014 12:36:30

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                04-AUG-2014 15:27:39
Uptime                    0 days 21 hr. 8 min. 54 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=127.0.0.1)(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
user1260098
  • 21
  • 1
  • As the name 'localhost' suggests, it's only valid for local connections. So you'll have to change your listener to listen on ypur PC's network-visible IP address as you already know, either instead of or as well as localhost. If 'instead of' then everything on PC1 will have to change to use that IP address (or hostname) as well. But importantly, is PC1's IP address static, or assigned via DHCP? – Alex Poole Aug 05 '14 at 21:06
  • Alex, thanks. I did change tnsnames to point to 192.168.1.1 in two places, but that didn't work, I got "ORA-12560: TNS:protocol adapter error" – user1260098 Aug 05 '14 at 22:01
  • You have to change the listener config too, as that is currently only listening on localhost. And check the DB is registering - that the service name appears in `lsnrctl services` - afterwards. – Alex Poole Aug 05 '14 at 22:07
  • I changed the listener.ora on PC #1 to be Host = 192.168.1.9, I verified this in lsnrctl services, but I still got timeout from PC #2. I verified Sql Plus from PC #1 so I didn't mess anything up. – user1260098 Aug 05 '14 at 23:18
  • 1
    did some extra research, had to register (alter system register;), re-boot, turn off firewalls, and voila ! I connected remotely :-) – user1260098 Aug 06 '14 at 00:07
  • sorry, new here - - - how do I close a question / accept an answer ? Registering as you suggested (and turning off local firewall) did the trick. Thanks – user1260098 Aug 06 '14 at 00:16
  • It's off-topic here really, but [I seem to have answered a similar question before](http://stackoverflow.com/q/22892999/266304), so maybe someone will close it as a duplicate. – Alex Poole Aug 06 '14 at 06:47

0 Answers0