Question: Users are connected to the database via TNS and abruptly get disconnected. What is the cause of the ORA-03135 error? How can this be resolved?
ORA-03135: connection lost contact
Answer: According to oerr the description of the ORA-03135 error is below.
1) Server unexpectedly terminated or was forced to terminate.
2) Server timed out the connection.
1) Check if the server session was terminated.
2) Check if the timeout parameters are set properly in SQLNET.ora.
The most common reason is that the database session was idle for a time period greater than the idle timeout setting on the Firewall. This results in the firewall terminating the session.
Solution: Configure Dead Connection Detection (DCD) in the environment by setting a value for SQLNET.EXPIRE_TIME parameter in the $ORACLE_HOME/network/admin/sqlnet.ora file.
Based on this value, the database server will send a packet back to the client. In cases when the packet is not acknowledged, the client connection is closed.
Below the value is set to 15 minutes in the sqlnet.ora file.
There are two cases to consider with this setting. One with the value lower that the Firewall setting and one with a higher value.
When the value of the SQLNET.EXPIRE_TIME is lower than the Firewall connection timeout, since a packet will always be sent before the fireawall connection timeout threshold the session will not be killed.
In case the value of the SQLNET.EXPIRE_TIME is higher than the Firewall connection timeout, when the firewall kills the session, the database will immediately close the session cleanly.
Note 787354.1 Troubleshooting ORA-3135 Connection Lost Contact
Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall
• Oracle Database Security – Log into a Schema Without a Password! – User Proxy Authentication Identified by values allow you to connect to a Schema without password. Obviously you cannot circumvent Oracle Database Security.
• Oracle Database Server Security Checklist – Security hardening of a database is an elaborate task requiring a detailed review of the environment. Read more on our Oracle Database Server Security Checklist
• Adding Disk Storage to Oracle VirtualBox Need more space on your VirtualBox? Follow the instructions and use the drive space on your host machine to share space with the VM Virtual Box.
• Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.
Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.