How to connect to multiple Oracle Autonomous cloud databases using single client - Part I

Today we will see about how to setup Oracle client, which could seamlessly connect to multiple databases in the Oracle cloud, for which we need the Oracle wallet. 
 
Lets say we got two databases one is running on Oracle 21c and other one is a 19c Instance, for which we will describe two methods of connecting to database.
 
·       First option is to use MY_WALLET_DIRECTORY (a dynamic parameter) option in tnsnames.ora file
·       Second option is to use variable in sqlnet.ora file
 
 
First step for both the options is to download the Oracle wallet for each database we want to connect – very similar to the steps listed in the previous blog post.
 
For example, lets create two subdirectories under TNS_ADMIN location one for each database instance
 
c:\Oracle\TNSnames>echo %TNS_ADMIN%
C:\Oracle\TNSnames\
 
c:\Oracle\TNSnames>dir /ad
 Volume in drive C is System
 Volume Serial Number is C814-8C19
 
 Directory of c:\Oracle\TNSnames
 
01-Aug-2022  06:22    <DIR>          .
27-Jul-2022  09:15    <DIR>          ..
01-Aug-2022  06:23    <DIR>          ATP19C
01-Aug-2022  06:23    <DIR>          ATP21C
               0 File(s)              0 bytes
               4 Dir(s)  402,418,094,080 bytes free
 
 
Download the wallet zip for the first database – say ATP 21c instance – to the directory ATP21C and store it there, then extract the contents into that subdirectory, the contents of the extract should be like this
 
c:\Oracle\TNSnames\ATP21C>dir
 Volume in drive C is System
 Volume Serial Number is C814-8C19
 
 Directory of c:\Oracle\TNSnames\ATP21C
 
01-Aug-2022  06:23    <DIR>          .
01-Aug-2022  06:22    <DIR>          ..
25-Jul-2022  08:12             6,701 cwallet.sso
25-Jul-2022  08:12             6,656 ewallet.p12
25-Jul-2022  08:12             7,475 ewallet.pem
25-Jul-2022  08:12             3,192 keystore.jks
25-Jul-2022  08:12               691 ojdbc.properties
25-Jul-2022  08:12             2,775 README
25-Jul-2022  08:12               114 sqlnet.ora
25-Jul-2022  08:12             1,840 tnsnames.ora
25-Jul-2022  08:12             3,378 truststore.jks
25-Jul-2022  01:42            26,411 Wallet_ATPDEMO.zip
              10 File(s)         59,233 bytes
               2 Dir(s)  402,401,660,928 bytes free
 
 
Do the same for the second database as well. So that its content will be like this
 
c:\Oracle\TNSnames\ATP19C>dir
 Volume in drive C is System
 Volume Serial Number is C814-8C19
 
 Directory of c:\Oracle\TNSnames\ATP19C
 
01-Aug-2022  06:23    <DIR>          .
01-Aug-2022  06:22    <DIR>          ..
25-Jul-2022  01:06             6,701 cwallet.sso
25-Jul-2022  01:06             6,656 ewallet.p12
25-Jul-2022  01:06             7,475 ewallet.pem
25-Jul-2022  01:06             3,190 keystore.jks
25-Jul-2022  01:06               691 ojdbc.properties
25-Jul-2022  01:06             2,792 README
25-Jul-2022  01:06               114 sqlnet.ora
25-Jul-2022  01:06             1,870 tnsnames.ora
25-Jul-2022  01:06             3,378 truststore.jks
25-Jul-2022  06:36            26,416 Wallet_ATPDEMO19C.zip
              10 File(s)         59,283 bytes
               2 Dir(s)  402,402,549,760 bytes free
 
 
While using the dynamic parameter we can specify the wallet location explicitly in the tnsnames.ora file and we don’t need to set the wallet_location in other files like sqlnet.ora
 
Open the sqlnet.ora from TNS_ADMIN location and remove the line containing WALLET_LOCATION parameter (both entries got commented out here)
 
c:\Oracle\TNSnames>type sqlnet.ora
# WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\TNSnames")))
# SSL_SERVER_DN_MATCH=yes
 
 
For each connection string we use, we need to add the following parameters
 
(MY_WALLET_DIRECTORY=C:\oracle\TNSnames\ATP21C)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)
 
Here is the example of two network alias, that we prepared for two different databases, each using a different wallet.
 
ATPDEMO21C = (description= (retry_count=20)(retry_delay=3)
                           (address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
                    (connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))
                           (security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
                    (MY_WALLET_DIRECTORY=C:\oracle\TNSnames\ATP21C)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)
                          )
                    )
 
ATPDEMO19C = (description= (retry_count=20)(retry_delay=3)
                           (address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
                    (connect_data=(service_name=g26be7c92912cdb_atpdemo19c_high.adb.oraclecloud.com))
                           (security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
                    (MY_WALLET_DIRECTORY=C:\oracle\TNSnames\ATP19C)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)
                          ))
 
 
Now if we try to connect both the databases
 
C:\Users\Rajeshwaran>sqlplus /nolog
 
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 1 22:32:36 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
idle> conn admin@atpdemo21c
Enter password:
Connected.
admin@ATPDEMO21C> select banner_full from v$version;
 
BANNER_FULL
-----------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 
 
admin@ATPDEMO21C> conn admin@atpdemo19c
Enter password:
Connected.
admin@ATPDEMO19C> select banner_full from v$version;
 
BANNER_FULL
-----------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
 
 
admin@ATPDEMO19C>
 
as you can see, there is no extra step on the client side, each network alias uses different wallet.