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

User · Answer

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)

User · Answer

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

User · Answer

First  make sure the listener on database server  computer A  that receives client connection requests is running  To do so  run lsnrctl status command   In case  if you get TNS no listener message  see below image   it means listener service is not running  To start it  run lsnrctl start command     Second  for database operations and connectivity from remote clients  the following executables must be added to the Windows Firewall exception list   see image   Oracle home bin oracle exe - Oracle Database executable  Oracle home bin tnslsnr exe - Oracle Listener    Finally  install oracle instant client on client machine  computer B  and run   sqlplus user password computerA port XE

User · Answer

Its quite easy on computer a you don t need to do anything just make sure both system are on same network if its not internet access for this you need static ip   Okay now on computer b go to start menu find configuration under oracle folder click Net Configuration Assistant under that folder when window pop up click Local net configuration option it must be third option   Now click add and click next in next screen it will ask service name here you need to add oracle global database name of computer A Normally I use oracle86 for my installation  now click next next screen choose protocol normally its tcp click next in host name enter computer A s name you can found that in my computer properties  Click next don t change port untill you have changed that in Computer A click next and choose test connection now here you can check your connection working or not if the error is username and password not correct then click login credential button and fill correct username and password  If its saying unable to reach computer ot target not found than you must add exception in firewall for 1521 port or just disable firewall on computer A

User · Answer

First  It is necessary add static IP address for Computer A AND B  For example in my case Computer A  172 20 14 13  and B  172 20 14 78     Second   In Computer A with Net Manager add for Listener new address  172 20 14 13   or manually add new record in listener ora     listener ora Network Configuration File  E  app user 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 user product 11 2 0 dbhome 1         PROGRAM   extproc         ENVS    EXTPROC DLLS ONLY E  app user product 11 2 0 dbhome 1 bin oraclr11 dll              LISTENER      DESCRIPTION LIST        DESCRIPTION          ADDRESS    PROTOCOL   IPC  KEY   EXTPROC1521              DESCRIPTION          ADDRESS    PROTOCOL   TCP  HOST   localhost  PORT   1521              DESCRIPTION          ADDRESS    PROTOCOL   TCP  HOST   172 20 14 13  PORT   1521              ADR BASE LISTENER   E  app user   Third  With Net Manager create Service Naming with IP address computer B  172 20 14 78  or manually add new record in  tnsnames ora    tnsnames ora Network Configuration File  E  app user product 11 2 0 dbhome 1 NETWORK ADMIN tnsnames ora   Generated by Oracle configuration tools   ALINADB      DESCRIPTION        ADDRESS LIST          ADDRESS    PROTOCOL   TCP  HOST   localhost  PORT   1521              CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   alinadb             LISTENER ALINADB      ADDRESS    PROTOCOL   TCP  HOST   localhost  PORT   1521     LOCAL      DESCRIPTION        ADDRESS LIST          ADDRESS    PROTOCOL   TCP  HOST   172 20 14 13  PORT   1521              CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   alinadb             ORACLR CONNECTION DATA      DESCRIPTION        ADDRESS LIST          ADDRESS    PROTOCOL   IPC  KEY   EXTPROC1521              CONNECT DATA          SID   CLRExtProc         PRESENTATION   RO             ORCL      DESCRIPTION        ADDRESS LIST          ADDRESS    PROTOCOL   TCP  HOST   172 20 14 78  PORT   1521              CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   orcl              Fourth  In computer  B  172 20 14 78  install  win64 11gR2 client  For example it is for me in Windows 10  Pro 64 bit    Five   Create with Net Configuration Assistant listener  localhost  or manually add record  in listener ora     listener ora Network Configuration File  F  app alinasoft product 11 2 0 client 1 network admin listener ora   Generated by Oracle configuration tools       LISTENER          DESCRIPTION LIST            DESCRIPTION              ADDRESS    PROTOCOL   TCP  HOST   myserver  PORT   1521              ADDRESS    PROTOCOL   IPC  KEY   EXTPROC1521                          ADR BASE LISTENER   F  app alinasoft   Six  With Net Manager create Service Naming with IP address computer   A  172 20 14 13  or manually add new record in  tnsnames ora   SERVER-DB      DESCRIPTION        ADDRESS LIST          ADDRESS    PROTOCOL   TCP  HOST   172 20 14 13  PORT   1521              CONNECT DATA          SERVER   DEDICATED         SERVICE NAME   alinadb              Seven  Computer A -  172 20 14 13   for database operations and connectivity from remote clients  the following executables must be added to the Windows Firewall exception list   see image  Oracle home bin oracle exe - Oracle Database executable Oracle home bin tnslsnr exe - Oracle Listener  Eight  Allow  connections  for port 1158  Computer A -  172 20 14 13     for Oracle  Enterprise Manager   https   172 20 14 13 1158 em console logon logon   Ninth  Allow  connections  for port 1521   in and out   Computer A -  172 20 14 17     Tenth  In computer B 172 20 14 78 sqlplus  NOLOG  CONNECT system oracle   172 20 14 13 1521 alinadb  If uses Toad  in my case is

User · Answer

u01 app oracle product 11 2 0 xe bin oracle env sh       sqlplus  nolog    SQL gt  connect sys password as sysdba                                             SQL gt   EXEC DBMS XDB SETLISTENERLOCALACCESS FALSE      SQL gt  CONNECT sys password hostname 1521 as sysdba

[oracle] How to connect to Oracle 11g database remotely

Examples related to oracle

Examples related to oracle11g