About Configuring the Database Connections

Learn how to install the automation script and perform the steps that lead to having functional Node-RED, Mosquitto, and Oracle Database instances. This also includes configuring the different solution components to connect to one another.

Run the Script

The script in the GitHub repo automates the provisioning of the environment and installs the dependencies.

1. See Download Code for the link to the GitHub repository.
2. Run the script.
3. The script provisions the infrastructure.
   The automation begins by:

   1. Creating a VCN and subnets, and opening the required ports.
   2. Provisioning an OCI Compute instance with Oracle Linux as the operating system.
4. The script installs the software.
   1. Installs Oracle Database (free developer) on the instance.
   2. Creates a schema and table for Node-RED and provides the schema user with the required privileges.
   3. Installs Node-RED dependencies, such as node and NPM.
   4. Installs Node-RED.
   5. Installs Mosquitto server.
   6. Updates the sfw rules to allow these ports to be accessed externally.
   7. Installs the Oracle client and the required Node-RED Oracle Database plugins node-red-contrib-oracledb-mod.
   8. Deploys a Node-RED flow that subscribes to a topic on Mosquitto and puts the data into the database.

Connect the Database

Once everything is up and running (which takes around 15 minutes), you'll need to add in the user name and password into Node-RED to connect to the database.

1. Log into Node-RED <Machine IP>:1880.
2. Double-click on the oracledb node.
3. Click the edit button.
4. Replace with Oracle Autonomous Database.

Create an Autonomous Database

Use OCI to set up Oracle Autonomous Database.

1. In OCI, navigate to Autonomous Database.
2. Click Create Autonomous Database.
3. Under Choose a workload type, select Transaction Processing.
4. Enable Compute autoscaling.
5. Under Create administrator credentials, enter your password.
6. At the bottom of the screen, click Create Autonomous Database.
   The Autonomous Database details screen opens.

Configure the Autonomous Database

Set up the user and schema needed by Node-RED, and create a test table with which to test the solution.

1. On the Autonomous Database details screen, click Database actions > SQL.
   The Database Actions | SQL editor opens.
2. Run the following script to create a schema and user, provide it with the required grants, and create an initial test table.

   CREATEUSER nodered IDENTIFIED BY<YourPassword>;
   GRANTCREATE SESSION TO nodered;
   GRANTCREATETABLETO nodered;
   GRANTCREATEPROCEDURETO nodered;
   GRANTCREATETRIGGERTO nodered;
   GRANTCREATEVIEWTO nodered;
   ALTERUSER C##NODERED QUOTA UNLIMITED ON USERS;
   CREATETABLE nodered.nodered (col1 varchar2(500),col2 varchar2(500));
   CREATETABLE nodered.sensor_data (
     id NUMBER GENERATED ALWAYS ASIDENTITY, -- Optional auto-generated ID column
     vibration NUMBER,
     sound NUMBER,
     temp NUMBER,
     humi NUMBER,
   timestampTIMESTAMPDEFAULTCURRENT_TIMESTAMP
   );

Configure the Node-RED Connection

Connect Node-RED to the previously set up Autonomous Database.

1. In OCI, navigate Autonomous Database.
2. Click Database connection.
3. Copy the connection screen.
4. Connect (via SSH) to the Node-RED server that was provisioned.
   1. Edit tnsnames.ora.
   2. Add the connection string: vi /opt/oracle/product/$DBHOME/dbhomeFree/network/admin/tnsnames.ora
   3. Paste the connection string.
   4. Rename the connection string to: adb = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=ixcsyvrmtjm8ebr_iottest_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
5. Navigate to the Node-RED console.
6. Edit the oracledb node.
7. Modify the server connection and security.

   Note:

   If you get error ORA-12506: TNS:listener rejected connection based on service ACL filtering, modify the Access Control List to include the IP of your Node-RED server. You'll also need to add the IP of the machine you are connecting from, and disable the requirement for mTLS.

Set Up Oracle APEX

You can use the data from Kaggle or your own sensor data.

1. Launch database actions.
2. Click APEX WORKSPACES.
3. Complete the form to create a workspace, ensuring you use the same database user created earlier.
4. Launch the APEX dashboard.
5. Click Create application.
6. Select Sample Charts.
7. Configure the charts to read the data from your table.