Oracle Autonomous Database in Production: What the Docs Don't Tell You

I've been running Oracle Autonomous Database in production for over a year. The documentation is comprehensive, but there are critical gotchas that will cost you days of debugging if you don't know them. Here's everything the docs don't tell you.

Thick Mode vs Thin Mode: This Will Bite You

The python-oracledb driver has two modes: thin (pure Python) and thick (uses Oracle Instant Client). The docs suggest thin mode is simpler and works fine. It doesn't work with Autonomous Database mTLS connections.

If you try thin mode with an ADB wallet, you'll get cryptic DPY-6000 or DPY-6001 errors. The fix is straightforward but underdocumented:

import oracledb
oracledb.init_oracle_client(lib_dir="/opt/oracle/instantclient_23_8")

# Now connections work with mTLS
pool = oracledb.create_pool(
    user="ADMIN",
    password=os.environ["ORACLE_PASSWORD"],
    dsn="albahubdb_high",
    min=2, max=10, increment=1
)

The lib_dir must point to your Oracle Instant Client installation. On Linux, that's typically /opt/oracle/instantclient_23_8. This single line is the difference between "it works" and hours of cryptic SSL errors.

The Wallet DIRECTORY Must Be Absolute

In your sqlnet.ora, the WALLET_LOCATION directive requires an absolute path. Relative paths don't error — they silently fail, and you get connection timeouts with no useful error message.

# WRONG - silently fails
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY = "./wallet")))

# CORRECT - absolute path required
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY = "/opt/oracle/wallet")))

I spent an entire afternoon on this one. The wallet was in the right place, the credentials were correct, but connections timed out. The moment I switched to an absolute path, everything worked.

TNS Hostname: Don't Use a CNAME

Your tnsnames.ora connects to the ADB using a hostname. If you've set up a custom CNAME (like db.yourdomain.com) pointing to the ADB endpoint, don't use it in tnsnames.ora. Oracle's internal routing resolves the hostname, and a CNAME confuses the TLS handshake.

Always use the actual ADB hostname: adb.us-ashburn-1.oraclecloud.com (or your region's equivalent). This is what Oracle provisions in the downloaded wallet, and deviating from it causes intermittent connection failures that are maddening to debug.

The Async Pooling Trap

If you're using FastAPI or any async Python framework, you might expect oracledb.create_pool() to be async-friendly. It's not. It's a synchronous call that blocks the event loop during pool creation, which can take 2-5 seconds as it establishes initial connections.

The fix: wrap pool creation in asyncio.to_thread():

import asyncio
import oracledb

async def create_db_pool():
    pool = await asyncio.to_thread(
        oracledb.create_pool,
        user="ADMIN",
        password=os.environ["ORACLE_PASSWORD"],
        dsn="albahubdb_high",
        min=2, max=10, increment=1
    )
    return pool

For individual queries, use the same pattern: await asyncio.to_thread(cursor.execute, sql, params). The oracledb async API exists but is less mature than wrapping sync calls.

Bash History Expansion in .env Files

This one is embarrassing but it happens to everyone. If your Oracle password contains ! and you echo it to a .env file using double quotes, bash interprets the ! as history expansion and mangles your password.

# WRONG - bash eats the ! character
echo "ORACLE_PASSWORD=MyP@ss!word123" >> .env

# CORRECT - single quotes prevent history expansion
echo 'ORACLE_PASSWORD=MyP@ss!word123' >> .env

# ALSO CORRECT - use sed
sed -i '/ORACLE_PASSWORD/d' .env
echo 'ORACLE_PASSWORD=MyP@ss!word123' >> .env

Connection Pool Sizing for ADB Free Tier

ADB free tier has a 300 concurrent connection limit. Sounds generous until you realize that each _high, _medium, and _low TNS entry creates separate sessions. If your pool min=5 and you have 3 services configured, that's 15 connections at startup.

My recommendation for web applications: min=2, max=10, increment=1. Use a single TNS service (_high for OLTP, _low for batch). Monitor with V$SESSION to see actual usage before increasing.