Dremio Jekyll

Installing Dremio and Oracle Database on Microsoft Azure

Intro

The third post in the serie about Dremio and most popular cloud providers covers Dremio and Oracle database deployment on Microsoft Azure platform. It will guide you through the process of launching Linux virtual machine and Oracle database instances, as well as Dremio installation and connecting it with the database.

Prerequisites

You must have Microsoft account to follow this tutorial. If you don’t have one, you can use Free one month trial. Choose Start free and follow the guidelines. After signing up, open Azure portal.

Microsoft Azure provides powerful browser-based command line tool Cloud Shell, which is used in this tutorial. Click “>_”” button on the menu in the upper-right of the portal.

Azure command line tool

On first launch Cloud Shell prompts to create a resource group, storage account, and file share on your behalf. This is a one-time step and will be automatically attached for all sessions.

Generating SSH key pair

The first step is to generate a SSH keys to access virtual instances.

  1. Open Cloud Shell, wait for the initialization and type the following command.
1
ssh-keygen -t rsa -b 2048

If you select all default options, your keys will be stored in the ~/.ssh folder.

Selectin the default options

Launching Oracle Database virtual machine

After signing in to Azure, the default Dashboard is opening. It looks like the following screenshot, except that resources list is empty for the first time.

View the Azure dashboard

  1. Choose New link at the left up corner, then search for the Oracle database in the Search the marketplace placeholder.

Create new Oracle database on Azure

  1. Select Oracle Database 12.1.0.2 Enterprise Edition.

Select Oracle 12.1.0.2 on Azure

  1. Choose Create.

Enter the following sample values:

  • Name Oracle
  • User name dremio
  • Resource MyResourceGroup
  • Location Central US
  • SSH public key

To get public key value, run Cloud Shell and open public key file.

1
vi .ssh/id_rsa.pub

Copy ssh key and then close the editor:

Shift + “:” q - Enter

Copy the SSH key on Azure

Then paste it into the SSH public key

Choose OK.

Confirm SSH key on Azure

  1. Choose a virtual machine Size. In this sample provision DS11_V2 is used, but it depends on the particular task.

Choose Select.

Chose the VM size on Azure

  1. Here default Settings can be left, they provide sufficient configuration for our sample architecture.

Choose OK.

Confirm the settings on Azure

  1. After passing validation steps, Purchase form will be opened.

Choose Purchase.

Choose purchase

  1. The default Dashboard will be opened, it requires a few minutes to deploy a virtual machine.

Choose All resources to get the list of the created resources.

Open the default dashboard on Azure

  1. Choose Oracle to open the virtual machine overview.

Open the Oracle virtual machine

Create an Oracle database

Now we have running virtual machine with installed Oracle database software. The next what we need is to configure and run database itself.

  1. Open Cloud Shell and connect to the VM using ssh. You can click Connect button and copy the connection string.
1
$ ssh dremio@<public IP address>

Open the Cloud Shell on Azure

  1. Switch to the oracle superuser, then initialize the listener for logging.
1
2
$ sudo su - oracle
$ lsnrctl start

Switch to the Oracle superuser on Azure

  1. Create the database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
dbca -silent \
       -createDatabase \
       -templateName General_Purpose.dbc \
       -gdbname cdb1 \
       -sid cdb1 \
       -responseFile NO_VALUE \
       -characterSet AL32UTF8 \
       -sysPassword OraPasswd1 \
       -systemPassword OraPasswd1 \
       -createAsContainerDatabase true \
       -numberOfPDBs 1 \
       -pdbName pdb1 \
       -pdbAdminPassword OraPasswd1 \
       -databaseType MULTIPURPOSE \
       -automaticMemoryManagement false \
       -storageType FS \
       -ignorePreReqs

Creating the Oracle database on Azure

It takes a few minutes to create the database.

  1. Before you connect, you need to set two environment variables: ORACLE_HOME and ORACLE_SID
1
2
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=cdb1; export ORACLE_SID

Setting environment variables for Oracle on Azure

  1. You also can add ORACLE_HOME and ORACLE_SID variables to the .bashrc file. This would save the environment variables for future sign-ins.

Open .bashrc file:

1
$ vi ~/.bashrc

Confirm the following statements have been added.

1
2
3
4
# Add ORACLE_HOME.
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
# Add ORACLE_SID.
export ORACLE_SID=cdb1

Save file:

Shift + ‘:’
wq - Enter

Saving file on Azure

  1. Connect to the database using sqlplus.
1
sqlplus / as sysdba
  1. Open the container PDB1 if not already opened, but first check the status.
1
select con_id, name, open_mode from v$pdbs;

Open the PDB1 container on Azure

  1. If the OPEN_MODE for PDB1 is not READ WRITE, then run the following commands to open PDB1 and preserve its state for auto startups.
1
2
3
4
5
alter session set container=pdb1;

alter database open;

alter pluggable database pdb1 save state;

Changing default for PDB1 on Azure

Automate database startup and shutdown

The Oracle database by default doesn’t automatically start when you restart the VM. To set up the Oracle database to start automatically, first sign in as root. Then, create and update some system files.

  1. Sign on as root
1
$ sudo su -

2.Using editor, edit the file /etc/oratab and change the default N to Y:

1
2
3
$ vi /etc/oratab

cdb1:/u01/app/oracle/product/12.1.0/dbhome_1:Y

Changing default value on Azure

  1. Create a file named /etc/init.d/dbora
1
$ vi /etc/init.d/dbora

and paste the following contents:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#!/bin/sh
# chkconfig: 345 99 10
# Description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to $ORACLE_HOME.

ORA_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    # Remove "&" if you don't want startup as a background process.

    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;
'stop')

    # Stop the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.

    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;

Esac

Creating the dbora file on Azure

  1. Change permissions on files with chmod as follows
1
2
3
$ chgrp dba /etc/init.d/dbora

$ chmod 750 /etc/init.d/dbora
  1. Create symbolic links for startup and shutdown as follows
1
2
3
4
5
$ ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora

$ ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora

$ ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Creating symbolic links on Azure

  1. To test your changes, restart the VM
1
$ reboot

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 public internet inbound connections on port 1521 for the Oracle VM.

  1. Open Oracle virtual machine overview, then Networking under the Settings title

Setting up Oracle on Azure

  1. Choose Add inbound port rule, then click Advanced button

Enter the following values

  • Destination port ranges 1521
  • Protocol TCP
  • Name OracleNetService

Choose OK

Creating inbound port rule on Azure

  1. New inbound rule is added

Creating new inbound rule on Azure

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

Open SQL Developer and choose New connection.

Opening SQL developer on Azure

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

Enter required information:

  • oracle-azure** as Connection Name**
  • system 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.

Testing the connection on Azure

  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
/* 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 test data.
insert into dremio.test values ('Hello world!');
Commit;

Creating test table in Oracle on Azure

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. Choose New link at the left up corner of the Azure portal, then select Compute and Red Ubuntu Server 16.04 LTS

Creating new Linux instance on Azure

  1. Enter Basic settings
  • Name DremioVM
  • User name dremio
  • Resource group select MyResourceGroup
  • Location Central US
  • SSH public key

To get public key value, run Cloud Shell and open public key file.

1
vi .ssh/id_rsa.pub

Copy ssh key and then close the editor.

Shift + “:”

q - Enter

Getting the SSH key

Then paste it into the SSH public key

Choose OK.

Confirm SSH key on Azure

  1. Select Size. In this tutorial DS2_V2 is used.

Choose Select.

Confirm size on Azure

  1. Here default Settings can be left, they provide sufficient configuration for our sample architecture.

Choose OK.

Confirm basic setup on Azure

  1. After passing validation steps, Purchase form will be opened.

Choose Purchase.

The default Dashboard will be opened, it requires a few minutes to deploy a virtual machine.

Choose All resources to get the list of the created resources.

View resources on Azure

  1. Click DremioVM to get virtual machine overview.

View VM on Azure

Accessing Linux virtual machine

  1. Open Cloud Shell and connect to the VM using ssh. You can click Connect button and copy the connection string.
1
$ ssh dremio@<public IP address>

SSH to Dremio on Azure

  1. By default only SSH access is allowed on port 22. Therefore it is required to configure TCP access on port 9047 for the Dremio web console.

Open Networking under the Settings title

Configuring network on Azure

  1. Choose Add inbound port rule, then click Advanced button

Enter the following values

  • Destination port ranges 1521
  • Protocol TCP
  • Name DremioWebConsole

Choose OK

Setting up rule on Azure

  1. New inbound rule is added

Confirm new rule on Azure

Installing Dremio

  1. Before installing Dremio, Java installation is also required.

It could be done using apt-get utility - connect to the DremioVM instance using ssh and run next commands:

1
2
3
4
5
6
7
8
# Update packages:
$ sudo apt-get update

# Install Java:
$ sudo apt-get install default-jre

# After Java installation you can check the result:
$ java -version

Install Java on Azure

  1. Create dremio folder for better file structuring, and download Dremio archive:
1
2
3
4
5
$ mkdir dremio

$ cd dremio

$ wget https://download.dremio.com/community-server/1.2.1-201710030121530889-8e49316/dremio-community-1.2.1-201710030121530889-8e49316.tar.gz

Creating folder for Dremio on Azure

  1. Extract Dremio files, rename Dremio folder (just for the commands simplifying), and run the service:
1
2
3
4
5
$ tar -xvzf dremio-community-1.2.1-201710030121530889-8e49316.tar.gz

$ mv dremio-community-1.2.1-201710030121530889-8e49316 dremio-community-1.2.1

$ dremio-community-1.2.1/bin/dremio start

Starting up Dremio on Azure

Accessing Oracle database using Dremio

1.Open Dremio on virtual instance Public IP address

  • http://Public IP address:9047

Connecting to Dremio

  1. After filling all necessary fields on the first Dremio page, we can proceed with the Oracle source and sample dataset.

Setting up Oracle connection on Azure

  1. Choose Add Source » Oracle

Enter connection parameters the same way as for SQL Developer.

  • Name azure-cloud
  • Private IP address of the database instance** as Hostname
  • pdb1 as Service name. It establishes a connection with the pluggable database PDB1.
  • Username dremio
  • Password pass

Choose Save.

Creating connection to Oracle on Azure

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

Connectd to Oralce on Azure

  1. Open DREMIO schema and then click TEST table.

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

Create virtual dataset from Oracle on Azure

  1. To save new virtual dataset into your own space choose Save As:

Saving virtual dataset from Oracle on Azure

Conclusion

In this tutorial typical scenario has been shown, how to use Dremio and relational database within Microsoft Azure cloud. Scenario includes the following steps:

  • Launching Oracle database service within Linux virtual machine
  • Launching Ubuntu virtual machine
  • Running Dremio within Ubuntu machine
  • Establish connection between Dremio and Oracle