{"id":5805,"date":"2026-07-19T22:10:32","date_gmt":"2026-07-19T16:40:32","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5805"},"modified":"2026-07-19T22:10:34","modified_gmt":"2026-07-19T16:40:34","slug":"oracle-network-and-connectivity","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-network-and-connectivity\/","title":{"rendered":"Oracle Network and Connectivity"},"content":{"rendered":"\n
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 \u2014 with real commands, expected outputs, and consultant-level notes for both standard OFA and enterprise custom path conventions.<\/p>\n\n\n\n
2. Oracle Network \u2014 Concepts You Must Know First<\/h3>\n\n\n\n
\n
\ud83d\udcdd How does Oracle network connectivity work?<\/strong> 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<\/code> (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.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
Oracle Net Connection Flow<\/h4>\n\n\n\n
CLIENT APPLICATION\n \u2502\n \u2502 1. Resolves service name via tnsnames.ora \/ Easy Connect \/ LDAP\n \u2502\n \u25bc\nORACLE LISTENER (port 1521)\n \u2502\n \u2502 2. Listener accepts connection request\n \u2502 3. Listener checks SID_LIST \/ service registry\n \u2502 4. Spawns dedicated server process OR hands to shared server\n \u2502\n \u25bc\nDATABASE SERVER PROCESS\n \u2502\n \u2502 5. Server process authenticates user\n \u2502 6. Session established\n \u2502\n \u25bc\nCLIENT RECEIVES CONNECTION<\/code><\/pre>\n\n\n\n\n\n\n\n
Key Oracle Network Files<\/h4>\n\n\n\n
File<\/th>
Location<\/th>
Purpose<\/th><\/tr><\/thead>
listener.ora<\/td>
$ORACLE_HOME\/network\/admin\/<\/td>
Defines the listener \u2014 port, protocol, static DB registration<\/td><\/tr>
tnsnames.ora<\/td>
$ORACLE_HOME\/network\/admin\/<\/td>
Maps service names to connection descriptors<\/td><\/tr>
sqlnet.ora<\/td>
$ORACLE_HOME\/network\/admin\/<\/td>
Client and server-side network behavior \u2014 encryption, tracing, naming methods<\/td><\/tr>
ldap.ora<\/td>
$ORACLE_HOME\/network\/admin\/<\/td>
LDAP\/OID directory server configuration (if using LDAP naming)<\/td><\/tr>
\ud83d\udcdd Where to put these files?<\/strong> By default Oracle looks in $ORACLE_HOME\/network\/admin\/<\/code>. In environments with multiple Oracle Homes on one server, you can also set TNS_ADMIN<\/code> environment variable to point to a central location \u2014 all homes then use the same files.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
Oracle Naming Methods (How Clients Resolve Service Names)<\/h4>\n\n\n\n
Method<\/th>
How It Works<\/th>
When to Use<\/th><\/tr><\/thead>
Local Naming (tnsnames.ora)<\/td>
Client reads local tnsnames.ora file<\/td>
Most common \u2014 simple, no dependencies<\/td><\/tr>
Easy Connect<\/td>
Connection string in host:port\/service format<\/td>
Quick connections, no tnsnames.ora needed<\/td><\/tr>
\ud83d\udcdd What is the listener?<\/strong> The Oracle Listener is a server-side process that accepts incoming client connection requests. It runs independently of the database \u2014 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).<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
4.1 \u2014 Basic listener.ora Structure and Configuration<\/h4>\n\n\n\n
\n
\ud83d\udcdd Two types of database registration with the listener:<\/strong><\/p>\n\n\n\n
\n
Dynamic registration<\/strong> \u2014 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.<\/li>\n\n\n\n
Static registration<\/strong> \u2014 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.<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
Complete listener.ora example with all important configurations:<\/strong><\/p>\n\n\n\n
# -------------------------------------------------------\n# listener.ora \u2014 Oracle Listener Configuration\n# Server: dbserver01.company.com\n# Modified: <date> by <DBA name>\n# -------------------------------------------------------\n\n# Listener name -- default is LISTENER\n# You can have multiple listeners with different names and ports\nLISTENER =\n (DESCRIPTION_LIST =\n (DESCRIPTION =\n # Primary protocol and port\n (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01.company.com)(PORT = 1521))\n # IPC for local connections (faster than TCP for local)\n (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))\n )\n )\n\n# Static database registration\n# Required for: RMAN duplicate, Data Guard, DB in MOUNT state,\n# when PMON registration is not working\n# If only using dynamic registration -- this section can be omitted\nSID_LIST_LISTENER =\n (SID_LIST =\n (SID_DESC =\n # GLOBAL_DBNAME = service name clients use to connect\n (GLOBAL_DBNAME = ORCL)\n # ORACLE_HOME = home of the database being registered\n (ORACLE_HOME = \/u01\/app\/oracle\/product\/19.3.0\/dbhome_1)\n # SID_NAME = the actual Oracle SID\n (SID_NAME = ORCL)\n )\n # For Data Guard Broker -- needs separate entry with _DGMGRL suffix\n (SID_DESC =\n (GLOBAL_DBNAME = ORCL_DGMGRL)\n (ORACLE_HOME = \/u01\/app\/oracle\/product\/19.3.0\/dbhome_1)\n (SID_NAME = ORCL)\n )\n # For pluggable database (CDB environment)\n # (SID_DESC =\n # (GLOBAL_DBNAME = PDB1)\n # (ORACLE_HOME = \/u01\/app\/oracle\/product\/19.3.0\/dbhome_1)\n # (SID_NAME = ORCL)\n # )\n )\n\n# Listener logging\n# LOGGING_LISTENER = ON -- enable logging (default)\n# LOG_FILE_LISTENER = listener -- log file name (listener.log)\n# LOG_DIRECTORY_LISTENER = \/u01\/app\/oracle\/product\/19.3.0\/dbhome_1\/network\/log\n\n# Listener tracing (only enable for troubleshooting -- disable in production)\n# TRACE_LEVEL_LISTENER = OFF -- OFF, USER, ADMIN, SUPPORT\n# TRACE_FILE_LISTENER = listener\n# TRACE_DIRECTORY_LISTENER = \/u01\/app\/oracle\/product\/19.3.0\/dbhome_1\/network\/trace\n\n# Security -- restrict listener administration to local connections only\n# ADMIN_RESTRICTIONS_LISTENER = ON<\/code><\/pre>\n\n\n\n\n\n\n\n
4.2 \u2014 Configure Multiple Listeners on Same Server<\/h4>\n\n\n\n
\n
\ud83d\udcdd When to use multiple listeners?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
vi $ORACLE_HOME\/network\/admin\/listener.ora<\/code><\/pre>\n\n\n\n
4.3 \u2014 Listener Control Commands (lsnrctl)<\/h4>\n\n\n\n
su - oracle\n\n# Start the default listener (named LISTENER)\nlsnrctl start\n\n# Start a specific named listener\nlsnrctl start LISTENER_TEST\n\n# Stop the listener\nlsnrctl stop\n\n# Stop specific listener\nlsnrctl stop LISTENER_TEST\n\n# Check listener status -- shows registered services and endpoints\nlsnrctl status\n\n# Status of specific listener\nlsnrctl status LISTENER_TEST\n\n# Show services registered with listener\nlsnrctl services\n\n# Reload listener -- picks up listener.ora changes without restart\n# Does NOT drop existing connections\nlsnrctl reload\n\n# Show listener version\nlsnrctl version<\/code><\/pre>\n\n\n\n\n\n\n\n
4.4 \u2014 Verify Dynamic Registration is Working<\/h4>\n\n\n\n
\n
\ud83d\udcdd How does dynamic registration work?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
# Check which services are dynamically registered\nlsnrctl services<\/code><\/pre>\n\n\n\n
Services Summary...\nService \"ORCL\" has 1 instance(s).\n Instance \"ORCL\", status READY, has 1 handler(s) for this service...\n Handler(s):\n \"DEDICATED\" established:0 refused:0 state:ready\n LOCAL SERVER<\/code><\/pre>\n\n\n\n
-- Force PMON to re-register with listener immediately\n-- (instead of waiting up to 60 seconds)\nsqlplus \/ as sysdba\nALTER SYSTEM REGISTER;<\/code><\/pre>\n\n\n\n\n\n\n\n
\ud83d\udcdd Why set LOCAL_LISTENER?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
sqlplus \/ as sysdba\n\n-- Check current LOCAL_LISTENER setting\nSHOW PARAMETER local_listener;\n\n-- Set LOCAL_LISTENER to register with specific listener\nALTER SYSTEM SET LOCAL_LISTENER=\n '(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1521))'\n SCOPE=BOTH;\n\n-- For multiple listeners\nALTER SYSTEM SET LOCAL_LISTENER=\n '(ADDRESS_LIST=\n (ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1521))\n (ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1522))\n )'\n SCOPE=BOTH;\n\n-- Force re-registration\nALTER SYSTEM REGISTER;<\/code><\/pre>\n\n\n\n\n\n\n\n
4.6 \u2014 Listener Log Analysis<\/h4>\n\n\n\n
\n
\ud83d\udcdd Why check listener logs?<\/strong> The listener log records every connection attempt \u2014 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.<\/p>\n<\/blockquote>\n\n\n\n
# Listener log location\nls -lh $ORACLE_HOME\/network\/log\/listener.log\n\n# View last 100 lines of listener log\ntail -100 $ORACLE_HOME\/network\/log\/listener.log\n\n# Find connection errors\ngrep -E \"TNS-|error|refused\" $ORACLE_HOME\/network\/log\/listener.log | tail -50\n\n# Find connections from a specific client IP\ngrep \"192.168.1.100\" $ORACLE_HOME\/network\/log\/listener.log | tail -20\n\n# Count connection attempts per hour\ngrep \"CONNECT_DATA\" $ORACLE_HOME\/network\/log\/listener.log | \\\n awk '{print $1,$2}' | cut -d: -f1 | sort | uniq -c\n\n# Rotate listener log (when it gets too large -- > 500MB)\n# The log cannot be truncated while listener is running\n# Use lsnrctl to rotate:\nlsnrctl set log_status off\nmv $ORACLE_HOME\/network\/log\/listener.log \\\n $ORACLE_HOME\/network\/log\/listener_$(date +%Y%m%d).log\nlsnrctl set log_status on<\/code><\/pre>\n\n\n\n\n\n\n\n
\ud83d\udcdd What is tnsnames.ora?<\/strong> tnsnames.ora is a client-side name resolution file. It maps logical service names (aliases) to the actual connection details \u2014 hostname, port, and service name. When an application connects using a service name like ORCL<\/code>, Oracle Net looks up ORCL<\/code> in tnsnames.ora to find the actual connection string.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
5.1 \u2014 tnsnames.ora Structure and Syntax<\/h4>\n\n\n\n
# Test TNS resolution without actually connecting\ntnsping ORCL\n\n# Expected output:\n# Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)\n# (HOST=dbserver01.company.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))\n# OK (10 msec)\n\n# Test multiple entries\ntnsping ORCL_STBY\ntnsping RACDB\ntnsping PDB1\n\n# Show what tnsnames.ora tnsping is using\ntnsping ORCL | head -5<\/code><\/pre>\n\n\n\n
# Test actual connection (not just resolution)\nsqlplus system\/Oracle_123@ORCL\n\n# Test with SYSDBA\nsqlplus sys\/Oracle_123@ORCL as sysdba\n\n# Test from remote client (substitute client's connection string)\nsqlplus hr\/Hr_123@\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.company.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))\"<\/code><\/pre>\n\n\n\n\n\n\n\n
5.3 \u2014 TNS_ADMIN \u2014 Central tnsnames.ora Location<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is TNS_ADMIN?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
# Set TNS_ADMIN to central location\n# Add to oracle user's ~\/.bash_profile\nvi ~\/.bash_profile<\/code><\/pre>\n\n\n\n
# -------------------------------------------------------\n# Central Oracle Net configuration\n# -------------------------------------------------------\n\n# TNS_ADMIN points all Oracle tools to central network config\n# All tnsnames.ora, sqlnet.ora, ldap.ora stored here\nexport TNS_ADMIN=\/u01\/app\/oracle\/network\/admin\n# Convention B: export TNS_ADMIN=\/oracle\/network\/admin<\/code><\/pre>\n\n\n\n
# Create central directory and copy files there\nmkdir -p \/u01\/app\/oracle\/network\/admin\ncp $ORACLE_HOME\/network\/admin\/tnsnames.ora \\\n \/u01\/app\/oracle\/network\/admin\/\ncp $ORACLE_HOME\/network\/admin\/sqlnet.ora \\\n \/u01\/app\/oracle\/network\/admin\/\ncp $ORACLE_HOME\/network\/admin\/listener.ora \\\n \/u01\/app\/oracle\/network\/admin\/\n\n# Apply profile\n. ~\/.bash_profile\n\n# Verify tnsping uses the central file\ntnsping ORCL<\/code><\/pre>\n\n\n\n\n\n\n\n
5.4 \u2014 Easy Connect (No tnsnames.ora Required)<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is Easy Connect?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
# Easy Connect format: user\/password@host:port\/service_name\nsqlplus hr\/Hr_123@dbserver01.company.com:1521\/ORCL\n\n# Short form (default port 1521)\nsqlplus hr\/Hr_123@dbserver01.company.com\/ORCL\n\n# With role\nsqlplus sys\/Oracle_123@dbserver01.company.com:1521\/ORCL as sysdba\n\n# Easy Connect Plus (19c) -- more options\n# Syntax: user@host:port\/service?option1=value1&option2=value2\nsqlplus hr\/Hr_123@\"dbserver01.company.com:1521\/ORCL?connect_timeout=10&retry_count=3\"<\/code><\/pre>\n\n\n\n
-- Ensure Easy Connect is enabled in sqlnet.ora\n-- NAMES.DIRECTORY_PATH controls which naming methods are tried and in what order\n-- Add EZCONNECT to enable Easy Connect<\/code><\/pre>\n\n\n\n\n\n\n\n
\ud83d\udcdd What is sqlnet.ora?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
vi $ORACLE_HOME\/network\/admin\/sqlnet.ora<\/code><\/pre>\n\n\n\n
# -------------------------------------------------------\n# sqlnet.ora \u2014 Oracle Net Client\/Server Configuration\n# Location: $ORACLE_HOME\/network\/admin\/sqlnet.ora\n# Modified: <date> by <DBA name>\n# -------------------------------------------------------\n\n# -------------------------------------------------------\n# NAMING METHODS\n# Controls how Oracle resolves service names\n# Order matters: Oracle tries methods left to right\n# TNSNAMES = look in local tnsnames.ora\n# EZCONNECT = use Easy Connect string format\n# LDAP = use LDAP\/OID directory\n# -------------------------------------------------------\nNAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)\n\n# -------------------------------------------------------\n# CONNECTION TIMEOUTS\n# -------------------------------------------------------\n\n# SQLNET.OUTBOUND_CONNECT_TIMEOUT\n# How long to wait for a connection to be established (seconds)\n# Prevents hanging indefinitely when server is unreachable\n# Recommended: 10-30 seconds\nSQLNET.OUTBOUND_CONNECT_TIMEOUT = 15\n\n# SQLNET.INBOUND_CONNECT_TIMEOUT\n# How long the server waits for the client to complete authentication\n# after the initial TCP connection is established (seconds)\n# Protects against denial-of-service attacks\n# Recommended: 60 seconds\nSQLNET.INBOUND_CONNECT_TIMEOUT = 60\n\n# SQLNET.SEND_TIMEOUT\n# How long to wait for a send operation to complete (seconds)\nSQLNET.SEND_TIMEOUT = 60\n\n# SQLNET.RECV_TIMEOUT\n# How long to wait for a receive operation to complete (seconds)\nSQLNET.RECV_TIMEOUT = 60\n\n# -------------------------------------------------------\n# DEAD CONNECTION DETECTION (DCD)\n# Detects and cleans up dead client connections on the server\n# -------------------------------------------------------\n\n# SQLNET.EXPIRE_TIME (minutes)\n# Server sends a probe packet every N minutes to check if client is alive\n# If client does not respond -- server cleans up the connection\n# Recommended: 10 minutes for production\n# Prevents ghost sessions accumulating after client crashes\nSQLNET.EXPIRE_TIME = 10\n\n# -------------------------------------------------------\n# NATIVE NETWORK ENCRYPTION (NNE)\n# Encrypts data in transit between client and server\n# NOTE: This is different from TDE which encrypts data at rest\n# No license required for NNE in Oracle 19c\n# -------------------------------------------------------\n\n# SQLNET.ENCRYPTION_SERVER and SQLNET.ENCRYPTION_CLIENT\n# Values: REQUIRED, REQUESTED, ACCEPTED, REJECTED\n# REQUIRED = always encrypt (reject unencrypted connections)\n# REQUESTED = prefer encryption, accept unencrypted\n# ACCEPTED = accept either encrypted or unencrypted\n# REJECTED = never encrypt\nSQLNET.ENCRYPTION_SERVER = REQUESTED\nSQLNET.ENCRYPTION_CLIENT = REQUESTED\n\n# Encryption algorithms in preference order\n# AES256 and AES192 are the strongest currently available\nSQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)\nSQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)\n\n# Data integrity (checksumming)\n# Ensures data is not tampered with in transit\nSQLNET.CRYPTO_CHECKSUM_SERVER = REQUESTED\nSQLNET.CRYPTO_CHECKSUM_CLIENT = REQUESTED\nSQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA384, SHA512)\nSQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256, SHA384, SHA512)\n\n# -------------------------------------------------------\n# LOGGING AND TRACING\n# -------------------------------------------------------\n\n# Log location for sqlnet errors\nLOG_DIRECTORY_CLIENT = \/tmp\nLOG_DIRECTORY_SERVER = \/u01\/app\/oracle\/product\/19.3.0\/dbhome_1\/network\/log\nLOG_FILE_CLIENT = sqlnet_client\nLOG_FILE_SERVER = sqlnet_server\n\n# Tracing (ONLY enable for troubleshooting -- disable in production)\n# Trace level: OFF, USER, ADMIN, SUPPORT\n# SUPPORT level generates massive files -- use carefully\nTRACE_LEVEL_CLIENT = OFF\nTRACE_LEVEL_SERVER = OFF\n# TRACE_DIRECTORY_CLIENT = \/tmp\n# TRACE_DIRECTORY_SERVER = \/tmp\n\n# -------------------------------------------------------\n# TDE WALLET LOCATION (if TDE is configured)\n# -------------------------------------------------------\n# ENCRYPTION_WALLET_LOCATION =\n# (SOURCE =\n# (METHOD = FILE)\n# (METHOD_DATA =\n# (DIRECTORY = \/u01\/app\/oracle\/admin\/ORCL\/wallet)\n# )\n# )\n\n# -------------------------------------------------------\n# ADDITIONAL SECURITY\n# -------------------------------------------------------\n\n# Prevent unauthorized database access by limiting remote OS authentication\n# SQLNET.AUTHENTICATION_SERVICES = (NONE) -- disable OS auth\n# SQLNET.AUTHENTICATION_SERVICES = (NTS) -- Windows only\n# SQLNET.AUTHENTICATION_SERVICES = (TCPS) -- SSL\/TLS auth\n# Leave commented for standard OS authentication on Linux<\/code><\/pre>\n\n\n\n\n\n\n\n
6.2 \u2014 Dead Connection Detection \u2014 Why It Matters<\/h4>\n\n\n\n
\n
\ud83d\udcdd What is Dead Connection Detection (DCD)?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
-- Check current ghost\/dead sessions (connected but no activity for hours)\nset linesize 200\nset pagesize 100\ncol username for a15\ncol machine for a25\ncol program for a30\ncol status for a10\ncol last_call_et for 9999999\ncol logon_time for a25\n\nSELECT sid, username, machine, program, status,\n last_call_et seconds_idle,\n TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time\nFROM v$session\nWHERE type = 'USER'\nAND last_call_et > 3600 -- idle for more than 1 hour\nAND status = 'INACTIVE'\nORDER BY last_call_et DESC;<\/code><\/pre>\n\n\n\n\n\n\n\n
6.3 \u2014 Configure Trace for Troubleshooting<\/h4>\n\n\n\n
\n
\ud83d\udcdd When to enable tracing?<\/strong> Only when you have a specific connection problem that you cannot diagnose from listener logs and error messages alone. Tracing generates large files quickly \u2014 always disable it after collecting the trace.<\/p>\n<\/blockquote>\n\n\n\n
# Enable trace for a specific client session (client-side)\n# Add to client's sqlnet.ora temporarily\n# TRACE_LEVEL_CLIENT = SUPPORT -- most detailed\n# TRACE_DIRECTORY_CLIENT = \/tmp\n# TRACE_FILE_CLIENT = sqlnet_client\n# TRACE_UNIQUE_CLIENT = ON -- separate file per connection\n\n# Enable server-side trace (SERVER sqlnet.ora)\nvi $ORACLE_HOME\/network\/admin\/sqlnet.ora<\/code><\/pre>\n\n\n\n
# Add temporarily for troubleshooting -- remove after collecting trace\nTRACE_LEVEL_SERVER = ADMIN\nTRACE_DIRECTORY_SERVER = \/tmp\/oracle_traces\nTRACE_FILE_SERVER = sqlnet_server<\/code><\/pre>\n\n\n\n
# Reload listener to pick up trace settings\nlsnrctl reload\n\n# Reproduce the problem\n# Then collect the trace files\nls -lh \/tmp\/oracle_traces\/\n\n# IMPORTANT: Disable trace immediately after collecting\n# Revert sqlnet.ora and reload<\/code><\/pre>\n\n\n\n\n\n\n\n
7. SCAN Listener Configuration in RAC<\/h3>\n\n\n\n
\n
\ud83d\udcdd What is SCAN?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd Why is SCAN better than node VIPs?<\/strong> 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 \u2014 only the DNS record for the SCAN hostname changes, and that is managed by the DBA.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
7.1 \u2014 SCAN Architecture<\/h4>\n\n\n\n
CLIENT\n \u2502\n \u2502 connects to: rac-scan.company.com:1521\n \u2502\n \u25bc\nDNS SERVER\n \u251c\u2500\u2500 rac-scan.company.com \u2192 192.168.1.15\n \u251c\u2500\u2500 rac-scan.company.com \u2192 192.168.1.16\n \u2514\u2500\u2500 rac-scan.company.com \u2192 192.168.1.17\n (3 SCAN IPs -- DNS round-robin)\n \u2502\n \u25bc\nSCAN LISTENER (on all 3 SCAN IPs)\n \u2502\n \u251c\u2500\u2500 Routes to: racnode1:1521 (local listener)\n \u2502 racnode2:1521 (local listener)\n \u2502\n \u25bc\nLOCAL LISTENER (on each node)\n \u2502\n \u25bc\nDATABASE INSTANCE (RACDB1 on node1, RACDB2 on node2)<\/code><\/pre>\n\n\n\n\n\n\n\n
7.2 \u2014 Check SCAN Configuration<\/h4>\n\n\n\n
# As grid user -- all SCAN management is done as grid\nsu - grid\n\n# Check SCAN name and IPs\n$ORACLE_HOME\/bin\/srvctl config scan\n\n# Expected output:\n# SCAN name: rac-scan.company.com, Network: 1\n# Subnet IPv4: 192.168.1.0\/255.255.255.0\/eth0, static\n# SCAN 1 IPv4 VIP: 192.168.1.15\n# SCAN 2 IPv4 VIP: 192.168.1.16\n# SCAN 3 IPv4 VIP: 192.168.1.17\n\n# Check SCAN listener status\n$ORACLE_HOME\/bin\/srvctl status scan_listener\n\n# Expected output:\n# SCAN Listener LISTENER_SCAN1 is enabled\n# SCAN listener LISTENER_SCAN1 is running on node racnode1\n# SCAN Listener LISTENER_SCAN2 is enabled\n# SCAN listener LISTENER_SCAN2 is running on node racnode2\n# SCAN Listener LISTENER_SCAN3 is enabled\n# SCAN listener LISTENER_SCAN3 is running on node racnode1\n\n# Detailed SCAN listener config\n$ORACLE_HOME\/bin\/srvctl config scan_listener<\/code><\/pre>\n\n\n\n\n\n\n\n
7.3 \u2014 Check Services Registered with SCAN Listener<\/h4>\n\n\n\n
su - grid\n\n# Check what services are registered with each SCAN listener\n# Run on each SCAN listener\nlsnrctl status LISTENER_SCAN1\nlsnrctl status LISTENER_SCAN2\nlsnrctl status LISTENER_SCAN3\n\n# Check services registered\nlsnrctl services LISTENER_SCAN1<\/code><\/pre>\n\n\n\n\n\n\n\n
7.4 \u2014 Manage SCAN Listener<\/h4>\n\n\n\n
su - grid\n\n# Start SCAN listener\n$ORACLE_HOME\/bin\/srvctl start scan_listener\n\n# Stop SCAN listener\n$ORACLE_HOME\/bin\/srvctl stop scan_listener\n\n# Start specific SCAN listener (by number 1, 2, or 3)\n$ORACLE_HOME\/bin\/srvctl start scan_listener -scannumber 1\n\n# Stop specific SCAN listener\n$ORACLE_HOME\/bin\/srvctl stop scan_listener -scannumber 2\n\n# Relocate SCAN listener to a different node\n# (for maintenance on current node)\n$ORACLE_HOME\/bin\/srvctl relocate scan_listener \\\n -scannumber 1 \\\n -n racnode2<\/code><\/pre>\n\n\n\n\n\n\n\n
7.5 \u2014 Configure Local Listeners in RAC<\/h4>\n\n\n\n
\n
\ud83d\udcdd In RAC, each node has its own local listener<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
su - grid\n\n# Check local listener configuration on all nodes\n$ORACLE_HOME\/bin\/srvctl config listener\n\n# Check local listener status on all nodes\n$ORACLE_HOME\/bin\/srvctl status listener\n\n# Start local listener on all nodes\n$ORACLE_HOME\/bin\/srvctl start listener\n\n# Start listener on specific node only\n$ORACLE_HOME\/bin\/srvctl start listener -n racnode1\n\n# Stop listener on specific node\n$ORACLE_HOME\/bin\/srvctl stop listener -n racnode2<\/code><\/pre>\n\n\n\n\n\n\n\n
7.6 \u2014 RAC tnsnames.ora for Clients<\/h4>\n\n\n\n
\n
\ud83d\udcdd Clients connecting to RAC should use the SCAN hostname.<\/strong> This is simpler and more resilient than connecting directly to node VIPs.<\/p>\n<\/blockquote>\n\n\n\n
\ud83d\udcdd What is DRCP?<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd When to use DRCP?<\/strong> DRCP is ideal for web applications with many short-lived connections \u2014 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.<\/p>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd When NOT to use DRCP?<\/strong> 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).<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
8.1 \u2014 Configure and Start DRCP<\/h4>\n\n\n\n
-- Connect as SYSDBA\nsqlplus \/ as sysdba\n\n-- Check if DRCP is already configured\nSELECT connection_pool, status, maxsize, minsize, incrsize,\n session_cached_cursors, inactivity_timeout,\n max_think_time\nFROM dba_cpool_info;\n\n-- Start DRCP with configuration\n-- This configures and starts the pool in one command\nEXEC DBMS_CONNECTION_POOL.START_POOL(\n pool_name => 'SYS_DEFAULT_CONNECTION_POOL',\n minsize => 4, -- minimum pooled server processes\n maxsize => 40, -- maximum pooled server processes\n incrsize => 2, -- grow by 2 when demand exceeds current\n session_cached_cursors => 20, -- cursors cached per pooled session\n inactivity_timeout => 300, -- idle connection timeout (seconds)\n max_think_time => 120, -- max time client holds a connection (seconds)\n max_use_session => 500000, -- reuse a server process max N times\n max_lifetime_session => 86400 -- max lifetime of a server process (seconds)\n);\n\n-- Verify DRCP started\nSELECT connection_pool, status, minsize, maxsize, incrsize\nFROM dba_cpool_info;<\/code><\/pre>\n\n\n\n\n\n\n\n
-- Modify existing DRCP configuration\nEXEC DBMS_CONNECTION_POOL.ALTER_PARAM(\n pool_name => 'SYS_DEFAULT_CONNECTION_POOL',\n minsize => 5,\n maxsize => 50,\n incrsize => 5\n);\n\n-- Stop the pool gracefully\nEXEC DBMS_CONNECTION_POOL.STOP_POOL(\n pool_name => 'SYS_DEFAULT_CONNECTION_POOL'\n);\n\n-- Stop immediately (drops all active connections)\nEXEC DBMS_CONNECTION_POOL.STOP_POOL(\n pool_name => 'SYS_DEFAULT_CONNECTION_POOL',\n immediate => TRUE\n);<\/code><\/pre>\n\n\n\n\n\n\n\n
8.3 \u2014 Connect Using DRCP<\/h4>\n\n\n\n
\n
\ud83d\udcdd To use DRCP, clients must connect using a pooled server connection.<\/strong> The connection string must specify SERVER=POOLED<\/code> in the CONNECT_DATA section.<\/p>\n<\/blockquote>\n\n\n\n
# Add DRCP entry to tnsnames.ora\nvi $ORACLE_HOME\/network\/admin\/tnsnames.ora<\/code><\/pre>\n\n\n\n
# DRCP connection entry\n# SERVER=POOLED tells Oracle to use DRCP pool\nORCL_POOLED =\n (DESCRIPTION =\n (ADDRESS =\n (PROTOCOL = TCP)\n (HOST = dbserver01.company.com)\n (PORT = 1521)\n )\n (CONNECT_DATA =\n (SERVICE_NAME = ORCL)\n (SERVER = POOLED) -- This is what enables DRCP\n )\n )<\/code><\/pre>\n\n\n\n
# Test DRCP connection\nsqlplus hr\/Hr_123@ORCL_POOLED<\/code><\/pre>\n\n\n\n\n\n\n\n
8.4 \u2014 Monitor DRCP<\/h4>\n\n\n\n
-- Monitor DRCP pool usage\nset linesize 200\nset pagesize 100\ncol connection_pool for a35\ncol status for a12\ncol num_open_servers for 9999\ncol num_busy_servers for 9999\ncol num_waiting_requests for 9999\n\nSELECT connection_pool,\n status,\n num_open_servers,\n num_busy_servers,\n num_waiting_requests\nFROM v$cpool_stats;\n\n-- DRCP statistics per database\nset linesize 200\nset pagesize 100\n\nSELECT num_requests,\n num_hits, -- requests served from pool (reused connection)\n num_misses, -- new connection had to be created\n num_waits, -- requests that had to wait for a server\n ROUND(num_hits*100\/NULLIF(num_requests,0),2) hit_pct\nFROM v$cpool_stats;<\/code><\/pre>\n\n\n\n
\n
\ud83d\udcdd What to look for:<\/strong> hit_pct<\/code> should be above 90%. High num_misses<\/code> means pool is too small \u2014 increase MAXSIZE. High num_waits<\/code> means all servers are busy \u2014 also increase MAXSIZE.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
9. Oracle Connection Manager (CMAN)<\/h3>\n\n\n\n
\n
\ud83d\udcdd What is CMAN?<\/strong> Oracle Connection Manager is a proxy server that sits between clients and the database. It provides:<\/p>\n\n\n\n
\n
Connection multiplexing<\/strong> \u2014 many client connections share fewer network connections to the database (reduces database server overhead)<\/li>\n\n\n\n
Access control<\/strong> \u2014 filter connections based on IP, username, or service name<\/li>\n\n\n\n
Protocol conversion<\/strong> \u2014 connect clients using one protocol to databases using another<\/li>\n\n\n\n
Firewall traversal<\/strong> \u2014 allows Oracle connections through firewalls with minimal port opening<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
\n
\ud83d\udcdd When is CMAN used?<\/strong> 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 \u2014 CMAN sits at the boundary and filters connections.<\/p>\n<\/blockquote>\n\n\n\n\n\n\n\n
9.1 \u2014 Install CMAN Software<\/h4>\n\n\n\n
\n
\ud83d\udcdd CMAN comes with Oracle Database software but is typically installed on a separate dedicated server<\/strong> \u2014 not on the database server itself. It acts as a middle tier.<\/p>\n<\/blockquote>\n\n\n\n
# CMAN is installed as part of Oracle Database software\n# Verify CMAN binary exists in Oracle Home\nls -lh $ORACLE_HOME\/bin\/cmctl\nls -lh $ORACLE_HOME\/bin\/cmadmin<\/code><\/pre>\n\n\n\n\n\n\n\n
# cman.ora goes in ORACLE_HOME\/network\/admin\/ on the CMAN server\nvi $ORACLE_HOME\/network\/admin\/cman.ora<\/code><\/pre>\n\n\n\n
# -------------------------------------------------------\n# cman.ora \u2014 Oracle Connection Manager Configuration\n# CMAN Server: cmanserver01.company.com\n# Modified: <date> by <DBA name>\n# -------------------------------------------------------\n\n# Define the Connection Manager instance\nCMAN =\n (CONFIGURATION =\n\n # Address where CMAN listens for client connections\n (ADDRESS =\n (PROTOCOL = TCP)\n (HOST = cmanserver01.company.com)\n (PORT = 1521)\n )\n\n # Parameter section\n (PARAMETER_LIST =\n\n # Maximum number of simultaneous connections CMAN will handle\n (MAX_CONNECTIONS = 1024)\n\n # Idle connection timeout in seconds\n # Closes connections idle for more than this many seconds\n (IDLE_TIMEOUT = 0)\n\n # Session timeout in seconds (0 = no timeout)\n (SESSION_TIMEOUT = 0)\n\n # Number of CMAN gateway processes\n # More gateways = more parallel connections\n (MAX_GATEWAY_PROCESSES = 16)\n (MIN_GATEWAY_PROCESSES = 2)\n\n # Logging\n (LOG_LEVEL = USER)\n (LOG_DIRECTORY = \/u01\/app\/oracle\/cman\/log)\n\n # Tracing (disable in production)\n (TRACE_LEVEL = OFF)\n (TRACE_DIRECTORY = \/u01\/app\/oracle\/cman\/trace)\n\n # Connection pooling parameters\n (MAXIMUM_RELAYS = 512)\n (CONNECTION_STATISTICS = YES)\n (SHOW_TNS_INFO = YES)\n )\n\n # Access Control List -- defines which connections are allowed or denied\n # Rules are evaluated top to bottom -- first match wins\n (RULE_LIST =\n\n # Allow all connections from internal network to ORCL database\n (RULE =\n (SRC_HOST = 192.168.1.0\/24) -- source IP range\n (DST_HOST = dbserver01.company.com) -- destination DB server\n (SRV = ORCL) -- service name\n (ACT = accept) -- allow\n )\n\n # Allow specific host to connect to any service\n (RULE =\n (SRC_HOST = 10.10.1.50)\n (DST_HOST = dbserver01.company.com)\n (SRV = *)\n (ACT = accept)\n )\n\n # Deny all other connections\n (RULE =\n (SRC_HOST = *)\n (DST_HOST = *)\n (SRV = *)\n (ACT = reject)\n )\n )\n )<\/code><\/pre>\n\n\n\n\n\n\n\n
9.3 \u2014 Start and Manage CMAN<\/h4>\n\n\n\n
# Start CMAN\nsu - oracle\ncmctl start cman\n\n# Check CMAN status\ncmctl show status\n\n# Show connections currently being handled by CMAN\ncmctl show connections\n\n# Show gateway processes\ncmctl show gateways\n\n# Stop CMAN gracefully\ncmctl stop cman\n\n# Reload CMAN configuration without stopping\ncmctl reload cman<\/code><\/pre>\n\n\n\n