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.
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:
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:
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!
Product Manager for Autonomous Database on Dedicated Exadata Infrastructure (ADB-D) and Autonomous Database on Exadata Cloud@Customer (ADB-ExaC@C)
Next Post