Creating a Connection in Oracle Analytics Cloud to the Exadata Express Cloud Service

Introduction

This post details the steps required to create a connection to the Exadata Express Cloud Service for Data Visualization projects.

For additional details on Exadata Express, visit the Exadata Express Cloud Service page. For additional details on OAC, visit the Analytics Cloud page.

Topics

Before you Begin

Preparing Windows to Upload Files

Preparing OAC for an Exadata Express Connection

Preparing the DWCS Wallet

Creating an Exadata Express Connection

Testing the Exadata Express Connection

Versions Used

Oracle Analytics Cloud 17.4.5 and 18.1.3
Exadata Express 18.1.4
Windows 7 SP 1

Disclaimer

An Exadata Express 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 Exadata Express, create a new instance of OAC and ensure the issue remains.

The unsupported modifications are:

ICopying 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

Changing the default MTU Networking Parameter Size

Before you Begin

Note: If you have performed the steps followed in the companion post Connecting an Oracle Analytics Cloud RPD to the Exadata Express Cloud Service 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 Exadata Express Cloud Service download and unzip the client credentials folder that contains the wallet and network configuration files, as well as the Oracle Instant Client Linux-64 zip 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.

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

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

Run the PSCP command on in a CMD window to upload the files.

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

Make a directory for unzipped 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

Make a backup and edit the sqlnet.ora file. For details on using the vi editor refer to Using the vi Editor.

cd $U01_DOMAIN_HOME/bidata/components/core/serviceinstances/bootstrap/oracledb
cp sqlnet.ora sqlnet.ora.orig
vi sqlnet.ora #Comment out the line with the SSL_VERSION parameter

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

SQLNET.ENCRYPTION_CLIENT=accepted
SQLNET.CRYPTO_CHECKSUM_CLIENT=accepted
# SSL
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

Make a backup and copy the tnsnames.ora file.

cp tnsnames.ora tnsnames.ora.orig
cp /tmp/cc/tnsnames.ora .

Changing the Default MTU Size

The Maximum Transfer Unit (MTU) size may need to be changed to avoid an ORA-12152: TNS:Unable to send break message error. Skip this section if the Oracle Instant Client test above completed successfully.

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 the error.

ifconfig eth0 mtu 1500

Retest the instant client.

To change the 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

Preparing the DWCS Wallet

An Exadata Express 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 $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.

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

Creating an Exadata Express Connection

Topics

Viewing the Data Visualization Home Page

Creating an Exadata Express 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

Creating an Exadata Express Connection

From the DV Home Page, click Create > Connection.

Select the Oracle Data Warehouse Cloud Connection Type.

Enter a New Connection Name. The required entries for HostPort, and Service Name are obtained from the tnsnames.ora file in the unzipped client credentials folder. The Username and Password are obtained from the Exadata Express 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 Exadata Express 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 Exadata Express Cloud Service (Exadata Express) 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

Exadata Express Cloud Service

Analytics Cloud

Understanding Customization and Administration

Preparing to Connect to the Exadata Express Cloud Service

Connecting an Oracle Analytics Cloud RPD to the Exadata Express Cloud Service

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