Oracle losing connection ( ORA-03114 ) after some time not using the software

oracle

After some time idle, if users try to use the our system, we receive the error:

ORA-03114 - not connected to Oracle.

I know there's a parameter called SQLNET.EXPIRE_TIME.

the recomendation is SQLNET.EXPIRE_TIME=10 but I didn't find what this 10 means.

I'm a SQL Server DBA trying to help our Oracle senior dba.

Our sqlnet.ora file is like this:

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Shoud I add **SQLNET.EXPIRE_TIME=10** on it?

To simulate the error, if we open the software and leave it idle for 1hr, we receive the error.

IDLE_TIME is unlimited too on dba_profiles.

Best Answer

Yes, that will work. The 10 is a value in minutes (documentation link).

To specify a time interval, in minutes, to send a check to verify that client/server connections are active

The official Oracle documentation regarding DCD (Dead Connection Detection) is here.

The primary purpose of SQLNET.EXPIRE_TIME=10 is to clean up dead connections, but (I quote):

A less common usage scenario for DCD is to keep database connections alive when an external firewall timeout is configured to terminate idle connections

Related Solutions

ORA-01034: ORACLE not available

dbstart will only start databases that have the third parameter in the oratab file set to Y. Check the value in that file.

~~You also don't need to pass the ORACLE_HOME as an argument; not sure if you actually meant to pass the SID, but anyway, the home is taken from the oratab too.~~

The script itself says that it should only be executed as part of the system boot procedure, e.g. from a script run during boot from an /etc/rc*.d directory. The $ORACLE_HOME parameter that's passed in is only used to start the listener; the home for each database is taken from oratab.

According to the documentation, dbstart is deprecated in favour of Oracle Restart (in non-RAC environments) in 11g Release 2; I guess that means you should use crsctl start has to start the listener and database from then. That also uses the third oratab value to decide whether to start the database.

ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64

To understand the problem and try to fix it you need to check related logs: listener.log, alert log, trace file, etc

possible causes and solutions:

listener problem:

Make sure there is no error message and listener is up and running by execute the following command:

lsnrclt status

if listener is down check the following it may help you troubleshoot this issue:

could be network error: check the even log for any network error and fix them.
service stopped or it has failure when trying to start: start--->run--->services.msc the search for oracle listener service should be like OracleInstancename try to start it, if it failed to start it will throw an exception inside listener error and windows error number. please provide that error

listener workaround:

add the following parameter to your listener:

DIRECT_HANDOFF_TTC_LISTENER=OFF

database problem:

1.database server memory were enough for new connection

database connection parameters such as: dedicated server, please checked processes parameter or query from v$resource_limit where resource_name=’processes’; checked max_utilization must less than Limit_value or shared server, verify large_pool_size or check dispatcher were enough for all connection.

Best Answer

Related Solutions

ORA-01034: ORACLE not available

ORA-12518: TNS:listener could not hand off client connection ORACLE XE 11.2 x64

Related Question