Easily connect to your Autonomous Database from Cloud Shell and generate a reusable Database Link script.

Why do I need to “easily” connect to my Autonomous Database from Cloud Shell? Moreover, why do I need to “easily” generate a Database Link?

Establishing a connection to your Autonomous Database through the Oracle Cloud Infrastructure Cloud Shell may seem overwhelming, especially for new users. The task of gathering the required infromation can be a daunting task. Moreover, integrating a Database Link into the mix adds an additional layer of complexity.

This blog aims to simplify this process. By ensuring you have key information at hand, namely, your Compartment Name, Database Name, Bucket Name, and Username & Password for your Autonomous Databases, you can effortlessly set up a connection and generate a script that can be used to create a database link.

This post adds additional context to both “Simple steps to manage Autonomous Database from OCI Cloud Shell” by Dan Yadav and “Connecting to Your Autonomous Database Has Never Been Easier” by Can Tuzla.

Log into your Tenancy, then open the Cloud Shell.

Edit a few simple variables, then run this to connect to your Autonomous Database.

# EDIT THESE
my_compartment='development'
my_adw='ADAMADW' ## CASE IS IMPORTANT
my_adw_user='ADMIN'
my_adw_pw='Welcome_12345'

# THIS WILL SETUP A CONNECTION TO YOUR AUTONOMOUS DATABASE, EDITING IS NOT ADVISED
oci db autonomous-database generate-wallet --autonomous-database-id $(oci db autonomous-database list\
  --compartment-id $(oci iam compartment list --all --include-root --access-level ACCESSIBLE |\
  jq -r '.data[] | select(.name=="'${my_compartment,,}'") | .id') |\
  jq -r '.data[] | select(."display-name"=="'${my_adw}'") | .id') \
--password ${my_adw_pw} --file ${my_adw}_wallet.zip
mkdir ${my_adw}_wallet ; unzip ${my_adw}_wallet.zip -d ${my_adw}_wallet ; rm ${my_adw}_wallet.zip
sed -i.bak "s|(DIRECTORY=\"?/network/admin\")|(DIRECTORY=\"${HOME}/${my_adw}_wallet\")|g"\
~/${my_adw}_wallet/sqlnet.ora
export TNS_ADMIN=~/${my_adw}_wallet

sql ${my_adw_user,,}/${my_adw_pw}@${my_adw}_high

Edit a few simple variables, then Generate a script to create a Database Link.

# EDIT THESE
my_compartment='test'
my_adw='SCOTTADW' ## CASE IS IMPORTANT
my_adw_user='ADMIN'
my_adw_pw='Welcome_12345'
my_bucket='jawhite-bucket'
 
# THIS WILL SETUP A CONNECTION TO YOUR AUTONOMOUS DATABASE, EDITING IS NOT ADVISED
# MORE IMPORTANTLY IT WILL MAKE THE cwallet.sso FILE AVAILABLE TO UPLOAD TO CLOUD STORE
oci db autonomous-database generate-wallet --autonomous-database-id $(oci db autonomous-database list\
  --compartment-id $(oci iam compartment list --all --include-root --access-level ACCESSIBLE |\
  jq -r '.data[] | select(.name=="'${my_compartment,,}'") | .id') |\
  jq -r '.data[] | select(."display-name"=="'${my_adw}'") | .id') \
--password ${my_adw_pw} --file ${my_adw}_wallet.zip
mkdir ${my_adw}_wallet ; unzip ${my_adw}_wallet.zip -d ${my_adw}_wallet ; rm ${my_adw}_wallet.zip
sed -i.bak "s|(DIRECTORY=\"?/network/admin\")|(DIRECTORY=\"${HOME}/${my_adw}_wallet\")|g"\
~/${my_adw}_wallet/sqlnet.ora
 
# ADDING A BIT HERE TO UPLOAD cwallet.sso FILE TO OBJECT STORE
oci os object put --bucket-name ${my_bucket} --namespace $(oci os ns get --query "data" \
--raw-output) --file ${HOME}/${my_adw}_wallet/cwallet.sso --name ${my_adw,,}/cwallet.sso
 
# THIS WILL GENERATE THE SCRIPT, EDITING IS NOT ADVISED
my_adw_string=$(oci db autonomous-database list --compartment-id $(oci iam compartment list --all --include-root \
--access-level ACCESSIBLE | jq -r '.data[] | select(.name=="'${my_compartment,,}'") | .id')|\
jq -r '.data[] | select((."id" == "'$(oci db autonomous-database list --compartment-id \
$(oci iam compartment list --all --include-root --access-level ACCESSIBLE |\
jq -r '.data[] | select(.name=="'${my_compartment,,}'") | .id') |\
jq -r '.data[] | select(."display-name"=="'${my_adw}'") | .id')'")) | ."connection-strings"."all-connection-strings".HIGH')
 
echo -e "CREATE OR REPLACE DIRECTORY ${my_adw^^}_DIR AS '${my_adw,,}_dir';\n\n\
BEGIN\n\tDBMS_CLOUD_ADMIN.DROP_DATABASE_LINK('${my_adw^^}DBLINK');\nEXCEPTION WHEN OTHERS THEN NULL;\nEND;\n/\n\n\
BEGIN\n\tDBMS_CLOUD.DROP_CREDENTIAL('${my_adw^^}_DBLINK_CRED');\nEXCEPTION WHEN OTHERS THEN NULL;\nEND;\n/\n\n\
BEGIN\n\tDBMS_CLOUD.CREATE_CREDENTIAL(\n\t\tcredential_name => '${my_adw^^}_DBLINK_CRED',\n\t\tusername =>\
 '${my_adw_user^^}',\n\t\tpassword => '${my_adw_pw}');\nEND;\n/\n\n\
BEGIN\n\tDBMS_CLOUD.DROP_CREDENTIAL(\n\t\tcredential_name => 'OCI_NATIVE_CRED');\nEXCEPTION WHEN OTHERS THEN NULL;\nEND;\n/\n\n\
BEGIN\n\tDBMS_CLOUD.CREATE_CREDENTIAL(\n\t\tcredential_name => 'OCI_NATIVE_CRED',\n\t\tuser_ocid => '${OCI_CS_USER_OCID}',\
\n\t\ttenancy_ocid => '${OCI_TENANCY}',\n\t\tprivate_key => '$(cat $(awk -F'key_file=' '/key_file=/ {print $2}' ~/.oci/config)|\
tr -d '\n' | sed 's/-----BEGIN PRIVATE KEY-----//;' | sed 's/-----END PRIVATE KEY-----//;')',\n\t\tfingerprint => \
'$(grep '^fingerprint=' .oci/config | sed -e 's/^fingerprint=//')');\nEND;\n/\n\n\
BEGIN\n\tDBMS_CLOUD.GET_OBJECT(\n\t\tobject_uri => \
'https://objectstorage.${OCI_REGION}.oraclecloud.com/n/$(oci os ns get --query "data" --raw-output)/b/${my_bucket}/o/${my_adw,,}%2Fcwallet.sso',\n\t\tcredential_name => 'OCI_NATIVE_CRED',\n\t\tdirectory_name => '${my_adw^^}_DIR');\nEND;\n/\n\n\
BEGIN\n\tDBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(\n\t\tdb_link_name => '${my_adw^^}DBLINK',\n\t\thostname => \
'$(echo ${my_adw_string}|cut -d':' -f1)',\n\t\tport => \
'$(echo ${my_adw_string}|cut -d':' -f2|cut -d'/' -f1)',\n\t\t\
service_name => '$(echo ${my_adw_string}|cut -d'/' -f2)',\n\t\tcredential_name => '${my_adw^^}_DBLINK_CRED',\n\t\t\
directory_name => '${my_adw^^}_DIR');\nEND;\n/\n\nexit;"|expand -t 2 > ${my_adw,,}_dblink.sql
chmod go-rw ${my_adw,,}_dblink.sql
  
unset my_adw my_adw_pw my_adw_user my_compartment my_bucket

Display the output of the Database Link Script that was generated.

echo ${my_adw,,}_dblink.sql
scottadw_dblink.sql

more scottadw_dblink.sql

CREATE OR REPLACE DIRECTORY SCOTTADW_DIR AS 'scottadw_dir';

BEGIN
  DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK('SCOTTADWDBLINK');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
  DBMS_CLOUD.DROP_CREDENTIAL('SCOTTADW_DBLINK_CRED');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'SCOTTADW_DBLINK_CRED',
    username => 'ADMIN',
    password => 'Welcome_12345');
END;
/

BEGIN
  DBMS_CLOUD.DROP_CREDENTIAL('OCI_NATIVE_CRED');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_NATIVE_CRED',
    user_ocid => 'ocid1.user.oc1..blahblahblahblahblahblahblahblahblahblahblahblahblahblahblah',
    tenancy_ocid => 'ocid1.tenancy.oc1..blahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb'
    private_key =>  'blahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahbla
hblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahbla
hblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahbla
hblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahbla
hblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahbla
hblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahbla
hblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahblahb
lahblahblahblahblahblahblahblahblahblah',
    fingerprint => 'bl:ah:bl:ah:bl:ah:bl:ah:bl:ah:bl:ah:bl:ah:bl:ah'
  );
END;
/

BEGIN
  DBMS_CLOUD.GET_OBJECT(
    object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/blahblahblah/b/jawhite-bucket/o/scottadw%2Fcwallet.sso',
    credential_name => 'OCI_NATIVE_CRED',
    directory_name => 'SCOTTADW_DIR');
END;
/

BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'SCOTTADWDBLINK',
    hostname => 'adb.us-ashburn-1.oraclecloud.com',
    port => '1522',
    service_name => 'blahblahblahbla_scottadw_high.adb.oraclecloud.com',
    credential_name => 'SCOTTADW_DBLINK_CRED',
    directory_name => 'SCOTTADW_DIR');
END;
/

exit;

Create a Database Link to ScottADW from AdamADW

my_adw='ADAMADW' ## CASE IS IMPORTANT 
my_adw_user='ADMIN' 
my_adw_pw='Welcome_12345'

export TNS_ADMIN=~/${my_adw}_wallet 
sql ${my_adw_user,,}/${my_adw_pw}@${my_adw}_high @ scottadw_dblink.sql
unset my_adw my_adw_pw my_adw_user

Easily connect to your Autonomous Database from Cloud Shell and generate a reusable Database Link script.

Why do I need to “easily” connect to my Autonomous Database from Cloud Shell? Moreover, why do I need to “easily” generate a Database Link?

Log into your Tenancy, then open the Cloud Shell.

Edit a few simple variables, then run this to connect to your Autonomous Database.

Edit a few simple variables, then Generate a script to create a Database Link.

Display the output of the Database Link Script that was generated.

Create a Database Link to ScottADW from AdamADW

Jameson White

Principal Research Engineer, SQL & Analytics

Discover OCI Native Credential with ease using Cloud Shell

Essbase 21.5.5 Marketplace and Independent Deployment is live

Easily connect to your Autonomous Database from Cloud Shell and generate a reusable Database Link script.

Why do I need to “easily” connect to my Autonomous Database from Cloud Shell? Moreover, why do I need to “easily” generate a Database Link?

Log into your Tenancy, then open the Cloud Shell.

Edit a few simple variables, then run this to connect to your Autonomous Database.

Edit a few simple variables, then Generate a script to create a Database Link.

Display the output of the Database Link Script that was generated.

Create a Database Link to ScottADW from AdamADW

Authors

Jameson White

Principal Research Engineer, SQL & Analytics

Discover OCI Native Credential with ease using Cloud Shell

Essbase 21.5.5 Marketplace and Independent Deployment is live