Oracle Autonomous is a great service and Always-Free is a nice offering launched at OpenWorld 2019.
But what happens if I need to connect via SQL*Plus to two or more ADB from the same machine/terminal? The Oracle documentation provides good reference usable when connectiong to one autonomous database but if we need to use several ADB’s, the instructions provided by Oracle will be usable only if we keep changing the local TNS_ADMIN environment variable! That would not be possible for some of my Clients that have single TNS_ADMIN location and content that is managed at entreprise level!
So I did the following:
- Created two serverless ADB by using the Always-Free offer:
- TRAINING1 is an ADW
- TRAINING2 is an ATP
- downloaded the wallets for both databases
- tested connection via wallets from SQL Developer (GUI) –> OK
- create two separate directories that will store the wallets and their contents, one for each ADB
- place the wallets in their own correspondent directory:
radu.parvu:cloud\==> pwd
/Users/radu.parvu/opt/instantclient/cloud
radu.parvu:cloud\==> ls -lha
total 96
drwxr-xr-x 5 radu.parvu staff 160B Feb 19 22:22 .
drwxr-xr-x 5 radu.parvu staff 160B Feb 19 16:06 ..
-rw-r--r--@ 1 radu.parvu staff 6.0K Feb 19 16:08 .DS_Store
-rw-r--r--@ 1 radu.parvu staff 20K Feb 19 15:26 wallet_TRAINING1.zip
-rw-r--r--@ 1 radu.parvu staff 20K Feb 19 17:16 wallet_TRAINING2.zip
radu.parvu:cloud\==> mkdir training1
radu.parvu:cloud\==> mkdir training2
radu.parvu:cloud\==> mv wallet_TRAINING1.zip training1/
radu.parvu:cloud\==> mv wallet_TRAINING2.zip training2/
radu.parvu:cloud\==> ls -lha
total 16
drwxr-xr-x 5 radu.parvu staff 160B Feb 19 22:24 .
drwxr-xr-x 5 radu.parvu staff 160B Feb 19 16:06 ..
-rw-r--r--@ 1 radu.parvu staff 6.0K Feb 19 16:08 .DS_Store
drwxr-xr-x 3 radu.parvu staff 96B Feb 19 22:23 training1
drwxr-xr-x 3 radu.parvu staff 96B Feb 19 22:24 training2
radu.parvu:cloud\==> - go into each of the newly created directories and unzip the wallets:
radu.parvu:cloud\==> cd training1
radu.parvu:training1\==> unzip wallet_TRAINING1.zip
Archive: wallet_TRAINING1.zip
inflating: cwallet.sso
inflating: tnsnames.ora
inflating: truststore.jks
inflating: ojdbc.properties
inflating: sqlnet.ora
inflating: ewallet.p12
inflating: keystore.jks
radu.parvu:training1\==> cd ../training2/
radu.parvu:training2\==> unzip wallet_TRAINING2.zip
Archive: wallet_TRAINING2.zip
inflating: cwallet.sso
inflating: tnsnames.ora
inflating: truststore.jks
inflating: ojdbc.properties
inflating: sqlnet.ora
inflating: ewallet.p12
inflating: keystore.jks- up to this point, the steps are very similar with Oracle’s documentation and with Tim Hall’s blog. One way to use SQL*Plus at this point would be to change the Oracle delivered sqlnet.ora as per the Oracle documentation and switch the TNS_ADMIN setting towards tohe service I wish to connect to. But I want to avoid setting an environment variable every time I need to change the database I want to login into! Therefore, going forward, I will use a slightly different setup approach
- I ensure that all the TNS entries from both Oracle delivered tnsnames.ora files unziped above are appended to the (already existing) tnsnames.ora file present in the $TNS_ADMIN location:
radu.parvu:training2\==> pwd
/Users/radu.parvu/opt/instantclient/cloud/training2
radu.parvu:training2\==> cd $TNS_ADMIN
radu.parvu:admin\==> more tnsnames.ora
training1_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training1_high.adwc.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")))
training1_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training1_low.adwc.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")))
training1_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training1_medium.adwc.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")))
training2_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_high.atp.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")))
training2_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_low.atp.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")))
training2_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_medium.atp.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")))
training2_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_tp.atp.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")))
training2_tpurgent = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_tpurgent.atp.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")))
tnsnames.ora (END)
- in my own TNS_ADMIN location I clean up the sqlnet ora file so it contains only (this is different compared to the official documentation!):
radu.parvu:admin\==> pwd
/Users/radu.parvu/opt/instantclient/instantclient_18_1/network/admin
radu.parvu:admin\==> ls
README sqlnet.ora tnsnames.ora
radu.parvu:admin\==> vi sqlnet.ora
radu.parvu:admin\==> more sqlnet.ora
SSL_SERVER_DN_MATCH=yes
sqlnet.ora (END)
- but SQL*Net should know somehow of the location of the cloud files so we will move that info into the tnsnames.ora file by adding it to each reelevant defined service there. So we will add another security atribute in the form:
(MY_WALLET_DIRECTORY = "path_to_the_location_with_the_cloud_wallet_files_for_this_ service")- for example, my changed tnsnames.ora file for the ‘high’ services will look like:
more tnsnames.ora
training1_high =
(description=
(retry_count=20)
(retry_delay=3)
(address=(protocol=tcps)(port=1522)
(host=adb.eu-zurich-1.oraclecloud.com))
(connect_data=(service_name=h07ppiy09hxfso7_training1_high.adwc.oraclecloud.com))
(security=(MY_WALLET_DIRECTORY = "/Users/radu.parvu/opt/instantclient/cloud/training1")
(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")))
training1_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training1_low.adwc.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")))
training1_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training1_medium.adwc.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")))
training2_high =
(description=
(retry_count=20)
(retry_delay=3)
(address=(protocol=tcps)(port=1522)
(host=adb.eu-zurich-1.oraclecloud.com))
(connect_data=(service_name=h07ppiy09hxfso7_training2_high.atp.oraclecloud.com))
(security=(MY_WALLET_DIRECTORY = "/Users/radu.parvu/opt/instantclient/cloud/training2")
(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")))
training2_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_low.atp.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")))
training2_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_medium.atp.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")))
training2_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_tp.atp.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")))
training2_tpurgent = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=h07ppiy09hxfso7_training2_tpurgent.atp.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")))
tnsnames.ora (END)- after this change I can login with SQL*Plus onto the ‘high’ services for both ADB’s, but not the ‘medium’ (as it still missed the MY_WALLET_DIRECTORY information):
radu.parvu:training2\==> sqlplus admin@training1_high
SQL*Plus: Release 18.0.0.0.0 Production on Wed Feb 19 23:01:31 2020
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Feb 19 2020 22:53:59 +02:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
radu.parvu:training2\==> sqlplus admin@training2_high
SQL*Plus: Release 18.0.0.0.0 Production on Wed Feb 19 23:01:50 2020
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Feb 19 2020 22:58:11 +02:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
radu.parvu:training2\==> sqlplus admin@training2_medium
SQL*Plus: Release 18.0.0.0.0 Production on Wed Feb 19 23:02:10 2020
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28759: failure to open fileIn conclusion, by using a slightly different procedure than in the Oracle documetation we can configure a Client environment that allows us SQL*Plus and other OCI connections to several ADB’s.
As an extra bonus, for large entreprise deployments that choose to keep their wallet data into a centrally managed networked directory, this setup might add an extra layer of security.
Ref.:
- Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure – E85417-82 February 2020
- ORACLE_BASE Blog: https://oracle-base.com/articles/vm/oracle-cloud-autonomous-data-warehouse-adw-create-service#connecting-to-the-adw-service