TNS-12150 TNS Unable to Send Data - Network Communication Error Resolution

TNS-12150: TNS Unable to Send Data

Error Overview

Error Text: TNS-12150: TNS:unable to send data

This error occurs when the Oracle TNS (Transparent Network Substrate) layer cannot send data across the network connection. It indicates a breakdown in network communication between the client and the Oracle database server, often caused by network interruptions, firewall issues, or connection timeouts.

Understanding TNS Communication

TNS Architecture

Client Application
    ↓
Oracle Client Libraries (TNS)
    ↓
Network Layer (TCP/IP)
    ↓
Oracle Listener
    ↓
Oracle Database Instance

Communication Flow

1. Connection Establishment - Client connects to listener
2. Data Transmission - SQL statements and results exchanged
3. Session Management - Connection maintained during session
4. Connection Termination - Clean disconnection

Common Causes

Network Infrastructure Issues

• Network congestion or packet loss
• Unstable network connections
• Router or switch problems
• Network cable issues
• WiFi connectivity problems

Firewall and Security

# Common firewall blocking scenarios
- Port 1521 (default Oracle port) blocked
- Intermediate firewalls dropping connections
- NAT/PAT translation issues
- VPN connection problems

Database Server Issues

• High server load causing timeouts
• Oracle listener overloaded
• Database instance problems
• Memory or resource constraints

Client-Side Problems

• Network adapter issues
• DNS resolution problems
• Client software bugs
• Connection pooling issues

Diagnostic Steps

Network Connectivity Tests

# Test basic network connectivity
ping database_server

# Test specific port connectivity
telnet database_server 1521
# or
nc -v database_server 1521

# Test with nmap
nmap -p 1521 database_server

# Check network route
traceroute database_server
# Windows: tracert database_server

Oracle-Specific Diagnostics

# Test TNS connectivity
tnsping service_name
tnsping database_server:1521

# Check listener status
lsnrctl status
lsnrctl services

# Test SQL*Net connectivity
sqlplus username/password@service_name

Client-Side Diagnostics

# Check TNS_ADMIN environment
echo $TNS_ADMIN

# Verify tnsnames.ora
cat $TNS_ADMIN/tnsnames.ora

# Check client configuration
cat $TNS_ADMIN/sqlnet.ora

# Enable SQL*Net tracing (if needed)
# Add to sqlnet.ora:
# TRACE_LEVEL_CLIENT = 16
# TRACE_DIRECTORY_CLIENT = /tmp

Server-Side Diagnostics

-- Check listener and database status
SELECT instance_name, status FROM v$instance;

-- Check current connections
SELECT count(*) FROM v$session;

-- Check for blocking sessions
SELECT blocking_session, sid, serial#, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

-- Check system load
SELECT * FROM v$system_event
WHERE event LIKE '%TNS%' OR event LIKE '%send%';

Resolution Steps

Network Troubleshooting

1. Verify Network Connectivity

# Step-by-step network verification
ping -c 4 database_server

# If ping fails, check network configuration
ifconfig  # Linux/Unix
ipconfig  # Windows

# Check routing table
route -n  # Linux/Unix
route print  # Windows

2. Firewall Configuration

# Linux iptables - allow Oracle port
sudo iptables -A INPUT -p tcp --dport 1521 -j ACCEPT
sudo iptables -A OUTPUT -p tcp --sport 1521 -j ACCEPT

# Windows Firewall - create rule for Oracle
netsh advfirewall firewall add rule name="Oracle Database" dir=in action=allow protocol=TCP localport=1521

# Check current firewall rules
sudo iptables -L -n  # Linux
netsh advfirewall firewall show rule name=all  # Windows

3. Port and Service Verification

# Check if Oracle listener is running and listening
netstat -an | grep 1521  # Linux/Unix
netstat -an | findstr 1521  # Windows

# Check process listening on port
lsof -i :1521  # Linux/Unix
netstat -ano | findstr :1521  # Windows

# Verify listener process
ps -ef | grep tnslsnr  # Linux/Unix

Oracle Configuration Fixes

1. Listener Configuration

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

# Sample listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

# Restart listener
lsnrctl stop
lsnrctl start

2. Client Configuration (tnsnames.ora)

# Verify tnsnames.ora entry
cat $TNS_ADMIN/tnsnames.ora

# Sample tnsnames.ora entry
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = database_server)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.domain.com)
    )
  )

# Test the connection
tnsping ORCL
sqlplus username/password@ORCL

3. SQL*Net Configuration (sqlnet.ora)

# Check sqlnet.ora settings
cat $TNS_ADMIN/sqlnet.ora

# Recommended sqlnet.ora settings for network issues
SQLNET.EXPIRE_TIME = 10
SQLNET.RECV_TIMEOUT = 600
SQLNET.SEND_TIMEOUT = 600
TCP.CONNECT_TIMEOUT = 60
TCP.NODELAY = yes

# Disable features that might cause issues
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES = (NONE)

Advanced Troubleshooting

1. Network Packet Analysis

# Capture network packets (requires root/admin)
tcpdump -i any -w oracle_traffic.pcap host database_server and port 1521

# Windows equivalent
netsh trace start capture=yes tracefile=oracle_traffic.etl provider=Microsoft-Windows-TCPIP

# Analyze with Wireshark
wireshark oracle_traffic.pcap

2. SQL*Net Tracing

# Enable detailed tracing in sqlnet.ora
TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = /tmp/trace
TRACE_FILE_CLIENT = sqlnet_client
TRACE_UNIQUE_CLIENT = ON

# Server-side tracing
TRACE_LEVEL_SERVER = 16
TRACE_DIRECTORY_SERVER = /tmp/trace
TRACE_FILE_SERVER = sqlnet_server

# After reproducing issue, check trace files
ls -la /tmp/trace/

3. Connection Multiplexing

# Add connection multiplexing to listener.ora
USE_SHARED_SOCKET_LISTENER = TRUE
CONN_SHARE_LISTENER = TRUE

# Or use connection manager (CMAN)
# Configure connection manager for connection multiplexing

Application-Level Solutions

Connection Retry Logic

// Java example with retry logic
public Connection getConnection() throws SQLException {
    int maxRetries = 3;
    int retryDelay = 1000; // 1 second

    for (int attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            return DriverManager.getConnection(
                "jdbc:oracle:thin:@database_server:1521:ORCL",
                username, password
            );
        } catch (SQLException e) {
            if (e.getErrorCode() == 12150 && attempt < maxRetries) {
                try {
                    Thread.sleep(retryDelay * attempt); // Exponential backoff
                } catch (InterruptedException ie) {
                    Thread.currentThread().interrupt();
                    throw new SQLException("Connection retry interrupted", ie);
                }
                continue;
            }
            throw e;
        }
    }
    return null;
}

Connection Pool Configuration

// Connection pool settings for network issues
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:oracle:thin:@database_server:1521:ORCL");
config.setUsername(username);
config.setPassword(password);

// Network timeout settings
config.setConnectionTimeout(30000);      // 30 seconds
config.setValidationTimeout(5000);       // 5 seconds
config.setIdleTimeout(600000);          // 10 minutes
config.setMaxLifetime(1800000);         // 30 minutes

// Pool sizing
config.setMinimumIdle(5);
config.setMaximumPoolSize(20);

// Connection validation
config.setConnectionTestQuery("SELECT 1 FROM DUAL");

Application Timeout Settings

# Python cx_Oracle example
import cx_Oracle

def create_connection():
    dsn = cx_Oracle.makedsn("database_server", 1521, service_name="orcl")

    connection = cx_Oracle.connect(
        user=username,
        password=password,
        dsn=dsn,
        # Network timeout settings
        tcp_connect_timeout=60,
        retry_count=3,
        retry_delay=1
    )

    return connection

Monitoring and Prevention

Network Monitoring Script

#!/bin/bash
# Oracle network connectivity monitor

DB_HOST="database_server"
DB_PORT=1521
LOG_FILE="/var/log/oracle_network_monitor.log"

while true; do
    timestamp=$(date '+%Y-%m-%d %H:%M:%S')

    # Test basic connectivity
    if nc -z -w5 $DB_HOST $DB_PORT; then
        echo "$timestamp: Connection to $DB_HOST:$DB_PORT successful" >> $LOG_FILE
    else
        echo "$timestamp: ERROR - Cannot connect to $DB_HOST:$DB_PORT" >> $LOG_FILE
        # Send alert
        echo "Oracle database connectivity failed" | mail -s "Oracle Alert" [email protected]
    fi

    # Test TNS ping
    if tnsping ORCL > /dev/null 2>&1; then
        echo "$timestamp: TNS ping successful" >> $LOG_FILE
    else
        echo "$timestamp: ERROR - TNS ping failed" >> $LOG_FILE
    fi

    sleep 300  # Check every 5 minutes
done

Database-Side Monitoring

-- Create monitoring table
CREATE TABLE network_connection_log (
    log_time TIMESTAMP,
    event_type VARCHAR2(50),
    client_info VARCHAR2(200),
    error_message VARCHAR2(4000)
);

-- Monitor connection events
CREATE OR REPLACE TRIGGER trg_logon_monitor
    AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO network_connection_log VALUES (
        SYSTIMESTAMP,
        'SUCCESSFUL_LOGON',
        SYS_CONTEXT('USERENV', 'HOST') || ':' || SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
        NULL
    );
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        NULL; -- Don't let monitoring affect login
END;
/

Proactive Health Checks

-- Database connectivity health check
CREATE OR REPLACE PROCEDURE check_network_health AS
    v_count NUMBER;
BEGIN
    -- Check current connections
    SELECT COUNT(*) INTO v_count FROM v$session WHERE type = 'USER';

    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: No user sessions detected');
    END IF;

    -- Check for network-related wait events
    SELECT COUNT(*) INTO v_count
    FROM v$session_wait
    WHERE event LIKE '%TCP%' OR event LIKE '%send%' OR event LIKE '%receive%';

    IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: Network-related wait events detected: ' || v_count);
    END IF;

    -- Check listener status
    FOR rec IN (
        SELECT program FROM v$session
        WHERE program LIKE '%LISTENER%'
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Listener process active: ' || rec.program);
    END LOOP;
END;
/

Emergency Procedures

Quick Network Recovery

#!/bin/bash
# Emergency network recovery script

echo "Starting Oracle network recovery procedure..."

# Restart network services
echo "Restarting network interface..."
sudo ifdown eth0 && sudo ifup eth0

# Restart Oracle listener
echo "Restarting Oracle listener..."
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
lsnrctl stop
sleep 5
lsnrctl start

# Test connectivity
echo "Testing connectivity..."
if tnsping ORCL; then
    echo "TNS connectivity restored"
else
    echo "TNS connectivity still failing"
fi

# Check database accessibility
if sqlplus -s / as sysdba <<< "SELECT 'DATABASE_OK' FROM dual;" | grep -q "DATABASE_OK"; then
    echo "Database accessible"
else
    echo "Database access issues"
fi

Connection Reset Procedure

-- Reset problematic connections
BEGIN
    FOR rec IN (
        SELECT sid, serial#
        FROM v$session
        WHERE status = 'INACTIVE'
        AND last_call_et > 3600  -- Inactive for more than 1 hour
        AND program NOT LIKE '%background%'
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
                             rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Failed to kill session: ' || rec.sid);
        END;
    END LOOP;
END;
/

Performance Optimization

Network Performance Tuning

# TCP kernel parameters for Oracle (Linux)
# Add to /etc/sysctl.conf
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_rmem = 4096 65536 4194304
net.ipv4.tcp_wmem = 4096 65536 4194304

# Apply changes
sudo sysctl -p

Oracle Network Parameters

-- Database network optimization
ALTER SYSTEM SET processes=500 SCOPE=BOTH;
ALTER SYSTEM SET sessions=555 SCOPE=BOTH;

-- Connection timeout settings
ALTER SYSTEM SET sqlnet.expire_time=10 SCOPE=BOTH;

Related Errors

• TNS-12151: TNS received no data
• TNS-12152: TNS unable to send break message
• TNS-12571: TNS packet writer failure
• ORA-03113: End-of-file on communication channel

Best Practices

1. Implement connection retry logic in applications
2. Monitor network connectivity continuously
3. Configure appropriate timeout values
4. Use connection pooling effectively
5. Regular network infrastructure maintenance
6. Implement proper firewall rules

Troubleshooting Checklist

• Test basic network connectivity (ping)
• Verify Oracle listener is running
• Check firewall and port accessibility
• Validate TNS configuration files
• Test with tnsping and sqlplus
• Review Oracle and system logs
• Check network performance metrics
• Implement connection retry logic