Connecting SQL*Plus in Cloud Shell to an Autonomous Database image 9

Connecting SQL*Plus in Cloud Shell to an Autonomous Database

imageObjective 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.

image

 

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

image

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.

image

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

image

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

SNAGHTMLcf19b91

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

image

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

image

Change into the directory and list the files

cd db
ls -l

image

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.

image

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

image

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:

 

image

 

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

4 Comments

  1. Saurabh Salunkhe November 11, 2020
  2. Rami Kelesli April 25, 2020
    • Paul Parkinson August 6, 2020
      • Cesar December 10, 2020