OCI: Database Link between 2 Autonomous Databases

Database links are seen by some as evil and as wonderful tools by others. Whatever your side on this, let's see how we can create database links between 2 autonomous databases. The characteristics of the below example are:

• We want to get data from the PROD1 database
• The database link will be created into the PROD2 database
• A network connectivity is already setup between the databases; the ones in this example are in the same tenancy and in the same region; if your databases are in different tenancies, you want to check this documentation first; if they are in different regions, you please have a look at this documentation first
• The databases can be in the same compartment or not; below example is with 2 databases in different compartments
• The autonomous databases can be ADW or ATP, it does not matter
• I will use one and unique Linux VM with a non privileged user fred@myvm on that VM
• Many operations are only doable by the ADMIN user so be sure you know the ADMIN user credentials
• Below assumes that your OCI client is configured and works

A summary of what needs to be done looks straigthforward on a graph:

But the fact that these are autonomous databases implies many steps which I will be describing in this blog.
Let's follow and detail each of the steps shown on the above picture.

0/ Requirements summary

A short summary of the requirements to be able to create a DB Link:

• Network connectivity between the 2 databases
• A database username and password on PROD1
• The PROD1 database certificate
• OCIDs of PROD1, PROD2, your compartments, etc...

1/ Get the PROD1 wallet

Ge the PROD1 database OCID from the console and use it to get the database wallet from your Linux VM:

[fred@myvm ~] oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.xxx --file adb_wallet.zip --password "abc123456"
Downloading file  [####################################]  100%
[fred@myvm ~] ls -ltr adb_wallet.zip
-rw-rw-r--. 1 fred fred 21974 Sep 30 13:05 adb_wallet.zip
[fred@myvm ~]

Note that we must specify a wallet password using this oci db autonomous-database generate-wallet command; related error message you would face (I tried :)) are:

Error: Missing option(s) --password.
"message": "The wallet download password cannot be an empty string.",
"message": "The wallet download password must be at least 8 characters.",

You can also download this wallet from the console: "Autonomous database details" => "Database connection" => "Download wallet"; we won't use this password so feel free to forget that password immediately! Let's unzip the wallet and points the files we will be interested in:

[fred@myvm ~] unzip adb_wallet.zip
Archive:  adb_wallet.zip
  inflating: ewallet.pem
  inflating: README
  inflating: cwallet.sso     <== the certificate we will work with
  inflating: tnsnames.ora    <== we will need host, service and port information at the end of the procedure
  inflating: truststore.jks
  inflating: ojdbc.properties
  inflating: sqlnet.ora
  inflating: ewallet.p12
  inflating: keystore.jks
[fred@myvm ~]

All right, step 1 was easy, let's jump into step 2.

2/ Transfer the certificate

This one looks simple but many steps are needed to achieve the transfer of the certificate; indeed, we have to transfer the PROD1 cwallet.sso file into a PROD2 directory (DBFS). The steps to achieve that are:

• 2.1/ Create a bucket (unless you already have one)
• 2.2/ Copy the cwallet.sso file from your Linux VM into your bucket
• 2.3/ Create a directory into the PROD2 database
• 2.4/ Copy the cwallet.sso file from your bucket into the PROD2 PROD2 directory
• 2.5/ Drop your bucket if you created one just for this transfer

And on top of that, we will need to create some credentials to get authenticated here and there. Yeaaaahh all of that just to copy a file . . . hopefully it is a one time thing :)

2.1/ Create a bucket (unless you already have one)

You'll find your PROD2 database compartment OCID in the console (search for "Compartment"):

[fred@myvm ~] oci os bucket create --name wallets --compartment-id ocid1.compartment.oc1..aaaaaaabbbbbbbbcccccccccccfffffffff  
{
    . . .
        "CreatedBy": "default/fred",
        "CreatedOn": "2024-09-30T13:07:25.850Z"
    . . .
    "name": "wallets",
    . . .
}
[fred@myvm ~]

2.2/ Copy the cwallet.sso file from your Linux VM into your bucket

[fred@myvm ~] oci os object put --bucket-name wallets --file cwallet.sso  
Uploading object  [####################################]  100%
{
  "etag": "9fba7327-d979-4b21-a898-8b795ea02f76",
  "last-modified": "Mon, 30 Sep 2024 13:09:42 GMT",
  "opc-content-md5": "sstACFWtW/4qBGNkdH/iuQ=="
}
 [fred@myvm ~]

Check that the file has been correctly copied into the wallet:

[fred@myvm ~] oci os object list --bucket-name wallets
{
  "data": [
      . . .
      "name": "cwallet.sso",
      . . .
}
[fred@myvm ~]

2.3/ Create a directory into the PROD2 database

Note that as of now only ADMIN can create directories and DB Links.

SQL> show user
USER is "ADMIN"
SQL> create directory wallets as 'wallets';
Directory created.
SQL> select OWNER, DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where directory_name = 'WALLETS';
OWNER    DIRECTORY_NAME   DIRECTORY_PATH
-------- ---------------- --------------------------------------------------------------------------------
SYS      WALLETS          /u03/dbfs/0B3BHK09F13420009KP8/data/wallets
SQL>

2.4/ Copy the cwallet.sso file from your bucket into the PROD2 database directory

We now need to create somne credentials to achieve this. I use to go with "Auth tokens" which are kind of the same tokens you use for github if you are familiar with. You'll need your user-id which you can find in the console "Your profile" (top right) => "My profile" then copy your OCID in "User information" or in the ~/.oci/config file (grep user ~/.oci/config):

[fred@myvm ~] oci iam auth-token create --user-id ocid1.user.oc1..xxxx --description fred_cred_for_wallet_dblink
  {
  "data": {
    "description": "fred_cred_for_wallet_dblink",  <== it is actually the credential name
    . . .
    "token": "c]CBZfi97_E(;U5",                    <== your token
    "user-id": "ocid1.user.oc1..xxxx"
  },
  "etag": "89c91cb65f03452fabd74c618609d0bb"
}
[fred@myvm ~]

You can also use the console to get a token: "Your profile" (top right) => "My profile" => "Auth tokens" (bottom left) => "Generate token".

Now create the credentials (as ADMIN):

SQL> show user
USER is "ADMIN"
SQL> BEGIN
  2  DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'fred_cred_for_wallet_dblink', username => 'fred', password => 'c]CBZfi97_E(;U5');
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>

Note that I kept "c]CBZfi97_E(;U5')" as token instead of putting "xxxx"; it is to show what an Auth token looks like, it is obviously not my real token. Also, the user used here is the one you connect to your tenancy with the console so most likely your email address.

Now to copy the cwallet.sso file into the WALLETS directory, we need to know the path of that file; for now we just know that it is located into the wallets bucket. You can find this path from the console in "Object Details" on the right of the file when you list the content of your bucket. I could not find a way to get that info using the oci client, this is then an opportunity to learn how these paths are built and build it ourselves.

Object paths are of this form (I put shell variables in the path as this is how we're gonna build it):

https://${NAMESPACE}.objectstorage.${REGION}.oci.customer-oci.com/n/${NAMESPACE}/b/${BUCKET_NAME}/o/${FILE}

We can find these values as below:

[fred@myvm ~] NAMESPACE=$(oci os ns get | jq -r '.data')
[fred@myvm ~] REGION="us-ashburn-1"     <== this can be found in the ~/.oci/config file
[fred@myvm ~] BUCKET_NAME="wallets"
[fred@myvm ~] FILE="cwallet.sso"
[fred@myvm ~] echo "https://${NAMESPACE}.objectstorage.${REGION}.oci.customer-oci.com/n/${NAMESPACE}/b/${BUCKET_NAME}/o/${FILE}"
https://ihdfd7683hjkz.objectstorage.us-ashburn-1.oci.customer-oci.com/n/ihdfd7683hjkz/b/wallets/o/cwallet.sso
[fred@myvm ~]

And we get file path of the file! We can finally copy the cwallet.sso file from our bucket into the directory (DBFS)!

SQL> show user
USER is "ADMIN"
SQL> BEGIN
  2  DBMS_CLOUD.GET_OBJECT(
  3  object_uri => 'https://ihdfd7683hjkz.objectstorage.us-ashburn-1.oci.customer-oci.com/n/ihdfd7683hjkz/b/wallets/o/cwallet.sso',
  4  directory_name => 'WALLETS',
  5  credential_name => 'fred_cred_for_wallet_dblink');
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL> SELECT OBJECT_NAME, BYTES, CREATED, LAST_MODIFIED from DBMS_CLOUD.LIST_FILES('WALLETS');
OBJECT_NAME             BYTES CREATED                                            LAST_MODIFIED
------------------ ---------- ------------------------------------------------ ---------------------------------------------
cwallet.sso              5349 30-SEP-24 02.21.22.000000 PM +00:00                30-SEP-24 02.21.22.000000 PM +00:00
SQL>

2.5/ Drop your bucket if you created one just for this transfer

If you have created a dedicated bucket for this transfer, you may want to drop it to keep things clean, we won't need it anymore.

[fred@myvm ~] oci os bucket delete --name wallets
Are you sure you want to delete this bucket? [y/N]: y
[fred@myvm ~]

3/ Create the DB Link

OK! we can now create the DB Link -- after we create some more credentials: the credentials to connect to the PROD1 database:

SQL> BEGIN
2  DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'PROD1_USER', username => 'A_PROD1_USER', password => 'xxxxxxxxxx');
3  END;
4  /
PL/SQL procedure successfully completed.
SQL>

And (finally !) create the DB Link. You'll need the below information from the tnsnames.ora file from the wallet we generated during step 1:

• hostname => xxxxxxx.adb.us-ashburn-1.oraclecloud.com
• port => 1522
• service_name => yyyyyyy.adb.oraclecloud.com

SQL> BEGIN
  2  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
  3  db_link_name => 'TO_PROD1',
  4  hostname => 'xxxxxxx.adb.us-ashburn-1.oraclecloud.com',
  5  port => '1522',
  6  service_name => 'yyyyyyy.adb.oraclecloud.com',
  7  credential_name => 'PROD1_USER',
  8  directory_name => 'WALLETS',
  9  private_target => TRUE,
 10  public_link => TRUE);
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>

And now the moment of truth: testing the DB Link !

SQL> select count(*) from user_objects@SOURCE_DATABASE;
  COUNT(*)
----------
      1994
SQL>

Congratulations, now go enjoy your new DB Link between your 2 autonomous databases!