Last validation: September 30, 2020 with 12.2.1.4.0

Introduction

This post details the steps required to create connections in Oracle Data Integrator (ODI) Marketplace to Autonomous Databases on Dedicated Infrastructure (ADB-D) e.g. Autonomous Data Warehouse (ADW-D). This post uses ADW-D, but the method is also applicable to Autonomous Transaction Processing (ATP-D) databases.

For additional details about ADW-D, visit Using Oracle Autonomous Data Warehouse on Dedicated Exadata Infrastructure. For additional details about ODI, refer to Oracle Data Integrator Overview.

ODI Studio is assumed to be provisioned. For information on installing ODI visit Deploying Oracle Data Integrator Marketplace in a Public Subnet with Autonomous Database

Updates and Validations

Validations:

September 30, 2020 with 12.2.1.4.0

Topics

Preparing to Connect to ADW-D

Creating Data Servers

Preparing to Connect to ADW-D

Connecting to autonomous databases from ODI Marketplace requires the following prerequisites.

Credential File

Download the Credentials

Connect to the ODI host using VNC. Refer to the Deployment blog above for details.

Launch Firefox from the Applications>Favorites list.

Follow the steps in Downloading Autonomous Data Warehouse Credentials to obtain the client credentials compressed folder containing the wallet and network configuration files used by ODI to make the connections.

Unzip the Credentials File.

Ensure the zip file remains.

Dynamic Groups

Ensure a dynamic group exists that includes the ODI compute instance. This group is used by a policy rule to grant permissions.

Refer here for the documentation.

An example matching rule that includes all instances in the ODI compartment is:

instance.compartment.id = '<compartment_ocid>'

Compartment Policies

Ensure there are policy rules granting the dynamic group permissions in the compartment containing ADW.

Refer here for the documentation.

Example policy rules are:

Allow dynamic-group <dynamic group name> to USE autonomous-database-family in compartment <ADW compartment>

Allow dynamic-group <dynamic group name> to INSPECT compartments in compartment <ADW compartment>

VCN Peering

If ODI resides in a different VCN than the ADB-D, the VCN must be peered. Refer here for information on peering the VCNs.

Security Lists

ODI connects to an ADB-D using TCP ports specified in the tnsnames.ora file found in the download client credentials zip file. These ports need to be opened for RDG using an ingress rule in either a Security List or a Network Security Group (NSG). Refer here for more information on Security Lists and here for NSGs.

Route Tables

If ODI resides in a different VCN than the ADB-D, Route Tables and Rules need to route traffic between the VCNs. Refer here for information on provisioning route tables and rules.

Domain Name System (DNS)

If ODI resides in a different VCN than the ADB-D, a hybrid DNS solution must be provisioned so that ODI can resolve the ADB-D hostnames. Refer here for more information on setting up a hybrid DNS solution.

Creating Data Servers

Connect to ODI Studio. Navigate to the Oracle technology in the Physical Architecture > Technologies tab. Right-Click on the Oracle technology and click New Data Server.

In the Definition pane: 

Give the server a Name.

In the Connection section, enter the schema User and Password.

Dedicated ADBs differ from Shared ADBs in that they offer TCP services as well as the TCPS services. For more information refer to Predefined Database Service Names for Autonomous Data Warehouse Dedicated Databases

TCP services may be used without a wallet file e.g. cwallet.sso. This is useful when connecting with a client that does not support the use of wallets. These services are named like <ADB-D name>_low e.g. dcadwdprv_low

TCPS services require the wallet file. These are the service types found in Shared ADBs. These services are named like <ADB-D name>_low_tls e.g. dcadwdprv_low_tls

Creating a TCPS Data Server

Definition Pane

Credential Details Section

If your ADW-D is in the ODI compartment, click the Discover ADBs button. This checks the Use Credential File box and populates the Credential File location. Note: If the ADW-D was created after the ODI deployment, the Studio connects to the ADW and downloads the credential file.

If your ADW-D is not in the ODI compartment, click the Search icon and choose the Credential File you downloaded. 

Use the Dropdown on the right of the Connection Details box and choose the desired connection. An example TCPS Definition tab is below.

JDBC Pane

Properties Section

Open the sqlnet.ora file in the unzipped credential folder. Note if the SSL_SERVER_DN_MATCH parameter exists and if it is set to OFF, NO, or FALSE. All three values mean FALSE. 

If the parameter does not exist or the value is FALSE, change the oracle.net_sll_server_dn_match property to false as shown below.

Test the Connection

Click Test Connection which also saves the Data Server if necessary.

Click Test.

Creating a TCP Data Server

JDBC Pane

JDBC URL

Open the tnsnames.ora file in the unzipped credential folder. Copy a TCP connect descriptor. A connect descriptor begins with (DESCRIPTION

Paste the connect descriptor and replace the contents after the jdbc:oracle:thin:@ prefix as shown below.

Test the Connection

Click Test Connection which also saves the Data Server if necessary.

Click Test.

Summary

You are now able to proceed with the logical architectures, models, mappings, etc.

This post provided the steps required to create connections in Oracle Data Integrator Marketplace to an Autonomous Data Warehouse on dedicated infrastructure.

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley