Oracle Cloud Infrastructure Documentation

Harvest Oracle Database Hosted in OCI Public Subnet Using Private Endpoint

Oracle Database systems are protected with network security rules that restrict the network traffic to only authorized subnets and IPs. Therefore, you must create and configure a private endpoint so thatData Catalog can connect to the database system.

In this tutorial, you do the following:

  1. Create the policies needed to harvest from Oracle database systems using private endpoint.
  2. Obtain the Oracle database system access details.
  3. Create a private endpoint in Data Catalog.
  4. Attach the private endpoint to your data catalog.
  5. Create a data asset.
  6. Harvest the data asset.

For more information, see configuring a private network.

Before you Begin

To successfully perform this tutorial, you must have the following:

Set up the Resources Needed for this Tutorial

1. Create Access Policies for Network Resources

In this setup, you create a policy to allow you to perform all networking operations in any compartment in your tenancy.

Perform the following steps:

  1. Open the navigation menu and click Identity & Security. Under Identity, click Policies.
  2. Click Create Policy.
  3. In the Create Policy panel, enter a unique name for the policy. The name must be unique across all policies in your tenancy. You can't change the name later. For example, create-private-network-policy.
  4. Enter a Description such as Grant permissions to create a private network .
  5. In the Policy Builder section, move the slider to Show manual editor, and enter the policy rule. For example, for the data-catalog-users group, enter the following policy rule: 
    allow group data-catalog-users to manage virtual-network-family in tenancy
    Note

    This policy allows users in the data-catalog-users group to perform all network-related operations in any compartment in the tenancy.
  6. Click Create.
You have successfully created the policy to access networking resources.
2. Create a Virtual Cloud Network

A Virtual Cloud Network (VCN) is a virtual, private network that you set up in a single Oracle Cloud Infrastructure region. A VCN has a single, contiguous IPv4 CIDR block of your choice.

The allowable VCN size range is /16 to /30. Decide on the CIDR block before you create a VCN. You can't change the CIDR value later. For your reference, here's a CIDR Calculator.

To create a VCN, complete the following steps:

  1. Open the navigation menu, click Networking, and then click Virtual cloud networks.
  2. Click Create VCN.
  3. Enter a name to identify your VCN and select the compartment you have permission to work in. For example, Public_VCN_PE.
  4. Enter the CIDR block for the VCN. For this tutorial, you can enter 10.0.0.0/16.
  5. Select DNS RESOLUTION and enter a DNS label.
  6. Click Create VCN.

The VCN is created and the Virtual Cloud Networks Details page for the VCN is displayed.

By default, a route table, DHCP option, and security list are automatically created for the VCN.

3. Create a Subnet

Subnets are divisions you create in a VCN. Each subnet consists of a contiguous range of IP addresses that do not overlap with other subnets in the VCN.

Depending on, whether you want your subnet to have public IP addresses, you can create a public or private subnet.

Create a Public Subnet

Complete the following steps to create a public subnet:

  1. Click Create Subnet from the Virtual Cloud Networks Details page of the VCN you created in the previous step.
  2. Enter a name for the public subnet. For example, Public_Subnet_01.
  3. Retain the default Regional selection for SUBNET TYPE.
  4. Enter the CIDR block for the public subnet. For this tutorial, you can enter 10.0.0.0/28.
  5. Select the default ROUTE TABLE.
  6. Select Public Subnet for SUBNET ACCESS.
  7. Select Use DNS Hostnames in this Subnet for DNS RESOLUTION.
  8. Enter a DNS label.
  9. Select the default DHCP options and security lists.
  10. Click Create Subnet.
Create a Private Subnet

You create a private subnet when you don't want the resources created in the subnet to have public IP addresses. Complete the following steps to create a private subnet:

  1. Click Create Subnet from the Virtual Cloud Networks Details page of the VCN you created in the previous step.
  2. Enter a name for the private subnet. For example, Private_Subnet_01.
  3. Retain the default Regional selection for SUBNET TYPE.
  4. Enter the CIDR block for the private subnet. For this tutorial, you can enter 10.0.0.16/28.
  5. Select the default ROUTE TABLE.
  6. Select Private Subnet for SUBNET ACCESS.
  7. Select Use DNS Hostnames in this Subnet for DNS RESOLUTION.
  8. Enter a DNS label.
  9. Select the default DHCP options and security lists.
  10. Click Create Subnet.
4. Create a DB system in Oracle Database

Complete the following steps to create a DB system in Oracle database:

  1. Open the navigation menu, click Oracle Database, and then click Bare Metal, VM, and Exadata.
  2. In the page that appears, click Create DB System.
  3. In the Create DB System page, in the DB System Information section, enter the details as described in the following table:
    Field/SectionDescription
    Select a compartment Select your compartment. For example, Resources.
    Name your DB system Enter a name for your DB system. For example, DBSystem 202106151447.
    Select an availability domain Retain AD-1.
    Select a shape type Retain Virtual Machine.
    Select a shape Retain VM.Standard2.4.
    Configure the DB system In the section, enter the following details:
    • Total node count: Select 1.
    • Oracle Database software edition: Select Enterprise Edition High Performance.
    Choose storage management software Retain Oracle Grid Infrastructure.
    Configure storage Retain 256 for the Available storage (GB) field.
    Add SSH Keys Select Paste SSH Keys and paste the key in the box.
    Choose a license type Select Bring Your Own License (BYOL).
    Specify the network information In this section, enter the following details:
    • Virtual cloud network: Select the VCN that you created (Public_VCN_PE).
    • Client subnet: Select the public subnet that your created (Public_Subnet_01).
    • Hostname prefix: Enter orcl
    • Host domain name: Displays the domain name based on the VCN and public subnet details. (publicsubnet01.publicvcnpe.oraclevcn.com).
    • Host and domain URL: Displays the host and domain URL.
    • Private IP address: This field is optional. Enter a private IP address within the CIDR block (10.0.0.0/28).
  4. Click Next.
  5. In the Database Information section, enter the details as described in the following table:
    Field/SectionDescription
    Database name Enter a name for the database.
    Database unique name suffix This is an optional field.
    Database unique name Displays the unique name of the database.
    Database image Retain Oracle Database 19c.
    PDB name This is an optional field.
    Create administrator credentials In this section, enter a password to create the DB credentials.
  6. Click Create DB System.
5. Create Security Rules

When you create a VCN, a security list is created by default for the VCN. You can add more security rules to this default security list or create a security list to permit traffic in and out of your VCN. In this tutorial, you add security rules to the default security list.

Depending on the subnet that you created, you create security rules for the subnet.

Create Security Rules for the Public Subnet

Complete the following steps to create a security list with the required security rules:

  1. Open the navigation menu, click Networking, and then click Virtual cloud networks.
  2. Click the VCN that you created, to view the VCN details.
  3. Click Security Lists from the Virtual Cloud Networks Details page of the VCN that you created.
  4. Click Default Security List for Public_VCN_PE.
  5. Click Egress Rules and do the following:
    1. Click Add Egress Rules.
    2. Enter the CIDR of your public subnet. For this tutorial, enter 10.0.0.0/16.
    3. Select All Protocols for IP PROTOCOL.
    4. Click Add Egress Rules.
  6. Click Ingress Rules and do the following:
    1. Click Add Ingress Rules.
    2. Enter the CIDR of your public subnet. For this tutorial, enter 10.0.0.0/16.
    3. Select TCP for IP PROTOCOL.
    4. Enter 1521–1522 for DESTINATION PORT RANGE
    5. Click Add Ingress Rules.

The ingress rules are added in the security list of the public subnet.

Create Security Rules for the Private Subnet

Complete the following steps to create a security rule that allows traffic from the private subnet to the public subnet:

  1. Open the navigation menu, click Networking, and then click Virtual cloud networks.
  2. Click the VCN that you created, to view the VCN details.
  3. Click Security Lists from the Virtual Cloud Networks Details page of the VCN that you created.
  4. Click the Default Security List for Public_VCN_PE.
  5. Click Egress Rules and do the following:
    1. Click Add Egress Rules.
    2. Enter the CIDR of your private subnet. For this tutorial, enter 10.0.0.16/28.
    3. Select All Protocols for IP PROTOCOL.
    4. Click Add Egress Rules.
  6. Click Ingress Rules and do the following:
    1. Click Add Ingress Rules.
    2. Enter the CIDR of your private subnet. For this tutorial, enter 10.0.0.16/28.
    3. Select TCP for IP PROTOCOL.
    4. Enter 1521–1522 for DESTINATION PORT RANGE.
    5. Click Add Ingress Rules.

The ingress rules are added in the security list of the public subnet.

1. Create Access Policies

To configure Data Catalog to access the private network of a data source, you need access to networking and data catalog resources.

If you already have access to perform all Data Catalog and Networking operations in your required compartments, you can skip this step.

To create the policy needed to configure a private network in data catalog, perform the following steps:

  1. Open the navigation menu and click Identity & Security. Under Identity, click Policies.
  2. In the Policies page, click Create Policy.
  3. In the Create Policy panel, enter the following details:
    • Name: Enter a unique name for the policy. The name must be unique across all policies in your tenancy. You can't change the name later. For example, data-catalog-private-endpoint-policy.
    • Description: Enter a description, such as Grant permissions to create private endpoint.
    • Compartment: Select a compartment in which you want to create the policy.
    • Policy Builder: In this section, move the slider to Show manual editor, and enter the policy rule. For example, for the data-catalog-users group, enter the following policy rule:
      allow group data-catalog-users to manage data-catalog-private-endpoints in tenancy
      Note

      This policy allows users in the data-catalog-users group to perform all data catalog private endpoint operations in any compartment in the tenancy.
    • Select Create Another Policy and enter the following policy rule: 
      allow group data-catalog-users to manage virtual-network-family in tenancy
      Note

      This policy allows users in the data-catalog-users group to perform all network-related operations in any compartment in the tenancy.
  4. Click Create.
You have successfully created the policies to access the required resources for configuring a private network in Data Catalog.

2. Obtain Data Source Details

You need the private network and database connection information for the Oracle database that you want to harvest.

Obtain the following details for the on-premise Oracle database from your administrator:

  • For configuring the private network, you need the VCN and subnet name and the URL for the Oracle database.
  • For creating the data asset, you need the Oracle database host, port, and database service name or SID.
  • For adding a connection, you need the database login credentials.

3. Create a Private Endpoint

You create a Data Catalog private endpoint to configure the network access details for the Oracle database data sources you want to harvest.

Create a Private Endpoint Using Public Subnet
To create a private endpoint in Data Catalog using the public subnet of Oracle database, follow these steps:
  1. Open the navigation menu and click Analytics & AI. Under Data Lake, click Data Catalog.
  2. Click Private Endpoints.
  3. In the Private Endpoints page, click Create Private Endpoint.
  4. In the Create Private Endpoint panel that appears, do the following:
    1. For Create In Compartment, select the compartment in which you want to create the private endpoint. Ensure that you have permission to work in the compartment that you selected.
    2. In the NAME field, enter a name for the private endpoint. For example, PE_Public_Subnet.
    3. In the Configuration section, enter the following details:
      • For Choose a VCN, select the VCN that you created. (Public_VCN_PE)
      • For Subnet, select the public subnet that you created. (Public_Subnet_01)
      • In the EXTERNAL DNS ZONES TO RESOLVE, enter the public subnet DNS. You can copy the DNS domain name from the details page of the public subnet that you created. (publicsubnet01.publicvcnpe.oraclevcn.com)
        Note

        You can add multiple external DNS zones as comma-separated values.
    4. Click Create.
Create a Private Endpoint Using Private Subnet
To create a private endpoint in Data Catalog using a private subnet that's different from the public subnet of Oracle database, follow these steps:
Note

The private subnet must be in the same VCN as the Oracle database system.
  1. Open the navigation menu and click Analytics & AI. Under Data Lake, click Data Catalog.
  2. Click Private Endpoints.
  3. In the Private Endpoints page, click Create Private Endpoint.
  4. In the Create Private Endpoint panel that appears, do the following:
    1. For Create In Compartment, select the compartment in which you want to create the private endpoint. Ensure that you have permission to work in the compartment that you selected.
    2. In the NAME field, enter a name for the private endpoint. For example, PE_Public_Subnet.
    3. In the Configuration section, enter the following details:
      • For Choose a VCN, select the VCN that you created. (Public_VCN_PE)
      • For Subnet, select the public subnet that you created. (Private_Subnet_01)
      • In the EXTERNAL DNS ZONES TO RESOLVE, enter the public subnet DNS. You can copy the DNS domain name from the details page of the public subnet that you created. (publicsubnet01.publicvcnpe.oraclevcn.com)
        Note

        You can use the public subnet DNS for creating a private endpoint using private subnet. You can also add multiple external DNS zones as comma-separated values.
    4. Click Create.

4. Attach a Private Endpoint

You attach a private endpoint to a data catalog to allow data assets to be created for data sources available in the private network.

To attach a private endpoint to a data catalog, perform the following steps:

  1. Click Data Catalogs.
  2. Click the Actions menu for the data catalog where you want to attach the private endpoint and select Attach Private Endpoint.
  3. Select the private endpoint you created in the previous step and click Attach.

5. Create a Data Asset

You are now ready to register your Oracle Database system data source with Data Catalog as a data asset.

You can create a data asset by specifying the Oracle database private IP or the Oracle database FQDN.

Create a Data Asset Using the Private IP of Oracle database
  1. Click the data catalog instance to which you attached the private endpoint.
  2. In the Data Catalog Home tab, from the Quick Actions tile, click Create Data Asset.
  3. In the Create Data Asset panel that appears, do the following:
    1. In the Name field, enter a name for the data asset.
    2. In the Description field, enter a description.
    3. For the Type field, select Oracle Database.
    4. In the Host field, enter the private IP from the node of the DB system that you created. (10.0.0.3)
    5. In the Port field, enter 1521.
    6. In the Database field, enter the service name of the database system.
    7. Select the Use private endpoint check box.
    8. Click Create.
Create a Data Asset Using the FQDN of Oracle database
  1. Click the data catalog instance to which you attached the private endpoint.
  2. In the Data Catalog Home tab, from the Quick Actions tile, click Create Data Asset.
  3. In the Create Data Asset panel that appears, do the following:
    1. In the Name field, enter a name for the data asset.
    2. In the Description field, enter a description.
    3. For the Type field, select Oracle Database.
    4. In the Host field, enter the FQDN of the node of the DB system that you created.
    5. In the Port field, enter 1521.
    6. In the Database field, enter the service name of the database system.
    7. Select the Use private endpoint check box.
    8. Click Create.
6. Add a Connection

After creating the data asset, you add a connection for the data asset.

For Oracle database data source types, you can use secrets in Oracle Cloud Infrastructure Vault to store the password that you need to connect to the source using a connection. By using OCI Vault, you provide the OCID of the secret when specifying the connection details, so you don't have to enter the actual password when you create the data asset.

A vault is a container for keys and secrets. Secrets store credentials such as required passwords for connecting to data sources. You use an encryption key in a vault to encrypt and import secret contents to the vault. Secret contents are based64-encoded. Data Catalog uses the same key to retrieve and decrypt secrets while connecting a data asset to the data source. For more information about vault, key, and secret, see Overview of Vault. For information about copying the secret OCID, see View Secret Details.

To add a connection for the Oracle Database data asset, follow these steps:

  1. On the Home tab, click Data Assets.
  2. In the Data Assets list, select the Oracle Database data asset that you created.
  3. On the data asset details page, under Summary, in the Connections section, click Add Connection.
  4. In the Add Connection panel, enter the details as described in the following table:
    Field Description
    Name Enter a unique name for your connection.
    Description Enter a short description for your connection.
    Type Select JDBC.
    User Name Enter your Oracle Database user name
    Use Password Select this option to enter the password associated with your Oracle Database user name. When you select this option, the following field appears:
    • Password - Enter the password associated with your Oracle Database user name.
    Use Vault Secret OCID Select this option to enter the OCID of the secret that's created in OCI Vault for password associated with your Oracle Database user name. When you select this option, the following field appears:
    • Vault Secret OCID for Password - Enter the OCID of the secret that's created in OCI Vault for the password associated with your Oracle Database user name. For information about copying the secret OCID, see View Secret Details
    Enable TLS Select this check box to enable TLS for this connection.
    Make this the default connection for the data asset. Select this check box to make this connection the default connection for the data asset.
    Test Connection Click the button to test your connection.
  5. Click Add.

7. Harvest the Data Asset

You're now ready to harvest your Oracle Database data asset.

To harvest your Oracle Database data asset, perform the following steps:

  1. Click Harvest on the data asset details page for the data asset.
  2. The Select Connection page displays and the default connection is selected.
  3. Click Next.
  4. The Select Data Entities page displays. View and add all the data entities you want to harvest from the Available Oracle Schema section.
    1. Click the add icon for each data entity you want to include in the harvest job.
    2. Click Add All to select all the entities for harvesting.
    3. Use the Filter Oracle Schema box to find a data entity from the available data entities.
    4. Use the page navigation icons to browse all the data entities.
    5. Click the remove icon for any selected data entity that you want to remove from the harvest job.
    6. If you need to start over, click Remove All and then start over.
    After you have reviewed the data entities you want to harvest from the Selected Oracle Schema or Data Entities section, click Next.
  5. The Create Job page displays. In the Job Name field, enter a unique name to identify the harvest job.
  6. Optionally, enter a Description.
  7. Select Run job now and then click Create Job.
    The job to harvest your Oracle Database data asset is created successfully and the Jobs tab displays. To view job details, click the job name.