Using one Client with multiple Oracle wallets

16. October 2020 Uncategorized 1

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

Now, when you try to connect to the other database – without changing the environment variable DB_NAME_DIR – you will get the error message:

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!


1 thought on “Using one Client with multiple Oracle wallets”

  • 1
    Chris Bebek on March 22, 2021 Reply

    Nice article.

    I was wondering if it’s possible to set a wallet path directly in the environment, without setting a TNS_ADMIN folder (and its associated ora files).

    And the be able to use the usual ez connect format

    sqlplus user@host:port/sid

Leave a Reply to Chris Bebek Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.