andersswanson.dev

Recently, I needed to connect to multiple databases using Oracle Net from a single client process with Oracle wallet (mTLS). Specifically, I had to update the Oracle Database Monitoring Exporter so it could concurrently scrape metrics from multiple databases.

However, the tnsnames.ora file is only loaded once per process by Oracle Net and ODPI-C. Now, I could use separate processes for each database wallet and tnsnames.ora file, but I wanted to initialize a connection pool for each database from just one operating system process, and delegate database-specific queries to threads/goroutines.

To work around this, I came up with the following solution: Combine the TNS names for each database in a multi-database configuration to a global tnsnames.ora file, separate out wallet information, and set the TNS admin globally using the TNS_ADMIN environment variable.

Here’s how I configured it, using godror as the database client:

(1) For each database the client connects to, download and unzip the corresponding wallet files. If you’re using ADB/ATP-S, download the regional wallet instead of the instance wallet only if the databases are in the same region.

(2) Combine the TNS names from each wallet into a global tnsnames.ora file, so all your database service names are in one file together.

(3) In the combined tnsnames.ora file, update each TNS name to include the wallet location in the connection string. if should look something like this:

(security=(MY_WALLET_DIRECTORY=/path/to/this/database/wallet))

(security=(MY_WALLET_DIRECTORY=/path/to/this/database/wallet))

The resulting TNS names with the added security configuration will look something like this

db1_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.****.oraclecloud.com))(connect_data=(service_name=****.adb.oraclecloud.com))(security=(MY_WALLET_DIRECTORY=/wallets/db1)(ssl_server_dn_match=yes)))

db2_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.****.oraclecloud.com))(connect_data=(service_name=****.adb.oraclecloud.com))(security=(MY_WALLET_DIRECTORY=/wallets/db2)(ssl_server_dn_match=yes)))

db1_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.****.oraclecloud.com))(connect_data=(service_name=****.adb.oraclecloud.com))(security=(MY_WALLET_DIRECTORY=/wallets/db1)(ssl_server_dn_match=yes)))

db2_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.****.oraclecloud.com))(connect_data=(service_name=****.adb.oraclecloud.com))(security=(MY_WALLET_DIRECTORY=/wallets/db2)(ssl_server_dn_match=yes)))

The resulting directory structure should look like the following, with wallet information separate from the combined tnsnames.ora file:

wallets
├── combined
│   ├── sqlnet.ora
│   └── tnsnames.ora // Combined tnsnames.ora
├── db1
│   ├── cwallet.sso
│   ├── ewallet.p12
│   └── ewallet.pem
└── db2
    ├── cwallet.sso
    ├── ewallet.p12
    └── ewallet.pem

wallets
├── combined
│   ├── sqlnet.ora
│   └── tnsnames.ora // Combined tnsnames.ora
├── db1
│   ├── cwallet.sso
│   ├── ewallet.p12
│   └── ewallet.pem
└── db2
    ├── cwallet.sso
    ├── ewallet.p12
    └── ewallet.pem

(4) Lastly, Set the TNS_ADMIN environment variable where the database client is running so it points to the directory containing your combined tnsnames.ora file:

export TNS_ADMIN=/wallets/combined

export TNS_ADMIN=/wallets/combined

If you’re using multiple databases with Oracle Net and a thick client like godror, I hope this helps you. If you have questions or comments, drop a line here or reach out to me on LinkedIn!