ORA-12547 - TNS Lost Contact

ORA-12547: TNS Lost Contact

The ORA-12547 error occurs when the Oracle client loses network contact with the Oracle server during an active database connection. This error indicates a communication breakdown between the client and server processes.

Use at Your Own Risk

This documentation is for educational purposes only. Always test solutions in development environments before production use. See our disclaimer for full terms.

Error Details

• Error Code: ORA-12547
• Error Message: “TNS:lost contact”
• Error Type: Network Communication Error
• Severity: Connection Error

Common Causes

1. Network Connectivity Issues

• Network cable disconnection
• Router or switch failures
• Network congestion or packet loss
• Firewall blocking connections

2. Oracle Listener Problems

• Listener service stopped or crashed
• Listener configuration errors
• Listener running out of processes

3. Server-Side Issues

• Database instance shutdown
• Server hardware failure
• Operating system crashes
• Memory or resource exhaustion

4. Client-Side Issues

• Client application crashes
• Client machine network problems
• Timeout settings too aggressive
• Client TNS configuration errors

5. Long-Running Operations

• Query or transaction timeouts
• Idle connection timeouts
• Network timeout configurations

Examples and Solutions

Example 1: Network Connectivity Test

Problem:

SQL> SELECT * FROM employees;
-- Connection works initially

SQL> SELECT COUNT(*) FROM large_table;
-- ORA-12547: TNS:lost contact

Solution:

# Test basic network connectivity
ping database_server

# Test TNS connectivity
tnsping SERVICE_NAME

# Check if listener is running
lsnrctl status

# Test telnet to listener port
telnet database_server 1521

Example 2: Listener Issues

Problem:

$ sqlplus user/password@database
-- ORA-12547: TNS:lost contact

Solution:

# Check listener status
lsnrctl status

# If listener is down, start it
lsnrctl start

# Reload listener configuration
lsnrctl reload

# Check listener log for errors
tail -f $ORACLE_BASE/diag/tnslsnr/hostname/listener/trace/listener.log

Example 3: Connection Timeout

Problem:

-- Long-running query loses connection
SQL> SELECT /*+ FULL(t) */ * FROM huge_table t WHERE complex_condition = 'value';
-- ORA-12547: TNS:lost contact (after several minutes)

Solution:

-- Increase timeout values in sqlnet.ora
-- On client side:
SQLNET.RECV_TIMEOUT=600
SQLNET.SEND_TIMEOUT=600
SQLNET.INBOUND_CONNECT_TIMEOUT=600
SQLNET.OUTBOUND_CONNECT_TIMEOUT=120

-- On server side (listener.ora):
INBOUND_CONNECT_TIMEOUT_LISTENER=600

-- Or break query into smaller chunks
SELECT COUNT(*) FROM huge_table
WHERE complex_condition = 'value'
AND ROWNUM <= 10000;

Example 4: Firewall Configuration

Problem:

# Connection works initially but drops during operation
$ sqlplus user/password@database
SQL> -- Works for simple queries
SQL> -- Fails on complex operations
-- ORA-12547: TNS:lost contact

Solution:

# Check firewall rules
# Linux iptables
iptables -L -n | grep 1521

# Windows firewall
netsh advfirewall firewall show rule name="Oracle"

# Add firewall rules for Oracle ports
# Linux (allow Oracle listener)
iptables -A INPUT -p tcp --dport 1521 -j ACCEPT

# Configure TNS for dedicated connections
# In listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

Example 5: Resource Exhaustion

Problem:

-- Multiple connections start failing
SQL> CONNECT user/password@database
-- ORA-12547: TNS:lost contact

Solution:

-- Check database parameters
SELECT name, value FROM v$parameter
WHERE name IN ('processes', 'sessions', 'shared_servers');

-- Check current usage
SELECT
    'Sessions' as resource,
    COUNT(*) as current_usage,
    (SELECT value FROM v$parameter WHERE name = 'sessions') as limit
FROM v$session
UNION ALL
SELECT
    'Processes' as resource,
    COUNT(*) as current_usage,
    (SELECT value FROM v$parameter WHERE name = 'processes') as limit
FROM v$process;

-- Increase limits if necessary (requires restart)
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=800 SCOPE=SPFILE;

Diagnostic Queries

Check Current Connections

-- Check active sessions
SELECT
    username,
    program,
    machine,
    status,
    logon_time,
    last_call_et
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et DESC;

-- Check connection counts by machine
SELECT
    machine,
    COUNT(*) as connection_count
FROM v$session
WHERE username IS NOT NULL
GROUP BY machine
ORDER BY connection_count DESC;

Monitor Listener Activity

-- Check listener endpoints
SELECT
    name,
    value
FROM v$parameter
WHERE name LIKE '%listener%'
OR name LIKE '%dispatch%';

-- Check TNS entries
SELECT
    network_service_banner
FROM v$session_connect_info
WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

Network Diagnostics

# Check network statistics
netstat -an | grep :1521

# Monitor network traffic
# Linux
ss -tuln | grep :1521

# Check for dropped packets
netstat -i

# Monitor connection attempts
# Check listener log
tail -f $ORACLE_BASE/diag/tnslsnr/*/listener/trace/listener.log

Resolution Steps

Step 1: Immediate Response

# Test basic connectivity
ping database_server

# Test TNS resolution
tnsping SERVICE_NAME

# Check listener status
lsnrctl status LISTENER_NAME

Step 2: Check Logs

# Check Oracle alert log
tail -f $ORACLE_BASE/diag/rdbms/*/trace/alert_*.log

# Check listener log
tail -f $ORACLE_BASE/diag/tnslsnr/*/listener/trace/listener.log

# Check system logs
# Linux
journalctl -u oracle-database
# Or
tail -f /var/log/messages

Step 3: Network Troubleshooting

# Check network path
traceroute database_server

# Test specific port
telnet database_server 1521

# Check for packet loss
ping -c 100 database_server | tail -5

Step 4: Configuration Verification

# Verify TNS configuration
cat $ORACLE_HOME/network/admin/tnsnames.ora

# Check sqlnet.ora settings
cat $ORACLE_HOME/network/admin/sqlnet.ora

# Verify listener configuration
cat $ORACLE_HOME/network/admin/listener.ora

Prevention Strategies

1. Network Monitoring

# Set up network monitoring
# Monitor packet loss, latency, bandwidth
# Alert on network issues

# Example monitoring script
#!/bin/bash
while true; do
    if ! ping -c 1 database_server >/dev/null 2>&1; then
        echo "$(date): Network connectivity lost to database_server" >> /var/log/oracle_network.log
    fi
    sleep 60
done

2. Connection Pooling

// Use connection pooling to handle transient network issues
// Example with Oracle UCP
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionPoolName("MyPool");
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(20);
pds.setTimeoutCheckInterval(5);
pds.setInactiveConnectionTimeout(300);

3. Timeout Configuration

# Configure appropriate timeouts in sqlnet.ora
SQLNET.RECV_TIMEOUT=600
SQLNET.SEND_TIMEOUT=600
SQLNET.INBOUND_CONNECT_TIMEOUT=600
SQLNET.OUTBOUND_CONNECT_TIMEOUT=120
SQLNET.EXPIRE_TIME=10

# TCP keepalive settings
SQLNET.TCP.NODELAY=YES

4. Health Checks

-- Implement regular health checks
CREATE OR REPLACE PROCEDURE check_database_health IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM dual;
    DBMS_OUTPUT.PUT_LINE('Database health check: OK at ' || SYSTIMESTAMP);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Database health check failed: ' || SQLERRM);
END;
/

Monitoring and Alerting

Real-time Monitoring

-- Monitor for connection issues
SELECT
    TO_CHAR(first_time, 'YYYY-MM-DD HH24:MI:SS') as error_time,
    message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%TNS%'
OR message_text LIKE '%lost contact%'
ORDER BY first_time DESC;

Connection Health Script

#!/bin/bash
# Oracle connection health check
ORACLE_SID=ORCL
SERVICE_NAME=mydb.example.com

# Test TNS ping
if tnsping $SERVICE_NAME | grep -q "OK"; then
    echo "TNS ping successful"
else
    echo "TNS ping failed - check listener"
    exit 1
fi

# Test SQL connection
if echo "SELECT 1 FROM dual;" | sqlplus -s system/password@$SERVICE_NAME | grep -q "1"; then
    echo "SQL connection successful"
else
    echo "SQL connection failed"
    exit 1
fi

Related Errors

• ORA-12541: TNS No Listener
• ORA-12514: TNS Service Registration
• ORA-03113: End-of-file on Communication
• TNS-12571: TNS Packet Writer Failure

Summary

ORA-12547 indicates a network communication failure between Oracle client and server. Resolution involves diagnosing network connectivity, checking listener status, verifying configurations, and implementing proper timeout settings. Prevention requires robust network monitoring, connection pooling, and appropriate timeout configurations to handle transient network issues gracefully.