Setting Up Dremio On Google Cloud Platform With Oracle

Intro

In the next series of posts I want to cover several scenarios using Dremio and relational databases with the most popular cloud providers.

The first sample will be devoted to the Amazon Web Services and Oracle database. I will guide you through the process of creating virtual Linux machine and Oracle database instance, as well as Dremio installation and connecting it with the database.

Assumptions

In this tutorial we’ll use the free tier of AWS and Dremio Community Edition, so you should be able to follow free and easily. We assume you are familiar with the basics of Dremio, so if you haven’t already, we suggest you read Getting Oriented to Dremio.

We also suggest you ask questions on the Dremio Community site - we love to help.

Installing Dremio and Oracle Database on Google cloud

Intro

The fourth post in the serie about Dremio and most popular cloud providers covers Dremio and Oracle database deployment on Google cloud platform. It will guide you through the process of launching Linux virtual machines for the Dremio and Oracle installations, as well as connecting Dremio with the database.

Prerequisites

You must have Google account to follow this tutorial. If you don’t have one, open Google cloud start page, click account thumbnail in the top right corner, choose Add account and follow the guidelines. It will take some time to verify your identity and payment information. After signing up you can also use Google Cloud Platform Free Tier. Choose View My Console and follow the guidelines.

Launching Linux virtual machine for the Oracle database

Unlike other platforms, covered in previous posts, Google cloud does not provide Oracle database as a configurable service. In such case it should be installed manually within the virtual machine. So this tutorial shows how to launch Linux virtual machine using Compute service and install Oracle 12c database.

1. After signing in, Dashboard is opened. It looks like the following screenshot, except that resources list is empty for the first time.

On this screenshot project Dremio is already displayed, but first it should be added.

1. On the top bar, click the drop-down to bring up the project selection dialog

1. Choose Create project on the right side (+)

Enter the following values

• Project name Dremio
• Project ID oracle-dremio

Choose Create

Project Dremio will be created.

1. On the top bar of the Dashboard, open left drop-down menu and choose Compute Engine » VM instances

It takes a while to open a virtual machines list for the first time

Choose Create

1. Customize VM instance. Although suggested values are for the tutorial purposes, the minimum RAM and storage volumes should be enough for the Oracle database normal execution.

• Name oracle
• Zone us-central-a
• Machine type 2 vCPUs, 7.5 GB memory
• Boot disk Red Hat Enterprise Linux 7, 30 GB

Choose Create

1. Accessing Linux instance.

The most simple way to access an instance is to open a browser-based terminal session.

The next task will be completed using this tool.

Download Oracle database distribution

As I mentioned before, we need to install Oracle database manually. This tutorial shows how to download Oracle 12.2.1 database distribution, prepare environment and install software, as well as create database and insert test data.

1. Create an Oracle account, if you don’t have. After finishing registration sign out!

1. Open database download page

1. Database distributions list will be opened.

Accept Licence agreement and choose File 1 link. You will get the Sign in page, enter your account information and proceed.

1. Just after downloading begins, pause it and copy the download link.

1. Open SSH web-console and download installation file using curl and copied link.

1
curl -o oracle_distrib.zip http://download.oracle.com/otn/linux/oracle12c/122010/linuxx64_12201_database.zip?AuthParam=1509038589_c846c27089a37b95d0cd65a9dc741c40

Preparing Linux environment

Full information about installation process can be found in Oracle documentation. In this tutorial I have compiled minimum basic steps for the RHEL 7 OS.

1. Changing Kernel Parameter Values. For the detail information refer related Oracle documentation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Open **/etc/sysctl.conf** file

sudo vi /etc/sysctl.conf

# Add the following parameters and save.

kernel.shmmax = 4294967295
kernel.shmall = 2097152
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

1. Reload settings from the configuration files

1
sudo /sbin/sysctl --system

1. Check required packages.

For detail information refer related Oracle documentation

To get a list of installed packages execute the following command:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' \
binutils \
compat-libcap1 \
compat-libstdc++-33 \
glibc \
glibc-devel \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
libxcb \
libX11 \
libXau \
libXi \
libXtst \
make \
net-tools \
smartmontools \
sysstat

1. Based on the output of the previous step, install missing packages

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sudo yum install compat-libcap1 \
compat-libstdc++-33 \
glibc-devel \
ksh \
libaio \
libaio-devel \
libstdc++-devel \
libxcb \
libX11 \
libXau \
libXi \
libXtst \
net-tools \
smartmontools \
sysstat

1. Configure Users and Groups.

Oracle recommendations can be found here.

For this tutorial I’m using the required minimum - one user and one group.

1
2
3
sudo groupadd -g 54321 oracle
sudo useradd -u 54321 -g oracle oracle
sudo passwd oracle

1. Create ORACLE_HOME directory

1
2
3
sudo mkdir -p /u01/app/oracle/product/12.2.0/dbhome_1
sudo chown -R oracle:oracle /u01
sudo chmod -R 775 /u01

1. Open bash profile file for the user oracle and add new ORACLE_HOME, ORACLE_SID and PATH values. It is required for the proper execution of the sqlplus tool.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# Open file

sudo vi /home/oracle/.bash_profile

# Add variables

# .bash_profile
# Get the aliases and functions

if [ -f ~/.bashrc ]; then        . ~/.bashrc
fi

# User specific environment and startup programs

export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_SID=cdb1
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin
export PATH

1. Checking Resource Limits for Oracle Software Installation Users.

Detail information can be found in related Oracle documentation.

Set ranges for resource allocation to Oracle installation owners.

1
2
3
4
5
6
7
8
9
10
11
12
# Open configuration file

sudo vi /etc/security/limits.conf

# Enter the following values

oracle	soft	nofile	1024
oracle	hard	nofile	65536
oracle	soft	nproc	2047
oracle	hard	nproc	16384
oracle	soft	stack	10240
oracle	hard	stack	32768

1. Create swap file. Oracle prerequisite is to have swap file more than 150MB.

1
2
3
4
sudo dd if=/dev/zero of=/swapfile bs=10M count=70
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile

1. Make swap file at startup

1
2
3
4
5
6
7
# Open configuration file

sudo vi /etc/fstab

# Add parameter

/swapfile none swap sw 0 0

Install Oracle database software

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# Install **unzip** tool

sudo yum install unzip

# Unzip downloaded **Oracle** distribution

unzip oracle_distrib.zip

# Change unzipped distribution folder owner

sudo chown -R oracle:oracle database

# Change folder permissions (grant write permission only to the user oracle)

sudo chmod -R 775 database

# Move database folder to the **oracle** user directory

sudo mv database /home/oracle

# Login as **oracle** user

sudo su - oracle

1. This tutorial shows how to install Oracle software using response files, it is a convenient way when we want to use only command line tool.

For the details refer please to the related Oracle documentation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# Backup default response file

cp database/response/db_install.rsp database/response/db_install.rsp.bck

# Open db_install.rsp file

vi database/response/db_install.rsp

# Set the following parameters, it is assumed, that we only install software.

oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oracle
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=oracle
oracle.install.db.OSOPER_GROUP=oracle
oracle.install.db.OSBACKUPDBA_GROUP=oracle
oracle.install.db.OSDGDBA_GROUP=oracle
oracle.install.db.OSKMDBA_GROUP=oracle
oracle.install.db.OSRACDBA_GROUP=oracle

1. Run the installation

1
2
3
4
5
6
7
# Change current folder

cd /home/oracle/database

# Run installer

./runInstaller -silent -responseFile /home/oracle/database/response/db_install.rsp

1. After finishing the installation process you will be asked to run two scripts.

1
2
3
4
5
sudo su root

/u01/app/oraInventory/orainstRoot.sh   

/u01/app/oracle/product/12.2.0/dbhome_1/root.sh

Configure listener and create a database

1. Run Net Configuration Assistant in silent mode to configure and start an Oracle Net listener, using default response file

1
2
3
4
5
6
7
8
9
10
11
# Login as oracle

sudo su - oracle

# Change current folder

cd /home/oracle/database/response

# Run Net Configuration Assistant

netca -silent -responseFile /home/oracle/database/response/netca.rsp

Start the listener service:

1
lsnrctl start

1. Create database folders

1
2
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/flash_recovery_area

1. Create the database in silent mode using Database Configuration Assistance

1
2
3
4
5
6
7
8
9
10
11
12
13
14
dbca -silent \
       -createDatabase \
       -templateName General_Purpose.dbc \
       -gdbname cdb1 \
       -sid cdb1 \
       -characterSet AL32UTF8 \
       -sysPassword OraPasswd1 \
       -systemPassword OraPasswd1 \
       -createAsContainerDatabase true \
       -numberOfPDBs 1 \
       -pdbName pdb1 \
       -pdbAdminPassword OraPasswd1 \
       -databaseType MULTIPURPOSE \
       -automaticMemoryManagement false

1. Keep in mind, that database service is not configured to startup. After reboot, listener and database must be started.

1
2
3
4
5
lsnrctl start
sqlplus / as sysdba
startup;
alter session set container=pdb1;
alter database open;

Accessing Oracle database

Tutorial shows how to access database service using Oracle SQL Developer, it is a is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments.

First we need to allow access on TCP port 1521. It can be done by adding a new ingress Firewall rule, but before this let us add a Network tag oracle to the VM instance. This tag will be used in the firewall rule to set the target.

1. Activate browser-based Google** Cloud Shell by clicking >_ on the right of the top bar. Execute the following command

gcloud compute instances add-tags oracle –tags oracle

1. On the top bar, click the drop-down menu and choose VPC network -> Firewall rules.

Firewall rules list will be opened.

1. Choose Create Firewall Rule.

Enter the following values

• Name oracle-net-service
• Target tags oracle
• Source IP ranges 0.0.0.0/0
• Protocols and ports tcp:1521

Choose Create

1. Now it is possible to connect using SQL Developer.

Open SQL Developer and choose New connection.

1. New / Select Database Connection dialog will be opened.

Enter tehe required information:

• oracle-google as Connection Name
• sys as User Name
• OraPasswd1 as Password
• Public IP address **as **Hostname
• pdb1 as Service name. It establishes a connection with the pluggable database PDB1.

Choose Test to test a connection.

1. Choose Connect to save the connection and open new SQL worksheet.

Creating TEST table

Just for the learning purposes let us create simple table and insert a data. Run the following statements in new SQL Worksheet.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. Add new Oracle user DREMIO.

CREATE USER dremio IDENTIFIED BY pass;

GRANT DBA TO dremio;

-- 2. Create TEST table in DREMIO schema.

CREATE TABLE dremio.test
(test varchar(2000));

-- 3. Insert  data.

INSERT INTO dremio.test VALUES ('Hello world!');

COMMIT;

Now we have running one instance with the Oracle database, and the next step is to launch the second machine for the Dremio.

Launching Linux virtual machine*

1. On the top bar of the Dashboard, open left drop-down menu and choose Compute Engine » VM instances

Choose Create

1. Customize VM instance. The suggested values are for the tutorial purposes, but the minimum RAM and storage volumes should be enough for the Dremio normal execution.

• Name dremio
• Zone us-central-a
• Machine type 1 vCPU, 3.75 GB memory
• Boot disk Red Hat Enterprise Linux 7, 10 GB

Choose Create

1. Add Network tag, it will be used within the Firewall rule

Activate browser-based Google Cloud Shell by clicking >_ on the right of the top bar. Execute the following command:

1
gcloud compute instances add-tags dremio --tags dremio

Install Dremio

1. Before installing Dremio, we need to Install Java. This tutorial uses Java 8 OpenJDK.

Open browser-based SSH session and type the following command:

1
2
3
4
5
sudo yum install java-1.8.0-openjdk

# After installing Java, you can check the result.

java -version

1. Download Dremio rpm installation.

The Dremio download link can be copied here.

Execute the following command.

1
curl --output dremio-community-1.2.1.rpm https://download.dremio.com/community-server/1.2.1-201710030121530889-8e49316/dremio-community-1.2.1-201710030121530889_8e49316_1.noarch.rpm

1. Install Dremio.

sudo yum localinstall dremio-community-1.2.1.rpm

1. Start Dremio

sudo service dremio start

1. By default all incoming tcp traffic is blocked, therefore we need to add one more Firewall rule for the Dremio UI port 9047.

Choose Create Firewall Rule.

Enter the following values:

• Name dremio
• Target tags dremio
• Source IP ranges 0.0.0.0/0
• Protocols and ports tcp:9047

Choose Create

Accessing Oracle database using Dremio

1.Open Dremio on Linux instance Public IP

http://104.154.216.232:9047

After entering information on the first page you will see the main Dremio screen.

Accessing Oracle database

1. Choose Add Source » Oracle

Enter the connection parameters.

• Name google-cloud
• Internal IP as Hostname
• pdb1 as Service name. It establishes a connection with the pluggable database PDB1.
• Username dremio
• Password pass

Choose Save.

1. After finishing the process choose oracle-cloud under the Sources to get available Oracle schemas.

1. Open DREMIO schema and then click TEST table.

That is what we need - get data from the Oracle table using Dremio query.

Conclusion

In this tutorial we explored a common scenario, how to use Dremio and relational database within Google Cloud Platform. We showed the following steps:

• Launching Oracle database service within Linux virtual machine
• Launching Red Hat Enterprise Linux 7 virtual machine
• Running Dremio within RHEL 7 machine
• Establish connection between Dremio and Oracle