Skip to content

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

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

Last Updated: January 2026 Configuration Version: HeliosDB 7.0+