How can I allow an Oracle database to be accessed remotely?

Question

I have an Oracle database installed on one machine, and I am trying to accessing the same from another machine.

I tried via Oracle SQL developer eclipse, and even via JDBC program, butnothing works. I am not sure if it is firewall issue (I have ruled out this because both the machines are in same domain) or something to do with Oracle database setting. I know in MySQL you need to grant permission to connect to database remotely. Do we need to set something similar on oracle SID also?

The error message I get:

Io Exception: The Network Adapter could not establish the connection

Answer 1

Oracle is an enterprise database server so you shouldnt need any strange setup to get it working, just the correct configuration on the DB server or client will be enough.

When you get connection errors, i suggest first ensuring that the client machine can ping the database server where is the name or ip address - try both as it's possible the name may not be configured in the hosts file of the client machine. (Note that ping may be disabled due to security but if you get a reply back, you know the connection is good.A non reply may not mean the network is missing but you can try to ssh or telnet to the dbhost from the client machine as an alternative)

 unix> ping <dbhost>

Once you have confirmed the client machine can see the db server, you can check the configuration on the DB server.

On the DB server, if you connect with the following, you are connecting directly to the database without using TNS.

 unix> sqlplus user/pass 

If you connect instead with

 unix> sqlplus user/pass@ORACLE_SID

where ORACLE_SID is the identifier of the database, you will be using TNS, the same as your client machine. If this doesnt work locally, then it's also not going to work on the remote machine.

Check the listener is running

 unix> lsnrctl status

and if it's not, start it as the oracle user

 unix> lsnrctl start

and try again to connect using user/pass@ORACLE_SID

If this connects OK, then check your JDBC configuration to ensure you have specified the connection string correctly.

see http://javaeesupportpatterns.blogspot.de/2011/08/network-adapter-could-not-establish.html for assistance with the correct jdbc connection string.

Answer 2

I know it's an old thread, but I had the same issue and did not find answer anywhere. Here is the reason: Oracle installs it's own Firewall. You can check if it's running on your system (execute as root):

~> systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
  Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
  Active: active (running) (thawing) since Fri 2021-01-01 00:00:00 UTC; 0min ago
  ...

Or

~> /bin/ps -ef | grep fire
root        1234       1  0 00:00 ?        00:00:00 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid

If it is running - that's what's blocking the connection. Issue commands:

~> firewall-cmd --zone=public --add-port=1521/tcp --add-port=5500/tcp --add-port=5520/tcp --add-port=3938/tcp --permanent
success
~> firewall-cmd --reload
success

And try it. It should be fine now.