Deploying Oracle Remote Data Connector in Tomcat for Oracle Analytics Cloud

Introduction

This post describes deploying Oracle Remote Data Connector (RDC) for Tomcat in a Compute instance running on Oracle Cloud Infrastructure (OCI) for use by Oracle Analytics Cloud (OAC).

RDC is used primarily in cases where data sources are not accessible using a public IP address.

It supplements the official RDC documentation found here and here with screen images and links to additional references.

It is a companion to other A-Team blogs found here.

A step-by-step example is provided for those desiring to perform a deployment. It uses a simple development method to validate basic connectivity.

The diagram below shows the components and accesses used.

 

 Before You Deploy

Prerequisites

Compute Instance on a Public Subnet
Security list for the VCN with ingress rules allowing access to ports 22, 80 and 1521
Apache and Tomcat configured and started on the compute instance.
Settings of unpackWARs=true and autoDeploy=true in Tomcat server.xml file
Client SSH /SCP Utilities
Private Key for SSH access to the compute instance
OAC Public key for use by RDC
Downloaded and Installed OAC Developer Client Tool
Downloaded RDC Web Archive from OTN
Oracle JDBC driver (provided with the Tomcat image used)

Authorizations

Administrator role in the Oracle cloud tenancy for provisioning compute instances and configuring networks..
Administrator role in OAC for replacing data models.
Connect privilege in the database instance accessed by RDC

About the Marketplace Compute Image with Tomcat

This post uses a compute instance provisioned from an OCI custom image in the Oracle Cloud Marketplace certified by Bitnami. This link here provides links to the image, instructions, and general Tomcat documentation. The provisioned instance provides a running Apache/Tomcat environment with the prerequisite settings and firewall rules.

Security list Ingress Rules Allowing Access to Ports 22, 80 and 1521

Refer here for detailed information. The simplest method is to update the default security list created with the Virtual Cloud Network (VCN).

Port 22 is for SSH access into the Tomcat instance

Port 80 is for HTTP access to RDC from OAC and the OAC Administration tool

Port 1521 is for database access from RDC

Client SSH /SCP Utilities

If using a Mac, the ssh and scp utilities should be available. If using Windows, PuTTY utilities are commonly used and can be downloaded from hereThis post uses PuTTY.

Private Key for SSH access to the Tomcat Instance  (.ppk fomat if using PuTTY)

The private key is associated with the public key used to provision the compute instance hosting Tomcat. It is required to use the SSH and SCP utilities.

The PuTTYgen utility converts the key into a PuTTY-format key (*.PPK) for use with PuTTY.

Apache and Tomcat Configured and Started on the Compute Instance

To verify the status of the services, SSH into the instance and run this command:

sudo /opt/bitnami/ctlscript.sh status 

      If necessary, start the services:

sudo /opt/bitnami/ctlscript.sh start

Settings of unpackWARs=true and autoDeploy=true in the Tomcat server.xml file

This deployment has the file in /opt/bitnami/apache-tomcat/conf. The relevant section in the server.xml file is:

OAC Public key for use by RDC

Download the OAC Public Key. RDC authenticates access using this key. Refer here for detailed instructions.

Downloaded and Installed OAC Developer Client Tool from OTN

The latest version can be downloaded from hereThis is also referred to as the OAC Administration Tool.

Downloaded RDC Web Archive (WAR) File from OTN

The latest version can be downloaded to a client workstation from here. If the download is a zip file, unzip the contents.

 Deploying RDC to the Tomcat Instance

This section describes deploying the RDC application, configuring the metadata security option, and validating the deployment.

All commands in the Tomcat instance are run as the root user.

Beginning the Deployment

This section describes uploading, copying, and deploying the RDC WAR file.

Uploading the WAR File

The WAR file is uploaded from a client workstation to the Tomcat instance using the secure copy utility and the Tomcat instance private key.

The pscp syntax is: pscp [options] source [source…] [user@]host:target. An example is below:

C:>pscp -i < Path to your Private Key file > < Path to your WAR file > opc@< Tomcat IP Address>:/tmp

Copying the WAR File

The WAR file is backed up and renamed to the application name required by RDC (obiee).

sudo su –
cd /tmp
cp < Your uploaded WAR file > < Your uploaded WAR file >.orig
mv < Your uploaded WAR file > obiee.war

Deploying the WAR file

The settings of unpackWARs=true and autoDeploy=true instruct Tomcat to unarchive the WAR file and deploy the web application. Additional details for deploying on a running Tomcat server are here. The Tomcat home in this example is /opt/bitnami/apache-tomcat

export TOMCAT_HOME=/opt/bitnami/apache-tomcat
cp /tmp/obiee.war $TOMCAT_HOME/webapps

Validating the Initial Deployment

The initial deployment of RDC is validated using a HTTP command. From a browser enter the URL:

http://< Tomcat IP Address >/obiee/javads?status

You should receive a HTTP Status 401 – Unauthorized response. If you receive a 404 – Not Found then RDC is not running.

Disabling Metadata Security

Disabling metadata security is required for RDC. Two options are presented. Both are performed by editing the shell script used to start Tomcat. This example uses the catalina.sh in the $TOMCAT_HOME/bin directory. Change to this directory, backup the script and use the vi editor.

cd $TOMCAT_HOME/bin
cp catalina.sh catalina.sh.preRDC
vi catalina.sh

Setting the JAVA_OPTS Variable

After line 1, insert the following then save and exit the editor.

— Or — Setting the DISABLE_RDC_METADATA_SECURITY Variable

After line 1, insert the following then save and exit the editor.

# the following lines added for RDC
export DISABLE_RDC_METADATA_SECURITY=1
echo export DISABLE_RDC_METADATA_SECURITY=1

Restarting Tomcat

Restarting Tomcat enables the metadata security changes. Run the following.

/opt/bitnami/ctlscript.sh restart tomcat

Revalidating the RDC Deployment

Revalidating for the metadata changes uses a HTTP command. From a browser enter the URL:

http://< Tomcat IP Address >/obiee/javads?status

You should now receive an XML response similar to the following:

Configuring the OAC Public Key in RDC

The prerequisite downloaded key is uploaded to the Tomcat instance and placed in the appropriate directory.

Uploading the OAC Public Key

Using an SCP utility, upload the key to the Tomcat instance. The example below uses PSCP.

pscp -i < Path to your Private Key file > < Path to the public key > opc@< Tomcat IP Address>:/tmp

Placing the OAC Public Key

Copy the key to the directory created by the deployment of RDC.

cp /tmp/oracle_bics_rdc.pem /opt/bitnami/apache-tomcat/webapps/obiee/rdc_keys

Configuring an RDC Database Connection for an OAC Data Model

This section is for use with an OAC Data Model connection. If using DV RDC Direct Connections, proceed to Restarting Tomcat below

Configuring an RDC Database Connection is performed by editing two configuration files, server.xml and context.xml, then restarting Tomcat.

Backup the Configuration Files

Backup the files using:

cd $TOMCAT_HOME/conf
cp server.xml server.xml.preRDC
cp context.xml context.xml.preRDC

Edit the Configuration Files

Use the vi editor to edit the files.

server.xml

Replace the GlobalNamingResources element with:

context.xml

Add the ResourceLink element within the Context element:

Restarting Tomcat

Restarting Tomcat enables the database connection and public key changes. Run the following.

/opt/bitnami/ctlscript.sh restart tomcat

 Configuring OAC to Use RDC

Connecting Directly to RDC using OAC 105.2+

Beginning with this version, direct data connections are possible using RDC without an RPD. Refer here and here for detailed instructions.

Configuring the Data Model with the OAC Developer Client Tool

To use RDC with an RPD Data Model, first load the Java Data Sources. Then open the RPD and enter the RDC details in a new or modified connection pool.

Loading the RDC Java Data Sources

Start the the OAC Developer Client tool. Before opening an RPD:

In the File menu, select Load Java Datasources.
In the Connect to Java Datasource Server dialog:

Specify the Hostname and the Port where Remote Data Connector is running.
Enter the Tomcat Administrator Username and Password.
Leave the SSL box unchecked.
Click OK.

Open the RPD

From the File menu, Open the RPD in Offline or In the Cloud mode.

Creating or Modifying a Physical Database Connection Pool

A connection pool is required for the physical database defined in RDC . In the connection pool dialog,

On the General tab:

Enter a meaningful Name
Choose JDBC/JNDI for the Call Interface
Enter the RDC URL for the Data Source Name. Enter the User Name and Password for the database defined in RDC.

The syntax of the Data Source Name is:

http://< Tomcat IP Address >:< Tomcat Port >/obiee/javads/myjdbcdatasource

On the Miscellaneous Tab, ensure

Required Cartridge Version is 12.1
Use SQL Over HTTP is true

Click OK.

Save and Close the RPD

From the File menu, Save the RPD and Check Consistency.

If the RPD is open In the Cloud mode:

From the File menu, click Cloud and Publish.

Replacing the Data Model in OAC

If the RPD was opened in offline mode above, replace it in OAC.

From the OAC Home page:

Click the  and Console

From Service Administration click Manage Snapshots

From the  on the right, click Replace Data Model.

Validating the RDC Data Model Connection

It may take a couple of minutes for a published or replaced data model to fully be synchronized. Run an analysis using a subject area associated with the RDC database connection pool in the RPD to validate the connection.

Summary

This post described deploying Oracle Remote Data Connector for Tomcat in a Compute instance running on Oracle Cloud Infrastructure for use by Oracle Analytics Cloud.

For more OAC, BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for OAC.

 

Add Your Comment