Using one Client with multiple Oracle wallets

Last week I got a task from one customer, to set up the Oracle Client, which could seamless connect to multiple databases in the Oracle Cloud, for which we need the Oracle wallet.

Let’s say there is one database in the EU region Frankfurt, and the second one is in the EU region Zurich. I will describe two methods, how to accomplish that.

First method, which I prefer, is to use dynamic parameter MY_WALLET_DIRECTORY within tnsnames.ora.

The second method uses a variable in the sqlnet.ora.

Download Wallet

First step for both methods is to download the Oracle wallet for each database we want to connect.

For example, you can create a subdirectory under $ORACLE_HOME/network/admin (or respectively on Windows to %ORACLE_HOME%\network\admin), and store it there, then extract it in that subdirectory.

It should look like this – %ORACLE_HOME%\network\admin\FRANKFURT and %ORACLE_HOME%\network\admin\ZURICH:

Do the same for the second database as well.

Using Dynamic parameter within tnsnames.ora

This method is much more simpler than the later one, because you can specify the wallet location explicitly in the tnsnames.ora file, and you don’t need to take care later changing the wallet location via some environment variable.

Important note: Open the %ORACLE_HOME%\network\admin\sqlnet.ora and remove the line containing the WALLET_LOCATION parameter or comment it out.

Then open both tnsnames.ora located in those subdirectories (%ORACLE_HOME%\network\admin\FRANKFURT\tnsnames.ora & %ORACLE_HOME%\network\admin\ZURICH\tnsnames.ora). Copy the all content and paste it into %ORACLE_HOME%\network\admin\tnsnames.ora.

For each network alias (connection string) you use, you have to add/embed following parameters:

(MY_WALLET_DIRECTORY=D:\_Downloads_\Oracle_DB_Client_19.3\network\admin\FRANKFURT)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)

Here is the example of the two network aliases I prepared for two different databases, each using a different wallet:

FRANKFURT = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=oracon_frankfurtdb.atp.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")(MY_WALLET_DIRECTORY=D:\_Downloads_\Oracle_DB_Client_19.3\network\admin\FRANKFURT)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)))

ZURICH = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=oracon_zurichdb_low.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.eu-zurich-1.oraclecloud.com,OU=Oracle ADB ZURICH,O=Oracle Corporation,L=Redwood City,ST=California,C=US")(MY_WALLET_DIRECTORY=D:\_Downloads_\Oracle_DB_Client_19.3\network\admin\ZURICH)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)))

Now try to connect to both databases:

C:\Users\oracon>sqlplus admin@FRANKFURT

SQL*Plus: Release 19.0.0.0.0 - Production on Fr Okt 16 02:21:14 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Kennwort eingeben:
Letzte erfolgreiche Anmeldezeit: Do Okt 15 2020 16:41:38 +02:00

Verbunden mit:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> exit
Verbindung zu Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0 beendet

C:\Users\oracon>sqlplus admin@ZURICH

SQL*Plus: Release 19.0.0.0.0 - Production on Fr Okt 16 02:21:27 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Kennwort eingeben:
Letzte erfolgreiche Anmeldezeit: Do Okt 15 2020 18:53:11 +02:00

Verbunden mit:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL>

As you can see, there is no extra steps on the client side – each network alias uses different wallet.

WALLET_LOCATION & environment variable within sqlnet.ora

As for the first method, you have to download the Oracle wallets and store/extraxt them, for example under the same locations as described in the first method.

The second method requires an environment variable within %ORACLE_HOME%\network\admin\sqlnet.ora, let’s name it DB_NAME_DIR (it is the name of the directory, where the wallet is stored).

WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY="D:\_Downloads_\Oracle_DB_Client_19.3\network\admin\%DB_NAME_DIR%")))

Now, before you can connect to the database, you have to explicitly set the DB_NAME_DIR environment variable to distinguish between directories where those wallets are stored.

C:\Users\oracon>set DB_NAME_DIR=FRANKFURT

C:\Users\oracon>sqlplus admin@FRANKFURT

SQL*Plus: Release 19.0.0.0.0 - Production on Fr Okt 16 02:39:11 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Kennwort eingeben:
Letzte erfolgreiche Anmeldezeit: Fr Okt 16 2020 02:21:21 +02:00

Verbunden mit:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> exit

C:\Users\oracon>sqlplus admin@ZURICH

SQL*Plus: Release 19.0.0.0.0 - Production on Fr Okt 16 02:40:41 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Kennwort eingeben:
ERROR:
ORA-28860: Schwerwiegender SSL-Fehler

Benutzernamen eingeben:
ERROR:
ORA-12560: TNS: Fehler bei Protokolladapter

Therefore, you have to set that environment variable accordingly, and then it will work too:

C:\Users\oracon>set DB_NAME_DIR=ZURICH

C:\Users\oracon>sqlplus admin@ZURICH

SQL*Plus: Release 19.0.0.0.0 - Production on Fr Okt 16 02:45:22 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Kennwort eingeben:
Letzte erfolgreiche Anmeldezeit: Fr Okt 16 2020 02:39:41 +02:00

Verbunden mit:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL>

You have to admit, that first method is much more practical to use than the other one.

Note: make sure, the parameter SSL_CLIENT_AUTHENTICATION is not set to FALSE within sqlnet.ora, otherwise your connection will not work!