Making an Oracle XE instance network wide available

Question

Im fairly new to Oracle so please bear with me.

I want to install an Oracle 18c XE DBMS on a server and access it's databases remotely via DBeaver. First of all there is some confusion on which client to use.

So far I've read the documentation and this is what I found regarding finding a client:

Oracle Client Software

The remote computer must have Oracle client software installed. It is through Oracle client software >that Oracle command-line utilities (and your applications) connect to the database. Oracle Database XE >accepts connections from all of the following types of Oracle client software:

• Oracle Database Express Edition Client (Oracle Database XE)

When you install Oracle Database XE, Oracle Database Express Edition Client is also installed on the >same computer. You can install Oracle Database XE separately on remote computers. It is available at

http://www.oracle.com/technetwork/database/express-edition/Opens a new window

• Instant Client

Instant Client is available at

http://www.oracle.com/technetwork/database/features/instant-client/index-100365.htmlOpens a new window Oracle client software for Oracle Database Enterprise Edition or Standard Edition (all supported >releases of Oracle Database)

• All Oracle client software includes Oracle Net, which is the Oracle network software that enables >client >applications on one computer to connect to databases on another computer over a network.

Question 1: Since Im not going to access the databases programmatically and actually want to use a GUI or SQLPLUS, does that mean I have to fully install Oracle 18c XE on all clients?

For testing purposes I have set up two devices:

• Device1 - private installation of Oracle 19c (IP: 192.168.178.201)
• Device2 - future server with an installation of Oracle 18c XE (IP: 192.168.178.54)

Device1 is connected with network 192.168.178.0 via vpn and both devices can ping each other.

Now from my understanding I should only have to configure my DBeaver client connection like this:

But the connection times out...

Question 2: Is my configuration of the tsnames.ora and listener.ora wrong?

tsnames.ora:

 # tnsnames.ora Network Configuration File: C:\app\install\product\18.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
  
  
XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xepdb1)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora

# listener.ora Network Configuration File: C:\app\install\product\18.0.0\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\install\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\install\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Answer 1

You should only install server software (i.e. Oracle 18c XE) on the server. You should install client software (i.e. Instant Client or full OCI Client) everywhere else. While the server software includes the client, it is intended for local access, or to allow servers to talk to each other. There is no need to (indeed every reason not to) install the full XE server on every client host.

For your server network configuration files, the defaults associated with an XE setup should be fine. The only thing you'd need to double check is that you don't have a firewall blocking access to the server port (1521 by default) for remote clients.