Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Associate Private Views to the VCN Private Resolvers with ATP Database and Oracle SQL Developer in Different VCNs

Introduction

In Oracle Cloud Infrastructure (OCI), associating private views to the Virtual Cloud Network (VCN) private resolver involves setting up Domain Name Server (DNS) configurations to ensure that specific DNS queries within your VCN resolve to private IP addresses according to your defined DNS records. This set up enhances the management and resolution of internal domain names.

A private view is a collection of DNS zones and records that can be associated with VCNs to resolve domain names internally.

In this tutorial, we will combine this with a use case to create an Oracle Autonomous Transaction Processing (ATP) database and perform basic network connectivity tests and tests with the Oracle SQL Developer application from within the same VCN, and from another VCN. This way we can see how the VCN private resolver works and how to configure the private views.

Objective

• Create a private view inside the Virtual Cloud Network (VCN) private resolver to allow components from one VCN to resolve DNS records from another VCN component.

• We will explain a use case where we will create an Oracle Autonomous Transaction Processing (ATP) database and perform some basic network connectivity tests and tests with the Oracle SQL Developer application from within the same VCN, and from another VCN.

• Use the wallet file (that uses the private access DNS name of the ATP database) and test the ATP database connection from the same and a different VCN. To allow connectivity from a different VCN using the private URL with a Fully Qualified Domain Name (FQDN), the private resolvers will need to be configured.

  The following image illustrates what we are going to achieve.

  

Task 1: Create the VCN Environments

To create an ATP database and test the connections across multiple VCNs we need to have multiple VCNs configured and because the networks inside the VCNs need to communicate with each other we need to attach the VCNs to a Dynamic Routing Gateway (DRG).

Creating the VCN environments is out of the scope for this tutorial. To create multiple VCNs with hub and spoke routing architecture, see Route Hub and Spoke VCN with pfSense Firewall in the Hub VCN .

In this tutorial, we will use:

• 1 x VCN with a public and private subnet.
• 1 x VCN with a private subnet.
• 1 x DRG that has both VCNs attached.
• 1 x Windows instance in the public subnet of the hub VCN.
• 1 x Windows instance in the private subnet of the spoke VCN.

The goal is to deploy the ATP database in the private subnet of the hub VCN and to test the connection from both Windows instances using Oracle SQL Developer.

Task 2: Create an Oracle Autonomous Transaction Processing Database with a Private Access Endpoint

• Log in to the OCI Console.

  1. Click the hamburger menu (≡) from the upper left corner.
  2. Click Databases.
  3. Click Autonomous Transaction Processing.

  

• Click Create Autonomous Database.

  

• Enter the following information.

  1. Display Name: Enter a display name.
  2. Database Name: Enter a database name.
  3. Choose a workload type: Select Transaction Processing.
  4. Scroll down.

  

  1. Choose the database type: Select Serverless.
  2. Scroll down.

  

  1. Password: Enter and confirm a password.
  2. Scroll down.

  

  1. Access type: Select Private endpoint access only.
  2. VCN: Select a VCN to deploy the ATP database.
  3. Subnet: Select a subnet to deploy the ATP database.
  4. Select Require mutual TLS (mTLS) authentication.
  5. Scroll down.

  

• Click Create Autonomous Database.

  

  1. Note that the status is AVAILABLE.
  2. Scroll down.

  

  1. Note down the Private IP address of the ATP database, we will use this to test the connection.
  2. Click Show and note down the Private Endpoint URL for testing.

  

  1. Note down the Private Endpoint URL, we will use this to test the connection.
  2. Scroll up.

  

• The following image illustrates what you have created so far.

  

Task 3: Download the Credentials Wallet

• Click Database connection.

  

  1. Wallet type: Select Instance Wallet.
  2. Click Download wallet to download the zip file with the wallet.
  3. Click Close.

  

• You will need this wallet file for testing or authentication.

Task 4: Use Oracle SQL Developer to Test the Database Connection from the Same VCN

Before we use the Oracle SQL Developer application to test the database connection, first we need to test the network connectivity towards the ATP database.

• We first use the Windows instance (client) that is hosted in the same VCN where the ATP database is deployed.

  • Open Command Prompt.
  • Run the telnet 172.16.0.64 1522 command. Because we are using a secured connection, the database will have to listen to TCP port 1522.
  • Click Enter.

  

• Note that the connection is accepted. So from a network level, we can confirm that the connection works using the IP address from the same VCN.

  

• Run the telnet zps0ilns.adb.eu-frankfurt-1.oraclecloud.com 1522 command to do the same test again with Private Endpoint URL.

  

• Note that the connection is accepted. So from a network level, we can confirm that the connection works using the Private Endpoint URL from the same VCN.

  

• Download and open Oracle SQL Developer. For more information, see Oracle SQL Developer.

  

  1. Right-click Oracle Connections.
  2. Click New Connection.

  

  1. Name: Enter a name.
  2. Username: Enter the username (admin) for the database.
  3. Password: Enter the password for the database.
  4. Connection type: Select Cloud Wallet.
  5. Click Browse to select the downloaded cloud wallet .zip file.
  6. Make sure the cloud wallet file is selected in the path.
  7. Service: Select ihatp_tpurgent.
  8. Click Save.

  

  1. Note that the new connection is saved.
  2. Click Test.

  

  1. Note that the status is Success.
  2. Click Connect.

  

  1. The Username is already specified.
  2. Enter the Password.
  3. Click OK.

  

• Note that the Oracle SQL Developer application is now connected to the ATP database.

  

• Click + sign to expand the ATP database connection.

  

• The following image illustrates what you have created so far.

  

Task 5: Use Oracle SQL Developer to Test the Database connection from a Different VCN

We have done the ATP database connection test with the Oracle SQL Developer in the same VCN, we will now test in a different VCN.

• Do the same network connectivity tests from a different VCN.

  • Open Command Prompt.

  • Run the telnet 172.16.0.64 1522 command. Because we are using a secured connection the database will have to listen to TCP port 1522.

  • Click Enter.

    

• Note that the connection is accepted. So from a network level, we can confirm that the connection works using the IP address from the other VCN.

  

• Run the telnet zps0ilns.adb.eu-frankfurt-1.oraclecloud.com 1522 command to do the same test again with Private Endpoint URL.

  

• Note that the connection is Failed. So from a network level, we can confirm that the connection does not work using the Private Endpoint URL from the other VCN.

  Connecting To [e-mail]...Could not open a connection to the host, on port 1522: Connection failed.
  

  

  1. Name: Enter a name.
  2. Username: Enter the username (admin) for the database.
  3. Password: Enter the password for the database.
  4. Connection type: Select Cloud Wallet.
  5. Click Browse to select the downloaded cloud wallet .zip file.
  6. Make sure the cloud wallet file is selected in the path.
  7. Service: Select ihatp_tpurgent.
  8. Click Save.

  

  1. Note that the new connection is saved.
  2. Click Test.

  

• The application is trying to connect.

  

• The connection fails with the following error:

  Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=zdt8muWqQs+N+gWfgIDCTg==)
  

  

• So the question arises:

  • Why can we successfully connect with the Oracle SQL Developer application using the cloud wallet in the same VCN but not in another VCN?

  • Why can we successfully test the network connection with telnet using the IP address of the ATP Database, but not the Private Endpoint URL?.

  By default, each VCN creates a private DNS resolver with DNS host entries for components inside that VCN. DNS host entries from other VCNs are not allowed by default.

  The connection wallet file is using the Private Endpoint URL to establish the connection and not the IP address. This means that when we tested the ATP database connection with the Oracle SQL Developer application from the same VCN we were able to successfully connect to the ATP database. But when we tested the ATP database connection with the Oracle SQL Developer application from another VCN, we were not able to connect to the ATP database. This also answers the question of why the telnet IP address connectivity test was successful on both VCNs and the Private Endpoint URL was not.

  To fix the issue we need to allow the associated private views to the VCN private resolver and allow both VCNs to resolve hosts or endpoints in the other VCN.

• The following image illustrates what you have created so far.

  

Task 6: Associated Private Views to the VCN Private Resolver

• Go to the OCI Console.

  1. Click the hamburger menu (≡) from the upper left corner.
  2. Click Networking.
  3. Click Virtual cloud networking.

  

• Click the VCN where the ATP database is hosted.

  

• Click the DNS resolver of the VCN.

  

• Scroll down.

  

• Click Manage private views.

  

  1. Select the other VCN to allow that previous selected VCN to resolve DNS hostnames in this VCN.
  2. Click Save changes.

  

• Note that the status of private resolver is UPDATING.

  

  1. After a few minutes, the status of private resolver should change to ACTIVE.

     Note: You might have to refresh the browser if the OCI Console does not update automatically.

  2. Note that the other VCN is added to the private views.
  3. Scroll up.

  

• Click Virtual cloud networks to return to the VCN page.

  

• Click the other VCN where the ATP database is not hosted.

  

• Click the DNS resolver of the VCN.

  

• Scroll down.

  

• Click Manage private views.

  

  1. Select the other VCN to allow that previous selected VCN to resolve DNS hostnames in this VCN.
  2. Click Save changes.

  

• Note that the status of private resolver is UPDATING.

  

  1. After few minutes, the status of private resolver will change to ACTIVE.
  2. Note that the other VCN is added to the private views.

  

• The following image illustrates what you have created so far.

  

Task 7: Use Oracle SQL Developer to Test the Database Connection from a Different VCN that has Private Views Associated to the VCN Private resolver

• Run the telnet zps0ilns.adb.eu-frankfurt-1.oraclecloud.com 1522 command to do the same test again with Private Endpoint URL.

  

• Note that the connection is now accepted. So from a network level, we can confirm that the connection works using the Private Endpoint URL from the other VCN.

  

• In Oracle SQL Developer application, click Test.

  

  1. Note that the status is Success.
  2. Click Connect.

  

  1. The username is already specified.
  2. Enter the password.
  3. Click OK.

  

• Note that the Oracle SQL Developer application is now connected to the ATP database.

  

• Click + sign to expand the ATP database connection.

  

• The following image illustrates what you have created so far.

  

Acknowledgments

• Author - Iwan Hoogendoorn (OCI Network Specialist)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Associate Private Views to the VCN Private Resolvers with ATP Database and Oracle SQL Developer in Different VCNs

G13385-01

August 2024

Copyright ©2024,

Oracle and/or its affiliates.