Creating database links to other Oracle databases and non-Oracle databases via Oracle Database Gateways has been supported in Autonomous Database on Shared Exadata Infrastructure (ADB-S) for quite some time. As you may remember, one of the prerequisites of creating a database link in ADB-S was to have a target database that is on a public IP or a public hostname. Well, not anymore! It's now possible to create database links to target databases that are on a private endpoint as well, which covers the following scenarios:
Update 04/19/22: ADB-S now supports creating database links to the target databases on private endpoints without a wallet! In other words, you do not have to configure TCPS authentication for the target database if it already doesn't have it. Second part of this blog post will focus on this with an example on how to create a database link to a DBCS instance over TCP (as opposed to TCPS).
Here's the outline of what we'll be doing:
In the first leg of this blog post, we are going to create a database link between two ADB-S instances that are both on a private endpoint but in different regions and VCNs. Before we get started, I’d like to point out a few important details about database links in ADB-S and this specific feature:
Back to our use-case… As I mentioned earlier, I have two ADB-S instances that are both on a private endpoint (i.e. no access through public internet). ctuzlasource is located in Ashburn while ctuzlatarget resides in Phoenix as you can see below:
In order for these ADB-S instances to communicate over a database link without routing the traffic over the internet, we need to make sure these two VCNs that are located in two different regions are peered. We are not going to cover the details of VCN peering here, but you can follow the steps in my colleague, Nilay’s blog post to set it up. His post also shows how to configure each VCN’s DNS resolver to forward one region’s ADB-S hostname across to the remote region where a listener translates it into a private IP address.
Now that we briefly talked about the prerequisites as well as different network configurations, we can get started with our database link creation. Once again, our goal is to create a database link from ctuzlasource (in Asbhurn) to ctuzlatarget (in Phoenix).
ctuzla-mac:~ ctuzla$ ssh -i sshIAD.key opc@129.213.84.20 [opc@ctuzlavm ~]$ [opc@ctuzlavm ~]$ cd /opt/oracle/sqlcl/bin/ [opc@ctuzlavm bin]$ ./sql ADMIN@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=l4u*****.adb.us-phoenix-1.oraclecloud.com))(connect_data=(service_name=ihs************_ctuzlatarget_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(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")))' SQLcl: Release 21.3 Production Copyright (c) 1982, 2021, Oracle. All rights reserved. Password? (**********?) ************ Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.1.0 SQL> select * from dual; DUMMY ________ XPlease also note that the connect string we used above uses TLS authentication on port 1521 (as opposed to mTLS on port 1522). Both our source and target databases are configured to allow TLS authentication so that we can just copy/paste the connect string from 'DB Connection' in ADB-S details page and simply connect without having to download a wallet. However, creating a database link still requires mTLS authentication, hence the wallet of the target database, but the TLS support for database links is on the way, so stay tuned!
[opc@ctuzlavm bin]$ ./sql ADMIN@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=btc*****.adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=mqs************_ctuzlasource_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(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")))' SQLcl: Release 21.3 Production Copyright (c) 1982, 2021, Oracle. All rights reserved. Password? (**********?) ************ Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.1.0 SQL> SQL> create directory wallet_dir as 'walletdir'; Directory WALLET_DIR created.
SQL> BEGIN 2 DBMS_CLOUD.GET_OBJECT( 3 credential_name => 'OBJ_STORE_CRED', 4 object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/ctuzlaDemo/b/ctuzlaBucket/o/cwallet.sso', 5 directory_name => 'WALLET_DIR'); 6 END; 7* / PL/SQL procedure successfully completed.
SQL> BEGIN 2 DBMS_CLOUD.CREATE_CREDENTIAL( 3 credential_name => 'DB_LINK_CRED', 4 username => 'ADMIN', 5 password => '*************'); 6 END; 7* / PL/SQL procedure successfully completed.
SQL> BEGIN 2 DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK( 3 db_link_name => 'PEDBLINK', 4 hostname => 'l4u*****.adb.us-phoenix-1.oraclecloud.com', 5 port => '1522', 6 service_name => 'ihs***********_ctuzlatarget_low.adb.oraclecloud.com', 7 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', 8 credential_name => 'DB_LINK_CRED', 9 directory_name => 'WALLET_DIR', 10 private_target => TRUE); 11 END; 12* / PL/SQL procedure successfully completed.
SQL> select * from dual@PEDBLINK; DUMMY ________ X
In this second leg of the blog, we have a slightly different use case. The databases that we have are both again on private endpoints; however, instead of two ADB-S instances, we now have one ADB-S instance and one DBCS instance that reside in the same VCN on OCI. You might remember my blog post from awhile ago that explains how to create a database link from ADB-S to DBCS. So, why are we repeating it again? There is a reason for it, as you may have noticed the update at the begining of this blog post. Creating database links to other cloud service or on-premises databases used to require configuring TCPS authentication on the target database, which takes some effort as we covered in my older blog post. Well, not anymore! ADB-S now supports creating database links to the target databases on private endpoints without having to configure TCPS authentication and this means you don't need a wallet for the target database anymore!
Before we get started with our example, I'd like to point out some details similar to what we did for the previous use case above:
Let's start!
[opc@ctuzlavm ~]$ sql /nolog SQLcl: Release 21.4 Production on Tue Apr 19 21:26:01 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. SQL> connect ADMIN@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=******.adb.us-phoenix-1.oraclecloud.com))(connect_data=(service_name=************_salesadb_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(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")))' Password? (**********?) ************ Connected. SQL> BEGIN 2 DBMS_CLOUD.CREATE_CREDENTIAL( 3 credential_name => 'SALES_CRED', 4 username => 'SALESADMIN', 5 password => '**********' 6 ); 7 END; 8* / PL/SQL procedure successfully completed.
SQL> BEGIN 2 DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK( 3 db_link_name => 'SALES_LINK', 4 hostname => 'salesdbcs.**********.ctuzlavcn.oraclevcn.com', 5 port => '1521', 6 service_name => 'salesdbcs.**********.ctuzlavcn.oraclevcn.com', 7 ssl_server_cert_dn => NULL, 8 credential_name => 'SALES_CRED', 9 directory_name => NULL, 10 private_target => TRUE); 11 END; 12* / PL/SQL procedure successfully completed.
SQL> select * from dual@SALES_LINK; DUMMY ________ X
To summarize, ADB-S now supports creating database links to the target databases that are on a private endpoint and if the target database is not another ADB-S instance or doesn't already have TCPS authentication configured, we now support creating a database link over TCP as well (i.e. no wallet needed!) . Depending on where the target database is located (same OCI VCN as the source, different VCN, or private on-premises network), different network configurations might be necessary such as VCN peering, FastConnect or VPN; however, the steps to create a database link remain almost identical whether your target database is on a public or a private endpoint. As we covered in the beginning of this post, if you want to use this feature, please file a Service Request at Oracle Support and request it to be enabled for your database. Last but not least, please check out the documentation for more details on database links in ADB-S.
Can is a Principal Product Manager for Oracle Autonomous Database (ADB-S) and has been with the company since 2014. Prior to joining the ADB-S team, he worked on the Oracle Multitenant and Oracle Query Optimizer teams. Can holds a MS (Computer Science) from Case Western Reserve University and a BS (Computer Engineering) from Bilkent University.