ORA-03114, ORA-03113, ORA-03135

During the last year I assisted a customer in upgrading their applications from clients running Oracle Forms & Reports 6i on platform Windows Server 2003 – 32 bits to Oracle Forms & Reports 12c on Windows Server 2016 – 64 bits.

During the last year I assisted a customer in upgrading their applications from clients running Oracle Forms & Reports 6i on platform Windows Server 2003 – 32 bits to Oracle Forms & Reports 12c on Windows Server 2016 – 64 bits.

The applications were running against an Oracle database 8i. 

As a part of the upgrade an Oracle database 12c was installed, configured and the data migrated from the old 8i version.

Short time after the application and database migration was completed I noticed that on Oracle Forms and Reports 12c the following error messages appeared:

  • ORA-03114 : not connected to Oracle.
  • ORA-03113 : end-of-file on communication channel .
  • ORA-03135 : connection lost contact.

The ORA-03114 and ORA-03113 used to appear together and the ORA-03135 appeared as any reports was called from Forms.

For example by executing the code:

Run_report_object();

At first I thought that the errors above could be related only to Forms and/or Reports 12c.  I checked the code, setup, configuration and all seemed to be ok.  Then I focused my attention to the server setup, especially the network configuration, firewalls, latency problems, but I found no issues there.

I recalled that the Windows Server 2016 was a virtual server and maybe the errors could be related to that.

By closing the application and/or restarting the database the errors disappeared, but they showed up again after a couple of hours.

After a short while I noticed that the error messages ORA-03114 and ORA-03113 appeared randomly by running code on SQL*PLUS as well.

For example:

SQL> conn user/pwd@db
SQL> start mycode.sql
SQL>
ORA-03114
ORA-03113

Sometimes no error messages appeared and other times they appeared every 2 minutes.

Then one could immediately conclude that the problem is more general and not related only to Forms & Reports, but Sql*Plus.  I even tested the connection to the database from SqlDeveloper and it seemed to be more stable.

I checked the database alert and trace logs, but I found nothing relevant.  None of the above error messages were found.

The problem persisted as the application was running against the database locally (RDC) or remote (client windows pc).

After a lot of troubleshooting I talked to a network expert to run network tests on the database server .  It turns out the network configuration was fine and then we focused on the database listener.

I had a suspicion about the database listener from the beginning, but checked the listener log and nothing relevant was found.

Then we turned the attention to the listener files: listener.ora, tnsnames.ora and  sqlnet.ora.

The file listener.ora looked like this:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1)
      (ORACLE_HOME = C:\Oracle\product\12.2.0\dbhome_1)
      (SID_NAME = orcl1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2)
      (ORACLE_HOME = C:\Oracle\product\12.2.0\dbhome_1)
      (SID_NAME = orcl2)
    )
  )

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

and the file tnsnames.ora looked like this:

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )

Note the value of HOST, it is the server name.  The IP address was static and then we checked the DNS setup.

The setup looked ok, but the DNS server resided externally relative to the database server and then this might cause delays or disruptions in the network from/to the server or the DNS server had some trouble in translating the server name to IP address.

Then the value of HOST in the files: listener.ora and tnsnames.ora was altered to contain the IP address, like this:

HOST = <IP address>

Then the listener and the database were shut down and restarted.

After that the running of the applications from remote and local clients was way more stable.

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *