How to connect to PC #1 (running Oracle) from PC #2 (running Oracle client)

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 re-configure or otherwise re-name "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)
    )
  )

Open in new window

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

Open in new window

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:
You need to set up a 'home' network.

http://windows.microsoft.com/en-us/windows/setting-home-network#1TC=windows-7

You should get an ip address like 192.168.0.xxx

Then tell the listener to use that ip address and on pc #2, use that ip address in the tnsnames.ora file.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
I do have a home network, and I can remote desktop from PC #2 to PC #1.

I got the local ip address as 192.168.1.9 on PC #1
Local IP address
I then added that to tnsnames on PC #2
# 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


but it didn't work :-(
remote access error
0
slightwv (䄆 Netminder) Commented:
In sqlplus you need to provide the remote database alias

sqlplus system@orcl

The '@orcl' is the magic...
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:
different error now . . . :-(
remote access error
0
slightwv (䄆 Netminder) Commented:
99.999% of the time the ORA-12178 is caused by a firewall.

For a test, drop the firewall on both systems and try.  If you can then connect, you'll need to add rules...
0
Gadsden ConsultingIT SpecialistAuthor Commented:
>>For a test, drop the firewall on both systems and try
-- if only I knew how to do that . . . I'll google it and let you know.

I also modified the listener file to listen to 192.168.1.9 and not 127.0.0.1, still no luck.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
Ok, I disabled the local firewall on both, now it says

"TNS listener does not currently know of service requested in connect descriptor"
0
slightwv (䄆 Netminder) Commented:
Did you change the listener.ora file on PC#1 to use the new IP address?
Did you restart the listener?

On PC#1 post the results of:
lsnrctl status

If ORCL isn't being listened for, try a manual registration.  Connect to the database sys or system and do:
alter system register;


Then check the listener status.
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:
worked !!!!!!!!!!!!!!!

So in changing to 192.168.1.9 things got a little out of whack, but the register (and re-boot) must have done the trick !

I'll put the firewalls back on and see if it still works.

Here's the listener status for the record:

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-AUG-2014 19:58:48

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                05-AUG-2014 19:53:18
Uptime                    0 days 0 hr. 5 min. 34 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

and for fun, my connection from the remote PC:
remote success
0
slightwv (䄆 Netminder) Commented:
>>I'll put the firewalls back on and see if it still works.

If it doesn't then you'll need to set up rules to allow at least 1521 through.

Oracle changed things recently so you may not need to add ALL the high-level ports through but in the past you did.  I've not been on the network side of things for MANY years so cannot help with the exact rules you'll need to add.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
I turned the firewall back on, on both PC's, and it didn't work. So I turned it off on PC # 1, and it worked.

Thanks for sticking with this !
0
Gadsden ConsultingIT SpecialistAuthor Commented:
big help to allow me to set up a remote connection to Oracle.
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
Windows 7

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.