Oracle Network and Connectivity

A complete production-ready SOP for Oracle Network and Connectivity administration on Linux. Covers tnsnames.ora, listener.ora, sqlnet.ora management, SCAN listener configuration in RAC, connection pooling with DRCP, Oracle Connection Manager (CMAN) setup, and SSL/TLS for Oracle connections — with real commands, expected outputs, and consultant-level notes for both standard OFA and enterprise custom path conventions.

1. Document Info

2. Oracle Network — Concepts You Must Know First

📝 How does Oracle network connectivity work? When a client application connects to an Oracle database, the connection goes through Oracle Net (formerly SQL*Net). The client looks up the database address in tnsnames.ora (or LDAP/Easy Connect), connects to the Oracle Listener on the specified port, and the Listener hands the connection off to the database server process. Understanding this flow is essential for troubleshooting connection failures.

Oracle Net Connection Flow

CLIENT APPLICATION
       │
       │  1. Resolves service name via tnsnames.ora / Easy Connect / LDAP
       │
       ▼
ORACLE LISTENER (port 1521)
       │
       │  2. Listener accepts connection request
       │  3. Listener checks SID_LIST / service registry
       │  4. Spawns dedicated server process OR hands to shared server
       │
       ▼
DATABASE SERVER PROCESS
       │
       │  5. Server process authenticates user
       │  6. Session established
       │
       ▼
CLIENT RECEIVES CONNECTION

Key Oracle Network Files

📝 Where to put these files? By default Oracle looks in $ORACLE_HOME/network/admin/. In environments with multiple Oracle Homes on one server, you can also set TNS_ADMIN environment variable to point to a central location — all homes then use the same files.

Oracle Naming Methods (How Clients Resolve Service Names)

3. Path Conventions and Environment Details

4. listener.ora — Complete Management

📝 What is the listener? The Oracle Listener is a server-side process that accepts incoming client connection requests. It runs independently of the database — it can be up while the database is down (and vice versa). Without a running listener, clients cannot connect to the database remotely (local OS-authenticated connections still work).

4.1 — Basic listener.ora Structure and Configuration

📝 Two types of database registration with the listener:

• Dynamic registration — the database automatically registers itself with the listener when it starts. The PMON background process sends registration information. This is the default and recommended method.
• Static registration — you manually define the database in listener.ora. Required for RMAN duplicate, Data Guard, and when the DB is in MOUNT state and needs to accept connections.

# Edit listener.ora
su - oracle
vi $ORACLE_HOME/network/admin/listener.ora

Complete listener.ora example with all important configurations:

# -------------------------------------------------------
# listener.ora — Oracle Listener Configuration
# Server: dbserver01.company.com
# Modified: <date> by <DBA name>
# -------------------------------------------------------

# Listener name -- default is LISTENER
# You can have multiple listeners with different names and ports
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      # Primary protocol and port
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.company.com)(PORT = 1521))
      # IPC for local connections (faster than TCP for local)
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# Static database registration
# Required for: RMAN duplicate, Data Guard, DB in MOUNT state,
#               when PMON registration is not working
# If only using dynamic registration -- this section can be omitted
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      # GLOBAL_DBNAME = service name clients use to connect
      (GLOBAL_DBNAME = ORCL)
      # ORACLE_HOME = home of the database being registered
      (ORACLE_HOME   = /u01/app/oracle/product/19.3.0/dbhome_1)
      # SID_NAME = the actual Oracle SID
      (SID_NAME      = ORCL)
    )
    # For Data Guard Broker -- needs separate entry with _DGMGRL suffix
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME      = ORCL)
    )
    # For pluggable database (CDB environment)
    # (SID_DESC =
    #   (GLOBAL_DBNAME = PDB1)
    #   (ORACLE_HOME   = /u01/app/oracle/product/19.3.0/dbhome_1)
    #   (SID_NAME      = ORCL)
    # )
  )

# Listener logging
# LOGGING_LISTENER = ON   -- enable logging (default)
# LOG_FILE_LISTENER = listener   -- log file name (listener.log)
# LOG_DIRECTORY_LISTENER = /u01/app/oracle/product/19.3.0/dbhome_1/network/log

# Listener tracing (only enable for troubleshooting -- disable in production)
# TRACE_LEVEL_LISTENER = OFF   -- OFF, USER, ADMIN, SUPPORT
# TRACE_FILE_LISTENER = listener
# TRACE_DIRECTORY_LISTENER = /u01/app/oracle/product/19.3.0/dbhome_1/network/trace

# Security -- restrict listener administration to local connections only
# ADMIN_RESTRICTIONS_LISTENER = ON

4.2 — Configure Multiple Listeners on Same Server

📝 When to use multiple listeners? When you have multiple databases on the same server and want each on a different port, or when a specific application requires its own dedicated listener.

vi $ORACLE_HOME/network/admin/listener.ora

# Primary listener on 1521
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.company.com)(PORT = 1521))
    )
  )

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

# Secondary listener on 1522 for TESTDB
LISTENER_TEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.company.com)(PORT = 1522))
    )
  )

SID_LIST_LISTENER_TEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB)
      (ORACLE_HOME   = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME      = TESTDB)
    )
  )

4.3 — Listener Control Commands (lsnrctl)

su - oracle

# Start the default listener (named LISTENER)
lsnrctl start

# Start a specific named listener
lsnrctl start LISTENER_TEST

# Stop the listener
lsnrctl stop

# Stop specific listener
lsnrctl stop LISTENER_TEST

# Check listener status -- shows registered services and endpoints
lsnrctl status

# Status of specific listener
lsnrctl status LISTENER_TEST

# Show services registered with listener
lsnrctl services

# Reload listener -- picks up listener.ora changes without restart
# Does NOT drop existing connections
lsnrctl reload

# Show listener version
lsnrctl version

4.4 — Verify Dynamic Registration is Working

📝 How does dynamic registration work? When the database starts, the PMON background process sends a registration packet to the listener every 60 seconds containing the database’s service names, instance name, and dispatcher information. The listener adds these to its active service list.

# Check which services are dynamically registered
lsnrctl services

Expected output showing dynamically registered service:

Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

-- Force PMON to re-register with listener immediately
-- (instead of waiting up to 60 seconds)
sqlplus / as sysdba
ALTER SYSTEM REGISTER;

4.5 — Configure LOCAL_LISTENER Parameter

📝 Why set LOCAL_LISTENER? By default PMON registers with listeners on port 1521. If your listener is on a non-standard port, or if you have multiple listeners, set this parameter to tell PMON which listener(s) to register with.

sqlplus / as sysdba

-- Check current LOCAL_LISTENER setting
SHOW PARAMETER local_listener;

-- Set LOCAL_LISTENER to register with specific listener
ALTER SYSTEM SET LOCAL_LISTENER=
    '(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1521))'
    SCOPE=BOTH;

-- For multiple listeners
ALTER SYSTEM SET LOCAL_LISTENER=
    '(ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1522))
    )'
    SCOPE=BOTH;

-- Force re-registration
ALTER SYSTEM REGISTER;

4.6 — Listener Log Analysis

📝 Why check listener logs? The listener log records every connection attempt — successful and failed. When clients report connection failures, the listener log is the first place to check. It shows the exact error and the client address.

# Listener log location
ls -lh $ORACLE_HOME/network/log/listener.log

# View last 100 lines of listener log
tail -100 $ORACLE_HOME/network/log/listener.log

# Find connection errors
grep -E "TNS-|error|refused" $ORACLE_HOME/network/log/listener.log | tail -50

# Find connections from a specific client IP
grep "192.168.1.100" $ORACLE_HOME/network/log/listener.log | tail -20

# Count connection attempts per hour
grep "CONNECT_DATA" $ORACLE_HOME/network/log/listener.log | \
    awk '{print $1,$2}' | cut -d: -f1 | sort | uniq -c

# Rotate listener log (when it gets too large -- > 500MB)
# The log cannot be truncated while listener is running
# Use lsnrctl to rotate:
lsnrctl set log_status off
mv $ORACLE_HOME/network/log/listener.log \
   $ORACLE_HOME/network/log/listener_$(date +%Y%m%d).log
lsnrctl set log_status on

5. tnsnames.ora — Complete Management

📝 What is tnsnames.ora? tnsnames.ora is a client-side name resolution file. It maps logical service names (aliases) to the actual connection details — hostname, port, and service name. When an application connects using a service name like ORCL, Oracle Net looks up ORCL in tnsnames.ora to find the actual connection string.

5.1 — tnsnames.ora Structure and Syntax

# Edit tnsnames.ora
vi $ORACLE_HOME/network/admin/tnsnames.ora

Complete tnsnames.ora with all common configurations:

# -------------------------------------------------------
# tnsnames.ora
# Location: $ORACLE_HOME/network/admin/tnsnames.ora
# Modified: <date> by <DBA name>
# -------------------------------------------------------

# Basic entry -- single database, dedicated server
ORCL =
  (DESCRIPTION =
    # ADDRESS_LIST defines where to connect
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST     = dbserver01.company.com)
        (PORT     = 1521)
      )
    )
    (CONNECT_DATA =
      # SERVICE_NAME is the preferred way (not SID)
      # Allows connection load balancing and failover
      (SERVICE_NAME = ORCL)
      # SERVER = DEDICATED forces dedicated server connection
      # Remove this line if using shared server
      (SERVER = DEDICATED)
    )
  )

# Entry with failover -- tries PRIMARY first, then SECONDARY
ORCL_HA =
  (DESCRIPTION =
    # FAILOVER = ON -- if first address fails, try the next
    (FAILOVER = ON)
    # LOAD_BALANCE = OFF -- connect to addresses in order, not randomly
    (LOAD_BALANCE = OFF)
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST     = dbserver01.company.com)
        (PORT     = 1521)
      )
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST     = dbserver02.company.com)
        (PORT     = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
      (FAILOVER_MODE =
        (TYPE   = SELECT)    -- session failover (SELECT only)
        (METHOD = BASIC)     -- reconnect after failure
        (RETRIES = 3)        -- retry 3 times
        (DELAY   = 3)        -- wait 3 seconds between retries
      )
    )
  )

# Entry for RAC -- connects via SCAN listener
# SCAN distributes connections across all RAC nodes automatically
RACDB =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST     = rac-scan.company.com)   -- SCAN hostname
        (PORT     = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = RACDB_APP)  -- RAC service name
      (SERVER = DEDICATED)
    )
  )

# Entry for Data Guard standby (read-only access)
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST     = dbserver02.company.com)
        (PORT     = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL_STBY)
      (UR = A)   -- UR=A allows connection even in restricted/mount mode
    )
  )

# Entry with wallet/SSL (for encrypted connections)
ORCL_SSL =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCPS)
      (HOST     = dbserver01.company.com)
      (PORT     = 2484)     -- TCPS default port
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
    (SECURITY =
      (MY_WALLET_DIRECTORY = /u01/app/oracle/wallet/client)
      (SSL_SERVER_CERT_DN  = "CN=dbserver01,OU=DBTeam,O=Company,C=US")
    )
  )

# Entry for CDB with PDB
PDB1 =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST     = dbserver01.company.com)
      (PORT     = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PDB1)   -- PDB service name
    )
  )

5.2 — Verify tnsnames.ora Entries Work

# Test TNS resolution without actually connecting
tnsping ORCL

# Expected output:
# Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
# (HOST=dbserver01.company.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
# OK (10 msec)

# Test multiple entries
tnsping ORCL_STBY
tnsping RACDB
tnsping PDB1

# Show what tnsnames.ora tnsping is using
tnsping ORCL | head -5

# Test actual connection (not just resolution)
sqlplus system/Oracle_123@ORCL

# Test with SYSDBA
sqlplus sys/Oracle_123@ORCL as sysdba

# Test from remote client (substitute client's connection string)
sqlplus hr/Hr_123@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

5.3 — TNS_ADMIN — Central tnsnames.ora Location

📝 What is TNS_ADMIN? By default each Oracle Home has its own tnsnames.ora. When you have many Oracle Homes or many servers, maintaining separate tnsnames.ora files is error-prone. Setting TNS_ADMIN to a central network directory means all homes share a single tnsnames.ora.

# Set TNS_ADMIN to central location
# Add to oracle user's ~/.bash_profile
vi ~/.bash_profile

# -------------------------------------------------------
# Central Oracle Net configuration
# -------------------------------------------------------

# TNS_ADMIN points all Oracle tools to central network config
# All tnsnames.ora, sqlnet.ora, ldap.ora stored here
export TNS_ADMIN=/u01/app/oracle/network/admin
# Convention B: export TNS_ADMIN=/oracle/network/admin

# Create central directory and copy files there
mkdir -p /u01/app/oracle/network/admin
cp $ORACLE_HOME/network/admin/tnsnames.ora \
   /u01/app/oracle/network/admin/
cp $ORACLE_HOME/network/admin/sqlnet.ora \
   /u01/app/oracle/network/admin/
cp $ORACLE_HOME/network/admin/listener.ora \
   /u01/app/oracle/network/admin/

# Apply profile
. ~/.bash_profile

# Verify tnsping uses the central file
tnsping ORCL

5.4 — Easy Connect (No tnsnames.ora Required)

📝 What is Easy Connect? Easy Connect allows connecting without a tnsnames.ora entry by specifying the connection details directly in the connection string. Very useful for quick connections, testing, and scripts.

# Easy Connect format: user/password@host:port/service_name
sqlplus hr/Hr_123@dbserver01.company.com:1521/ORCL

# Short form (default port 1521)
sqlplus hr/Hr_123@dbserver01.company.com/ORCL

# With role
sqlplus sys/Oracle_123@dbserver01.company.com:1521/ORCL as sysdba

# Easy Connect Plus (19c) -- more options
# Syntax: user@host:port/service?option1=value1&option2=value2
sqlplus hr/Hr_123@"dbserver01.company.com:1521/ORCL?connect_timeout=10&retry_count=3"

-- Ensure Easy Connect is enabled in sqlnet.ora
-- NAMES.DIRECTORY_PATH controls which naming methods are tried and in what order
-- Add EZCONNECT to enable Easy Connect

6. sqlnet.ora — Complete Management

📝 What is sqlnet.ora? sqlnet.ora controls the behavior of Oracle Net at both client and server level. It configures naming methods, connection timeouts, dead connection detection, encryption, tracing, and more. Every Oracle environment should have a properly configured sqlnet.ora.

6.1 — Complete sqlnet.ora Configuration

vi $ORACLE_HOME/network/admin/sqlnet.ora

# -------------------------------------------------------
# sqlnet.ora — Oracle Net Client/Server Configuration
# Location: $ORACLE_HOME/network/admin/sqlnet.ora
# Modified: <date> by <DBA name>
# -------------------------------------------------------

# -------------------------------------------------------
# NAMING METHODS
# Controls how Oracle resolves service names
# Order matters: Oracle tries methods left to right
# TNSNAMES = look in local tnsnames.ora
# EZCONNECT = use Easy Connect string format
# LDAP = use LDAP/OID directory
# -------------------------------------------------------
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

# -------------------------------------------------------
# CONNECTION TIMEOUTS
# -------------------------------------------------------

# SQLNET.OUTBOUND_CONNECT_TIMEOUT
# How long to wait for a connection to be established (seconds)
# Prevents hanging indefinitely when server is unreachable
# Recommended: 10-30 seconds
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 15

# SQLNET.INBOUND_CONNECT_TIMEOUT
# How long the server waits for the client to complete authentication
# after the initial TCP connection is established (seconds)
# Protects against denial-of-service attacks
# Recommended: 60 seconds
SQLNET.INBOUND_CONNECT_TIMEOUT = 60

# SQLNET.SEND_TIMEOUT
# How long to wait for a send operation to complete (seconds)
SQLNET.SEND_TIMEOUT = 60

# SQLNET.RECV_TIMEOUT
# How long to wait for a receive operation to complete (seconds)
SQLNET.RECV_TIMEOUT = 60

# -------------------------------------------------------
# DEAD CONNECTION DETECTION (DCD)
# Detects and cleans up dead client connections on the server
# -------------------------------------------------------

# SQLNET.EXPIRE_TIME (minutes)
# Server sends a probe packet every N minutes to check if client is alive
# If client does not respond -- server cleans up the connection
# Recommended: 10 minutes for production
# Prevents ghost sessions accumulating after client crashes
SQLNET.EXPIRE_TIME = 10

# -------------------------------------------------------
# NATIVE NETWORK ENCRYPTION (NNE)
# Encrypts data in transit between client and server
# NOTE: This is different from TDE which encrypts data at rest
# No license required for NNE in Oracle 19c
# -------------------------------------------------------

# SQLNET.ENCRYPTION_SERVER and SQLNET.ENCRYPTION_CLIENT
# Values: REQUIRED, REQUESTED, ACCEPTED, REJECTED
# REQUIRED = always encrypt (reject unencrypted connections)
# REQUESTED = prefer encryption, accept unencrypted
# ACCEPTED = accept either encrypted or unencrypted
# REJECTED = never encrypt
SQLNET.ENCRYPTION_SERVER = REQUESTED
SQLNET.ENCRYPTION_CLIENT = REQUESTED

# Encryption algorithms in preference order
# AES256 and AES192 are the strongest currently available
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)

# Data integrity (checksumming)
# Ensures data is not tampered with in transit
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUESTED
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUESTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA384, SHA512)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256, SHA384, SHA512)

# -------------------------------------------------------
# LOGGING AND TRACING
# -------------------------------------------------------

# Log location for sqlnet errors
LOG_DIRECTORY_CLIENT  = /tmp
LOG_DIRECTORY_SERVER  = /u01/app/oracle/product/19.3.0/dbhome_1/network/log
LOG_FILE_CLIENT       = sqlnet_client
LOG_FILE_SERVER       = sqlnet_server

# Tracing (ONLY enable for troubleshooting -- disable in production)
# Trace level: OFF, USER, ADMIN, SUPPORT
# SUPPORT level generates massive files -- use carefully
TRACE_LEVEL_CLIENT  = OFF
TRACE_LEVEL_SERVER  = OFF
# TRACE_DIRECTORY_CLIENT  = /tmp
# TRACE_DIRECTORY_SERVER  = /tmp

# -------------------------------------------------------
# TDE WALLET LOCATION (if TDE is configured)
# -------------------------------------------------------
# ENCRYPTION_WALLET_LOCATION =
#   (SOURCE =
#     (METHOD = FILE)
#     (METHOD_DATA =
#       (DIRECTORY = /u01/app/oracle/admin/ORCL/wallet)
#     )
#   )

# -------------------------------------------------------
# ADDITIONAL SECURITY
# -------------------------------------------------------

# Prevent unauthorized database access by limiting remote OS authentication
# SQLNET.AUTHENTICATION_SERVICES = (NONE)   -- disable OS auth
# SQLNET.AUTHENTICATION_SERVICES = (NTS)    -- Windows only
# SQLNET.AUTHENTICATION_SERVICES = (TCPS)   -- SSL/TLS auth
# Leave commented for standard OS authentication on Linux

6.2 — Dead Connection Detection — Why It Matters

📝 What is Dead Connection Detection (DCD)? When a client process crashes or the network between client and server goes down, the server-side process (shadow process) does not immediately know. Without DCD, these orphaned server processes accumulate, consuming memory and connection slots. DCD sends a probe packet periodically to detect dead clients and clean them up.

-- Check current ghost/dead sessions (connected but no activity for hours)
set linesize 200
set pagesize 100
col username      for a15
col machine       for a25
col program       for a30
col status        for a10
col last_call_et  for 9999999
col logon_time    for a25

SELECT sid, username, machine, program, status,
       last_call_et               seconds_idle,
       TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time
FROM   v$session
WHERE  type         = 'USER'
AND    last_call_et > 3600      -- idle for more than 1 hour
AND    status       = 'INACTIVE'
ORDER BY last_call_et DESC;

6.3 — Configure Trace for Troubleshooting

📝 When to enable tracing? Only when you have a specific connection problem that you cannot diagnose from listener logs and error messages alone. Tracing generates large files quickly — always disable it after collecting the trace.

# Enable trace for a specific client session (client-side)
# Add to client's sqlnet.ora temporarily
# TRACE_LEVEL_CLIENT = SUPPORT   -- most detailed
# TRACE_DIRECTORY_CLIENT = /tmp
# TRACE_FILE_CLIENT = sqlnet_client
# TRACE_UNIQUE_CLIENT = ON   -- separate file per connection

# Enable server-side trace (SERVER sqlnet.ora)
vi $ORACLE_HOME/network/admin/sqlnet.ora

# Add temporarily for troubleshooting -- remove after collecting trace
TRACE_LEVEL_SERVER  = ADMIN
TRACE_DIRECTORY_SERVER = /tmp/oracle_traces
TRACE_FILE_SERVER   = sqlnet_server

# Reload listener to pick up trace settings
lsnrctl reload

# Reproduce the problem
# Then collect the trace files
ls -lh /tmp/oracle_traces/

# IMPORTANT: Disable trace immediately after collecting
# Revert sqlnet.ora and reload

7. SCAN Listener Configuration in RAC

📝 What is SCAN? SCAN (Single Client Access Name) is the recommended connection method for Oracle RAC. Instead of clients needing to know the individual node addresses, they connect to a single SCAN hostname that resolves to 1-3 IPs. The SCAN listener distributes connections across all RAC nodes. If a node fails, the SCAN listener automatically redirects connections to surviving nodes.

📝 Why is SCAN better than node VIPs? Before SCAN, clients connected to node-specific VIPs. When nodes were added or removed from the cluster, all client tnsnames.ora files had to be updated. With SCAN, the connection string never changes — only the DNS record for the SCAN hostname changes, and that is managed by the DBA.

7.1 — SCAN Architecture

CLIENT
  │
  │ connects to: rac-scan.company.com:1521
  │
  ▼
DNS SERVER
  ├── rac-scan.company.com → 192.168.1.15
  ├── rac-scan.company.com → 192.168.1.16
  └── rac-scan.company.com → 192.168.1.17
           (3 SCAN IPs -- DNS round-robin)
  │
  ▼
SCAN LISTENER (on all 3 SCAN IPs)
  │
  ├── Routes to: racnode1:1521 (local listener)
  │              racnode2:1521 (local listener)
  │
  ▼
LOCAL LISTENER (on each node)
  │
  ▼
DATABASE INSTANCE (RACDB1 on node1, RACDB2 on node2)

7.2 — Check SCAN Configuration

# As grid user -- all SCAN management is done as grid
su - grid

# Check SCAN name and IPs
$ORACLE_HOME/bin/srvctl config scan

# Expected output:
# SCAN name: rac-scan.company.com, Network: 1
# Subnet IPv4: 192.168.1.0/255.255.255.0/eth0, static
# SCAN 1 IPv4 VIP: 192.168.1.15
# SCAN 2 IPv4 VIP: 192.168.1.16
# SCAN 3 IPv4 VIP: 192.168.1.17

# Check SCAN listener status
$ORACLE_HOME/bin/srvctl status scan_listener

# Expected output:
# SCAN Listener LISTENER_SCAN1 is enabled
# SCAN listener LISTENER_SCAN1 is running on node racnode1
# SCAN Listener LISTENER_SCAN2 is enabled
# SCAN listener LISTENER_SCAN2 is running on node racnode2
# SCAN Listener LISTENER_SCAN3 is enabled
# SCAN listener LISTENER_SCAN3 is running on node racnode1

# Detailed SCAN listener config
$ORACLE_HOME/bin/srvctl config scan_listener

7.3 — Check Services Registered with SCAN Listener

su - grid

# Check what services are registered with each SCAN listener
# Run on each SCAN listener
lsnrctl status LISTENER_SCAN1
lsnrctl status LISTENER_SCAN2
lsnrctl status LISTENER_SCAN3

# Check services registered
lsnrctl services LISTENER_SCAN1

7.4 — Manage SCAN Listener

su - grid

# Start SCAN listener
$ORACLE_HOME/bin/srvctl start scan_listener

# Stop SCAN listener
$ORACLE_HOME/bin/srvctl stop scan_listener

# Start specific SCAN listener (by number 1, 2, or 3)
$ORACLE_HOME/bin/srvctl start scan_listener -scannumber 1

# Stop specific SCAN listener
$ORACLE_HOME/bin/srvctl stop scan_listener -scannumber 2

# Relocate SCAN listener to a different node
# (for maintenance on current node)
$ORACLE_HOME/bin/srvctl relocate scan_listener \
    -scannumber 1 \
    -n racnode2

7.5 — Configure Local Listeners in RAC

📝 In RAC, each node has its own local listener on port 1521 that handles connections redirected from the SCAN listener. The SCAN listener accepts the initial connection and redirects it to the local listener on the appropriate node.

su - grid

# Check local listener configuration on all nodes
$ORACLE_HOME/bin/srvctl config listener

# Check local listener status on all nodes
$ORACLE_HOME/bin/srvctl status listener

# Start local listener on all nodes
$ORACLE_HOME/bin/srvctl start listener

# Start listener on specific node only
$ORACLE_HOME/bin/srvctl start listener -n racnode1

# Stop listener on specific node
$ORACLE_HOME/bin/srvctl stop listener -n racnode2

7.6 — RAC tnsnames.ora for Clients

📝 Clients connecting to RAC should use the SCAN hostname. This is simpler and more resilient than connecting directly to node VIPs.

# Client tnsnames.ora entry for RAC (using SCAN -- preferred)
vi $ORACLE_HOME/network/admin/tnsnames.ora

# RAC connection using SCAN (preferred)
# Clients just need to know the SCAN hostname
RACDB =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST     = rac-scan.company.com)
      (PORT     = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = RACDB_APP)
    )
  )

# RAC connection with explicit load balancing across multiple SCAN IPs
# Use this if DNS round-robin is not configured for SCAN
RACDB_LB =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.15)(PORT=1521))
      (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.16)(PORT=1521))
      (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.17)(PORT=1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = RACDB_APP)
    )
  )

7.7 — Verify SCAN DNS Resolution

# Verify SCAN resolves to 1-3 IPs from client
nslookup rac-scan.company.com

# Expected output (3 IPs for production):
# Server:   192.168.1.1
# Name:    rac-scan.company.com
# Address: 192.168.1.15
# Name:    rac-scan.company.com
# Address: 192.168.1.16
# Name:    rac-scan.company.com
# Address: 192.168.1.17

# Also check forward and reverse resolution
host rac-scan.company.com
host 192.168.1.15

# Test actual connection through SCAN
sqlplus system/Oracle_123@rac-scan.company.com:1521/RACDB_APP

# Tnsping through SCAN
tnsping RACDB

8. DRCP — Database Resident Connection Pooling

📝 What is DRCP? DRCP (Database Resident Connection Pooling) is an Oracle connection pool that lives inside the database server. When many application connections share a small number of pooled server processes, DRCP dramatically reduces memory consumption. Each connection in the pool is a pooled server process that can be reused by different client connections.

📝 When to use DRCP? DRCP is ideal for web applications with many short-lived connections — PHP, Python, Node.js, web services. A web application with 1000 concurrent users needs 1000 database connections without DRCP. With DRCP, those 1000 users can share, say, 50 pooled server processes.

📝 When NOT to use DRCP? Long-running connections (batch jobs, ETL), applications that heavily use session-level state (DBMS_APPLICATION_INFO, temporary tables), or dedicated application servers that already implement their own connection pool (like Oracle UCP or application server pools).

8.1 — Configure and Start DRCP

-- Connect as SYSDBA
sqlplus / as sysdba

-- Check if DRCP is already configured
SELECT connection_pool, status, maxsize, minsize, incrsize,
       session_cached_cursors, inactivity_timeout,
       max_think_time
FROM   dba_cpool_info;

-- Start DRCP with configuration
-- This configures and starts the pool in one command
EXEC DBMS_CONNECTION_POOL.START_POOL(
    pool_name              => 'SYS_DEFAULT_CONNECTION_POOL',
    minsize                => 4,       -- minimum pooled server processes
    maxsize                => 40,      -- maximum pooled server processes
    incrsize               => 2,       -- grow by 2 when demand exceeds current
    session_cached_cursors => 20,      -- cursors cached per pooled session
    inactivity_timeout     => 300,     -- idle connection timeout (seconds)
    max_think_time         => 120,     -- max time client holds a connection (seconds)
    max_use_session        => 500000,  -- reuse a server process max N times
    max_lifetime_session   => 86400    -- max lifetime of a server process (seconds)
);

-- Verify DRCP started
SELECT connection_pool, status, minsize, maxsize, incrsize
FROM   dba_cpool_info;

8.2 — Modify DRCP Configuration

-- Modify existing DRCP configuration
EXEC DBMS_CONNECTION_POOL.ALTER_PARAM(
    pool_name   => 'SYS_DEFAULT_CONNECTION_POOL',
    minsize     => 5,
    maxsize     => 50,
    incrsize    => 5
);

-- Stop the pool gracefully
EXEC DBMS_CONNECTION_POOL.STOP_POOL(
    pool_name => 'SYS_DEFAULT_CONNECTION_POOL'
);

-- Stop immediately (drops all active connections)
EXEC DBMS_CONNECTION_POOL.STOP_POOL(
    pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
    immediate => TRUE
);

8.3 — Connect Using DRCP

📝 To use DRCP, clients must connect using a pooled server connection. The connection string must specify SERVER=POOLED in the CONNECT_DATA section.

# Add DRCP entry to tnsnames.ora
vi $ORACLE_HOME/network/admin/tnsnames.ora

# DRCP connection entry
# SERVER=POOLED tells Oracle to use DRCP pool
ORCL_POOLED =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST     = dbserver01.company.com)
      (PORT     = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
      (SERVER       = POOLED)    -- This is what enables DRCP
    )
  )

# Test DRCP connection
sqlplus hr/Hr_123@ORCL_POOLED

8.4 — Monitor DRCP

-- Monitor DRCP pool usage
set linesize 200
set pagesize 100
col connection_pool   for a35
col status            for a12
col num_open_servers  for 9999
col num_busy_servers  for 9999
col num_waiting_requests for 9999

SELECT connection_pool,
       status,
       num_open_servers,
       num_busy_servers,
       num_waiting_requests
FROM   v$cpool_stats;

-- DRCP statistics per database
set linesize 200
set pagesize 100

SELECT num_requests,
       num_hits,         -- requests served from pool (reused connection)
       num_misses,       -- new connection had to be created
       num_waits,        -- requests that had to wait for a server
       ROUND(num_hits*100/NULLIF(num_requests,0),2) hit_pct
FROM   v$cpool_stats;

📝 What to look for: hit_pct should be above 90%. High num_misses means pool is too small — increase MAXSIZE. High num_waits means all servers are busy — also increase MAXSIZE.

9. Oracle Connection Manager (CMAN)

📝 What is CMAN? Oracle Connection Manager is a proxy server that sits between clients and the database. It provides:

• Connection multiplexing — many client connections share fewer network connections to the database (reduces database server overhead)
• Access control — filter connections based on IP, username, or service name
• Protocol conversion — connect clients using one protocol to databases using another
• Firewall traversal — allows Oracle connections through firewalls with minimal port opening

📝 When is CMAN used? In large enterprise environments where you have thousands of client connections and want to reduce the connection overhead on the database server. Also used in DMZ environments where a database is in a secure zone and clients are in a less secure zone — CMAN sits at the boundary and filters connections.

9.1 — Install CMAN Software

📝 CMAN comes with Oracle Database software but is typically installed on a separate dedicated server — not on the database server itself. It acts as a middle tier.

# CMAN is installed as part of Oracle Database software
# Verify CMAN binary exists in Oracle Home
ls -lh $ORACLE_HOME/bin/cmctl
ls -lh $ORACLE_HOME/bin/cmadmin

9.2 — Create cman.ora Configuration File

# cman.ora goes in ORACLE_HOME/network/admin/ on the CMAN server
vi $ORACLE_HOME/network/admin/cman.ora

# -------------------------------------------------------
# cman.ora — Oracle Connection Manager Configuration
# CMAN Server: cmanserver01.company.com
# Modified: <date> by <DBA name>
# -------------------------------------------------------

# Define the Connection Manager instance
CMAN =
  (CONFIGURATION =

    # Address where CMAN listens for client connections
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST     = cmanserver01.company.com)
      (PORT     = 1521)
    )

    # Parameter section
    (PARAMETER_LIST =

      # Maximum number of simultaneous connections CMAN will handle
      (MAX_CONNECTIONS  = 1024)

      # Idle connection timeout in seconds
      # Closes connections idle for more than this many seconds
      (IDLE_TIMEOUT     = 0)

      # Session timeout in seconds (0 = no timeout)
      (SESSION_TIMEOUT  = 0)

      # Number of CMAN gateway processes
      # More gateways = more parallel connections
      (MAX_GATEWAY_PROCESSES  = 16)
      (MIN_GATEWAY_PROCESSES  = 2)

      # Logging
      (LOG_LEVEL        = USER)
      (LOG_DIRECTORY    = /u01/app/oracle/cman/log)

      # Tracing (disable in production)
      (TRACE_LEVEL      = OFF)
      (TRACE_DIRECTORY  = /u01/app/oracle/cman/trace)

      # Connection pooling parameters
      (MAXIMUM_RELAYS           = 512)
      (CONNECTION_STATISTICS    = YES)
      (SHOW_TNS_INFO            = YES)
    )

    # Access Control List -- defines which connections are allowed or denied
    # Rules are evaluated top to bottom -- first match wins
    (RULE_LIST =

      # Allow all connections from internal network to ORCL database
      (RULE =
        (SRC_HOST    = 192.168.1.0/24)       -- source IP range
        (DST_HOST    = dbserver01.company.com) -- destination DB server
        (SRV         = ORCL)                   -- service name
        (ACT         = accept)                 -- allow
      )

      # Allow specific host to connect to any service
      (RULE =
        (SRC_HOST    = 10.10.1.50)
        (DST_HOST    = dbserver01.company.com)
        (SRV         = *)
        (ACT         = accept)
      )

      # Deny all other connections
      (RULE =
        (SRC_HOST    = *)
        (DST_HOST    = *)
        (SRV         = *)
        (ACT         = reject)
      )
    )
  )

9.3 — Start and Manage CMAN

# Start CMAN
su - oracle
cmctl start cman

# Check CMAN status
cmctl show status

# Show connections currently being handled by CMAN
cmctl show connections

# Show gateway processes
cmctl show gateways

# Stop CMAN gracefully
cmctl stop cman

# Reload CMAN configuration without stopping
cmctl reload cman

9.4 — Configure Clients to Connect Through CMAN

📝 Clients connect to CMAN exactly as they would connect to the database directly — they use tnsnames.ora with CMAN’s address. CMAN then forwards the connection to the actual database.

# On CLIENT machines -- update tnsnames.ora to point to CMAN
vi $ORACLE_HOME/network/admin/tnsnames.ora

# Client connects to CMAN which forwards to actual database
ORCL_VIA_CMAN =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST     = cmanserver01.company.com)  -- CMAN server, not DB server
      (PORT     = 1521)                       -- CMAN listening port
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)                  -- actual database service name
    )
  )

9.5 — Monitor CMAN Statistics

# Show detailed connection statistics
cmctl show status -con_name all

# Show version
cmctl show version

# Show parameters
cmctl show parameters

10. SSL/TLS for Oracle Connections

📝 What is SSL/TLS for Oracle? Configuring SSL/TLS (TCPS protocol) encrypts the network connection between Oracle client and server using certificates. It provides:

• Encryption in transit — all data between client and server is encrypted
• Server authentication — client verifies it is connecting to the correct server
• Client authentication — server verifies the client’s identity (optional — mutual authentication)

📝 SSL/TLS vs Native Network Encryption (NNE):

• NNE (sqlnet.ora ENCRYPTION) — simpler to configure, no certificates needed, Oracle-proprietary
• SSL/TLS (TCPS) — industry standard, uses certificates, required for compliance with some standards (PCI-DSS etc.)

⚠️ IMPORTANT: SSL/TLS configuration requires creating a wallet (Oracle Wallet or Java KeyStore) containing the certificate. The wallet must be present on BOTH the server and client.

10.1 — Create Oracle Wallet for SSL

📝 Two types of certificates:

• Self-signed certificate — created by yourself. Free, easy. Not trusted by external clients without manually importing it. Use for internal connections.
• CA-signed certificate — issued by a Certificate Authority (VeriSign, DigiCert etc.). Trusted automatically by clients. Use for external-facing connections.

# Create wallet directory on SERVER
su - oracle
mkdir -p /u01/app/oracle/wallet/server
chmod 700 /u01/app/oracle/wallet/server

# Create an Oracle Wallet using orapki
# This creates an empty wallet with the wallet password
orapki wallet create \
    -wallet /u01/app/oracle/wallet/server \
    -pwd WalletPwd_123 \
    -auto_login

# Verify wallet was created
ls -lh /u01/app/oracle/wallet/server/
# Expected files: cwallet.sso, ewallet.p12

10.2 — Generate Self-Signed Certificate

# Add a self-signed certificate to the server wallet
# -keysize 2048 = RSA 2048-bit key (minimum recommended)
# -validity 3650 = valid for 10 years (3650 days)
orapki wallet add \
    -wallet /u01/app/oracle/wallet/server \
    -pwd WalletPwd_123 \
    -dn "CN=dbserver01,OU=DBTeam,O=Company,C=US" \
    -keysize 2048 \
    -self_signed \
    -validity 3650

# View wallet contents -- confirm certificate was added
orapki wallet display \
    -wallet /u01/app/oracle/wallet/server \
    -pwd WalletPwd_123

10.3 — Export Certificate for Client Import

# Export the server certificate so clients can trust it
orapki wallet export \
    -wallet /u01/app/oracle/wallet/server \
    -pwd WalletPwd_123 \
    -dn "CN=dbserver01,OU=DBTeam,O=Company,C=US" \
    -cert /tmp/server_cert.crt

# The server_cert.crt file must be copied to client machines
# and imported into their wallets

10.4 — Configure Listener for SSL (TCPS)

# Edit listener.ora to add TCPS address
vi $ORACLE_HOME/network/admin/listener.ora

# Add SSL/TLS listener configuration
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      # Regular TCP on 1521 (keep for backward compatibility)
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.company.com)(PORT = 1521))
    )
    (DESCRIPTION =
      # SSL/TLS on 2484 (standard TCPS port)
      (ADDRESS = (PROTOCOL = TCPS)(HOST = dbserver01.company.com)(PORT = 2484))
    )
  )

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

# SSL wallet location for LISTENER
SSL_CLIENT_AUTHENTICATION = FALSE   -- TRUE for mutual (2-way) SSL
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet/server)
    )
  )

10.5 — Configure sqlnet.ora for SSL on Server

vi $ORACLE_HOME/network/admin/sqlnet.ora

# Add SSL configuration to server sqlnet.ora

# SSL wallet location
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet/server)
    )
  )

# SSL version to use
# TLSv1.2 is current minimum. TLSv1.3 if all clients support it
SSL_VERSION = 1.2

# SSL cipher suites -- list in preference order
# Use strong ciphers only
SSL_CIPHER_SUITES =
  (SSL_RSA_WITH_AES_256_CBC_SHA256,
   SSL_RSA_WITH_AES_128_CBC_SHA256,
   SSL_RSA_WITH_AES_256_CBC_SHA)

# SSL_CLIENT_AUTHENTICATION controls 2-way (mutual) SSL
# FALSE = server-only authentication (most common)
# TRUE  = both client and server must present certificates
SSL_CLIENT_AUTHENTICATION = FALSE

# SQLNET authentication for TCPS connections
SQLNET.AUTHENTICATION_SERVICES = (TCPS, NTS, BEQ)

10.6 — Configure Client for SSL

# On CLIENT machine -- create client wallet
mkdir -p /u01/app/oracle/wallet/client
chmod 700 /u01/app/oracle/wallet/client

# Create client wallet
orapki wallet create \
    -wallet /u01/app/oracle/wallet/client \
    -pwd ClientWallet_123 \
    -auto_login

# Import the server certificate into client wallet
# (server_cert.crt was copied from server in Section 10.3)
orapki wallet add \
    -wallet /u01/app/oracle/wallet/client \
    -pwd ClientWallet_123 \
    -trusted_cert \
    -cert /tmp/server_cert.crt

# Verify client wallet contains the trusted certificate
orapki wallet display \
    -wallet /u01/app/oracle/wallet/client \
    -pwd ClientWallet_123

10.7 — Configure Client sqlnet.ora for SSL

vi $ORACLE_HOME/network/admin/sqlnet.ora

# Client sqlnet.ora SSL configuration

# Client wallet location
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet/client)
    )
  )

SSL_VERSION = 1.2

SSL_CIPHER_SUITES =
  (SSL_RSA_WITH_AES_256_CBC_SHA256,
   SSL_RSA_WITH_AES_128_CBC_SHA256)

SSL_CLIENT_AUTHENTICATION = FALSE

# Enable TCPS in naming
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

SQLNET.AUTHENTICATION_SERVICES = (TCPS, NTS, BEQ)

10.8 — Configure tnsnames.ora for SSL Connection

vi $ORACLE_HOME/network/admin/tnsnames.ora

# SSL/TLS connection entry
# PROTOCOL = TCPS (not TCP) for SSL
# PORT = 2484 (standard TCPS port)
ORCL_SSL =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCPS)
      (HOST     = dbserver01.company.com)
      (PORT     = 2484)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
    (SECURITY =
      # Client wallet location
      (MY_WALLET_DIRECTORY = /u01/app/oracle/wallet/client)
      # Server certificate DN -- client verifies server presents this DN
      (SSL_SERVER_CERT_DN  = "CN=dbserver01,OU=DBTeam,O=Company,C=US")
    )
  )

10.9 — Restart Listener and Test SSL Connection

# Restart listener to pick up SSL configuration
lsnrctl stop
lsnrctl start
lsnrctl status

# Verify TCPS endpoint is listening
lsnrctl status | grep -i "tcps\|2484"

# Test SSL connection
tnsping ORCL_SSL
sqlplus system/Oracle_123@ORCL_SSL

# Verify connection is using SSL
sqlplus system/Oracle_123@ORCL_SSL

-- Inside the SQL*Plus session -- confirm SSL is being used
SELECT sys_context('USERENV','NETWORK_PROTOCOL') network_protocol,
       sys_context('USERENV','IDENTIFICATION_TYPE') id_type
FROM   dual;
-- network_protocol should show: tcps

10.10 — Verify SSL Certificate Details

# Check server certificate expiry
orapki wallet display \
    -wallet /u01/app/oracle/wallet/server \
    -pwd WalletPwd_123

# Using openssl to check certificate
openssl s_client \
    -connect dbserver01.company.com:2484 \
    -showcerts

# Check certificate expiry date
openssl s_client -connect dbserver01.company.com:2484 2>/dev/null | \
    openssl x509 -noout -dates

⚠️ IMPORTANT: Set a calendar reminder for SSL certificate expiry. An expired SSL certificate causes ALL SSL-based connections to fail immediately. For a self-signed certificate with 10-year validity this is less of a concern, but for CA-signed certificates that expire in 1-2 years it is critical.

11. Network Troubleshooting Guide

📝 Use this section when you get a “cannot connect to database” call. Work through it in order.

11.1 — Connection Troubleshooting Decision Tree

"Cannot connect to Oracle database"
              │
    ┌─────────▼──────────┐
    │ Can you ping the    │
    │ DB server?          │
    └─────────┬──────────┘
         NO ──┼──► Network issue -- contact network team
              │
         YES ──►
    ┌─────────▼──────────┐
    │ Is the listener     │
    │ running?            │
    │ lsnrctl status      │
    └─────────┬──────────┘
         NO ──┼──► Start listener: lsnrctl start
              │
         YES ──►
    ┌─────────▼──────────┐
    │ Does tnsping work?  │
    │ tnsping ORCL        │
    └─────────┬──────────┘
         NO ──┼──► Check tnsnames.ora, TNS_ADMIN, NAMES.DIRECTORY_PATH
              │
         YES ──►
    ┌─────────▼──────────┐
    │ Is DB open?         │
    │ SELECT status FROM  │
    │ v$instance          │
    └─────────┬──────────┘
         NO ──┼──► Start database: STARTUP
              │
         YES ──►
    ┌─────────▼──────────┐
    │ Is service          │
    │ registered?         │
    │ lsnrctl services    │
    └─────────┬──────────┘
         NO ──┼──► ALTER SYSTEM REGISTER; or check LOCAL_LISTENER
              │
         YES ──►
    Check credentials, max sessions, firewall

11.2 — Common Connection Errors and Fixes

11.3 — Common Troubleshooting Commands

# Check if listener is running
ps -ef | grep tnslsnr | grep -v grep

# Check which port listener is on
ss -tlnp | grep 1521

# Test TCP connectivity to listener port
telnet dbserver01.company.com 1521
# OR (if telnet not available)
nc -zv dbserver01.company.com 1521

# Test TNS resolution
tnsping ORCL

# Check TNS_ADMIN being used
echo $TNS_ADMIN

# Find which tnsnames.ora is being used
tnsping ORCL | head -3

# Check listener log for specific error
grep "TNS-12514" $ORACLE_HOME/network/log/listener.log | tail -20

# Check firewall rules (as root)
iptables -L -n | grep 1521
firewall-cmd --list-ports

# Check SELinux is not blocking Oracle
getenforce
# If ENFORCING -- check audit log
grep oracle /var/log/audit/audit.log | tail -20

11.4 — Enable Network Tracing for Deep Troubleshooting

# Server-side tracing -- modify sqlnet.ora temporarily
vi $ORACLE_HOME/network/admin/sqlnet.ora

# Add these lines temporarily for troubleshooting
TRACE_LEVEL_SERVER      = ADMIN
TRACE_DIRECTORY_SERVER  = /tmp/oracle_traces
TRACE_FILE_SERVER       = svr_trace

# Create trace directory
mkdir -p /tmp/oracle_traces
chmod 777 /tmp/oracle_traces

# Reload listener
lsnrctl reload

# Reproduce the connection problem
# Collect trace files
ls -lh /tmp/oracle_traces/

# Read the trace file
cat /tmp/oracle_traces/svr_trace*.trc | grep -E "error|TNS|ORA-" | head -50

# IMPORTANT: Remove tracing after collecting
# Revert sqlnet.ora and reload listener

12. Post-Configuration Checks

⚠️ IMPORTANT: Run all post-configuration checks after any network file change. A mistake in listener.ora or tnsnames.ora will break all connections.

12.1 — Verify Listener Status

lsnrctl status

# Check specific listener
lsnrctl status LISTENER

# Verify all expected services are registered
lsnrctl services | grep -i "service\|instance\|handler"

12.2 — Verify All TNS Entries Resolve

# Test each entry in tnsnames.ora
tnsping ORCL
tnsping ORCL_STBY
tnsping RACDB
tnsping PDB1

# All should return: OK (N msec)

12.3 — Verify Actual Connections Work

# Test connection with each TNS entry
sqlplus system/Oracle_123@ORCL
sqlplus system/Oracle_123@ORCL_STBY
sqlplus system/Oracle_123@RACDB

12.4 — Verify sqlnet.ora Parameters

# Check sqlnet.ora is syntactically correct
# (No built-in validator -- check by attempting connection and reviewing log)

# Verify naming methods
grep NAMES.DIRECTORY_PATH $ORACLE_HOME/network/admin/sqlnet.ora

# Verify DCD is configured
grep SQLNET.EXPIRE_TIME $ORACLE_HOME/network/admin/sqlnet.ora

12.5 — Check Network Configuration Inside Database

sqlplus / as sysdba

set linesize 200
set pagesize 100
col name  for a35
col value for a60

-- Check listener-related parameters
SELECT name, value
FROM   v$parameter
WHERE  name IN (
    'local_listener',
    'remote_listener',
    'dispatchers',
    'shared_servers',
    'max_shared_servers',
    'db_domain'
)
ORDER BY name;

-- Check if dispatchers are running (for shared server)
SELECT name, status, dispatched, circuit, idle, busy
FROM   v$dispatcher;

-- Check shared server sessions if configured
SELECT * FROM v$shared_server;

12.6 — Verify SCAN (RAC Only)

su - grid

# Full SCAN check
$ORACLE_HOME/bin/srvctl config scan
$ORACLE_HOME/bin/srvctl status scan
$ORACLE_HOME/bin/srvctl status scan_listener

# Verify SCAN resolves correctly
nslookup rac-scan.company.com

# Test connection through SCAN
sqlplus system/Oracle_123@rac-scan.company.com:1521/RACDB

12.7 — Verify DRCP (If Configured)

sqlplus / as sysdba

-- DRCP pool must be ACTIVE
SELECT connection_pool, status, num_open_servers, num_busy_servers
FROM   v$cpool_stats;

-- Test DRCP connection
CONNECT hr/Hr_123@ORCL_POOLED

12.8 — Verify SSL (If Configured)

# TCPS endpoint must be in listener status
lsnrctl status | grep -i tcps

# Test SSL connection
tnsping ORCL_SSL
sqlplus system/Oracle_123@ORCL_SSL

-- Verify SSL is active in session
SELECT sys_context('USERENV','NETWORK_PROTOCOL') network_protocol
FROM   dual;
-- Must show: tcps

13. Quick Reference Card

This SOP covers everything you need to manage Oracle Network and Connectivity on Linux without referring to any other source. Always keep tnsnames.ora and listener.ora backed up before making changes, always reload the listener after listener.ora changes rather than restarting to avoid dropping active connections, always use SERVICE_NAME instead of SID in tnsnames.ora for better HA and load balancing support, configure SQLNET.EXPIRE_TIME on all production servers to prevent ghost session accumulation, and always test SSL certificate expiry dates proactively before they cause outages.