Oracle Protocol Configuration

Oracle Protocol Configuration

Complete configuration reference for HeliosDB’s Oracle protocol implementation, including TNS listener setup, NLS settings, security configuration, and connection pooling.

Table of Contents

• Server Configuration
• TNS Listener Configuration
• NLS Settings
• Security Configuration
• Connection Pooling
• Client Configuration
• Performance Tuning
• Monitoring and Diagnostics

---

Server Configuration

Basic Network Settings

Configure the Oracle protocol listener in heliosdb.toml:

[oracle]
# Enable Oracle TNS protocol
enabled = true

# Listen address (0.0.0.0 for all interfaces)
listen_address = "0.0.0.0"

# Oracle TNS port (default: 1521)
port = 1521

# Service name for connections
service_name = "heliosdb"

# Maximum concurrent Oracle connections
max_connections = 500

# Connection timeout in seconds
connection_timeout = 60

# Idle connection timeout in seconds
idle_timeout = 3600

# Enable protocol logging (for debugging)
protocol_logging = false

Multi-Service Configuration

Configure multiple Oracle services on the same instance:

[oracle.services]
# Primary service
[[oracle.services.entry]]
name = "heliosdb"
description = "HeliosDB Primary Service"
default_schema = "public"

# Read-only replica service
[[oracle.services.entry]]
name = "heliosdb_ro"
description = "Read-Only Service"
default_schema = "public"
read_only = true

# Analytics service
[[oracle.services.entry]]
name = "heliosdb_analytics"
description = "Analytics Workload Service"
default_schema = "analytics"
workload_type = "olap"

---

TNS Listener Configuration

Listener Parameters

[oracle.tns]
# Enable TNS listener
enabled = true

# TNS protocol version compatibility
# Options: "11.2", "12.1", "12.2", "18c", "19c", "21c", "23ai"
tns_version = "12.2"

# Oracle SID (System Identifier) for legacy connections
sid = "HELIOS"

# Enable service registration
register_services = true

# Maximum TNS packet size (bytes)
max_packet_size = 8192

# SDU (Session Data Unit) size
sdu_size = 8192

# TDU (Transport Data Unit) size
tdu_size = 32767

# Enable session multiplexing
session_multiplexing = false

# Connection timeout during handshake (seconds)
handshake_timeout = 30

# Keep-alive interval (seconds)
keepalive_interval = 60

# Enable TNS debugging
debug = false

Listener Address Configuration

[oracle.tns.addresses]
# Primary listener address
[[oracle.tns.addresses.entry]]
protocol = "TCP"
host = "0.0.0.0"
port = 1521

# SSL/TLS listener address
[[oracle.tns.addresses.entry]]
protocol = "TCPS"
host = "0.0.0.0"
port = 2484

# IPC listener (local connections)
[[oracle.tns.addresses.entry]]
protocol = "IPC"
key = "HELIOSDB"

Server-Side tnsnames.ora Equivalent

Create service definitions for client tools:

/etc/heliosdb/oracle/tnsnames.ora

HELIOSDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
      (SERVER = DEDICATED)
    )
  )

HELIOSDB_SSL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
    )
    (SECURITY =
      (SSL_SERVER_DN_MATCH = TRUE)
    )
  )

HELIOSDB_FAILOVER =
  (DESCRIPTION =
    (FAILOVER = ON)
    (LOAD_BALANCE = OFF)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = secondary.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 3)
        (DELAY = 5)
      )
    )
  )

Listener.ora Configuration

/etc/heliosdb/oracle/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 2484))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = heliosdb)
      (SID_NAME = HELIOS)
      (ORACLE_HOME = /opt/heliosdb)
    )
  )

# Connection rate limiting
RATE_LIMIT = 100

# Logging configuration
LOGGING_LISTENER = ON
LOG_DIRECTORY_LISTENER = /var/log/heliosdb/oracle
LOG_FILE_LISTENER = listener.log

---

NLS Settings

Character Set Configuration

[oracle.nls]
# Database character set
# Common options: "AL32UTF8", "UTF8", "WE8ISO8859P1", "WE8MSWIN1252"
character_set = "AL32UTF8"

# National character set (for NCHAR, NVARCHAR2, NCLOB)
nchar_character_set = "AL16UTF16"

# Client character set translation
enable_charset_translation = true

# Maximum character expansion for conversion
max_string_size = "EXTENDED"  # or "STANDARD" (4000 bytes)

Date and Time Format Settings

[oracle.nls.datetime]
# Date format (NLS_DATE_FORMAT)
date_format = "DD-MON-YYYY"

# Timestamp format (NLS_TIMESTAMP_FORMAT)
timestamp_format = "DD-MON-YYYY HH24:MI:SS.FF"

# Timestamp with timezone format (NLS_TIMESTAMP_TZ_FORMAT)
timestamp_tz_format = "DD-MON-YYYY HH24:MI:SS.FF TZR"

# Session timezone
# Options: Database timezone, OS timezone, or explicit (e.g., "America/New_York")
time_zone = "UTC"

# Calendar system
calendar = "GREGORIAN"

# First day of week (1=Monday, 7=Sunday)
territory = "AMERICA"

Numeric and Currency Settings

[oracle.nls.numeric]
# Numeric characters (decimal separator, group separator)
# Format: "decimal_separator,group_separator"
numeric_characters = ".,"

# Currency symbol
currency = "$"

# ISO currency code
iso_currency = "AMERICA"

# Dual currency symbol (for Euro conversion)
dual_currency = "$"

Sorting and Comparison Settings

[oracle.nls.sorting]
# Sort order
# Options: "BINARY", "BINARY_CI" (case-insensitive), linguistic sorts
sort = "BINARY"

# Comparison behavior
# Options: "BINARY", "LINGUISTIC", "ANSI"
comp = "BINARY"

# Language for linguistic sorting
language = "AMERICAN"

# Length semantics for character columns
# Options: "BYTE", "CHAR"
length_semantics = "BYTE"

Session-Level NLS Commands

Set NLS parameters per session via SQL:

-- Date and time formats
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6 TZR';

-- Time zone
ALTER SESSION SET TIME_ZONE = 'America/New_York';
ALTER SESSION SET TIME_ZONE = '-05:00';

-- Numeric formatting
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
ALTER SESSION SET NLS_CURRENCY = '$';

-- Sorting and comparison
ALTER SESSION SET NLS_SORT = 'BINARY_CI';  -- Case-insensitive
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';

-- Language settings
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

-- Length semantics
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

---

Security Configuration

Authentication Methods

[oracle.auth]
# Primary authentication method
# Options: "password", "radius", "ldap", "kerberos", "certificate"
method = "password"

# Allow multiple authentication methods
fallback_methods = ["password"]

# Password verification function (for custom password policies)
password_verify_function = ""

# Case sensitivity for usernames and passwords
case_sensitive_usernames = false
case_sensitive_passwords = true

# Maximum failed login attempts before lockout
max_failed_attempts = 10

# Account lockout duration (seconds)
lockout_duration = 600

# Password expiration (days, 0 = never expires)
password_lifetime = 0

# Password reuse limit
password_reuse_max = 0

# Enable password complexity requirements
password_complexity = false

Oracle Wallet Configuration

Configure Oracle Wallet for secure credential storage and SSL certificates:

[oracle.wallet]
# Enable Oracle Wallet
enabled = true

# Wallet location
wallet_location = "/opt/heliosdb/oracle/wallet"

# Auto-login wallet (no password required)
auto_login = true

# Wallet type: "FILE", "SSO", "PKCS12"
wallet_type = "FILE"

# SSL certificate storage
ssl_wallet = true

# Client certificate authentication
client_cert_required = false

Creating and Managing Wallet

# Create a new wallet directory
mkdir -p /opt/heliosdb/oracle/wallet

# Create the wallet using orapki (if available)
orapki wallet create -wallet /opt/heliosdb/oracle/wallet -pwd WalletPassword123 -auto_login

# Add server certificate
orapki wallet add -wallet /opt/heliosdb/oracle/wallet -trusted_cert -cert server.crt -pwd WalletPassword123

# Add CA certificate
orapki wallet add -wallet /opt/heliosdb/oracle/wallet -trusted_cert -cert ca.crt -pwd WalletPassword123

# Alternative: Create wallet with openssl
openssl pkcs12 -export -out wallet.p12 -inkey server.key -in server.crt -certfile ca.crt

SSL/TLS Encryption Configuration

[oracle.ssl]
# Enable SSL/TLS for Oracle connections
enabled = true

# SSL port (TCPS)
port = 2484

# TLS protocol versions
# Options: "TLSv1.2", "TLSv1.3"
protocols = ["TLSv1.2", "TLSv1.3"]

# SSL cipher suites
cipher_suites = [
    "TLS_AES_256_GCM_SHA384",
    "TLS_AES_128_GCM_SHA256",
    "TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384",
    "TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256"
]

# Certificate files
server_cert = "/opt/heliosdb/certs/server.crt"
server_key = "/opt/heliosdb/certs/server.key"
ca_cert = "/opt/heliosdb/certs/ca.crt"

# Client certificate verification
# Options: "NONE", "OPTIONAL", "REQUIRED"
client_auth = "NONE"

# Certificate DN matching
server_dn_match = true

sqlnet.ora Security Settings

/etc/heliosdb/oracle/sqlnet.ora

# Encryption settings
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
SQLNET.ENCRYPTION_CLIENT = REQUESTED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)

# Checksum (integrity) settings
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA384, SHA512)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUESTED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256, SHA384, SHA512)

# SSL configuration
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA256)
SSL_VERSION = 1.2

# Wallet location
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/heliosdb/oracle/wallet)
    )
  )

# Authentication services
SQLNET.AUTHENTICATION_SERVICES = (TCPS, NTS)

# Connection timeout
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
SQLNET.RECV_TIMEOUT = 60
SQLNET.SEND_TIMEOUT = 60

---

Connection Pooling

Server-Side Connection Pooling

[oracle.pool]
# Enable server-side connection pooling
enabled = true

# Pool name
pool_name = "heliosdb_pool"

# Minimum connections in pool
min_connections = 5

# Maximum connections in pool
max_connections = 100

# Connection increment when pool needs to grow
increment = 5

# Maximum time a connection can be idle (seconds)
idle_timeout = 300

# Maximum lifetime of a connection (seconds)
max_lifetime = 3600

# Connection validation query
validation_query = "SELECT 1 FROM DUAL"

# Validate connections on borrow
validate_on_borrow = true

# Connection wait timeout (seconds)
connection_wait_timeout = 30

# Enable pool statistics
statistics_enabled = true

DRCP (Database Resident Connection Pooling) Emulation

[oracle.drcp]
# Enable DRCP emulation
enabled = true

# DRCP pool name
pool_name = "SYS_DEFAULT_CONNECTION_POOL"

# Minimum pooled servers
min_size = 4

# Maximum pooled servers
max_size = 40

# Increment size
incr_size = 2

# Session timeout (seconds)
session_timeout = 120

# Connection class support
connection_class_enabled = true

# Maximum connections per class
max_per_connection_class = 10

Client Connection Pool Configuration

Python (oracledb) Connection Pool

import oracledb

# Create connection pool
pool = oracledb.create_pool(
    user="admin",
    password="password",
    dsn="localhost:1521/heliosdb",
    min=2,              # Minimum pool size
    max=10,             # Maximum pool size
    increment=1,        # Pool increment
    getmode=oracledb.POOL_GETMODE_WAIT,  # Wait for connection
    wait_timeout=30,    # Wait timeout in milliseconds
    timeout=60,         # Idle timeout in seconds
    max_lifetime_session=3600,  # Maximum session lifetime
    session_callback=init_session,  # Session initialization callback
    ping_interval=60,   # Connection health check interval
    stmtcachesize=40,   # Statement cache size
    # DRCP configuration
    cclass="MYAPP",     # Connection class
    purity=oracledb.PURITY_SELF  # Session purity
)

def init_session(connection, requested_tag):
    """Initialize session settings"""
    cursor = connection.cursor()
    cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'")
    cursor.close()

# Acquire connection from pool
with pool.acquire() as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM employees")
    for row in cursor:
        print(row)

# Pool statistics
print(f"Open connections: {pool.opened}")
print(f"Busy connections: {pool.busy}")
print(f"Pool timeout: {pool.timeout}")

Java (JDBC) Connection Pool with HikariCP

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:oracle:thin:@//localhost:1521/heliosdb");
config.setUsername("admin");
config.setPassword("password");

// Pool settings
config.setMinimumIdle(5);
config.setMaximumPoolSize(20);
config.setIdleTimeout(300000);        // 5 minutes
config.setMaxLifetime(1800000);       // 30 minutes
config.setConnectionTimeout(30000);    // 30 seconds
config.setValidationTimeout(5000);     // 5 seconds

// Oracle-specific settings
config.addDataSourceProperty("oracle.jdbc.implicitStatementCacheSize", "20");
config.addDataSourceProperty("oracle.net.CONNECT_TIMEOUT", "10000");
config.addDataSourceProperty("oracle.net.READ_TIMEOUT", "60000");
config.addDataSourceProperty("oracle.jdbc.fanEnabled", "false");

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

// Initialize pool
HikariDataSource dataSource = new HikariDataSource(config);

// Use connection
try (Connection conn = dataSource.getConnection()) {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
    while (rs.next()) {
        System.out.println(rs.getString("employee_name"));
    }
}

Node.js (node-oracledb) Connection Pool

const oracledb = require('oracledb');

async function init() {
    // Create connection pool
    await oracledb.createPool({
        user: 'admin',
        password: 'password',
        connectString: 'localhost:1521/heliosdb',
        poolMin: 2,
        poolMax: 10,
        poolIncrement: 1,
        poolTimeout: 60,          // Idle timeout in seconds
        poolPingInterval: 60,     // Health check interval
        stmtCacheSize: 30,        // Statement cache
        queueMax: 500,            // Maximum queue size
        queueTimeout: 60000,      // Queue wait timeout (ms)
        // DRCP settings
        poolAlias: 'mypool',
        homogeneous: true,
        sessionCallback: initSession
    });
}

async function initSession(connection, requestedTag, actualTag) {
    // Session initialization
    await connection.execute(
        "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'"
    );
}

async function query() {
    const connection = await oracledb.getConnection('mypool');
    try {
        const result = await connection.execute(
            'SELECT * FROM employees',
            [],
            { outFormat: oracledb.OUT_FORMAT_OBJECT }
        );
        console.log(result.rows);
    } finally {
        await connection.close();
    }
}

---

Client Configuration

Oracle Client Environment Variables

# Oracle environment setup
export ORACLE_HOME=/opt/oracle/instantclient_21_9
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH

# TNS configuration location
export TNS_ADMIN=/etc/heliosdb/oracle

# NLS settings
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

Easy Connect Plus Syntax

-- Basic format
sqlplus user/pass@//host:port/service_name

-- With connect timeout (seconds)
sqlplus user/pass@//host:port/service_name?connect_timeout=30

-- With load balancing
sqlplus user/pass@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=heliosdb)))

-- SSL connection
sqlplus user/pass@tcps://host:2484/service_name

-- With wallet
sqlplus /@heliosdb

---

Performance Tuning

PL/SQL Optimization

[oracle.plsql]
# Enable PL/SQL execution
enabled = true

# Maximum PL/SQL package size (bytes)
max_package_size = 10485760  # 10MB

# PL/SQL optimization level (0-3)
# 0 = No optimization
# 1 = Basic optimization
# 2 = Standard optimization (default)
# 3 = Aggressive optimization
optimization_level = 2

# Enable native compilation (experimental)
native_compilation = false

# Debug mode (adds overhead)
debug = false

# Statement cache size
stmt_cache_size = 50

# Anonymous block caching
anonymous_block_cache = true
anonymous_block_cache_size = 100

Query Optimizer Settings

[oracle.optimizer]
# Optimizer mode
# Options: "ALL_ROWS", "FIRST_ROWS", "FIRST_ROWS_1", "FIRST_ROWS_10", "FIRST_ROWS_100", "FIRST_ROWS_1000"
mode = "ALL_ROWS"

# Enable cost-based optimization
cost_based = true

# Index cost adjustment (percentage, 1-10000)
index_cost_adj = 100

# Optimizer index caching (percentage)
optimizer_index_caching = 0

# Query rewrite for materialized views
query_rewrite = true

# Enable parallel query
parallel_query = true
parallel_degree = 4

# Star transformation
star_transformation = false

# Statistics aging
statistics_aging = true

Memory Configuration

[oracle.memory]
# PGA aggregate target (bytes)
# Memory for sorting, hashing, and PL/SQL execution
pga_aggregate_target = 1073741824  # 1GB

# Sort area size per operation
sort_area_size = 65536

# Hash area size per operation
hash_area_size = 131072

# Result cache size
result_cache_size = 104857600  # 100MB

# PL/SQL collection memory limit
plsql_memory_limit = 268435456  # 256MB

# Large object (LOB) cache size
lob_cache_size = 33554432  # 32MB

---

Monitoring and Diagnostics

Session Monitoring

-- Current session information
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS user,
       SYS_CONTEXT('USERENV', 'DB_NAME') AS database,
       SYS_CONTEXT('USERENV', 'HOST') AS host,
       SYS_CONTEXT('USERENV', 'INSTANCE') AS instance,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service
FROM DUAL;

-- Active sessions
SELECT sid, serial#, username, status, machine, program,
       sql_id, event, wait_class, seconds_in_wait
FROM v$session
WHERE type = 'USER'
AND status = 'ACTIVE';

-- Session statistics
SELECT name, value
FROM v$mystat ms
JOIN v$statname sn ON ms.statistic# = sn.statistic#
WHERE value > 0
ORDER BY value DESC;

-- Session wait events
SELECT event, total_waits, time_waited, average_wait
FROM v$session_event
WHERE sid = SYS_CONTEXT('USERENV', 'SID')
ORDER BY time_waited DESC;

Connection Pool Monitoring

-- Pool statistics (server-side)
SELECT pool_name, min_size, max_size, inuse, free,
       total_requests, successful_requests, failed_requests
FROM v$connection_pool;

-- Connection pool waits
SELECT pool_name, wait_count, wait_time_avg
FROM v$connection_pool_wait;

-- Per-session pool statistics
SELECT username, pool, connection_class, connections
FROM v$drcp_session;

Performance Diagnostics

-- SQL execution statistics
SELECT sql_id, executions, elapsed_time, cpu_time,
       buffer_gets, disk_reads, rows_processed
FROM v$sql
WHERE parsing_schema_name = USER
ORDER BY elapsed_time DESC;

-- Query execution plan
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Enable SQL trace
ALTER SESSION SET SQL_TRACE = TRUE;
-- Run queries
ALTER SESSION SET SQL_TRACE = FALSE;

-- Session trace file location
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

---

Configuration File Locations

File	Location	Purpose
heliosdb.toml	/etc/heliosdb/heliosdb.toml	Main server configuration
tnsnames.ora	/etc/heliosdb/oracle/tnsnames.ora	TNS service definitions
listener.ora	/etc/heliosdb/oracle/listener.ora	Listener configuration
sqlnet.ora	/etc/heliosdb/oracle/sqlnet.ora	Network configuration
wallet	/opt/heliosdb/oracle/wallet/	SSL certificates and credentials

---

Related Documentation

• README.md - Protocol overview and compatibility
• COMPATIBILITY.md - Feature compatibility matrix
• EXAMPLES.md - Connection and query examples
• PLSQL_SUPPORT.md - PL/SQL configuration and support

---

Last Updated: January 2026 Configuration Version: HeliosDB 7.0+