Oracle ODBC / GePaRD Setup

WarningReference only

GePaRD database access from Bertha was experimental and has been removed. GePaRD is only accessible from within the BIPS network with additional restrictions, and Bertha does not currently have access.

This documentation is retained as a reference for setting up Oracle ODBC connectivity on new systems, particularly for the upcoming secure environment for GePaRD access.

Overview

GePaRD is an Oracle database that requires specific drivers and configuration for access from R/RStudio/Positron. This guide covers two connection methods:

1. ODBC (Recommended): Better performance, Positron/RStudio integration, dbplyr support
2. ROracle: Traditional method, lacks some modern features

IDE Compatibility: This setup works with both RStudio Server and Posit Positron. Oracle connectivity issues in early Positron releases (2024.07) have been resolved in Positron 2024.08.0+.

Understanding ODBC Configuration Fields

ODBC configuration can be confusing due to inconsistent field naming across different standards and drivers. Here’s what you need to know:

Field Name Standards

ODBC Standard Fields (defined by ODBC specification):

• Driver - References driver name from odbcinst.ini
  
• UID - User ID (official ODBC standard)
• PWD - Password (official ODBC standard)
• DSN - Data Source Name

Oracle Driver Fields (Oracle-specific preferences):

• UserID - Oracle driver’s preferred username field
• ServerName - Oracle server hostname
• Port - Database port (typically 1521)
• Database - Oracle service name
• DBQ - Full Oracle connection string (host:port/service_name)

Common Variations (for compatibility):

• User, Username - Alternative username fields
• Host, Server - Alternative server fields

Context Matters

The correct field name depends on where you’re configuring the connection:

In ~/.odbc.ini files (DSN configuration):

[gepard]
Driver          = Oracle21
UserID          = your_username  # Oracle driver prefers this
ServerName      = 10.10.11.33
Port            = 1521
Database        = phdb2

In R connection strings (DSN-less connections):

dbConnect(
  odbc::odbc(),
  Driver = "Oracle21",
  UID = "your_username",        # ODBC standard works better here
  PWD = "your_password",
  DBQ = "10.10.11.33:1521/phdb2"
)

Why This Confusion Exists

1. Multiple Standards: ODBC specification vs Oracle documentation vs driver implementations
2. Legacy Compatibility: Drivers accept multiple field names for backward compatibility
   
3. Connection Methods: Different approaches (DSN vs connection string vs TNS) prefer different conventions

Recommendation: Use UserID in configuration files and UID in R code for maximum compatibility.

Prerequisites

• Ubuntu 20.04+ (tested on Ubuntu 24.04 LTS)
• R and RStudio Server installed
• sudo access for system-wide installation
• GePaRD database credentials

Ubuntu 24.04 Note: This version uses libaio1t64 instead of libaio1. The setup script handles this automatically.

About libaio: The Linux Asynchronous I/O library (libaio) provides kernel-level asynchronous I/O capabilities essential for high-performance database operations. Oracle Instant Client requires this library for optimal performance, enabling non-blocking I/O operations that significantly improve database connectivity performance compared to synchronous I/O.

Quick Setup (Recommended)

For an automated installation on Ubuntu 24.04, use the provided setup script:

sudo setup-scripts/setup-oracle-odbc.sh

This script handles all dependencies, downloads, and configuration automatically.

Manual Setup

Step 1: Install Oracle Instant Client

Download and Extract

Download Oracle Instant Client 21.11 components:

# Create temporary directory
mkdir -p ~/oracle-client-setup
cd ~/oracle-client-setup

# Download Basic, SDK, and ODBC packages
wget https://download.oracle.com/otn_software/linux/instantclient/2111000/instantclient-basic-linux.x64-21.11.0.0.0dbru.zip
wget https://download.oracle.com/otn_software/linux/instantclient/2111000/instantclient-sdk-linux.x64-21.11.0.0.0dbru.zip
wget https://download.oracle.com/otn_software/linux/instantclient/2111000/instantclient-odbc-linux.x64-21.11.0.0.0dbru.zip

# Extract all packages
unzip instantclient-basic-linux.x64-21.11.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-21.11.0.0.0dbru.zip
unzip instantclient-odbc-linux.x64-21.11.0.0.0dbru.zip

Install to System Location

# Create Oracle client directory structure
sudo mkdir -p /usr/lib/oracle/21/client64/lib

# Copy all files from extracted directories
sudo cp -r instantclient_21_11/* /usr/lib/oracle/21/client64/lib/

# Update library cache
echo "/usr/lib/oracle/21/client64/lib" | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
sudo ldconfig

Step 2: Configure Environment Variables

System-wide Configuration

Create a system-wide environment file:

sudo tee /etc/profile.d/oracle.sh << 'EOF'
# Oracle Instant Client environment variables
export ORACLE_HOME=/usr/lib/oracle/21/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib:${LD_LIBRARY_PATH}
export OCI_LIB=/usr/lib/oracle/21/client64/lib
EOF

# Source immediately for current session
source /etc/profile.d/oracle.sh

RStudio Server Configuration

Enable Oracle libraries in RStudio Server:

# Edit RStudio server configuration
echo "rsession-ld-library-path=/usr/lib/oracle/21/client64/lib" | sudo tee -a /etc/rstudio/rserver.conf

# Restart RStudio Server
sudo systemctl restart rstudio-server

Step 3: ODBC Setup (Recommended)

Install unixODBC

# Install unixODBC driver manager (version 2.3.1+ recommended for Oracle 12c+)
sudo apt update
sudo apt install unixodbc unixodbc-dev odbcinst

# Verify installation
odbcinst -j  # Shows configuration file locations

Install Oracle ODBC Driver

The ODBC driver was already copied in Step 1. Now register it:

# Verify the driver file exists
ls -la /usr/lib/oracle/21/client64/lib/libsqora.so.21.1

Configure ODBC Driver

Edit the system-wide ODBC driver configuration:

sudo tee /etc/odbcinst.ini << 'EOF'
[Oracle21]
Description     = Oracle ODBC driver for Oracle 21c
Driver          = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
Setup           = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
Driver64        = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
Setup64         = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
FileUsage       = 1
DontDLClose     = 1
UsageCount      = 1
EOF

# Verify driver registration
odbcinst -q -d

Configure Data Sources

Create user-specific ODBC data source in ~/.odbc.ini:

cat > ~/.odbc.ini << 'EOF'
[gepard]
Driver          = Oracle21
ServerName      = 10.10.11.33
Port            = 1521
Database        = phdb2
UserID          = YOUR_USERNAME
Password        = 
# Full connection string format (alternative to ServerName/Port/Database)
# DBQ           = 10.10.11.33:1521/phdb2
EOF

# Replace YOUR_USERNAME with actual username
sed -i "s/YOUR_USERNAME/$(whoami)/" ~/.odbc.ini

# Set appropriate permissions
chmod 600 ~/.odbc.ini

Alternative: Using tnsnames.ora

If you prefer using TNS names:

# Create Oracle network admin directory
sudo mkdir -p /usr/lib/oracle/21/client64/lib/network/admin

# Create tnsnames.ora
sudo tee /usr/lib/oracle/21/client64/lib/network/admin/tnsnames.ora << 'EOF'
GEPARD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.33)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = phdb2)
    )
  )
EOF

# Set TNS_ADMIN environment variable
echo 'export TNS_ADMIN=/usr/lib/oracle/21/client64/lib/network/admin' | sudo tee -a /etc/profile.d/oracle.sh
source /etc/profile.d/oracle.sh

Connect via R

# Load required packages
library(DBI)
library(odbc)

# Method 1: Using DSN from ~/.odbc.ini
con <- dbConnect(
  odbc::odbc(),
  dsn = "gepard",
  UID = Sys.info()[["user"]],  # or specify username
  PWD = rstudioapi::askForPassword("Enter GePaRD password")
)

# Method 2: Direct connection string (no DSN needed)
con <- dbConnect(
  odbc::odbc(),
  Driver = "Oracle21",
  DBQ = "10.10.11.33:1521/phdb2",
  UID = Sys.info()[["user"]],
  PWD = rstudioapi::askForPassword("Enter GePaRD password")
)

# Test connection
dbListTables(con, schema = "YOUR_SCHEMA")

# Disconnect when done
dbDisconnect(con)

Step 4: ROracle Setup (Alternative)

Install System Dependencies

# Ubuntu 24.04
sudo apt install libaio1t64 libaio-dev

# Ubuntu 22.04 and earlier
sudo apt install libaio1 libaio-dev

Install R Package

# Ensure environment variables are set
Sys.setenv(LD_LIBRARY_PATH = "/usr/lib/oracle/21/client64/lib")
Sys.setenv(OCI_LIB = "/usr/lib/oracle/21/client64/lib")

# Install from CRAN
install.packages("ROracle")

# Alternative: Install with explicit configuration
install.packages("ROracle", 
  configure.args = "--with-oci-lib=/usr/lib/oracle/21/client64/lib"
)

Connect via ROracle

library(ROracle)
library(DBI)

# Create connection
con <- dbConnect(
  ROracle::Oracle(),
  username = Sys.info()[["user"]],
  password = rstudioapi::askForPassword("Enter GePaRD password"),
  dbname = "10.10.11.33:1521/phdb2"
)

# Test connection
dbListTables(con)

# Disconnect
dbDisconnect(con)

Credential Management

Never store passwords in plain text. Use the keyring package for secure credential storage:

Setup Keyring

# Install keyring package
install.packages("keyring")

library(keyring)

# Create a keyring for your user (do this once)
my_user <- Sys.info()[["user"]]
keyring_create(my_user)

# Store GePaRD password securely
key_set(
  service = "gepard",
  username = my_user,
  keyring = my_user,
  prompt = "Enter GePaRD password to store"
)

Helper Function

Create a helper function for password retrieval:

# Save this in your .Rprofile or project setup
gepard_pw <- function(user = Sys.info()[["user"]]) {
  if (requireNamespace("keyring", quietly = TRUE)) {
    tryCatch({
      keyring::key_get("gepard", username = user, keyring = user)
    }, error = function(e) {
      if (requireNamespace("rstudioapi", quietly = TRUE)) {
        rstudioapi::askForPassword("Enter GePaRD password")
      } else {
        readline("Enter GePaRD password: ")
      }
    })
  } else if (requireNamespace("rstudioapi", quietly = TRUE)) {
    rstudioapi::askForPassword("Enter GePaRD password")
  } else {
    readline("Enter GePaRD password: ")
  }
}

# Use in connections
con <- dbConnect(
  odbc::odbc(),
  dsn = "gepard",
  UID = Sys.info()[["user"]],
  PWD = gepard_pw()
)

Troubleshooting

Common Issues and Solutions

1. “Can’t open lib ‘Oracle21’”
   • Check driver path in /etc/odbcinst.ini
   • Verify library exists: ls -la /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
   • Run sudo ldconfig to refresh library cache
2. “ORA-12154: TNS:could not resolve the connect identifier”
   • Verify connection string format: host:port/service_name
   • Check network connectivity: ping 10.10.11.33
   • Test port: nc -zv 10.10.11.33 1521
3. RStudio/Positron doesn’t find Oracle libraries
   • Restart RStudio Server after configuration changes
   • For Positron: Ensure using version 2024.08.0+ for Oracle compatibility
   • Check: cat /etc/rstudio/rserver.conf | grep rsession-ld-library-path
   • Verify in R: Sys.getenv("LD_LIBRARY_PATH")
4. ODBC driver not found
   • List registered drivers: odbcinst -q -d
   • Check unixODBC version: odbcinst --version (need 2.3.1+ for Oracle 12c+)
5. Permission denied errors
   • Check file permissions: ls -la ~/.odbc.ini
   • Ensure readable by your user: chmod 600 ~/.odbc.ini

Testing Connection

Command Line Test

# Test ODBC connection using isql
isql -v gepard YOUR_USERNAME YOUR_PASSWORD

# If successful, you'll see:
# +---------------------------------------+
# | Connected!                            |
# |                                       |
# | sql-statement                         |
# | help [tablename]                      |
# | quit                                  |
# |                                       |
# +---------------------------------------+

R Test Script

# Complete test script
test_gepard_connection <- function() {
  library(DBI)
  library(odbc)
  
  # Check environment
  cat("Oracle environment variables:\n")
  cat("LD_LIBRARY_PATH:", Sys.getenv("LD_LIBRARY_PATH"), "\n")
  cat("ORACLE_HOME:", Sys.getenv("ORACLE_HOME"), "\n")
  cat("OCI_LIB:", Sys.getenv("OCI_LIB"), "\n\n")
  
  # List ODBC drivers
  cat("Available ODBC drivers:\n")
  print(odbcListDrivers())
  
  # List ODBC data sources
  cat("\nAvailable ODBC data sources:\n")
  print(odbcListDataSources())
  
  # Attempt connection
  tryCatch({
    con <- dbConnect(
      odbc::odbc(),
      dsn = "gepard",
      UID = Sys.info()[["user"]],
      PWD = rstudioapi::askForPassword("Enter GePaRD password")
    )
    
    cat("\nConnection successful!\n")
    cat("Database version:", dbGetQuery(con, "SELECT * FROM v$version WHERE ROWNUM = 1")$BANNER, "\n")
    
    # List some tables
    tables <- dbListTables(con, schema = toupper(Sys.info()[["user"]]))
    cat("\nNumber of tables in your schema:", length(tables), "\n")
    
    dbDisconnect(con)
    cat("Disconnected successfully.\n")
    
  }, error = function(e) {
    cat("\nConnection failed with error:\n")
    cat(conditionMessage(e), "\n")
  })
}

# Run test
test_gepard_connection()

References

• Oracle Instant Client Downloads
• Oracle ODBC Installation Notes
• RStudio Pro Database Connections
• unixODBC Documentation
• ROracle Package Documentation