How to connect to Oracle 11g database remotely

Question

I have Oracle 11g XE installed on computer A. I can connect through the sql command line using the command connect username/password. I also can send SQL instructions to the Demo database: Select * from demo_customers;

The database is running on localhost of computer A.

I want computer B to connect to computer A's database on localhost. How can I do that?

Answer 1

You will need to run the lsnrctl utility on server A to start the listener. You would then connect from computer B using the following syntax:

sqlplus username/password@hostA:1521 /XE

The port information is optional if the default of 1521 is used.

Listener configuration documentation here. Remote connection documentation here.

Answer 2

I install the Oracle server and it allows to connect from the local machine with no problem. But from another Maclaptop on my home network, it can't connect using either Sql Developer or Sql Plus. After doing some research, I figured out there is this additional step you have to do:

Use the Oracle net manager. Select the Listener. Add the IP address (in my case it is 192.168.1.12) besides of the 127.0.0.1 or localhost.

This will end up add an entry to the [OracleHome]\product\11.2.0\dbhome_1\network\admin\listener.ora

• restart the listener service. (note: for me I reboot machine once to make it work)

• Use lsnrctl status to verify
  Notice the additional HOST=192.168.1.12 shows up and this is what to make remote connection to work.

  C:\Windows\System32>lsnrctl status
  LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-SEP-2015 13:51:43
  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-SEP-2015 13:45:18
  Uptime 0 days 0 hr. 6 min. 24 sec
  Trace Level off
  Security ON: Local OS Authentication
  SNMP OFF
  Listener Parameter File
  D:\oracle11gr2\product\11.2.0\dbhome_1\network\admin\listener.ora
  Listener Log File d:\oracle11gr2\diag\tnslsnr\eagleii\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)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521)))
  Services Summary...
  Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
  Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service... Service "xeXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service... The command completed successfully

• use tnsping to test the connection
  ping the IPv4 address, not the localhost or the 127.0.0.1

C:\Windows\System32>tnsping 192.168.1.12
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-SEP-2015 14:09:11
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
D:\oracle11gr2\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1521)))
OK (0 msec)