Ameer Khan

Database link is useful for allowing data queries from one database to another instantly without needing to schedule batch activity that pulls the data together. You can also pull data into a staging table and perform transformation in an extract, load, and transform (ELT) process.

Database link helps secure your data too. Imagine an employee submitting an expense to an account application. The Business Intelligence users don’t need to access to the account application database directly. Instead, they can use a database link to retrieve certain information from the account application database securely.

At the time of this blog post, Oracle Autonomous Database only supports connecting to other databases through a database link with a public IP address. This limitation poses a problem to most customers, especially when they have strict rules about not exposing the database with a public IP attached to it. They want their autonomous database to connect to their on-premises database through private IP using either Oracle Cloud Infrastructure (OCI) FastConnect or IPSec VPN.

Solution

You can overcome this problem in a few ways. One simple solution only takes you 30 minutes to implement.

The source and target autonomous databases are deployed in private subnets with a network security group (NSG) attached to each. They have SSL preconfigured, which is required if you want to use a database link to connect from a secure autonomous database. These autonomous databases can from Oracle Database Cloud service or an on-premises database.

I set up another Compute instance in a public subnet. It has all the default packages installed from the provisioning process. No extra software is required. To forward traffic coming from the source database, I use firewall rules.

We also need a bucket to store the cwallet.sso file of the target autonomous database. When creating the database link, we need to copy it into the source database.

At the end of the setup, the traffic flows from the source autonomous database to the public virtual machine (VM) and from the public VM to the target autonomous database..

Steps to deploy

In this example, I only allow one IP address in the security list for extra security. The IP is the outbound IP of the source autonomous database. You can get it by running the following command against the source autonomous database:

SQL> select cloud_identity FROM v$pdbs;

Run the following commands to configure the firewall rules, where 140.238.193.37 is the IP from the source database and 10.0.2.45 is the IP for target database. The target can also be your on-premises database with a private IP).

$ sudo firewall-cmd –add-rich-rule=’rule family=ipv4 source address=”140.238.193.37″ forward-port to-addr=”10.0.2.45″ to-port=”1522″ protocol=”tcp” port=”1522″‘

  $ sudo firewall-cmd –add-masquerade

  $ sudo firewall-cmd –runtime-to-permanent

5. Now create an Object Storage credential in your source data to fetch the cwallet.sso from the bucket. To create Object Storage credentials, or log in to sqlplus or sqldeveloper and run the following command:

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

  credential_name => ‘DEF_CRED_NAME’,

  username => ‘oracleidentitycloudservice/db_backup@oracle.com’,

  password => ‘<auth_token>’

  );

  END;

  /</auth_token>

6. Create a database link wallet directory in the source database to store the cwallet.sso file. The file is then used to create the database link. To create the database link in Autonomous Database, or run the following command:

SQL> create directory dblink_wallet as ‘wallet’;

7. Fetch the cwallet.sso file of the target database from Object Storage to the source database wallet directory with the following command:

BEGIN

  DBMS_CLOUD.GET_OBJECT(

  credential_name => ‘DEF_CRED_NAME’,

  object_uri => ‘https://objectstorage.ap-sydney-1.oraclecloud.com/p/SE4s6s5RtHA-F8GkIOYu71e5xSIl4Ek_rUTem90yb07rCd1Ya55213sdSkFicnRSN1/n/id03wiznwkrx/b/sam-sydney/o/cwallet.sso’,

  directory_name => ‘DBLINK_WALLET’

  );

  END;

  /

8. Create the database link credential in the source database.

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

  credential_name => ‘DB_LINK_CRED’,

  username => ‘ADMIN’,

  password => ‘<password>’

  );

  END;

  /</password>

9. Create the database link in the source database to point to the target database using the public IP of the VM that we set up.

BEGIN

  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(

  db_link_name => ‘DBLINK’,

  hostname => ‘152.67.101.36’, — public IP of compute

  port => ‘1522’,

  service_name => ‘ja2z6moea6xwh3v_atpsyd3_high.adb.oraclecloud.com’, — service name of target database

  ssl_server_cert_dn => ‘CN=adb.ap-sydney-1.oraclecloud.com, OU=Oracle ADB SYDNEY, O=Oracle Corporation, L=Redwood City, ST=California, C=US’, — ssl_server_cert_db of target database

  credential_name => ‘DB_LINK_CRED’,  — database link credential that we created at step 8

  directory_name => ‘DBLINK_WALLET’   — database link wallet directory that we created at step 6 and 7

  );

  END;

  /

Summary

While the Autonomous Database link only supports a target database with a public IP address for now, this is the simplest workaround that you can implement to connect Autonomous Database to your on-premises database through FastConnect or IPSec VPN.