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

Deprecation

Recent enhancements to OAC have made this post obsolete.  The most current post is found Here.

Introduction

This post details the steps required to create a connection to the Autonomous Data Warehouse Cloud (ADWC) for Data Visualization projects.

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

Topics

Before you Begin

Preparing Windows to Upload Files

Preparing OAC for an ADWC Connection

Preparing the DWCS Wallet

Creating an ADWC Connection

Testing the ADWC Connection

Versions Used

Oracle Analytics Cloud 17.4.5,18.1.3,18.2.1 and 18.2.1-1473. 18.2.1-1473 Last validated on May 3, 2018
ADWC 18.1.1.0 and 18.1.4
Windows 7 SP 1

Disclaimer

An ADWC 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

Creating a DWCS directory and copying a wallet to it

Before you Begin

Note: If you have performed the steps followed in the companion post Connecting an Oracle Analytics Cloud RPD to the Autonomous Data Warehouse Cloud then proceed to the Preparing the DWCS Wallet 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 WindowsIf 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

Topics

Patching the OAC Instance if Necessary

Uploading the Client Credentials to OAC

Preparing the Wallet and Network Configuration Files

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. 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 and unzip the credentials. 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

Preparing the DWCS Wallet

An ADWC connection requires the client credentials wallet to be in a designated DWCS directory.

If needed, 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. Create the directory and copy the wallet to it.

export U01_DOMAIN_HOME=/u01/data/domain/fmw/user_projects/domains/bi
mkdir -p $U01_DOMAIN_HOME/bitmp/DWCS
cp /tmp/cc/cwallet.sso $U01_DOMAIN_HOME/bitmp/DWCS

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

Note: The OAC services should be running before issuing these commands.

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

Creating an ADWC Connection

Topics

Viewing the Data Visualization Home Page

Creating an ADWC Connection

Viewing the Data Visualization Home Page

Login to OAC with an administrative account and navigate to the Data Visualization (DV) Home Page. The URL is like this:

https://<OAC Public IP>/dv/ui for a non-idcs instance                                              https://<OAC Load Balancer Host>/dv/ui for an IDCS enabled instance

Creating an ADWC Connection

From the DV Home Page, click Create > Connection.

Select the Oracle Data Warehouse Cloud Connection Type. Refer to Creating Oracle Autonomous Data Warehouse Cloud Connections for details.

Enter a New Connection Name. The required entries for Host, Port, and Service Name are obtained from the tnsnames.ora file in the unzipped client credentials folder. The Username and Password are obtained from the ADWC service administrator. Note: Use the database service name and not the TNS net service name.

Clicking Save validates the connection information and creates the connection.

Testing the ADWC Connection

Testing the connection involves starting to create a data set and viewing the available schemas.

From the DV Home Page, click Create > Data Set.

Click the New Connection Name.

View the Schemas available..

Summary

This post detailed the steps required to create a connection to the Autonomous Data Warehouse Cloud (ADWC) for Data Visualization projects.

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

Connecting an Oracle Analytics Cloud RPD 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

Creating Oracle Autonomous Data Warehouse Cloud Connections

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

Add Your Comment