Oracle Analytics Server 5.9 - Silent Install

Oracle Analytics Server 5.9 is now out and is available via the software delivery site of Oracle. I just want to quickly take you through the installation steps using silent mode.

Before we proceed, there are some pre-requisites which needs to be met. 

• Database for hosting metadata schemas 
• RPM packages 
• Software Zip files
• OS user with required permissions
• JDK version

Let's get to it. I will outline high level steps here a more detailed steps were documented in my GitHub repo.

Step 1: Directories 

We need to host the Oracle Fusion Middleware and Oracle Analytics Server domain, Ideal way to this is in two different folders 

• OAS_HOME
• oracle_home - for FMW and OAS software 
• domain_home - for bi domain configuration

Step 2 : FMW installation

Below is the simple command to install the fusion middleware infrastructure using a response file. You can find the sample response files in the Git repository.

java -jar /u01/app/oas/softwares/fmw_12.2.1.4.0_infrastructure.jar -silent -responseFile /u01/app/oas/resposefiles/fmw.response

Step 3 : FMW Patch Installation

Move the patch zip file to [ORACLE_HOME]/OPatch and unzip it there. change into the newly created path folder for ex cd 30657796. To apply patch use the below command.

[ORACLE_HOME]\OPatch\opatch apply -silent

Step 4 : OAS Installation

Unzip the Oracle Analytics Server installer zip file (~3.5GB). Modify the oas.response file to the oracle_home path as per previous step.

java -jar /u01/app/oas/softwares/Oracle_Analytics_Server_Linux_5.9.0.jar -silent -responseFile /u01/app/oas/resposefiles/oas.response

Step 5 : Domain Configuration

Update the config.respose to include details of the domain_home, Database details (hostname,port, servicename and SYS details) and port information etc.

[ORACLE_HOME]/bi/bin/config.sh -silent -responseFile /u01/app/oas/resposefiles/config.response

Install VNC Server on Oracle Cloud Infrastructure - Compute VM's

 This will be a simple and straight guide to install vnc server on Oracle Linux on Oracle Cloud Infrastructure Compute VM's.

This post outlines the steps to install and enable tiger vnc on Oracle Linux 7.

Step 1 : Install the necessary System Group Lists 

yum group install -y "Server with GUI"

Step 2 : Install tigervnc Server

yum install -y tigervnc-server

Step 3 : Create a service file and edit for a User

cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver@\:1.service
vi /etc/systemd/system/vncserver@\:1.service

Once the file is open replace the <USER> with a OS linux user like opc/root or oracle and save the file

Step 4 : Enable and Start Vnc Server

systemctl daemon-reload
systemctl enable vncserver@\:1.service
systemctl start vncserver@\:1.service

Step 5 : Set VNC Password 

Login as the user with which we will use vnc server and use vncpasswd to set a new password.

vncpasswd

Step 6 : Open ports on Machine and Security Lists

firewall-cmd --permanent --zone=public --add-port=5901-5905/tcp
firewall-cmd --reload
firewall-cmd --permanent --zone=public --list-ports

In the Security List of your VCN, make sure to open ports 5901-5905 to access the vnc server from a vnc client like vncviewer etc.

Now from your client try the vnc server using <IP_ADDRESS>:<PORT> and Password as set in Step 5.

If you dont know on which port the vncserver is running, type vncserver it will display :1,:2 etc. For :1 the port is 5901, for :2 the port is 5902 and so on.

Add SWAP size while installing a Oracle Database 19c on Oracle Linux

 Below is the error message I received while I was trying to install Oracle Database 19c on Oracle Linux 7.

PRVF-7573 : Sufficient swap size is not available on node

As per my error on the installer screen I needed 16GB of SWAP space where as my system has just 4GB. So I need to add a swap file for the installation to continue. 

Below steps will outline the steps for the same.

dd if=/dev/sdb of=/tmp/swap01 bs=1K count=16M
chmod 600 /tmp/swap01
mkswap /tmp/swap01
swapon /tmp/swap01

Make sure the bs (*) count is equal to your required SWAP space. Also, the /dev/sdb has enough storage as needed

Oracle Autonomous Database : Delete files from DATA_PUMP_DIR

To delete a single file from DATA_PUMP_DIR, we need to first know the name of the file.

The below statement can be used to list down all the files avialable in the DATA_PUMP_DIR

select * from DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

Now, copy the filename which you want to delete and use below command to delete it

BEGIN
UTL_FILE.FREMOVE('DATA_PUMP_DIR','FILE_NAME');
COMMIT;
END:
/

There can be a case where you want to delete all the files from the DATA_PUMP_DIR or any other directory defined in Oracle Database / ADB. Use the below to delete all files in one shot.

BEGIN
FOR r IN (SELECT object_name FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'))
LOOP
UTL_FILE.FREMOVE ('DATA_PUMP_DIR',r.object_name );
END LOOP;
COMMIT;
END;
/

We can modify the select statement to filter for only the files to be deleted for example to meet a specific criteria like .log or .dmp etc.

Oracle Big Data SQL aka Cloud SQL

The post is intended to outline the Oracle Big Data SQL offering, more of an introduction to what it is and where can we find further information.

Introduction and terminology

Oracle Big Data SQL leverages Oracle cell smart-scan technology to fetch a large amount of data defined as an Oracle external table using Oracle Big Data SQL drivers (oracle_hdfs, oracle_hive, and for Big Data SQL (aka. BDSQL) 4.0, oracle_bigdata)

An Oracle query that involves such an external table triggers a describe call from an Oracle database foreground process to access metadata (from hadoop Hive Metastore / namenode) which includes granules (i.e., data blocks ) and location information. The granules are then batched and assigned to eligible multiple Parallel Queries Slaves, PQs (or a foreground process if a serialized query) which interface with Big Data SQL (or BDSQL) cells running in data nodes. There can be hundreds or thousands of granules involved in a single query. A query involving granules in all data nodes will be able to leverage all Big Data SQL cells running in data nodes, thus scale (i.e., parallelize) the query the best, and get the most benefits. After the data is fetched to the cell, the data can be filtered before sending back to the database, thus shortening the overall query elapsed time significantly.

Retries/Fallback

There are three replicas of each granule by default, each in different datanode. Each granule has three datanodes assigned, thus there will be three attempts to fetch each granule. If all of the three attempts (aka. retries) fail, the granule will have to fetched directly from the database (aka fallback mode), in which case, data will not be able to be filtered (impacting performance).

A small number of 'retries count' might not represent a problem. In any event, if the number keeps increasing, it indicates either the Big Data SQL cell memory is not large enough to handle the workload, or there are code issues / configuration issues.

Quarantines

With any internal error (i.e., ORA-600) occurs on any offload server thread, a quarantine is created in the Big Data SQL cell. The quarantine has the SQLID stored along with other attributes. Once a quarantine is created in a Big Data SQL cell, the same SQL (with the same SQLID) will not be allowed to be processed in that cell. The basic idea is that the same SQL will very likely cause the same internal error. Once there are three quarantines (within a certain time range), the Big Data SQL cell will not accept any further SQL from the database.

Each quarantine will cause an incident trace created in:/opt/oracle/bd_cell/log/diag/bdsql/cell/SYS*/incident/

Below is the working Architecture of Big Data SQL , which I will try to explain in later post.

Documentation 

          User Guide 

          Installation Guide

Installing unrar on Oracle Enterprise Linux, Red Hat etc.

unrar is not generally available in yum repositories of all the Linux platforms. Even though most people don't use or recommend RAR as a popular format for compression, there are still times where u might be in a situation where you need to extract a .RAR file.

I was quite in that situation today when one of colleague asked me to help extract a RAR file on Oracle Enterprise Linux.

I had to so quite some research to find out the steps by going through some blogs, community forums and some official repo sites. So recording them for others and my self.


1) Login to you Linux machine through SSH, make sure your'e root or has sudo access.

2) We have to download the rar libraries, use below command. Check the www.rarlab.com for latest releases.

wget https://www.rarlab.com/rar/rarlinux-x64-5.6.1.tar.gz

3) Extract the tar.gz file using below command.

tar -zxvf rarlinux-x64-5.6.1.tar.gz

4) Navigate into the extracted folder (rar)

cd rar

5) Check if the extracted files are working. Run the unrar just to make sure.

./unrar

You should an output similar to below.

6)  Now copy the files to /bin so that you can use them as default programs from anywhere.

cp rar unrar /bin

Once this is done you're good to use unrar command. Example command below.

unrar e filename.rar

Hope this helps. 

Creating connection from Oracle Analytics Cloud(OAC) to Autonomous DataWarehouse Cloud(ADWC)

Since the introduction of Autonomous services in Oracle cloud, there has been a lot of buzz around the Self-Driving, Self-Securing and Self-Repairing Database called Oracle Autonomous Data Warehouse Cloud service. I will try posting the features and other stuff about it at a later point, for now my concentration is on how to use the Data from this ADWC service and use it in my Business Intelligence tools, specifically Oracle Analytics Cloud.

Like any other database we would need certain details like Host name, Port, service name and user/password. For ADWC we would need an additional security measure in the format of a wallet file, so if you wish to connect to a ADWC this is must and should.

I have detailed the process on how to get these connection parameters along with creating a connection in OAC as a video. Hope you guys like it.