Sample Header Ad - 728x90

ORA-03114 Unable to connect to remote Oracle database

0 votes
1 answer
2111 views
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. ORA-03114 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)
    )
  )
Asked by dontMashMe (1 rep)
Jun 25, 2021, 11:51 AM
Last activity: Jun 25, 2021, 12:06 PM