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
Can we also use this method to connect to Autonomous database in a private subnet?
I’m getting ORA-12162: TNS:net service name is incorrectly specified
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
And what value you must put there ? I try with path where the unzipped credentials files are and same error?