Connecting an Oracle Analytics Cloud RPD to the Autonomous Data Warehouse Cloud

Introduction

This post details the steps required to prepare an OAC instance for an RPD with an Autonomous Data Warehouse Cloud (ADWC) data source connection. It uses the RPD created with the steps in Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud.

For additional details about ADWC, visit the Autonomous Data Warehouse Cloud page. For additional details on OAC, visit the Analytics Cloud page.

Updates

Updated and Validated May 9, 2018 for OAC 18.2.1-1473 connecting to ADWC 18.1.4

Updated and Validated May 4, 2018 for OAC 18.2.1-1393 connecting to ADWC 18.1.4

Topics

Before you Begin

Preparing Windows to Upload Files

Preparing OAC for an ADWC Connection in the RPD

Uploading the RPD to OAC

Testing the RPD ADWC Data Source Connection in OAC

Versions Used

Oracle Analytics Cloud 17.4.5, 18.1.3, and 18.2.1
ADWC 18.1.1.0 for OAC 17.4.5 and 18.1. ADWC 18.1.4 for all OAC versions above
Windows 7 SP 1

Disclaimer

An RPD connection in OAC requires customizations that are not supported by Oracle Support. Refer to Understanding Customization and Administration for details.

Before contacting Oracle Support with an issue unrelated to ADWC, create a new instance of OAC and ensure the issue remains.

The unsupported modifications are:

Copying a wallet and the network configuration files to the $TNS_ADMIN directory used by OAC

Possibly changing the default MTU Networking Parameter Size

Before you Begin

Note: If you have performed the steps in the companion post Creating a Connection in Oracle Analytics Cloud to the Autonomous Data Warehouse Cloud then proceed to the Uploading the RPD to OAC section.

This post uses PuTTY and PSCP SSH applications on Windows to connect to the OAC. The PuTTY programs may be downloaded from https://www.putty.org/

For details on using these tools refer to Connecting to a Compute Node Using the PuTTY Program on Windows. If you are not using Windows substitute SCP for PSCP and SSH for PuTTY.

Topics

Obtaining the OAC Private Key and Administrator Credentials

Obtaining the OAC Public IP Address

Ensuring the OAC SSH Port is Accessible

Backing Up the OAC Instance

Obtaining the OAC Private Key and Administrator Credentials

Obtain the SSH private key file for the OAC instance from the OAC Instance Administrator and convert the private key file to the PuTTY .ppk format. SSH requires the usage of a public / private key pair associated with the instance. Refer to Accessing an Instance Using SSH for details.

 To do this:

Run the PuTTYgen program

Load the private key file obtained from the OAC instance administrator

Save the private key using the defaults SSH-2 RSA and 2048 for Number of bits. Save as type PuTTY Private Key Files (*.ppk).

Obtaining the OAC Public IP Address

Obtain the OAC instance public IP address from the Oracle Cloud My Services – OAC Instance Overview page. Refer to Viewing and Managing Services for details.

Ensuring the OAC SSH Port is Accessible

PuTTY uses port 22 for access. This port should be open in OAC via an access rule by default. For details on how to view the instance access rules, refer to Managing Access Rules.

Backing Up the OAC Instance

Backup the OAC Instance. For details refer to Backing Up a Service.

Preparing Windows to Upload Files

The steps described in the post Preparing to Connect to the Autonomous Data Warehouse Cloud  download the client credentials folder that contains the wallet and network configuration files.

Note: For OAC version 17.4.5 and higher, the Java JDK and the OCI libraries are pre-configured and do not need to be downloaded.

Preparing OAC for an ADWC Connection in the RPD

Topics

Patching the OAC Instance if Necessary

Uploading the Client Credentials to OAC

Preparing the Wallet and Network Configuration Files

Changing the Default MTU Size

Restarting the OAC Services

Patching the OAC Instance if Necessary

Ensure the OAC instance is at version 17.4.5 or higher to use the pre-configured Java JDK JCE policy files and OCI libraries.

Refer to Patching and Rolling Back for details.

Uploading the Client Credentials to OAC

in a CMD window, change to the PuTTY directory and run the PSCP command to upload the file.

cd C:\Program Files (x86)\PuTTY
pscp –i <Your OAC SSH Private Key> <Your Client Credentials Zip File> opc@<OAC Public IP>:/tmp

Note: If there is a passphrase for the private key, you are prompted for it.

Preparing the Wallet and Network Configuration Files

Start a SSH session in the OAC instance server using PuTTY, the OAC public IP address, and the OAC instance private key file (.ppk). Connect as the opc user. Note: You may receive a warning that access is provided read-only and to contact Oracle Support prior to making any changes. This is because changes may be undone when a patch is applied.

Connect as the oracle user using sudo:

sudo su – oracle

Create a variable for the BI Domain and stop the services. Note: The variable is used throughout the remainder of this post. If you log out and return, then recreate the variable.

export U01_DOMAIN_HOME=/u01/data/domain/fmw/user_projects/domains/bi

Preparing the Wallet File

Make a directory for the client credentials files, change to the directory, unzip the credentials and copy the wallet file.

mkdir /tmp/cc
cd /tmp/cc; unzip /tmp/<Your Client Credentials Zip File>
cp /tmp/cc/cwallet.sso $U01_DOMAIN_HOME/bidata/components/core/serviceinstances/bootstrap/oracledb/wallets/client

Copying the tnsnames.ora file

Make a backup and copy the tnsnames.ora file.

cd $U01_DOMAIN_HOME/bidata/components/core/serviceinstances/bootstrap/oracledb
cp tnsnames.ora tnsnames.ora.orig
cp /tmp/cc/tnsnames.ora .

Modifying the sqlnet.ora file

In versions 18.2.1+, the SSL_VERSION parameter is set to UNDETERMINED and does not need to be changed.

For versions prior to 18.2.1, make a backup and edit the sqlnet.ora file to comment out the line with the SSL_VERSION parameter . For details on using the vi editor refer to Using the vi Editor.

cp sqlnet.ora sqlnet.ora.orig
vi sqlnet.ora

Save and exit and the file should look something like this:

SQLNET.ENCRYPTION_CLIENT=accepted
SQLNET.CRYPTO_CHECKSUM_CLIENT=accepted
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /bi/domain/fmw/user_projects/domains/bi/bidata/components/core/serviceinstances/bootstrap/oracledb/wallets/client)))
#SSL_VERSION=1.0

Changing the Default MTU Size

The Maximum Transfer Unit (MTU) networking parameter size may need to be changed to avoid an ORA-12152: TNS:Unable to send break message error. Only change this parameter if the connection fails.

Obtain the name of the Ethernet adaptor and check the Maximum Transfer Unit (MTU) size.

ifconfig

The results look something like this:


eth0 Link encap:Ethernet HWaddr C6:B0:85:C2:26:F1
inet addr:10.16.125.190 Bcast:10.16.125.191 Mask:255.255.255.252
inet6 addr: fe80::c4b0:85ff:fec2:26f1/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:9000 Metric:1

If the MTU size is not 1500, change the size for the current session. Note: Other sizes besides 1500 may work, but they were not tested. This post found1500 succeeds and 9000 causes an error.

ifconfig eth0 mtu 1500

To change the MTU setting for all future sessions, switch to the root user and add a line to the /etc/rc.local file. Note: Check with your network administrator to ensure the change does not conflict with other networking operations.

# return to the opc user session
exit
# change to the root user and edit the file
sudo su –
# Add the following line
echo ifconfig eth0 mtu 1500 up >>/etc/rc.local
# switch back to opc
exit
# switch to the oracle user
sudo su – oracle

Restarting the OAC Services

Stop and Start the Services. Refer to Using Commands to Start, Stop, and View Status of Oracle BI EE Processes for details.

Note: Ensure the OAC services are running before issuing these commands.

$U01_DOMAIN_HOME/bitools/bin/stop.sh; $U01_DOMAIN_HOME/bitools/bin/start.sh

Uploading the RPD to OAC

Replace the data model in OAC with the new RPD.

Topics

Viewing the OAC Console

Creating a Snapshot

Replacing the Data Model File

Viewing the OAC Console

Login to OAC with an administrative account and navigate to the Console page. The URL is like this:

https://<OAC Public IP>/dv/ui/home.jsp?consoletab=snapshotsTab&pageid=console

Creating a Snapshot

Create a snapshot to save the current BI Components such as roles, models, and the catalog. From the Snapshots tab, click New Snapshot

Enter a Description and click OK.

The new Snapshot shows in the list after it is created..

Replacing the Data Model File

From the same Snapshots page, click Replace Data Model

Click Proceed without a virus scanner if prompted.

Choose the Data Model File to upload, enter the Password used to create the file on Windows and click OK

Note: After the data model is replaced, there may be a short delay until it is available.

Testing the RPD ADWC Data Source Connection in OAC

Create a new analysis to test the RPD ADWC Data Source Connection.

Connect to the OAC analytics home page. The URL should be

https://<OAC Public IP>/analytics

From the New menu choose Analysis

Select the subject area created in the RPD e.g. ADWC – CHANNELS

Expand a table e.g. CHANNELS

Double-Click a column to add it to the analysis

Click Results and the connection returns the rows.

Summary

This post detailed the steps required to prepare an OAC instance for an RPD with an Autonomous Data Warehouse Cloud (ADWC) data source connection.

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

References

Autonomous Data Warehouse Cloud

Analytics Cloud

Preparing to Connect to the Autonomous Data Warehouse Cloud

Understanding Customization and Administration

Creating a Connection in Oracle Analytics Cloud to the Autonomous Data Warehouse Cloud

Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud

PuTTY Downloads

Accessing an Instance Using SSH

Viewing and Managing Services

Managing Access Rules

Backing Up a Service

Patching and Rolling Back

Connecting to a Compute Node Using the PuTTY Program on Windows

Using Commands to Start, Stop, and View Status of Oracle BI EE Processes

Using the vi Editor

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

Add Your Comment