Connecting SQL*Plus in Cloud Shell to an Autonomous Database

0

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

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Leave a Reply

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