Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse

Introduction

This post details the steps required in OAC version 5 (18.3+) to upload Autonomous Data Warehouse (ADW) wallets and to create ADW data, replication and console connections.

The wallets are used for Data Visualization (DV) data sets, DV Replications, Metadata (RPD) connection pools, and Thin Client Modeler (TCM) models.

Data connections are used for DV data sets.

Replication connections are used for DV replications.

Console connections are used for TCM models.

For OAC Version 4 ADW Connections refer to Connecting Oracle Analytics Cloud Version 4 to the Autonomous Data Warehouse.

For additional details on ADW, visit the Autonomous Data Warehouse page. For additional details on OAAC and OAC, visit the Analytics Cloud page.

The instructions found in Connect to Oracle Autonomous Data Warehouse Cloud are sufficient for experienced users. This post supplements the instructions with images of the screens used.

Please review the Known Issues section if you encounter difficulties with RPD Connection Pools and/or connections when using IP Networks.

Updates

      Latest Validation: September 18, 2018 using OAC  and OAAC 18.3.3-69

Last Updated October 3rd, 2018 with an additional workaround for the SQL*Net SSL Wallet Location issue that also works for OAAC.

Past Validations: None

Published on: September 20, 2018

Topics

Uploading ADW Wallets to OAC

Creating an ADW Data Connection

Creating an ADW Replication Connection

Creating an ADW Console Connection

Known Issues

Versions Used

Oracle Analytics Cloud 18.3.3-69
Oracle Autonomous Analytics – 18.3.3-69

Uploading ADW Wallets to OAC

To enable secure communication between Oracle Analytics Cloud and the Autonomous Data Warehouse, your administrator must upload trusted SSL certificates contained in an ADW wallet to Oracle Analytics Cloud.

Topics

Obtaining the Client Credentials Zip File from the Oracle Autonomous Data Warehouse Console

Unzipping the Client Credentials Wallet Zip File

Uploading the Extracted Wallet File

Known Issues

Obtaining the Client Credentials Zip File from the ADW Console

The ADW wallet file is one of the files provided in the Client Credentials zip file. Refer to Downloading Client Credentials (Wallet) for the steps.

Unzipping the Client Credentials Zip File

Use an unzip utility to unzip the Client Credentials Zip File e.g. wallet_DB201806211045.zip to a directory e.g. wallet_DB201806211045.

Uploading the Unzipped Wallet File for a Console Connection

The wallet uploaded via the DV Console GUI is used by the TCM and by RPD Connection Pools.

Login into the DV home page and click the Action Menu

Click Console

Click Service Administration

Click Manage Connections               

Click the Options icon > Upload Wallet to upload a wallet for the first time or Replace Wallet to update an existing wallet

Click Proceed without a virus scanner when warned


Click Browse and select the wallet file (cwallet.sso) in the unzipped ADW directory e.g. wallet_DB201806211045

Click OK.

You can now upload a RPD that contains either a TNS Connect Descriptor from the tnsnames.ora file in the unzipped ADW directory or a Externalize connection reference to the DV Console Connection . Refer to Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud for details on creating an ADW connection inside an RPD.

Creating an ADW Data Connection

A data connection is used to create DV data sets. With OAC versions 18.3+, an ADW wallet is uploaded for each ADW Data Connection.

Click Home to return to the DV home page

Click Create

Click Connection

Click the Oracle Autonomous Data Warehouse connection type

Complete the connection details and click Save.

The Host, Port, and Service Name are found in the tnsnames.ora file in the unzipped ADW directory.

The Client Credentials (wallet) is also in the unzipped ADW directory.

Use the Username e.g. Admin and Password specified when the ADW instance was created or a different Schema’s credentials.

You can now create DV data sets from the connection.

Creating an ADW Replication Connection

A replication connection is used to create DV replications. They are very similar to Data Connections.

Click Home to return to the DV home page

Click Create

Click Replication Connection

Click the Oracle Autonomous Data Warehouse connection type

Complete the connection details and click Save

The Host, Port, and Service Name are found in the tnsnames.ora file in the unzipped ADW directory.

The Client Credentials (wallet) is also in the unzipped ADW directory.

Use the Username e.g. Admin and Password specified when the ADW instance was created or a different Schema’s credentials.

You can now create DV replications from the connection.

Creating an ADW Console Connection

A console connection is used by the TCM to create models. It may also be used by RPD connection pools uses the Externalize connection option.

Click Console

Click Service Administration

Click Manage Connections.

             

Click Create

Complete the connection details, click Test and then click OK.

Choose Host, Port, and Service Name for Connect using.

The Host, Port, and Service Name are found in the tnsnames.ora file in the unzipped ADW directory.

Use the Username e.g. Admin and Password specified when the ADW instance was created or a different Schema’s credentials.

Check the Enable SSL box.

Click Test.

Click OK.

TCM models and RPD connection pools can now use the connection.

Known Issues

SQL*Net SSL Wallet Location

After uploading a wallet from the DV Console, the wallet location is not placed in the sqlnet.ora file. The file location in version 18.3.3-69 is /u01/data/domain/fmw/user_projects/domains/bi/bidata/components/core/serviceinstances/bootstrap/oracledb/sqlnet.ora

This affects the ability of the BI Server to connect to an ADW instance using an RPD connection.

The error messages in the log may be:

nQSError: 17014] Could not connect to Oracle database.

nQSError: 17001] Oracle Error code: 28759, message: ORA-28759: failure to open file

Two workarounds exist:

One workaround that works for both OAC and OAAC is to:

Create a DV ADW Console Connection

Change the connection pool in the RPD to use the Externalize connection option.

Another workaround (not possible with Autonomous OAC) is to:

SSH into the OAC VM

Backup the sqlnet.ora file

Edit the sqlnet.ora file and add the wallet location

# SSH or PuTTY into the OAC VM as the opc user
sudo su – oracle
# Set a variable to point to the sqlnet.ora directory
export TNS_ADMIN=< Your sqlnet.ora directory >
# Example below
# export TNS_ADMIN=/u01/data/domain/fmw/user_projects/domains/bi/bidata/components/core/serviceinstances/bootstrap/oracledb
# Change to the directory and backup the file
cd $TNS_ADMIN
cp sqlnet.ora sqlnet.ora.orig
# Edit the file and insert the wallet location
vi sqlnet.ora

An example wallet location text is as follows:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /bi/domain/fmw/user_projects/domains/bi/bidata/components/core/serviceinstances/bootstrap/oracledb/wallets/client)
)
)

Save and display the file.

cat sqlnet.ora

The file should look like this:

# Oracle advanced security encryption (not to be confused with SSL)
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=UNDETERMINED

The obis1 system component needs to be stopped and restarted.

# Set a variable for the domain home
export DOMAIN_HOME=< Your Domain Home >
# Example
# export DOMAIN_HOME=/u01/data/domain/fmw/user_projects/domains/bi
# Change to the BI Tools Bin directory
cd $DOMAIN_HOME/bitools/bin
# Stop the obis1 component
./stop.sh -i obis1
# Start the obis1 component
./start.sh -i obis1

The RPD connection may still not work if OAC has been provisioned on an IP Network. See the MTU Size issue below.

MTU Size

There is an open bug regarding creating a connection to ADW from an OAC Customer Managed instance created on an IP Network.

If using the TCM, you may see this error:

For RPD, Data, and Replication errors, SSH or PuTTY into the OAC VM and then find and view the obis1-diagnostic.log file.

If the error message is: [nQSError: 17001] Oracle Error code: 28864, message: ORA-28864: SSL connection closed gracefully then you have hit the bug.

The workaround is to change the Maximum Transfer Unit (MTU) size set for the Ethernet adaptor.

Use the ifconfig command to determine the name of the adaptor, e.g. eth0 and to change the MTU size to 1500:

# Find the name of the adaptor
ifconfig
# In this case it is eth0

eth0 Link encap:Ethernet HWaddr 02:5D:03:1E:C0:A2
inet addr:10.0.21.7 Bcast:10.0.21.255 Mask:255.255.255.0
inet6 addr: fe80::5d:3ff:fe1e:c0a2/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:8900 Metric:1
RX packets:7308336 errors:0 dropped:0 overruns:0 frame:0
TX packets:8274684 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1775730503 (1.6 GiB) TX bytes:5721839529 (5.3 GiB)

#Switch to the root user and change the setting to 1500
exit
sudo su –
ifconfig eth0 mtu 1500

Retry creating the connection. If successful, ensure the change to the MTU size persists across restarts:

echo ifconfig eth0 mtu 1500 up >>/etc/rc.local

Summary

This post detailed the steps required in OAC version 5 (18.3+) to upload Autonomous Data Warehouse (ADW) wallets and to create ADW data, replication and console ADW connections.

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

Analytics Cloud

Connect to Oracle Autonomous Data Warehouse Cloud

Connecting Oracle Analytics Cloud Version 4 to the Autonomous Data Warehouse

Downloading Client Credentials (Wallet)

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

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

Add Your Comment