My own IoT deployment at Oracle Cloud Free using Autonomous DB and ML

Following my previous two posts about using Oracle Cloud Free options:

• Oracle Always Free Cloud instances in HA configuration
• My own dev/test cloud environment using Oracle Always Free instances

if you already have a Docker Swarm two-node deployment with HA functionality now you are ready to start using Oracle Autonomous Transaction Processing DB to support an IoT deployment, for this example, I am choosing OpenHab server

usually openHab is deployed using Raspberry as Home Automation, but We will deploy at the cloud using an always free account and replacing the storage with an Autonomous DB with Machine Learning support.

To create an Autonomous DB with an ML account enable please see this great video

once you have a DB up and running with the mluser1 created let start deployment openHab Server using Docker.

Unlike the above guide I changed a deployment directory to my HA GlusterFS directory and created a sub-directory to hold openHab persistent data:

# cd /gluster-storage
# mkdir -p openhab/addons
# mkdir -p openhab/conf
# mkdir -p openhab/userdata
# mkdir -p openhab/lib
# chown -R 9001:9001 openhab/

Lib directory is for Oracle JDBC libs downloaded at Oracle.com site link. Download and unzip to get:

[root@node2 gluster-storage]# ll openhab/lib/
total 8296
-r--r--r--. 1 9001  9001 4161744 ago  2  2018 ojdbc8.jar
-r-xr-xr-x. 1  501 games   11596 ago  2  2018 ojdbc.policy
-r--r--r--. 1  501 games  144428 ago  2  2018 ons.jar
-r--r--r--. 1 9001  9001  307817 ago  2  2018 oraclepki.jar
-r--r--r--. 1  501 games 1661545 ago  2  2018 orai18n.jar
-r--r--r--. 1 9001  9001  205152 ago  2  2018 osdt_cert.jar
-r--r--r--. 1 9001  9001  306854 ago  2  2018 osdt_core.jar
-rw-r--r--. 1  501 games    2595 ago 20  2018 README.txt
-r--r--r--. 1  501 games   29103 ago  2  2018 simplefan.jar
-r--r--r--. 1 9001  9001 1398331 ago  2  2018 ucp.jar
-r--r--r--. 1  501 games  262415 ago  2  2018 xdb6.jar

Another required information is the Autonomous DB Wallet zip file you can find it by clicking “Service Console -> Administration -> Download Client Credentials (Wallet)”

unzip it at openhab/conf/wallet directory, here how it looks like:

[root@node2 gluster-storage]# ll openhab/conf/wallet/
total 24
-rw-r--r--. 1 9001 9001 6733 oct 31 13:03 cwallet.sso
-rw-r--r--. 1 9001 9001 6688 oct 31 13:03 ewallet.p12
-rw-r--r--. 1 9001 9001 3275 oct 31 13:03 keystore.jks
-rw-r--r--. 1 9001 9001   87 oct 31 13:03 ojdbc.properties
-rw-r--r--. 1 9001 9001  107 oct 31 13:18 sqlnet.ora
-rw-r--r--. 1 9001 9001 1701 oct 31 13:03 tnsnames.ora
-rw-r--r--. 1 9001 9001 3336 oct 31 13:03 truststore.jks

Finally, prepare your JPA persistent file to connect to Oracle Autonomous DB:

[root@node2 gluster-storage]# cat openhab/conf/services/jpa.cfg 
# connection string url
url=jdbc:oracle:thin:@test1_low
# driver class name
#driver=org.postgresql.Driver
#driver=org.apache.derby.jdbc.ClientDriver
driver=oracle.jdbc.OracleDriver
# username
user=mluser1
# password
password=YourStrongRDBMSPasswordHere

Note that test1_low is one of the connect string defined in your wallet, check first at tnsnames.ora

[root@node2 gluster-storage]# grep test1_low openhab/conf/wallet/tnsnames.ora 
test1_low = (description= (address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com)).............

Depending on how many insert by second you will send to your RDBMS there are other options.

Now We are ready to deploy openHab using Docker, here my docker-compose.yml file

docker-compose.yml for openhab stack

Some comments on the above stack definition

• Oracle JDBC drivers files are published at /openhab/runtime/lib/boot/ to avoid classpath problems
• addons/conf/userdata persistent directories are mapped using GlusterFS volume storage
• MQTT port is exposed as 11883 see below on NGINX reverse proxy traffic
• HTTP access will be using NGINX reverse proxy
• TNS_ADMIN environment variable is required to use Oracle Wallet

Deploy above stack using the command line or Portainer.IO:

# docker stack deploy -c docker-compose.yml openhab

Expose openHab outside the world using NGINX reverse proxy, a modified nginx.conf from my previous post is shown

nginx.conf including reverse proxy for OpenHab and MQTT services

Note some differences:

• load_module directive to support JS in stream section
• stream directive to support MQTT load balancing traffic
• mqtt.conf file is included
• mqtt.js file is at GitHub here

mqtt.conf

an openhab.conf file is added to NGINX conf.d directory with our new WebSite

openhab.conf

Note that, like other sites deployed with NGINX as a reverse proxy we put as upstream servers the two IPs of the cluster nodes, it will provide the HA configuration feature required to stop one node for maintenance. Finally, a modified docker-compose.yml file is shown for Nginx stack.

docker-compose.yml for nginx stack

update the stack using

# docker stack deploy -c docker-compose.yml nginx

Log at the new site behind Nginx using openhab.mydomain.com and follow the guide First-time setup of openHAB your site will look like

openHab Welcome page

Using PaperUI page you could add for example an OpenWeather app or Astro Binding to start recording events at the Oracle RDBMS. Once you have a service registered at openHab enable JPA to persist functionality following this video but using jpa.cfg showed above

If you finished with the JPA configuration you could see the logs using JDeveloper as is shown in this screenshot

SQLDeveloper connected to ADB Cloud

Our last testing is what happen if We want to send MQTT message from an IoT device at home to the cloud environment. First enable an embedded MQTT broker at openHab following this video

Remember that we open a port 1883 on NGINX to forward request to the openHab embedded broker listening at 11883 port, to allow this traffic We have to do these steps, first open outside the world 1883 port TCP and in the internal network 11883, using “Networking -> Virtual Cloud Networks -> VirtualCloudNetwork -> Security List Details” define this:

ports opened at cloud SDN

and open traffic into Oracle Linux firewall (node1 and node2 instances)

# firewall-cmd --permanent --zone=public --add-port=1883/tcp
# firewall-cmd --permanent --zone=public --add-port=11883/tcp
# firewall-cmd --reload

once the traffic is open you can check MQTT broker connection as is show in video using MQTT.Fx and setting the connection as

MQTT Connection settings

Note that I am using node1.mydomain.com, this is converted to IP address either using static /etc/hosts file or defining in your router these values, here a Mikrotik setting to access to my Oracle Cloud Free instances

Mikrotik DNS static setting to define Oracle Cloud Free instances

Well, We have almost done, my last test is to configure an IoT device to send MQTT traffic to Oracle Cloud, here a code snipped from my ESP8266 device connected in my internal network and sending temperature and humidity information sensed with an DHT sensor

#include <credentials.h>
#include <Arduino.h>
#include <ESP8266HTTPClient.h>
#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include <ESP8266mDNS.h>
#include <ESP8266WebServer.h>
#include <ESP8266HTTPUpdateServer.h>
#include <Sensor.h>
#include <Servo.h>
// for HTTPupdate and webserver
const char* host = "Termo_control_side";
ESP8266WebServer httpServer(80);
ESP8266HTTPUpdateServer httpUpdater;
const char* software_version = "version 1";
/*credentials & definitions */
//MQTT
const char* mqtt_id = "Termo_control_side";
const char* position_topic = "termo/side/position";
const char* program_topic = "termo/side/program";
const char* status_topic = "termo/side/status";
const char* debug_topic = "termo/side/debug";
const char* temp_topic = "termo/side/temp"; // Temperature Range: 0°C to 50°C
const char* humi_topic = "termo/side/humi"; // Humidity Range: 20% to 90%
// Temperature and Humidity both are 16-bit. Accuracy: ±1°C and ±1%
.......
String tmp_str; // String for publishing the int's as a string to MQTT
char buf[5];
char selected[7];
WiFiClient espClient;
PubSubClient client(espClient);
.....
static DHTSensor sensor(D1, DHT::DHT11);
.....
void setup() {
  Serial.begin(115200);                 //Serial connection
....
  setup_wifi();   //WiFi connection
....
  // MQTT
  client.setServer("node2.mydomain.com", 1883);
  client.setCallback(callback);
  if (!client.connected()) {
    reconnect();
  }
  waitConnection();
}

and that’s all once I power-on my IoT device it start sending information to the cloud using MQTT protocol.

That concludes the POC We have:

• A docker two node HA cluster up and working
• An Autonomous DB with Machine Learning functionality to analyze our IoT data
• An OpenHab and MQTT server receiving information
• A reverse proxy with HA configuration receiving either HTTP/HTTPs traffic or MQTT traffic

and my ML hello world with my house temperature

Oracle ML Notepad example