Using Database Links with Autonomous Dedicated

Just a quick blog on a valuable but simple topic: Database Links and Autonomous Dedicated Databases (ADB). I have received multiple questions on database links lately, so I thought I would do a how-to and mention some things to look out for when using them with an ADB.

Read More: 1Z0-060: Upgrade to Oracle Database 12c

Many Oracle customers use database links between different Oracle databases on the same host or to read or transfer data to/from other hosts. Even though Autonomous is a locked-down system, database links are still a usable feature. 

Before I go into an example, there are a few key differences from what you may be used to on-prem:

1. Only TCP connections are supported currently.

2. Easy Connect syntax or the complete descriptor must be used, since there is no access to the local tnsnames.ora file for editing.

To refresh our memory, here is a typical syntax for creating a database link:

CREATE DATABASE LINK dblink CONNECT TO remote_user IDENTIFIED BY password USING 'remote_database';

I am going to create a database link in the ADB connecting to a database on OCI (VMDB) in a different subnet. This other machine could be anywhere as long as there is proper network connectivity with a private or public address. Many customers use VPNs or FastConnect with OCI to connect to their on-prem systems and this will work with database links also. 

CREATE DATABASE LINK dblinktest 

CONNECT TO jcowen IDENTIFIED by AdbTest2021##

USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db19cjjc.ggsub.adbvcn.oraclevcn.com)(PORT = 1521))

(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = dblinktest.ggsub.adbvcn.oraclevcn.com)))';

Now that we have our link created, we can test access with a simple query:

We can also create database links back into the Autonomous database. This connect string can be found in the tnsnames.ora that is located in the wallet zip file downloaded from the OCI console. 

CREATE DATABASE LINK JJCFLEETPDB.ADW.ORACLECLOUD.COM 

CONNECT TO jcowen identified by AdbTest2021##

USING '(DESCRIPTION=(CONNECT_TIMEOUT=120)(RETRY_COUNT=20)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=host-qr7it-scan.fleetsubnet.adbvcn.oraclevcn.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=JJCFLEETPDB_medium.adw.oraclecloud.com)))';

Now that we have connected to and from an Autonomous dedicated database there are a few key points to keep in mind:

1. OCI Egress Rules on the Autonomous Dedicated Infrastructure subnet must be open on port 1521 using a security list or Network Security Group (NSG).

2. If connecting on-prem to Autonomous, you will need to make sure the hostname/scan used is resolvable by your source server through a host file or DNS lookup. 

3. If connecting over VPN or FastConnect, work with your network team to ensure the on-prem firewall is not blocking or timing out 1521 traffic for long queries.  

4. If connecting to a public IP Address the Autonomous Dedicated Infrastructure subnet will need proper route rules and network features enabled in the VCN such as a NAT Gateway if using a private subnet (recommended).

5. GLOBAL_NAMES is set to TRUE in OCI database services which requires the dblink and the database to have the same name. This parameter is modifiable if needed. 

As you can see, except for a few networking areas to pay attention to, creating a database link in Autonomous Dedicated is not much different than what you are already doing. But you get all the added features of Autonomous Dedicated!

Source: oracle.com