Creating Database Links from Autonomous Database to Databases with Private Endpoints

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:

• Both the ADB-S instance and the target database are in the same Oracle Cloud Infrastructure (OCI) VCN.
• The ADB-S instance and the target database are in different OCI VCNs but the VCNs are peered.
• The target database is an on-premises database that is on a private network and connected to the ADB-S instance’s OCI VCN using FastConnect or VPN.

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:

1. Create a Database Link between Two ADB-S Instances (both on a private endpoint)
2. Create a Database Link from an ADB-S Instance to a DBCS Instance Without a Wallet (both on a private endpoint)

1. Create a Database Link between Two ADB-S Instances (both on a private endpoint)

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:

• Ingress and egress rules play a crucial role when it comes to allowing or blocking certain traffic within a VCN. Therefore, as mentioned in our doc, we need to make sure the following ingress and egress rules are defined when creating a database link to a private endpoint:
  • An egress rule in the source database's subnet security list or network security group such that the traffic over TCP is allowed to the target database's IP address and port number. For example:
    
    The first egress rule (highlighted in red) is to be able to access the target database over a database link. The second egress rule is just to be able to connect to my source database from my VM (both my VM and source database are in the same OCI VCN). The destination port range for this second rule includes the port 1521 as well because we'll be using TLS authentication when connecting from our VM to the source database (which uses the port 1521 and doesn't require downloading a wallet).
     
  • An ingress rule in the target database's subnet security list or network security group such that the traffic over TCP is allowed from the source database IP address to the destination port. For example:
    
    The ingress rule highlight in red allows connections from the CIDR range that belongs to the VCN where our source database and VM resides.
     
• This feature is not enabled by default. If you want to create database links to the targets that are on a private endpoint, please file a Service Request at Oracle Support and request it to be enabled for your database. Update 06/07/22: This feature is now enabled by default in all commercial regions; hence, you do not need to file an SR to get it enabled anymore.

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).

1. After following Nilay’s post to set up VCN peering, we should now be able to connect to ctuzlatarget from the VM I have in my VCN in Ashburn. 
   
   Note: If your source and target databases are located in the same VCN, you don't have to worry about VCN peering. All you need to do is to define the right ingress and egress rules for the source and target databases as we covered earlier.
   
   Let’s confirm the VCN peering was successful:

   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 
   ________ 
   X        
   

   Please 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!
    
2. We are now ready to create our database link. Assuming the target database wallet is already available in the Object Storage, we will first connect to our source ADB-S and create a directory to store the target database wallet:

   [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.

    
3. Next step is to pull the target database’s wallet from Object Storage into the directory we just 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.

    
4. In the source ADB-S instance, we need to create credentials to access the target database. The username and password we specify in the DBMS_CLOUD.CREATE_CREDENTIAL procedure are the credentials for the target database that we will use to create the database link. We just need to make sure the username consists of all uppercase letters:

   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.

    
5. Now let’s create our database link! Please note the new parameter called private _target which is set to TRUE. This parameter basically tells the database that we are creating a database link to a target database that is on a private endpoint and that the hostname specified here needs to be resolved within our source database’s VCN. Even though we are working with two different VCNs in different regions, DNS resolution still works as if there is only one single VCN thanks to the VCN peering:

   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.

    
6. As the final step, let’s test the database link we just created:

   SQL> select * from dual@PEDBLINK;
   
   DUMMY 
   ________ 
   X   

2. Create a Database Link from an ADB-S Instance to a DBCS Instance Without a Wallet (both on a private endpoint)

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:

• Even though both of my databases are in the same VCN, ingress and egress rules are still very important. I'm not going to repeat details we covered above but we simply need to make sure we have the following:
  • An egress rule such that the traffic over TCP is allowed to the target database's IP address and port number.
  • An ingress rule such that the traffic over TCP is allowed from the source database IP address to the destination port.
     
• This feature is not enabled by default. To create database links to the targets that are on a private endpoint, please file a Service Request at Oracle Support and request it to be enabled for your database. Update 06/07/22: This feature is now enabled by default in all commercial regions; hence, you do not need to file an SR to get it enabled anymore.

Let's start!

1. In the source database (ADB-S instance), we need to create credentials to access the target database. The username and password we specify in the DBMS_CLOUD.CREATE_CREDENTIAL procedure are the credentials for the target database that we will use to create the database link. We just need to make sure the username consists of all uppercase letters:
    

   [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.

    
2. We are ready create our database link! Please note that we will not be using a wallet for the target database so we will pass NULL for ssl_server_cert_dn and directory_name parameters. The new parameter, private _target, is set to TRUE once again:

   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.

    
3. As our final step, we'll quickly test the database link that we just created:

   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.