SQL*Net Configuration

Purpose: Guide to managing Oracle SQL*Net configuration using OraDBA templates and tools.

Audience: DBAs configuring Oracle network connectivity.

Prerequisites:

• OraDBA installed
• TNS_ADMIN directory configured
• Appropriate permissions to modify network configuration files

Overview

This chapter covers managing Oracle SQL*Net configuration using OraDBA templates and tools for sqlnet.ora, tnsnames.ora, and ldap.ora files.

OraDBA provides comprehensive SQL*Net configuration templates and management tools to simplify Oracle network setup, improve security, and ensure consistent configurations across environments. The system works with all Oracle product types supported by the Plugin System.

Quick Start

Install Basic Configuration

For development or test environments:

# Install basic sqlnet.ora
oradba_sqlnet.sh --install basic

# Validate configuration
oradba_sqlnet.sh --validate

Install Secure Configuration

For production environments:

# Install secure sqlnet.ora with encryption
oradba_sqlnet.sh --install secure

# Validate configuration
oradba_sqlnet.sh --validate

Add Database Connection

# Generate tnsnames entry for your database
oradba_sqlnet.sh --generate PRODDB

# Test the connection
oradba_sqlnet.sh --test PRODDB

# List all TNS aliases
oradba_sqlnet.sh --list

Centralized TNS_ADMIN Setup

OraDBA v0.2.0+ supports centralized SQL*Net configuration management, particularly useful for:

• Multiple Oracle Homes - Manage configurations independently per database
• Read-Only Oracle Homes - Oracle 18c+ feature with logically read-only ORACLE_HOME
• Simplified Administration - Single location for all SQL*Net configurations
• Better Logging - Separate log/trace directories per database

Understanding Read-Only Oracle Homes

Oracle 18c introduced the read-only Oracle Home feature, where:

• ORACLE_HOME is logically read-only (configuration immutable)
• ORACLE_BASE_HOME stores writable Oracle Home files
• ORACLE_BASE_CONFIG stores configuration files
• Use orabasehome command to detect mode:
• If output = $ORACLE_HOME → Read-Write mode
• If output = $ORACLE_BASE/homes/HOME_NAME → Read-Only mode

# Check if Oracle Home is read-only
cd $ORACLE_HOME/bin
./orabasehome
# Output: /u01/app/oracle/homes/OraDB19Home1 (read-only mode)
# Output: /u01/app/oracle/product/19c/dbhome_1 (read-write mode)

Note: If orabasehome command doesn't exist, your Oracle version doesn't support read-only homes.

Centralized Structure

OraDBA creates this directory structure:

$ORACLE_BASE/network/
├── SID1/
│   ├── admin/       # Configuration files (sqlnet.ora, tnsnames.ora, etc.)
│   ├── log/         # SQL*Net client logs
│   └── trace/       # SQL*Net client traces
├── SID2/
│   ├── admin/
│   ├── log/
│   └── trace/
└── ...

Setup Single Database

# Setup centralized TNS_ADMIN for specific database
oradba_sqlnet.sh --setup PRODDB

This will:

1. Create directory structure: $ORACLE_BASE/network/PRODDB/{admin,log,trace}
2. Migrate existing files from $ORACLE_HOME/network/admin:
3. sqlnet.ora
4. tnsnames.ora
5. ldap.ora
6. listener.ora (if exists)
7. Backup original files with timestamp
8. Create symlinks in $ORACLE_HOME/network/admin → centralized location
9. Update sqlnet.ora with correct LOG_DIRECTORY and TRACE_DIRECTORY paths

Setup All Databases

# Setup centralized TNS_ADMIN for all databases in /etc/oratab
oradba_sqlnet.sh --setup-all

Processes all databases defined in /etc/oratab, creating centralized structures for each.

Benefits

For Read-Only Homes:

• Configuration stored outside ORACLE_HOME (required)
• Complies with Oracle 18c+ architecture
• Enables patching without configuration impact

For Multiple Homes:

• Independent configurations per database
• No configuration conflicts
• Easier troubleshooting (separate logs per database)

For All Environments:

• Centralized backup location
• Simplified configuration management
• Clear separation: code vs. configuration
• Version control friendly (config outside ORACLE_HOME)

Environment Variables

After setup, set TNS_ADMIN in your profile:

# In .bash_profile or similar
export ORACLE_SID=PRODDB
export TNS_ADMIN=$ORACLE_BASE/network/$ORACLE_SID/admin

Or source OraDBA environment scripts which set TNS_ADMIN automatically.

Available Templates

sqlnet.ora Templates

Basic Template

File: sqlnet.ora.basic
Use Case: Development and test environments

Features:

• TNSNAMES, EZCONNECT, and HOSTNAME naming methods
• Basic timeout settings (60s inbound, 120s outbound)
• ADR diagnostics enabled
• Dead connection detection (10 minutes)
• Commented wallet configuration

Example content:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
DIAG_ADR_ENABLED = ON
SQLNET.EXPIRE_TIME = 10
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 120

Secure Template

File: sqlnet.ora.secure
Use Case: Production environments requiring encryption

Features:

• Network Encryption: AES256/192/128 (REQUIRED)
• Data Integrity: SHA256/384/512 checksums (REQUIRED)
• Authentication: TCPS and BEQ support
• Security: Case-sensitive logon, minimum Oracle 12c
• Wallet: Pre-configured for credential storage

Example content:

SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED

Important: Network encryption requires Oracle Advanced Security license.

tnsnames.ora Template

File: tnsnames.ora.template
Use Case: Connection descriptor examples

Includes examples for:

1. Basic Connection

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

1. Failover Configuration
2. Automatic failover to secondary node
3. Configurable retry count and delay

4. TYPE = SELECT for session failover

5. Load Balancing

6. Distributes connections across multiple nodes
7. LOAD_BALANCE = ON

8. Multiple ADDRESS entries

9. Secure TCPS Connection

10. SSL/TLS encryption (port 2484)
11. Certificate DN validation

12. Production security

13. PDB Connection

14. Connect directly to pluggable database

15. SERVICE_NAME = pdb_name

16. RAC Connection

17. SCAN listener address

18. High availability setup

19. Connection Pooling

20. SERVER = POOLED

21. Connection class specification

22. Data Guard Standby

23. Connect to standby database

24. Separate service name

25. Custom Timeouts

26. CONNECT_TIMEOUT
27. TRANSPORT_CONNECT_TIMEOUT
28. RETRY_COUNT

ldap.ora Template

File: ldap.ora.template
Use Case: LDAP directory naming

Features:

• Oracle Internet Directory (OID) configuration
• Active Directory (AD) support
• SSL/TLS for secure LDAP
• Authentication credentials
• Cache configuration

Example content:

DEFAULT_ADMIN_CONTEXT = "dc=example,dc=com"
DIRECTORY_SERVERS = (ldap.example.com:389:636)
DIRECTORY_SERVER_TYPE = OID

Management Tool

oradba_sqlnet.sh

Comprehensive SQL*Net configuration management script.

Synopsis

oradba_sqlnet.sh [OPTIONS]

Options

Option	Description
-i, --install TYPE	Install template (basic|secure)
-g, --generate SID	Generate tnsnames entry for SID
-v, --validate	Validate current configuration
-b, --backup	Backup current configuration
-t, --test ALIAS	Test TNS alias connection
-l, --list	List all TNS aliases
-s, --setup [SID]	Setup centralized TNS_ADMIN structure
-a, --setup-all	Setup TNS_ADMIN for all databases in oratab
-h, --help	Show help message

Examples

Setup Centralized Configuration

# Setup for specific database
oradba_sqlnet.sh --setup PRODDB

# Setup for all databases in /etc/oratab
oradba_sqlnet.sh --setup-all

Install Templates

# Install basic template
oradba_sqlnet.sh --install basic

# Install secure template with encryption
oradba_sqlnet.sh --install secure

Generate TNS Entries

# Generate entry for PRODDB
oradba_sqlnet.sh --generate PRODDB

# Generate entry for TESTDB
oradba_sqlnet.sh --generate TESTDB

Validate Configuration

# Validate current configuration
oradba_sqlnet.sh --validate

# Output example:
# Validating SQL*Net configuration...
# TNS_ADMIN: /u01/app/oracle/network/admin
# [OK] sqlnet.ora exists
# [OK] sqlnet.ora is readable
# [OK] tnsnames.ora exists
# [OK] Configuration validation passed

Backup Configuration

# Backup all configuration files
oradba_sqlnet.sh --backup

# Creates timestamped backups:
# sqlnet.ora.20251219_143022.bak
# tnsnames.ora.20251219_143022.bak

Test Connections

# Test TNS alias
oradba_sqlnet.sh --test PRODDB

# Shows:
# - tnsping results
# - Connection descriptor
# - Connection status

List TNS Aliases

# List all defined aliases
oradba_sqlnet.sh --list

# Output:
# TNS Aliases in /u01/app/oracle/network/admin/tnsnames.ora:
# 1  ORCL
# 2  PRODDB
# 3  TESTDB

Configuration Files

File Locations

Configuration files are stored in $TNS_ADMIN or $ORACLE_HOME/network/admin:

$TNS_ADMIN/
├── sqlnet.ora       # Network configuration
├── tnsnames.ora     # Connection descriptors
└── ldap.ora         # LDAP naming (optional)

OraDBA automatically detects the correct location:

1. $TNS_ADMIN (if set)
2. $ORACLE_HOME/network/admin (if ORACLE_HOME set)
3. $HOME/.oracle/network/admin (fallback)

Variable Substitution

Templates support environment variable substitution:

Variable	Description	Example
${ORACLE_BASE}	Oracle base directory	/u01/app/oracle
${ORACLE_SID}	Database SID	PRODDB
${ORACLE_HOME}	Oracle home	/u01/app/oracle/product/19c

Example template:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet)
    )
  )

After substitution:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/PRODDB/wallet)
    )
  )

Security Configuration

Network Encryption

The secure template enables Oracle Advanced Security features:

Encryption Settings:

SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)

Encryption Levels:

• REQUIRED - Connections must be encrypted (recommended for production)
• REQUESTED - Encryption preferred but not mandatory
• ACCEPTED - Accept encrypted or unencrypted

Cipher Suites:

• AES256 - Strongest encryption (recommended)
• AES192 - Strong encryption
• AES128 - Standard encryption

Data Integrity

Checksumming prevents data tampering:

SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256, SHA384, SHA512)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA384, SHA512)

Hash Algorithms:

• SHA512 - Strongest integrity protection
• SHA384 - Strong integrity protection
• SHA256 - Standard integrity protection

Authentication

Supported Methods:

• BEQ - for native operating system authentication for operating systems other than Microsoft Windows.
• TCPS - TCP with SSL/TLS
• KERBEROS5 - Kerberos authentication

SQLNET.AUTHENTICATION_SERVICES = (TCPS, BEQ)

Secure Connections (TCPS)

For SSL/TLS encrypted connections:

ORCL_SECURE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = db.example.com)(PORT = 2484))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SECURITY = 
        (SSL_SERVER_CERT_DN = "CN=db.example.com,OU=IT,O=Corp,C=US")
      )
    )
  )

Requirements:

• Oracle Wallet with certificates
• Server certificate configuration
• Port 2484 (default TCPS port)

High Availability

Failover Configuration

Automatic client failover to secondary nodes:

ORCL_FO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
      (FAILOVER = ON)
      (LOAD_BALANCE = OFF)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 3)
        (DELAY = 5)
      )
    )
  )

Failover Types:

• SESSION - Reconnect after connection loss
• SELECT - Reconnect and restore SELECT state

Failover Methods:

• BASIC - Failover on connection failure
• PRECONNECT - Maintain backup connection

Load Balancing

Distribute connections across multiple nodes:

ORCL_LB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Benefits:

• Even distribution of client connections
• Better resource utilization
• Improved scalability

RAC Configuration

Oracle RAC SCAN listener:

ORCL_RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Best Practices:

• Use SCAN address for RAC clusters
• Single entry for entire cluster
• Automatic load balancing and failover

Best Practices

Development Environments

1. Use sqlnet.ora.basic template
2. Enable diagnostics for troubleshooting
3. Set reasonable timeouts (60/120 seconds)
4. Include HOSTNAME naming for flexibility

Production Environments

1. Use sqlnet.ora.secure template
2. Enable encryption (REQUIRED level)
3. Enable checksumming for integrity
4. Use wallet-based authentication
5. Set minimum logon version to 12+
6. Disable unnecessary naming methods
7. Regular security audits

Network Configuration

1. Timeouts:
2. Development: 60s inbound, 120s outbound
3. Production: 60s inbound, 120s outbound

4. Aggressive failover: 30s inbound, 30s outbound

5. Dead Connection Detection:

6. Set SQLNET.EXPIRE_TIME (10 minutes recommended)
7. Helps detect broken connections

8. Sends keep-alive packets

9. Connection Retry:

10. Set RETRY_COUNT for transient failures
11. Configure RETRY_DELAY between attempts
12. Use for unreliable networks

Backup and Recovery

1. Always backup before changes:

oradba_sqlnet.sh --backup

1. Test changes in non-production first

2. Keep backup files for rollback:

# Restore from backup
cp sqlnet.ora.20251219_143022.bak sqlnet.ora

1. Document custom changes in comments

Validation

1. Validate after changes:

oradba_sqlnet.sh --validate

1. Test connections:

oradba_sqlnet.sh --test PRODDB
tnsping PRODDB

1. Check syntax with tnsping:

tnsping <alias> 3

Troubleshooting

Connection Issues

Problem: Cannot connect to database

Solution:

# 1. Validate configuration
oradba_sqlnet.sh --validate

# 2. Test TNS alias
oradba_sqlnet.sh --test PRODDB

# 3. Use tnsping
tnsping PRODDB

# 4. Check listener status
lsnrctl status

Common Causes:

• TNS alias not defined
• Wrong host/port in tnsnames.ora
• Listener not running
• Network connectivity issues
• Firewall blocking connections

Encryption Issues

Problem: ORA-12650: No common encryption checksumming types

Cause: Client and server encryption settings don't match

Solution:

# 1. Check encryption settings on both sides
grep ENCRYPTION sqlnet.ora

# 2. Ensure compatible cipher suites
# Client: SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)
# Server: SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)

# 3. Change REQUIRED to REQUESTED for testing
SQLNET.ENCRYPTION_CLIENT = REQUESTED

TNS-12154: Could not resolve service name

Cause: TNS alias not found in tnsnames.ora

Solution:

# 1. List available aliases
oradba_sqlnet.sh --list

# 2. Generate missing entry
oradba_sqlnet.sh --generate PRODDB

# 3. Verify TNS_ADMIN
echo $TNS_ADMIN

# 4. Check tnsnames.ora location
ls -l $TNS_ADMIN/tnsnames.ora

Performance Issues

Problem: Slow connection establishment

Causes and Solutions:

1. High timeout values:

# Reduce timeouts
SQLNET.INBOUND_CONNECT_TIMEOUT = 30
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 30

1. HOSTNAME naming overhead:

# Remove HOSTNAME from naming methods
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

1. Network latency:

# Enable TCP_NODELAY
TCP.NODELAY = YES

Wallet Issues

Problem: Cannot access wallet

Solution:

# 1. Check wallet location
grep WALLET_LOCATION sqlnet.ora

# 2. Verify wallet exists and has correct permissions
ls -l $ORACLE_BASE/admin/$ORACLE_SID/wallet/

# 3. Test wallet access
mkstore -wrl $WALLET_LOCATION -list

Trace and Diagnostics

Enable tracing for troubleshooting:

# In sqlnet.ora
TRACE_LEVEL_CLIENT = 16
TRACE_LEVEL_SERVER = 16
TRACE_DIRECTORY_CLIENT = /tmp/oracle_trace
LOG_DIRECTORY_CLIENT = /tmp/oracle_log

After troubleshooting, disable tracing:

TRACE_LEVEL_CLIENT = OFF
TRACE_LEVEL_SERVER = OFF

Integration with OraDBA

With oraenv.sh

OraDBA's oraenv.sh automatically sets TNS_ADMIN:

# Source oraenv
. oraenv PRODDB

# TNS_ADMIN is now set
echo $TNS_ADMIN
# /u01/app/oracle/admin/PRODDB/network/admin

With oradba_install.sh

Templates are installed to:

/usr/local/oradba/templates/sqlnet/

Configuration Hierarchy

OraDBA respects this precedence:

1. $TNS_ADMIN/sqlnet.ora (highest priority)
2. $ORACLE_HOME/network/admin/sqlnet.ora
3. $HOME/.oracle/network/admin/sqlnet.ora (fallback)

Compliance and Standards

PCI-DSS Requirements

For PCI-DSS compliance:

1. Use sqlnet.ora.secure template
2. Enable AES256 encryption (REQUIRED)
3. Enable SHA256+ checksums
4. Set minimum logon version 12+
5. Regular security audits

HIPAA Compliance

For HIPAA compliance:

1. Encrypt all database connections
2. Use strong authentication (wallet/Kerberos)
3. Audit configuration changes
4. Maintain encryption key management

SOX Requirements

For SOX compliance:

1. Version control configurations
2. Change management process
3. Access control for configurations
4. Regular compliance reviews

Advanced Topics

Multiple TNS_ADMIN Locations

Support database-specific configurations:

# Set in profile
export TNS_ADMIN=/u01/app/oracle/admin/${ORACLE_SID}/network/admin

# Or per-connection
TNS_ADMIN=/custom/path sqlplus user/pass@PRODDB

Connection Pooling

Optimize application connections:

PRODDB_POOL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = proddb)
      (POOL_CONNECTION_CLASS = MYAPP)
    )
  )

LDAP Integration

Centralized naming with Oracle Internet Directory:

# Install LDAP template
cp $ORADBA_BASE/templates/sqlnet/ldap.ora.template $TNS_ADMIN/ldap.ora

# Configure for your environment
vi $TNS_ADMIN/ldap.ora

# Update sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES)

See Also

• Environment Management - TNS_ADMIN configuration
• Configuration - OraDBA configuration system
• Aliases - Network configuration file aliases (vit, vil, visql)
• Troubleshooting - Network troubleshooting

Navigation

Previous: Quick Reference
Next: Log Management