Best Practices from Oracle Development's A‑Team

Deploying Oracle Data Integrator Marketplace in a Private Subnet with Autonomous Database


Last Validated December 14, 2020


The latest releases of Oracle Data Integrator (ODI) are now available on Oracle Cloud Marketplace.

From the Using Oracle Cloud Marketplace website:

"Oracle Cloud Marketplace is an online store—a one-stop shop—selling hundreds of business apps and professional services that complement your existing Oracle Cloud implementation." 

This offering includes a full instance of ODI with a pre-configured repository. This repository may optionally be deployed on Oracle's Autonomous Databases.

This post is a step-by-step guide to installing and configuring ODI Marketplace in a private subnet on Oracle Cloud Infrastructure (OCI) using an Autonomous Database for the pre-configured repository. It is a companion blog to Deploying Oracle Data Integrator Marketplace in a Public Subnet with Autonomous Database

The official documentation is Using Oracle Data Integrator on Oracle Cloud Marketplace


December 14, 2020 with ODI V12.

February 24, 2020 with


Before You Begin

Preparing the ODI Marketplace Stack

Applying the ODI Marketplace Stack

Additional Configuration

Validating the ODI Configuration

 Before You Begin

Deploying ODI from the Oracle Marketplace requires these items. Links to the relevant documentation are provided. Managing these items require Administrator privileges and are assumed to exist prior to beginning the steps in this post.

An Oracle Cloud Tenancy in Oracle Cloud Infrastructure (OCI) 

When an OCI account is created, Oracle creates a tenancy, which is a secure and isolated partition within Oracle Cloud Infrastructure where you can create, organize, and administer cloud resources.

A Tenancy User Account within an Identity Provider

A tenancy is federated with Oracle Identity Cloud Service (IDCS) as the identity provider. OCI's native Identity and Access Management (IAM) service may also be used as the identity provider. More advanced options like federating Active Directory are outside the scope of this post. For more information on federated users, see Managing Oracle Identity Cloud Service Users and Groups in the Oracle Cloud Infrastructure Console.

This post uses a federated IDCS user account named Lisa.Jones

A Group Membership within IDCS

The user account needs to belong to an IAM group. OCI privileges are granted at the IAM group level. For an IDCS user to be assigned to an IAM group, the user first needs to be associated with an IDCS Group. See the link above for additional IDCS group information. 

This post uses an IDCS group named dcarley_compartment_ODI_Group

A Group Membership within IAM

An IAM group is what receives permissions to act on OCI resources. For more information on IAM groups see Managing Groups 

This post uses an IAM group named dcarley_compartment_ODI_group

A Mapping of the IDCS Group to the IAM group

This mapping grants an IDCS user membership in an IAM group. For more information see Map an IDCS group to an IAM group

A Compartment to Contain ODI resources

Compartments are logical groupings of OCI resources used for organizing and managing permissions. For more information on compartments see Here.

This post uses a compartment named ODI_Compartment

Note: With the exception of using an existing Virtual Cloud Network, all ODI resources used or created in the provisioning of ODI marketplace must reside in the same compartment.

A Dynamic Group for ODI Instances

Dynamic Groups allow you to group Oracle Cloud Infrastructure computer instances as "principal" actors (similar to user groups). Permissions can then be granted to these groups to manage and use OCI resources. For more information on dynamic groups see Here.

This post uses a dynamic group named dcarley_ODI_dyn_grp

Dynamic Groups require Matching Rules that identify which instances belong to the group. A rule that gives membership to all instances in a compartment i.e. ODI_Compartment is written as:

ALL {instance.compartment.id = 'ocid1.compartment.oc1..aaaaaaaaashjhg2nim567cpqjjazrwtcuqgupj77jj5ur2k7obk2v7suss6a'}

Policies to Use and Manage ODI resources

Policies are used to grant permissions on ODI resources. For more information see Here and Here. These ODI resources include the Virtual Cloud Network (VCN), the Compute instance hosting the ODI application and the Database hosting the ODI repository.

A typical example noted in the OCI documentation is for users to "Have full permissions on all resources, but only in a specific compartment".

This post uses the above example with a policy created in the ODI Compartment named dcarley_compartment_ODI_policy.

The policy has two rules: 

  • Allow group dcarley_compartment_ODI_group to manage all-resources in COMPARTMENT ODI_Compartment

  • Allow dynamic-group dcarley_ODI_dyn_grp to manage all-resources in COMPARTMENT ODI_Compartment

These rules allow the creation of the ODI compute instance as well as the use or creation of a VCN and the use of an Autonomous Repository Database.

A Provisioned Autonomous Transaction Processing (ATP) Database

This post uses an ATP database for the ODI repository. This database needs to be provisioned prior to preparing the ODI Marketplace Stack described below.

The user who provisions ODI and who has the privileges described above can provision an ATP database in the compartment designated for ODI resources. For more information see Provision Autonomous Transaction Processing

Note: The ADB must be in the same compartment as ODI. Move the ADB if necessary or deploy ODI in the ADB compartment.

After these prerequisites are in place ODI can be provisioned from the Marketplace.

 Preparing the ODI Marketplace Stack

This section is performed by the example provisioning IDCS user Lisa.Jones. All the information required for the ODI provisioning is collected and stored in an OCI Resource Manager Stack. A stack is defined as a "collection of Oracle Cloud Infrastructure resources corresponding to a given Terraform configuration" For more information see Overview of Resource Manager.

It is important to note that creating the Stack does not create the ODI resources. The resources are created in the next section.

Log into the OCI Console

Log into the OCI Cloud Console using your credentials. This post uses IDCS credentials for Lisa.Jones. 

Select the OCI Region

Select the desired region from the drop-down on the menu bar.

Navigate to the OCI Marketplace

Click on the hamburger icon in the top left portion of the menu bar to expand the list of services. Click on Marketplace.

Search for and click on Oracle Data Integrator

Use Oracle for the Publisher and Data Integration for the Category. Then click on Oracle Data Integrator.

Launch the Stack

Select the compartment i.e. ODI_Compartment where your privileges are granted, check the Terms box and click Launch Stack.

Complete the Stack Dialogs

Optionally enter a NAME and DESCRIPTION for the stack. 

Click Next

General Settings

There are two choices for NETWORKING OPTIONS. You can have the Stack create a VCN for you or use an existing VCN. The Stack creates a VCN for the ODI instance in the same compartment specified for the Stack. If using an existing VCN in a different compartment, additional policy privileges may be required.

This post chooses to create a VCN.

There are three options for the ODI REPOSITORY LOCATION.

  1. Use an existing Repository created by an RCU utility.

  2. Create a new ODI Repository in an existing Autonomous database.

  3. Create a new ODI Repository in a new MySQL database embedded into the ODI compute instance.

This post chooses to create a new repository in an existing ATP database.

Optionally provide a PREFIX for all new resources.

Select the TARGET COMPARTMENT. This is the target compartment where your privileges are granted. If you are using an existing Autonomous Database, it needs to be in this compartment also.

Network Configuration

If using an existing VCN, choose the VCN COMPARTMENT, VCNSUBNET COMPARTMENT, and private SUBNET from the drop-downs. Do not check the ASSIGN PUBLIC IP ADDRESS box.

If creating a new VCN, accept the default or specify the VIRTUAL NETWORK CIDR notation and do not check the CREATE A PUBLIC ODI SUBNET box.

ODI Instance Settings

Accept the default for the ODI NODE SHAPE or choose a larger shape from the dropdown.

Generate or locate an SSH Key pair. The public key should have a .pub extension. To generate a pair run the ssh-keygen utility. Open the .pub file with a text editor and copy the entire text. Paste it into the SSH PUBLIC KEY box.

Select an ODI AVAILABILITY DOMAIN from the dropdown.

Enter an ODI VNC PASSWORD to use with ODI Studio.

New ODI Metadata Repository

For the New Repository in an Existing ATP option:

Select an AUTONOMOUS DATABASE INSTANCE from the dropdown.


Enter an ODI SUPERVISOR PASSWORD for the new repository.

Enter a SCHEMA PREFIX for the new ATP repository schemas.

Enter a SCHEMA PASSWORD for the new ATP repository schemas.

Click Next above to complete the Stack dialogs.

Review and Save the Stack

Verify your entries and click Save Changes.

 Applying the ODI Marketplace Stack

After the Stack has been created above it appears in the Resource Manager service in an Active state. 

Apply the Stack

To use and create the resources identified in the stack, the stack needs to be applied. This is done via a Resource Manager Job. To run the job, choose Apply from the Terraform Actions dropdown. Note: The apply job may start automatically.

When the Job succeeds, the ODI resources have been created and are soon ready to use. Note: Even though the stack is done, processes inside the ODI VM may run for up to 30 minutes creating the ODI repository and the Studio repository connection details.


Below is a sample diagram of the OCI topology after a successful deployment.

 Additional Configuration

Additional configuration may be necessary for the Service Gateway and for VNC access.

Service Gateway

Service Gateway is preferred over a NAT gateway for private access to the Oracle Services Network.

Navigate to Networking > Virtual Cloud Networks (VCN) in the Compartment where ODI is provisioned.

Click on the provisioned VCN.

Click on the <prefix>-app-subnet 

Click on the Route Table

If the Target Type is Service Gateway, no further action is necessary. 

Otherwise click the icon on the right of the route rule and click Edit

Use the Target Type drop-down to select Service Gateway

Use the Destination Service drop-down to select All <Region> Services in Oracle Service Network

Use the Target Service Gateway drop-down to select <Prefix>-service-gateway

Click Save Changes 

The Route Rule looks like this:

Egress Rule for VNC

The provisioned Bastion instance is used as stop-over on the way to the ODI Server. The VNC process is provisioned to listen on port 5901. The egress rule in the Bastion's subnet security list needs a rule allowing egress for port 5901 traffic.

Navigate to Networking > Virtual Cloud Networks (VCN) in the Compartment where ODI is provisioned

Click on the provisioned VCN

Click on the <prefix>-bastion-subnet

Click on the <prefix>-bastion-security-list

Click on Egress Rules on the left under Resources

If there is an egress rule allowing access to port 5901, no further action is necessary. 

Otherwise note the Destination CIDR block e.g. and click Add Egress Rule 

Leave Stateless unchecked

Leave Destination Type as CIDR 

Add the Destination CIDR with the value noted above

Leave IP Protocol as TCP 

Leave Source Port Range blank

Enter 5901 for Destination Port Range

Click Add Egress Rules

The Egress Rules look like this:

 Validating the ODI Configuration

This section describes connecting to the ODI instance, running ODI Studio and connecting to the pre-configured repository in ATP.

Connecting to the ODI instance VNC Server

Provisioning of the Linux compute instance hosting ODI includes a VNC server running on Port 5901. The best practice is to use an SSH tunnel and connect to it via the SSH port 22 on the bastion server and port 5901 on the ODI server. 

Opening the SSH Tunnel

An SSH tunnel can be created via the PuTTY utility on Windows or via the ssh command on Linux and Mac. This post uses the ssh command. An example is below:

ssh -I <Path to Private Key > -L 5901:<ODI Private IP>:5901 opc@<BASTION Public IP>

Running the above command opens an SSH session and creates the tunnel.

Connecting to the VNC Server 

The next step is to connect to the VNC server via a VNC viewer. An example viewer on Windows is TigerVNC. The Mac OS contains a VNC viewer which is used by this post. To use the MAC viewer:

  • Start the Finder application

  • From the menu bar, click Connect to Server from the Go dropdown

  • In the Server Address box, enter vnc://localhost:5901 and click Connect

  • Enter the VNC password supplied to the ODI Stack and click Connect

The VNC window appears. If it is the first time using it, click through the language and privacy settings.

Starting ODI Studio

To start ODI Studio, click on ODI Studio from the Programming tab in the Applications dropdown

If it is the first use, Click No for Confirm Import Preferences 

Connecting to the ATP Repository

From the ODI Studio home page, click on Connect to Repository.

If it is the first use, for the New Wallet Password dialog, check the box for the choice of secure wallet usage and click OK. This post uses a development-only method of not using a secure wallet.

The Oracle Data Integrator Login dialog has been pre-populated and uses the SUPERVISOR password supplied to the ODI Stack. Click OK.

The ODI Initialization Dialog may take a while the first time. When it finishes you are connected to the Repository and the Studio.


This post described provisioning and configuring ODI Marketplace in a private subnet on Oracle Cloud Infrastructure (OCI) using an Autonomous Database (ATP) for the pre-configured repository.

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


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha