0

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)
    )
  )
1
  • 1
    why would not NOT use the default port of 1521?
    – EdStevens
    Jun 25, 2021 at 15:07

1 Answer 1

-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

2
  • It seems like this syntax is only available on 19c, however locally I have 18c version of the SQL *Plus. I have manually added the SQLNET.EXPIRE_TIME=10 parameter to sqlnet.ora and restarted the database. Still that did not work.
    – dontMashMe
    Jun 25, 2021 at 12:40
  • Please try this format as you have 19c db. Client should not matter here.
    – mohd atif
    Jun 25, 2021 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.