Practical Autonomous DB with OCI CLI — Generate Wallet and Connection Strings

Generate Wallet, Easy Connect, TLS, and mTLS connection strings using OCI CLI. Automate routine tasks with few simple commands.

In the previous article we discussed how to create ADB using CLI. The next step is to generate wallet and share connection string with users. In this post I will demonstrate how to achieve this using simple CLI commands and save precious mouse-clicks.

Why would you do this in CLI?

1. You want to automate DB creation and upload wallet to bucket for better auditing instead of sharing via email.
2. Share connection strings with various users.
3. You are a power user and believe in power of terminal over console.
4. Wallet rotation and sharing with new wallet with users.
5. ……Insert your reason here!

Whatever be your reason, CLI is here for everything.

Task 1 — Generate Wallet

If you have been following this series, by now you should know that we need few OCID’s before executing CLI commands.

# Replace <INSERT_COMPARTMENT_NAME> with actual compartment name
export compname=<INSERT_COMPARTMENT_NAME>
export Compocid=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[] | select(.name == \"${compname}\") | .id"`

# Replace <INSERT_DB_NAME> with actual DB name.
export DBname=<INSERT_DB_NAME>
# Save DB OCID in variable based on name(case sensitive)
export ATPocid=`oci db autonomous-database list --compartment-id $Compocid --query "data[?contains(\"display-name\",'$DBname')]"|jq -r ".[].id"`

# Get object storage namespace 
export namespace=`oci os ns get|jq -r ".data"`
export bucketname=Wallet_$DBname

# Create bucket with object events enabled
#Optionally Configure bucket notifications to notify users
export Bucketocid=`oci os bucket create --compartment-id $Compocid --name $bucketname --namespace-name $namespace --object-events-enabled true |jq -r ".data.id"`

#Set Wallet filename
export filename=Wallet_$DBname.zip
echo $filename
export wallet_password=<SUPER_SECRET_PASSWORD>

Generate Wallet.

#Generate Wallet
oci db autonomous-database generate-wallet --autonomous-database-id $ATPocid --file $filename --password $wallet_password
 
# Upload to object store bucket. Overwrite existing file using --force option
oci os object put -bn $bucketname --file $filename -ns $namespace --force

Wallet is uploaded to bucket

That’s it, the wallet is generated. No more death by a thousand clicks. You can also configure email notification based on bucket events to notify users.

Wallet upload mail notification

The email could use some cosmetic make-up, but that’s for another day.

Task 2 — Generate connection strings

If your ADB uses TLS connections i.e. wallet-less connections, then you may need to share TLS connection strings with users. How can you do that with CLI?

There is no get-connection-string CLI option. We need to perform some command line magic to get the connection strings.

I have used jq to extract the values since I find it easier to use. CLI “query” option can also be used for same purpose.

The below code writes Easy Connect, TLS and mTLS connection strings to a text file and uploads the file to object store bucket.

# Replace <INSERT_COMPARTMENT_NAME> with actual compartment name
export compname=<INSERT_COMPARTMENT_NAME>
export Compocid=`oci iam compartment list --compartment-id-in-subtree true --all |jq -r ".data[] | select(.name == \"${compname}\") | .id"`
# Replace <INSERT_DB_NAME> with actual DB name.
export DBname=<INSERT_DB_NAME>

# Save DB OCID in variable based on name(case sensitive)
export ATPocid=`oci db autonomous-database list --compartment-id $Compocid --query "data[?contains(\"display-name\",'$DBname')]"|jq -r ".[].id"`
export bucketname=Wallet_$DBname

#get namespace 
export namespace=`oci os ns get|jq -r ".data"`

# use [] to filter {} from jq output and extract only connection strings
echo "=============== EZConnect connection strings =================================" > ConnectionStrings_$DBname.txt
oci db autonomous-database get --autonomous-database-id $ATPocid |jq -r '.data."connection-strings"."all-connection-strings"[]' >> ConnectionStrings_$DBname.txt

echo "=============================================================================" >> ConnectionStrings_$DBname.txt
echo "=============== TLS Long form connection strings =================================" >> ConnectionStrings_$DBname.txt
oci db autonomous-database get --autonomous-database-id $ATPocid |jq -r '.data."connection-strings".profiles[].value'|grep 1521 >> ConnectionStrings_$DBname.txt
echo "=============================================================================" >> ConnectionStrings_$DBname.txt
echo "=============== mTLS Long form connection strings =================================" >> ConnectionStrings_$DBname.txt
oci db autonomous-database get --autonomous-database-id $ATPocid |jq -r '.data."connection-strings".profiles[].value'|grep 1522 >> ConnectionStrings_$DBname.txt
echo "=============================================================================" >> ConnectionStrings_$DBname.txt

#Upload connection strings to bucket.  Use --force to overwrite existing file
oci os object put -bn $bucketname --file ConnectionStrings_$DBname.txt -ns $namespace --force

Thank you for reading. Hope this was informative and helpful. Leave a note with your thoughts in comments.

Access all Code Samples here

GitHub - GitJig/PracticalOCI: OCI examples for practical use

Sample Connection strings file generated using CLI.

=============== EZConnect connection strings =================================
adb.eu-frankfurt-1.oraclecloud.com:1522/abcde.................._calvin_high.adb.oraclecloud.com
adb.eu-frankfurt-1.oraclecloud.com:1522/abcde.................._calvin_low.adb.oraclecloud.com
adb.eu-frankfurt-1.oraclecloud.com:1522/abcde.................._calvin_medium.adb.oraclecloud.com
adb.eu-frankfurt-1.oraclecloud.com:1522/abcde.................._calvin_tp.adb.oraclecloud.com
adb.eu-frankfurt-1.oraclecloud.com:1522/abcde.................._calvin_tpurgent.adb.oraclecloud.com
=============================================================================
=============== TLS Long form connection strings =================================
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_tpurgent.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
=============================================================================
=============== MTLS Long form connection strings =================================
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
(description= (retry_count=15)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=abc.......adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=abcde.................._calvin_tpurgent.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
=============================================================================