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

In this blogpost we will see the option of connecting to the Oracle cloud database using the variable in sqlnet.ora files
 
Similar to the first method, this also requires having the Oracle wallets downloaded and store/extract them under their own location.
 
Let’s remove the “MY_WALLET_DIRECTORY” from the TNS entries – so that it will be like this
 
C:\Users\Rajeshwaran>tnsping ATPDEMO21C
 
TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 01-AUG-2022 22:45:46
 
Copyright (c) 1997, 2021, Oracle.  All rights reserved.
 
Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (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)))
OK (2820 msec)
 
C:\Users\Rajeshwaran>tnsping ATPDEMO19C
 
TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 01-AUG-2022 22:45:53
 
Copyright (c) 1997, 2021, Oracle.  All rights reserved.
 
Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (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)))
OK (2490 msec)
 
 
We don’t see any reference to MY_WALLET_DIRECTORY in the above TNS entries. Now this second option requires an environment variable within, lets name it as DB_NAME_DIR (it is the name of the directory, where the wallet is stored)
 
c:\Oracle\TNSnames>type sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\TNSnames\%DB_NAME_DIR%")))
SSL_SERVER_DN_MATCH=yes
 
 
Now, before we can connect to the database, we must explicitly set the DB_NAME_DIR environment variable to distinguish between the directories where those wallets are stored.
 
 
C:\Users\Rajeshwaran>set DB_NAME_DIR=ATP21C
 
C:\Users\Rajeshwaran>sqlplus admin@atpdemo21c
 
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 1 22:53:14 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
Enter password:
Last Successful login time: Mon Aug 01 2022 22:33:00 +05:30
 
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 
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
 
 
Now to connect to the other database, we need to change the environment variables like this
 
C:\Users\Rajeshwaran>set DB_NAME_DIR=ATP19C
 
C:\Users\Rajeshwaran>sqlplus admin@atpdemo19c
 
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 1 22:55:07 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
Enter password:
Last Successful login time: Mon Aug 01 2022 22:33:45 +05:30
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
 
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
 
 
Now if we try to connect to the other database without changing the environment variable DB_NAME_DIR, we will get the error message like this
 
admin@ATPDEMO19C> $ echo %DB_NAME_DIR%
ATP19C
 
admin@ATPDEMO19C> conn admin@atpdemo21c
Enter password:
ERROR:
ORA-12529: TNS:connect request rejected based on current filtering rules
 
 
Warning: You are no longer connected to ORACLE.