Connecting SQL*Plus in Cloud Shell to an Autonomous Database

Objective of this article: connect SQL*Plus in Cloud Shell to an Autonomous Database instance (in a free tier – in the same or a different cloud tenancy)

The starting situation: I have an autonomous database instance running somewhere in Oracle Cloud Infrastructure. I have Cloud Shell as a versatile management and development environment. Cloud Shell includes SQL*Plus (19.5). I want to connect my SQL*Plus Client in Cloud Shell to the Autonomous Database instance.

Here are the steps for doing that.

Start Autonomous Database – for example in the OCI Console – if it is not currently running.

Then, in the ATP or ADW Console,  click on DB Connection to download credentials files for connecting to the database – in a wallet file.

Click on Download Wallet. I have created a password for the wallet – but frankly I never used it again:

Download the Client Credentials – a zip file with tnsnames.ora and sqlnet.ora files used by SQL*Plus to connect to the database instance. At this point, this zip-file is on my laptop – certainly not yet on Cloud Shell.

I have now uploaded the zip file to OCI Object Storage in the same tenancy as where I am running Cloud Shell.

The file is now in bucket-01 as Wallet_labdb.zip:

I need the file in Cloud Shell. Using OCI CLI I can easily download the file to Cloud Shell:

oci os object get -bn bucket-01 --name Wallet_labdb.zip --file wallet.zip

At this point, the zip-file is on Cloud Shell. Next I create a directory db and extract the contents of the zip-file into this directory:

unzip wallet.zip -d db

Change into the directory and list the files

cd db
ls -l

Open sqlnet.ora in the vi editor and set the DIRECTORY to refer to the current directory – that holds the tnsnames.ora file. Save the sqlnet.ora file.

Set the TNS_ADMIN environment variable to the directory where the unzipped credentials files are, not to the credentials file itself.

export TNS_ADMIN=/home/jellema/db

Connect using a database user (ADMIN), password, and database TNS name provided in the tnsnames.ora file.

sqlplus admin@labdb_low

then when prompted type the password defined when creating the database user:

The connection is made and SQL*Plus can be used as always – from Cloud Shell to an Autonomous Database somewhere in some cloud.

Resources

Documentation Connect Autonomous Data Warehouse Using a Client Application  https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-preparing.html#GUID-EFAFA00E-54CC-47C7-8C71-E7868279EF3B

Documentation Connect with SQL*Plus https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-sqlplus.html#GUID-A3005A6E-9ECF-40CB-8EFC-D1CFF664EC5A