Ошибка ora 03114

ORA-03114

Oracle programs or not, it is safe to say that every user of a computer in the past twenty years has faced the frustration of attempting to initiate a program to have it halted by a problem with network connectivity. The very potential of computer technology in the 20^th and 21^st centuries has hinged thus far on the ability to connect to a network of other users and servers, so, invariably, issues will occur in the quest of working this out. These types of problems are particularly pertinent when the success of a database system relies on its accessibility to several users at various locations within a company. The ORA-03114 is one of many Oracle error messages that are prompted when these type of connectivity issues are present.

The Problem

The ORA-03114 is an error message triggered when a user attempts a call to Oracle when no connection was established. This will typically happen in response to a user-written program not being logged on. In many cases, the server or listener process may not be running or another location shut down the database while trying to access.

This error message can also happen due to communication trouble caused by a disconnection from the database. This can derive from problems with connection pooling such as shared servers or multi-threaded server. There can also be some problems with missing entries to the tnsnames.ora.

The Solution

The ORA-03114 can occur because of a number of different problems. As with many Oracle errors that have numerous possible triggers, the first user step is looking at the log to see if anything seems out of character. Let’s go through a few different scenarios that a user might find after searching the log.
In some cases, a user will be prompted to enter a password in the middle of creating a database. Despite entering the correct password, the user is unable to connect. So, what’s the problem? Typically in a situation such as this, the database is held up in the EMCA step (Enterprise Manager Configuration Assistant). When configuring a database using EMCA, the database will be temporarily unavailable and users will not be able to connect. The listener will need a new tna connection, but it does not have access to the new database yet. The best way to prevent this issue is, prior to the creation of the database, modifying the SID_LIST section of the listener.ora and adding an entry to the yet-to-be-formed database, then reloading – this will establish a connection for the listener.

In another scenario, the database server remains idle for a certain interval of time, triggering a router of firewall to disconnect the server. What can be done in response? Verify the configuration by setting the database to send a packet every ten minutes to offset the firewall settings. Use the parameter SQLNET.EXPIRE_TIME to specify the time interval. It will default to ‘0’, but it is recommended to adjust this field to ‘10’. To run a test, stop your Oracle database instance, edit the SQLNET.EXPIRE_TIME, restart the database, do a full connect to the Oracle driver as SYSTEM/MANAGER and execute the stored sleep procedure in Oracle. Adjust the expire time as is necessary for your system requirements. You can also check the EMCA, select your database and click down to “securityprofileidle time” and set the time that the system can remain idle before disconnecting to your preferences.

Looking forward

These are just a few of the primary prompts for the ORA-03114 error. These corrections should provide the user with plenty of tactics to take the problem on. However, there is a vast assortment of issues that can prompt an ORA-03114, as in far too many to list. Fortunately, the aforementioned solutions can successfully correct a number of variations of the error. In the event that you need further assistance, you should contact a licensed Oracle database consultant who can work further with your company to prevent these types of errors.