Creating DB Links between two Oracle Autonomous databases

Creating db links between Oracle Cloud Autonomous databases is similar in concept to connecting to a cloud db from on-premises; but the steps are necessarily different because you don’t have access to the database server’s file system to adjust the sqlnet.ora or tnsnames.ora files.

From within the Oracle Cloud interface select the database you want to link to, go to the DB connection tab and download the client credentials wallet. You will be given a choice of Instance or Regional wallet. If your databases share the same region, then select the Regional wallet. This download is a zip file containing, among other contents, the cwallet.sso wallet file. You will also want to look at the tnsnames.ora to provide connection information for the remote db.

Once you have your wallet file you need to upload it to the cloud. Go to the Object Storage page and create bucket for it. You can accept the default creation options but should give it a meaningful name. After creating the bucket, open it and click the Upload button. Select or drag your cwallet.sso file, upload it and close the bucket.

If you were using on premises database you would now edit your sqlnet.ora file to tell it where to find your wallet file. For two ADBs though, you will instead use the DBMS_CLOUD and DBMS_CLOUD_ADMIN packages to move your wallet file into a known directory and then create the link to point to it.

First you will need to know the URL for your wallet file. Click the menu icon for your file and select Object Details. You should see a URL something like this, the iazqmiad4rkg path is a random alphanumeric string, yours will be different:

https://objectstorage.MY_REGION.oraclecloud.com/n/iazqmiad4rkg/b/MY_BUCKET/o/cwallet.sso

If you don’t have a credential already you will need to create one in the database where you will create the db link.

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'DEF_CRED_NAME',
     username => 'my_email@example.com',
     password => 'MY_AUTH_TOKEN'
   );
END;
/

If you don’t have an Auth Token you will need to create one in the cloud console under Identity/Users. Select your user, go to Auth Tokens, and create a new token, copy the token value and use it as the password.

After you have both your credential and the URL to your wallet file you can copy the file to one of your database directories. By default links will use the DATA_PUMP_DIR but if you have another directory available you can use it. In this example I’ll create a parent directory to hold my wallets and then a subdirectory for each wallet file I use. In this case a wallet for a 21c db I’m going to link to.

create directory wallets as 'wallets';
create directory wallets_my21cdb as 'wallets/my21cdb';

Next copy the wallet file from the Object Store to your directory using the credential you defined.

BEGIN 
  DBMS_CLOUD.GET_OBJECT(
  credential_name => 'DEF_CRED_NAME',
  object_uri => 'https://objectstorage.MY_REGION.oraclecloud.com/n/iazqmiad4rkg/b/MY_BUCKET/o/cwallet.sso',
  directory_name => 'WALLETS_MY21CDB'); 
END;
/

Rather than embedding the user name and password in the db link with CREATE DATABASE LINK DDL, you will need to create another credential with the remote database user’s name and password. These require execute privileges on the DBMS_CLOUD and DBMS_CLOUD_ADMIN packages for the local user creating the link. Also, even though you don’t issue the CREATE DATABASE LINK command directly, the local user will still need the privilege to do so

GRANT EXECUTE on DBMS_CLOUD_ADMIN to MY_LOCAL_USER;
GRANT EXECUTE on DBMS_CLOUD to MY_LOCAL_USER;
GRANT CREATE DATABASE LINK to MY_LOCAL_USER;

Once you have those privileges you can create the credential and the link. Fill in the hostname, port, service, and ssl values with those from the tnsnames.ora file downloaded with the wallet in the zip file.

BEGIN
   dbms_cloud.create_credential(
      credential_name => 'MY_21C_CREDENTIAL',
      username        => 'MY_REMOTE_USER',
      password        => 'myremoteuserpassword');
END;
/

BEGIN
     dbms_cloud_admin.create_database_link(
         db_link_name      => 'MY_LINK_TO_21C_DB',
         hostname          => 'adb.MY_REGION.oraclecloud.com',
         port              => '1522',
         service_name      => 'my_db_service_name',
         ssl_server_cert_dn   =>
             'CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
         credential_name   => 'MY_21C_CREDENTIAL',
         directory_name    => 'WALLETS_MY21CDB');
END;
/

At this point the link will be created and ready for use.

SQL> select ora_database_name@my_link_to_21c_db from dual;

ORA_DATABASE_NAME
-----------------------------
EHS3FFFMGKLU5X_MY21CDB

If you no longer need the link, you can use DBMS_CLOUD_ADMIN to drop the link or simply issue a DROP DATABASE LINK statement as you would with an on-premises db link.

While there are significantly more steps involved than linking between two on-premises databases none of them are particularly difficult and should, hopefully, not be needed very often.

This was a learning exercise for me and I hope my notes here help