ORA-03114 Unable to connect to remote Oracle database

Question

I have an Oracle Database installed on Oracle Linux Server which I'm trying to connect to from my PC. I have configured the listener in the following way

[oracle@ol8ngs1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-JUN-2021 13:30:15

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.11.86)(PORT=1539)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-JUN-2021 11:18:41
Uptime                    0 days 2 hr. 11 min. 34 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/ol8ngs1/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.11.86)(PORT=1539)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
 Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

The listener is started and it's status is OK, so the connection should be possible from a remote location.

However, when trying to connect to it through SQL *Plus first it says connected to the database but executing any query on it returns ORA-03114: not connected to ORACLE error.

I have also tried to connect to it through SQL Developer, but that returns: Status : Failure -Test failed: IO Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond, Authentication lapse 0 ms.

What's even more perplexing is that the connection is possible through other PC's, as I asked one more person in the company if he can connect to the database through SQL Developer.

What I have tried so far:

• Change the database port
• Allow the inbound and outbound rules for the 1539 port
• Configure different services
• Different hostname for database

Here are the contents of listeners.ora file on the server:

# listener.ora Network Configuration File: /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME =  /oracle/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = orcl)
    )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.11.86)(PORT = 1539))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LOCAL_OS_AUTHENTICATION_LISTENER = OFF

And here are the contents of tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.11.86)(PORT = 1539))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.11.86)(PORT = 1539))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Answer 1

You can refer to below answer. Issue seems to be at network layer i.e. connection is established but then somehow you are unable to keep that connection alive.

Please try this : "mydbmachine.example.com/orclpdb1?expire_time=2"

Post