Monday, November 16, 2026

Troubleshooting TNS-12535 and TNS-00505 Errors in Oracle Data Guard Standby Databases

In Oracle Database environments, especially those configured with Oracle Data Guard, database administrators may occasionally receive unexpected network-related alerts even when both primary and standby database servers have been running normally for months or years.

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.
B. Firewall or Network Security Changes

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
Compare the error timestamps with the application and database alert logs.

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.
Then the issue may have been a temporary network interruption or a connection that exceeded its timeout threshold.

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:
  1. A running database server does not guarantee that network communication is healthy.
  2. Do not restart the Oracle listener or database without identifying the root cause.
  3. Always correlate the standby alert log, listener log, and primary database alert log.
  4. Involve the network team if there are indications of packet drops, firewall timeouts, or routing issues.
  5. Verify Data Guard transport lag and apply lag before declaring the standby healthy.
  6. Monitor whether the error repeats or was an isolated event.
Conclusion

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

No comments:

Post a Comment