How to connect to Oracle Autonomous cloud databases

To connect your applications to an Oracle Autonomous cloud database – any of the services like Autonomous Datawarehouse (ADW), Autonomous transaction processing (ATP) or Autonomous JSON database (ADJ) – we need to download and use a “wallet” and that gives the mutual TLS which provides enhanced security for authentication and encryption.
 
This post shows how to download a wallet and connect to autonomous database from application tools like SQL*Plus.
 
This blog post assumes that you got an access to a Cloud database. During creation ensure that “allow secure access from everywhere” option is selected and that will give access to the database from all your applications.
 

 
 
Then Download the Oracle database wallet files – downloading the wallet is simple, navigate to the database console page and select “DB Connection”
 

  
Then select “download wallet”, this will prompt for a wallet password. (This password won’t be used at runtime)
 

 
C:\Users\Rajeshwaran>cd c:\Oracle\TNSnames
 
c:\Oracle\TNSnames>dir *.zip
 Volume in drive C is System
 Volume Serial Number is C814-8C19
 
 Directory of c:\Oracle\TNSnames
 
25-Jul-2022  01:42            26,411 Wallet_ATPDEMO.zip
25-Jul-2022  06:36            26,416 Wallet_ATPDEMO19C.zip
               2 File(s)         52,827 bytes
               0 Dir(s)  402,420,989,952 bytes free
 
 
 
Once the wallet ZIP file has been downloaded, extract the content to a different folder (say ATP21C), the extracted contents will have files like cwallet.sso, sqlnet.ora and tnsnames.ora files, unlike other Oracle wallet files these files does not container database credentials, but we need to keep these wallets and its files into a secure location.
 
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,412,912,640 bytes free
 
 
Now here is the important step, edit the sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file.
 
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\TNSnames")))
SSL_SERVER_DN_MATCH=yes
 
View the tnsnames.ora file, it will contain some service names – each of these service names will give the choice of workload capabilities, these “network services” provide different levels of performance and concurrency for a particular database - that we can use it in our applications
 
atpdemo_high = (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")))
 
then we did a TNSPING it went like this
 
C:\Users\Rajeshwaran>tnsping atpdemo_high
 
TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 01-AUG-2022 21:37:36
 
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 (1390 msec)
 
Now we can connect from sql*plus and enter the ADMIN user password that we set when the database was initially created.
 
C:\Users\Rajeshwaran>sqlplus /nolog
 
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 1 21:40:33 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
idle> conn admin@atpdemo_high
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