In today’s world, there are many reasons why enterprises are heavily investing in multi-cloud strategies and looking for cloud vendors that interoperate with other clouds. The question of “which cloud to use” has evolved into “which clouds to use” in recent years. As you can imagine, implementing a multi-cloud strategy is more about having a seamless experience between the services of different vendors than using those services independently. This has been the goal for Autonomous Database as well, just like other Oracle Cloud Infrastructure (OCI) services. Accessing AWS resources via resource names, enabling the use of any AWS S3 compatible object store with Autonomous Database are some of the capabilities that we delivered as part of this effort. Today, I’d like to introduce you another cool feature that can be a crucial step in your multi-cloud story by letting you access Amazon Redshift, Snowflake and other non-Oracle databases from your Autonomous Database.

This might sound familiar to you if you remember my blog post from a while ago about accessing non-Oracle databases via Oracle Database Gateway (aka heterogenous connectivity). It was again a very similar and valid use-case; however, required many steps from setting up the gateway itself to creating a wallet and configuring the network. In other words, it works but it’s a bit of an involving process. As you have probably started guessing, we wanted to eliminate all those intermediary steps to provide a much simpler and smoother user experience to add on to our autonomous journey. Hence, we are now pleased to offer Oracle-managed heterogenous connectivity, where you get to create a database link to the databases like Amazon Redshift, Snowflake, MySQL, and PostgreSQL as if you are creating a database link to another Oracle database. No need to worry about how to set up a gateway, deal with wallets, or configure networking. Autonomous Database takes all this burden off your shoulders and does it for you!

Let’s take a look at how it works… In this blog post, I will create a database link to my Redshift instance and run a query over that database link. Before we get our hands dirty, let’s quickly review the prerequisites:

  • The target database must be accessible from the public internet and use a supported port number. We covered supported database types above, but you can find the full list along with their supported port numbers in our doc.
  • The target database must be configured to allow incoming SSL/TLS connections (Redshift allows both SSL and non-SSL connections by default so we are good to proceed with our example).

Step-1: Make Sure Redshift is Configured to Allow Public Access

Under “Actions” menu of your Redshift cluster on AWS console, we need to select “Modify publicly accessible setting” to make sure our Redshift cluster is publicly accessible:

We then navigate to the VPC security group that is assigned to our Redshift cluster and create an inbound rule to port 5439 from the source IP or CIDR range of our choice:

Step-2: Create a Database Link to your Redshift Instance

First, create a credential object with the credentials (username and password) of the target database:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'REDSHIFT_CRED',
    username => 'awsadmin',
    password => '************');
END;
/

PL/SQL procedure successfully completed.

Next, we will create a database link to our Redshift instance. As you can tell, it looks nearly identical to any other database link creation except for the gateway_params parameter for which we are passing ‘awsredshift‘ as our database type:

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'REDSHIFT_LINK',
          hostname => 'redshift-cluster-1.******.us-west-1.redshift.amazonaws.com',
          port => '5439',
          service_name => 'dev',          
          credential_name => 'REDSHIFT_CRED',          
          gateway_params => JSON_OBJECT('db_type' value 'awsredshift'),
          ssl_server_cert_dn => NULL);
END;
/

PL/SQL procedure successfully completed.

I told you, it’s as simple as creating a database link to another Oracle Database and doesn’t require you to know anything about database gateways at all. The database link creation might take longer (up to 30-40sec) than a traditional Oracle-to-Oracle database link due to internal gateway propagation and configuration, but it’s still negligible compared to amount of time and effort you save by letting us do all the intermediary steps for you.

Before we move onto the last step, there is one more thing I’d like to point out. If you have noticed, we passed NULL for ssl_server_cert_dn parameter. You might be wondering why this is the case since we use this parameter help us specify the SSL server certificate DN of the target database. Well, the good news is that Autonomous Database automatically configures and handles the secure connection to a target database and your connections are still end-to-end encrypted. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates; hence, we pass NULL for this parameter.

Step-3: Run a Query Over the Database Link

SELECT COUNT(*) FROM SALES@REDSHIFT_LINK;

COUNT(*)
-----------
172456

That’s it! We’ve just successfully created a database link to our Redshift instance and run a query over it. To summarize, using database gateways to access non-Oracle databases is not news. However, not having to know a single thing about the concept of database gateway and the ability to access other cloud databases such as Redshift and Snowflake in a couple of simple steps is the news we wanted to share with you as another milestone towards your multi-cloud journey. *** Spoiler Alert **** More to come!