Creating link between 2 autonomous databases

A database link can serve for several purposes such as manual replication of data or other task related. Let’s hands on!

STEP 1: Create A bucket TO STORE THE WALLET OF TARGET DATABASE

We create a bucket in which we are going to put the wallet of the remote database. Execute the following in the cloud shell:

# put your values here
export DBID=ocid1.autonomousdatabase.oc1....a
export COMPARTMENT=ocid1.compartment.oc1..aaaa...q
#
oci os bucket create --name wallet  --compartment-id $COMPARTMENT
oci db autonomous-database generate-wallet --autonomous-database-id $DBID --file mywallet.zip --password <database password for admin user>
unzip mywallet.zip
oci os object put --bucket-name wallet --file cwallet.sso

STEP 2: CREATE A DYNAMIC-GROUP AND A POLICY

To avoid use api signing keys, we are using the resource principal concept, an OCI entity that helps to work with OCI resources in a controlled way regarding security and privileges.

#
# create dynamic group
#
oci iam dynamic-group create --name dblinkdyngroup --description dblinkdyngroup --matching-rule "ALL {resource.type = 'autonomous-databases'}"
#
# create policy at root level
#
TENANCY=$(oci iam availability-domain list --all | jq -r '.data[0]."compartment-id"')
oci iam policy create --compartment-id $TENANCY --name dblinkpolicy --statements '["allow dynamic-group dblinkdyngroup to manage buckets in tenancy"]' --description dblinkpolicy
#

STEP 3: GATHER CONNECTION DETAILS

We continue obtaining data needed to the setup just by issuing commands in cloud shell:

#
# crostruct the url of the wallet file stored in object storage
#
home=$(oci iam region-subscription list | jq -r '.data[0]."region-name"')
namespace=$(oci os ns get | jq -r .data)
echo 'https://objectstorage.'$home'.oraclecloud.com/n/'$namespace'/b/wallet/cwallet.sso'
#
# cat the content of tnsnames.ora to get connection details
#
cat tnsnames.ora extracted from the wallet zipfile exploded in step1
#

STEP 4: EXECUTE PL/SQL CODE iN SQLDEVELOPER WEB (DATABASE ACTIONS) THE CREATION OF DATABASE LINK

-- this sentence allows the database to execute things in OCI depending on a dynamic-group and policies attached to it
EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
/
SELECT owner, credential_name FROM dba_credentials 
        WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN';
/
CREATE DIRECTORY dblink_wallet_dir AS 'wallet_dir';
/
BEGIN 
     DBMS_CLOUD.GET_OBJECT(
        credential_name => 'OCI$RESOURCE_PRINCIPAL',
        object_uri => 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/wallet/o/cwallet.sso',
        directory_name => 'DBLINK_WALLET_DIR'); 
END;
/
BEGIN
     --DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK('MYDBLINK');
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'MYDBLINK', 
          hostname => 'adb.<region>.oraclecloud.com', 
          port => '1522',
          service_name => '<xxxxxxxx>.adb.oraclecloud.com',
          ssl_server_cert_dn => 'CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
          credential_name => 'OCI$RESOURCE_PRINCIPAL',
          directory_name => 'DBLINK_WALLET_DIR');
END;
/

STEP 5: TEST SOME STUFF

create table t (c varchar2(10));
/
insert into t values ('00000');
/
insert into t@MYDBLINK select * from t
/
select * from t@MYDBLINK
/
select * from t@MYDBLINK minus select * from t -- this sentence should return nothing
/

That’s all, hope it helps! 🙂