Oracle's Visual Builder includes a database that you can use for your applications.  If this database doesn't meet your needs, or you prefer to use another ATP database, you can easily configure your instance to use another database through a public subnet.  If your database needs to stay in a private subnet, Visual Builder doesn't currently let you connect to it directly, but you can use a load balancer to resolve this.

In this post we'll walk through the steps to setup a Load Balancer to make the connection to your private database.

Review with your Security Admin

Review this entire post with your Cloud Security Admin before proceeding!
If you're not familiar with configuring a Virtual Cloud Network or the terms used below, contact your cloud administrator for assistance.

Before we begin

  • You'll need an ATP database setup on a private subnet
  • Make sure the ports you want to connect through have been added to the security list for the private subnet.  For the examples I will use port 1522 and its set to be open from everywhere

Open port 1522

Get the Database IP address and Wallet

  • In your Cloud Console, go to the details page for your Database
  • Save the private IP address
  • Click the Database Connection button
    Click Database Connection
  • Click Download Wallet button
    Click download wallet
  • Enter a password
  • Click Download

For the examples I've named this wallet file "PrivateWallet.zip"

Verify that your Database is only reachable from the private subnet

  • Open the Cloud Shell

Open the Cloud Shell

  • Upload your wallet .zip file

Upload file to cloud shell

Try to connect on the public subnet

  • Open SQLcl without making a connection
    sql /nolog
  • Set cloudconfig to use your wallet
    set cloudconfig PrivateWallet.zip
  • Attempt to connect
    connect admin/NotMyPassword@privatedatabase_tp

The connection attempt should timeout and fail.

blaine_car@cloudshell:~ (us-ashburn-1)$ sql /nolog

SQLcl: Release 23.1 Production on Mon Jun 12 18:02:03 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> set cloudconfig PrivateWallet.zip
SQL> connect admin/NotMyPassword@privatedatabase_tp
  USER          = admin
  URL           = jdbc:oracle:thin:@blaineprivatenetwork_tp
  Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=/4maFXSXTIO0WDBwo8f/Qg==)

Switch your cloud shell to the private subnet

  • Expand the Network list
    Open network list
  • Select "Ephemeral Private Network Setup"
    Choose Ephemeral Private Network Setup
  • Choose the VCN used by your database
  • Choose the private subnet used by your database
  • Click the "Use as active network" button
    Setup private network
  • Try to Connect to the database again (you should still be in SQLcl in the Cloud Shell)
    connect admin/NotMyPassword@privatedatabase_tp
    You should now be connected to the database.  This verifies that your database is only accessible from the private subnet and your wallet is valid.
SQL> connect admin/NotMyPassword@privatedatabase_tp
Connected.
SQL>
  • Exit SQLcl
SQL> exit
blaine_car@cloudshell:~ (us-ashburn-1)$

If you were unable to make the connection, verify that your are using the same private subnet as your database and that the port is properly configured in your security list.

Create a Load Balancer

  • In your Cloud Console, go to Networking > Load balancers > Load balancer
  • Click the "Create load balancer" button
    Create Load Balancer
  • Add details section
    • Enter a Load balancer name or keep the default
      Add details name
    • In the Choose networking section
      • Select the VCN used by your database
      • Select a public subnet
    • Click Next
      Add details networking
  • Choose backends section
    • In the Specify health check policy section
      • Change the Protocol to TCP
      • Set the Port to the one you will use for your connection
    • Click Next
      Choose backends
  • Configure listener section
    • Enter a Listener name or keep the default
    • Select TCP
    • Set the Port to the one you will use for your connection
    • Click Next
      Configure listener
  • Manage logging section
    • For this example, I disable the logging.
      You may configure the logging options to whatever is best for your project.
    • Click Submit
      Manage logging

It will take a couple of minutes for the system to configure the Load Balancer. Wait until its status is Active. (Ignore the smart check warning for now).

Load Balancer Active

Add the backend

  • Under Resources, click Backend sets
  • Click on the Backend set that was created with the Load balancer
    Select Backend Set
  • Under Resources, click Backends
  • Click the "Add backends" button
    Click Add Backend
  • Select "IP addresses"
  • Enter the Private IP address for your database
  • Enter the Port you will use to connect to the database
  • Click the Add button
    Backend Details
  • Click the Close button in the Work request submitted window.
    Close Work Request Window

It will take a couple of minutes for the backend health check to complete.  Wait until it is OK (You may need to refresh the screen).
Backend Complete

In your Cloud Console, go to Networking > Load balancers > Load balancer 

Save the IP address for the Load Balancer you just created.

Copy load balancer IP

Make a wallet for the Load balancer

  • Make a copy of the PrivateWallet.zip file. I called my copy "LoadBalancerWallet.zip"
  • Open the newLoadBalancerWallet.zip file
  • Edit the tnsnames.ora file
  • Replace the private DB host value with the public IP for the Load Balancer

Old:

blaineprivatenetwork_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=tcd5xzoi.adb.us-ashburn-1.oraclecloud.com)

New:

blaineprivatenetwork_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=129.80.250.187)
  • Save and update the LoadBalancerWallet.zip file

Test the connection through the Load Balancer

  • Upload the LoadBalancerWallet.zip file to the Cloud Shell
    Upload file
  • If your SQLcl session is still active disconnect your session
SQL> disconnect
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.1.0
  • Expand the Network list
  • Select Public Network
    SelectPublicNetwork
  • In the Cloud Shell, open SQLcl without making a connection
    sql /nolog
  • Set cloudconfig to use your new wallet
    set cloudconfig LoadBalancerWallet.zip
  • Attempt to connect
    connect admin/NotMyPassword@privatedatabase_tp
    You should now be connected to the database.  This verifies that your load balancer is providing access to your database from the public subnet to the private subnet and your new wallet is valid.
blaine_car@cloudshell:~ (us-ashburn-1)$ sql /nolog

SQLcl: Release 23.1 Production on Mon Jun 12 18:11:32 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> set cloudconfig PrivateWallet.zip
SQL> connect admin/NotMyPassword@privatedatabase_tp
Connected.
SQL> 
  • Exit SQLcl
SQL> exit
blaine_car@cloudshell:~ (us-ashburn-1)$

Configure Visual Builder

  • Open the Details page for your Visual Builder Instance
  • Open the Service Homepage
    Open Visual Builder Service Homepage
  • Open the Settings page
    Visual Builder Settings
  • Switch to the Tenant Database tab
  • Click the "Use Different Database" button
    Use Different Database
  • Change the Connection Type to "Oracle Autonomous Transaction Processing Cloud Wallet"
  • Drag the LoadBalancerWallet.zip file into the Upload Wallet box
  • Enter the Wallet Password
  • Select your TNS connection from the TNS Name list
  • Enter a user with DBA privileges
  • Enter the password for the DBA user
  • Click Next
    Database Details
  • Once your Database connection is verified, click Finish
    Database Verified

Your Visual Builder instance should now be configured to use your database in the private subnet.

Setup Network Access Rules

The above examples use Access Rules with no restrictions.  It's a good idea to restrict access to only allow the minimum required systems.

For example, you could:

  • Add a Network Security Group (NSG) to your private ATP database with a rule that only allows Ingress from the CIDR block of the public Load Balancer.
  • Add a Network Security Group (NSG) to your public Load Balancer with a rule that only allows Ingress from the CIDR block of Visual Builder.
    • If you are on an OCI instance of Visual Builder, you can find the service IP address in the instance details screen.
      VB Service NAT gateway IP
    • Otherwise, contact Visual Builder support to obtain the CIDR block values for Visual Builder.

If you're not familiar with configuring a Network Security Group or the terms used above, contact your cloud administrator for assistance.

This document demonstrates how to allow an application running inside Oracle Cloud Infrastructure on a virtual machine (VM) in the same VCN to connect to your private Autonomous Database.

Connection Troubleshooting Tips

There are steps throughout the post that verify the connection as you progress through the sections.

If you have a connection issue try the following:

  • Verify you're using the correct IP address for the correct objects. (Load Balancer PUBLIC IP and Database PRIVATE IP)
  • Check your passwords.
  • Download a new wallet file.
  • Open the tnsnames.ora file inside the wallet zip. Make sure you're using the port inside that file for all of the port values throughout the post.
  • If you copy/paste into the Cloud Shell, make sure you use "paste as plain text". If you use a standard "paste" you might have hidden characters in the clipboard.
  • When you copy/paste values between the different screens, check to make sure you don't accidentally have an extra space at the front or back of the value.

Credits

Special thanks to Danny Ju for a lot of the information in this post.