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.
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.
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.
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.
On this screenshot project Dremio is already displayed, but first it should be added.
Enter the following values
Choose Create
Project Dremio will be created.
It takes a while to open a virtual machines list for the first time
Choose Create
Choose Create
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.
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.
Accept Licence agreement and choose File 1 link. You will get the Sign in page, enter your account information and proceed.
1
curl -o oracle_distrib.zip http://download.oracle.com/otn/linux/oracle12c/122010/linuxx64_12201_database.zip?AuthParam=1509038589_c846c27089a37b95d0cd65a9dc741c40
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
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
sudo /sbin/sysctl --system
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
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
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
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
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
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
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
2
3
4
5
6
7
# Open configuration file
sudo vi /etc/fstab
# Add parameter
/swapfile none swap sw 0 0
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
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
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
2
3
4
5
sudo su root
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/12.2.0/dbhome_1/root.sh
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
2
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/flash_recovery_area
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
2
3
4
5
lsnrctl start
sqlplus / as sysdba
startup;
alter session set container=pdb1;
alter database open;
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.
gcloud compute instances add-tags oracle –tags oracle
Firewall rules list will be opened.
Enter the following values
Choose Create
Open SQL Developer and choose New connection.
Enter tehe required information:
Choose Test to test a connection.
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.
Choose Create
Choose Create
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
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
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
sudo yum localinstall dremio-community-1.2.1.rpm
sudo service dremio start
Choose Create Firewall Rule.
Enter the following values:
Choose Create
1.Open Dremio on Linux instance Public IP
After entering information on the first page you will see the main Dremio screen.
Accessing Oracle database
Enter the connection parameters.
Choose Save.
That is what we need - get data from the Oracle table using Dremio query.
In this tutorial we explored a common scenario, how to use Dremio and relational database within Google Cloud Platform. We showed the following steps: