Connecting SQL*Plus in Cloud Shell to an Autonomous Database

Lucas Jellema 2

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

2 thoughts on “Connecting SQL*Plus in Cloud Shell to an Autonomous Database

    1. There are a few ways to address this but I get around this ORA-12162 by changing the DIRECTORY value in the WALLET_LOCATION line of the sqlnet.ora

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next Post

Install SQLcl on Oracle Cloud Infrastructure Cloud Shell and make it work against an Autonomous Database

Facebook0TwitterLinkedinCloud Shell on Oracle Cloud Infrastructure is a versatile management and development environment to access and manage Oracle Cloud Infrastructure resources. Cloud Shell comes with many tools and runtime out of the box (or rather inside the box) including OCI CLI, Java and SQL*Plus. However, the Oracle SQL Developer Command […]