Resolving ORA-3135: Connection Lost Contact
Introduction
Understanding the Cause
Step-by-Step Solutions with Examples
Conclusion
Introduction
The Oracle error ORA-3135: Connection Lost Contact indicates that a client connection to the Oracle database was unexpectedly terminated. This error can result from network issues, server configuration problems, or resource constraints. Understanding and resolving this error is crucial for maintaining reliable database operations.
Understanding the Cause
The ORA-3135 error can be triggered by several factors, including:
- Network Instability: Unstable or intermittent network connections causing disconnections.
- Firewall Settings: Firewalls blocking or terminating idle connections.
- Resource Constraints: Insufficient server resources such as CPU or memory.
- Timeout Settings: Inadequate timeout configurations in Oracle Net or the client.
- Misconfigurations: Issues with Oracle Net (sqlnet.ora) or listener configurations.
Step-by-Step Solutions with Examples
1. Check Network Stability
Ensure the network connection between the client and the server is stable. Use tools like ping and traceroute to diagnose network issues:
# Using ping to check network stability
ping -c 10 your_database_server
# Using traceroute to diagnose network issues
traceroute your_database_server
2. Review Firewall Settings
Ensure that firewalls are not blocking or prematurely closing the database connection. Adjust firewall settings to allow traffic on the database port (default is 1521):
# Example of allowing Oracle traffic through iptables
sudo iptables -A INPUT -p tcp --dport 1521 -j ACCEPT
3. Increase Server Resources
Ensure the database server has sufficient resources. Monitor CPU and memory usage, and upgrade resources if necessary:
# Use top or htop to monitor system resources
top
# Upgrade server resources if necessary
# This step will vary depending on your server environment
4. Adjust Timeout Settings
Adjust timeout settings in Oracle Net and the client to prevent premature disconnections:
# Example of adjusting SQLNET.ORA on the server
# The file is located under $ORACLE_HOME/network/sqlnet.ora
SQLNET.EXPIRE_TIME=10
# Example of adjusting the client connection string
(DESCRIPTION=(CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(ADDRESS=(PROTOCOL=TCP)(HOST=your_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=your_service)))
5. Verify Oracle Net Configuration
Check the Oracle Net configuration files (sqlnet.ora and listener.ora) for correctness. Ensure the configurations match between the client and server:
# Sample sqlnet.ora configuration
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# Sample listener.ora configuration
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_host)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = your_sid)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
)
)
Example: Correcting Oracle Net Configuration
Ensure the Oracle Net configuration is consistent and correct:
# Correct sqlnet.ora configuration
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# Correct listener.ora configuration
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
)
)
Conclusion
ORA-3135: Connection Lost Contact is a critical Oracle error that can disrupt database operations. By understanding its causes and implementing the provided solutions, such as checking network stability, reviewing firewall settings, increasing server resources, adjusting timeout settings, and verifying Oracle Net configuration, you can effectively resolve this error and maintain stable and reliable database connections. Proper diagnosis and proactive measures are essential to ensuring the smooth operation of Oracle databases.
Related content