[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-05
12
Medium Priority
?
549 Views
Last Modified: 2014-08-05
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

0
Comment
Question by:Gadsden Consulting
  • 7
  • 5
12 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1600 total points
ID: 40242419
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
 

Author Comment

by:Gadsden Consulting
ID: 40242511
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1600 total points
ID: 40242522
In sqlplus you need to provide the remote database alias

sqlplus system@orcl

The '@orcl' is the magic...
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Gadsden Consulting
ID: 40242544
different error now . . . :-(
remote access error
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1600 total points
ID: 40242591
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
 

Author Comment

by:Gadsden Consulting
ID: 40242651
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40242664
Ok, I disabled the local firewall on both, now it says

"TNS listener does not currently know of service requested in connect descriptor"
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1600 total points
ID: 40242684
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
 

Author Comment

by:Gadsden Consulting
ID: 40242703
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40242708
>>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
 

Author Comment

by:Gadsden Consulting
ID: 40242709
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
 

Author Closing Comment

by:Gadsden Consulting
ID: 40242718
big help to allow me to set up a remote connection to Oracle.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
Unable to change the program that handles the scan event from a network attached Canon/Brother printer/scanner. This means you'll always have to choose which program handles this action, e.g. ControlCenter4 (in the case of a Brother).
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses
Course of the Month20 days, 1 hour left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question