Recently, we encountered the following errors on an Oracle Data Guard standby database server:
TNS-12535: TNS:operation timed out
TNS-00505: Operation timed out
The interesting part was that the standby server was up and running, and there had been no planned database restart or maintenance activity.
Why do Oracle TNS timeout errors suddenly occur on servers that have been running without issues for a long time? Let's understand the possible causes and how to troubleshoot them.
1. Understanding TNS-12535 and TNS-00505
Oracle uses Transparent Network Substrate (TNS) to establish communication between database clients, listeners, and Oracle database instances.
The errors indicate that an Oracle Net operation did not complete within the expected timeout period.
TNS-12535: An Oracle Net operation timed out.
TNS-00505: The underlying network operation timed out.
These errors do not necessarily indicate that the Oracle database is down.
They can occur because of temporary network interruptions, firewall behavior, listener delays, DNS resolution issues, or server resource constraints.
2. Why Do These Errors Suddenly Appear?
Even when database servers have been running for a long time, several external or internal factors can cause sudden connection timeouts.
A. Network Connectivity Issues
Temporary network interruptions between primary and standby servers can cause Oracle Net connections to time out.
Possible reasons include:
- Network packet drops.
- Network congestion.
- Routing changes.
- Network interface issues.
- Temporary connectivity interruptions.
Firewalls may terminate idle connections or interfere with Oracle listener communication.
Sometimes, network infrastructure changes occur without any database-side configuration changes. Check with the network team whether there were firewall policy updates or network maintenance activities around the alert timestamp.
C. Oracle Listener Issues
The Oracle listener may be running but experiencing delays in accepting or processing new connections.
Check listener status:
$lsnrctl status
$lsnrctl services
Review listener logs for connection timeout errors.
D. DNS Resolution Issues
If primary and standby servers communicate using hostnames, DNS resolution delays can affect connection establishment.
Check hostname resolution:
$ nslookup PRIMARY_HOST
$ nslookup STANDBY_HOST
Also verify hostname resolution from both servers.
E. Server Resource Utilization
High CPU utilization, memory pressure, or disk I/O bottlenecks can delay network operations.
Check system resource utilization:
$top
$ free -h
$ vmstat 1 5
$ iostat -x 1 5
3. Step-by-Step Troubleshooting
Before making any configuration changes, identify where and when the error occurred.
Step 1: Check Server Availability
On the standby server:
$ uptime
$ hostname
$ date
Verify that the server has not rebooted unexpectedly.
Step 2: Test Network Connectivity
From standby to primary:
$ ping -c 10 PRIMARY_HOST
Test Oracle listener connectivity:
$ nc -vz PRIMARY_HOST 1521
Test Oracle Net connectivity:
$ tnsping PRIMARY_TNS_ALIAS
Perform similar checks from primary to standby.
Note that a successful TNSPING only confirms Oracle Net listener connectivity; it does not guarantee that the database connection or Data Guard redo transport is healthy.
Step 3: Check Oracle Listener Logs
Review the listener log on the standby server.
Typical location:
$ORACLE_BASE/diag/tnslsnr/
Check recent entries:
$tail -200 listener.log
Look for:
- TNS-12535
- TNS-00505
- TNS-12541
- TNS-12170
Step 4: Check the Standby Database Alert Log
Review the standby alert log:
$ tail -200 alert_STANDBY.log
Look for connection errors, archive log transport failures, recovery errors, or unexpected database events.
Step 5: Verify Data Guard Status
Connect to the standby database:
$sqlplus / as sysdba
Check database role and open mode:
SQL> SELECT DATABASE_ROLE,
OPEN_MODE,
PROTECTION_MODE
FROM V$DATABASE;
Check archive destination status:
SQL> SELECT DEST_ID, STATUS, ERROR, DESTINATION
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'INACTIVE';
Check Data Guard transport and apply lag:
SQL> SELECT NAME, VALUE, UNIT
FROM V$DATAGUARD_STATS;
These queries help determine whether the standby is receiving redo and applying it successfully.
Step 6: Check Oracle Net Configuration
Review the following configuration files:
$ORACLE_HOME/network/admin/sqlnet.ora
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/tnsnames.ora
Pay attention to timeout-related parameters, including:
SQLNET.INBOUND_CONNECT_TIMEOUT
SQLNET.OUTBOUND_CONNECT_TIMEOUT
SQLNET.RECV_TIMEOUT
SQLNET.SEND_TIMEOUT
Do not increase timeout values blindly. First identify which operation is timing out and whether the timeout is caused by network delays or Oracle configuration.
4. What If the Error Occurs Only Once?
If the TNS-12535 and TNS-00505 alerts appear once and disappear automatically, while:
- The standby database remains available.
- The listener is running.
- Data Guard redo transport is healthy.
- There is no transport or apply lag.
- No archive log gaps are reported.
However, a single alert should not automatically be ignored. Check the surrounding logs and confirm that Data Guard synchronization has recovered.
5. Important Lessons for Oracle DBAs
When troubleshooting sudden TNS timeout errors in production environments, remember:
- A running database server does not guarantee that network communication is healthy.
- Do not restart the Oracle listener or database without identifying the root cause.
- Always correlate the standby alert log, listener log, and primary database alert log.
- Involve the network team if there are indications of packet drops, firewall timeouts, or routing issues.
- Verify Data Guard transport lag and apply lag before declaring the standby healthy.
- Monitor whether the error repeats or was an isolated event.
TNS-12535 and TNS-00505 are Oracle Net timeout errors that can appear unexpectedly, even in stable Oracle Data Guard environments.
When these alerts occur, the first priority should be to identify whether the issue is related to network connectivity, firewall behavior, DNS resolution, listener performance, or database resource utilization.
Instead of immediately restarting database services or changing timeout parameters, follow a systematic troubleshooting approach and verify the actual impact on Data Guard synchronization.
The key takeaway: A database server can be up and running while its network communication is experiencing problems. Always investigate the connectivity path before making changes to a healthy production database.
#OracleDatabase #OracleDBA #OracleDataGuard #TNS12535 #TNS00505 #DatabaseAdministration #OracleTroubleshooting #ProductionSupport #DBA
