Techblogs

Keeping the cloud alive – the real work

Terug naar overzicht

17 maart 2023

In my previous article, I showed you how to install Oracle Linux on a Raspberry Pi 3B and make a connection to the Oracle Always Free Tier database. This was all done as the root user, which is, I know, very bad practice. Time to create a better solution.

First, I created a user:

[root@rpi ~]# useradd oracle

Then, I gave this user a password:

[root@rpi ~]# passwd oracle Changing password for user oracle. New password: Retype new password: passwd: all authentication tokens updated successfully.

Now that the user is added, I logged in as this user and installed the Oracle Connector for Python. I think it should be installed for every user that wants to execute Python code connecting to an Oracle database.

[oracle@rpi ~]# python3 -m pip install cx_Oracle --upgrade --user

To keep things clean, I created a separate user in my cloud database for this ‘keepalive’ project. I logged in as the administrator of the database on my computer. This is the equivalent of SYS or any user with the DBA role in the cloud.

λ sqlplus admin@sandbox_low SQL*Plus: Release 12.2.0.1.0 Production ON Fri Sep 17 08:58:34 2021 Copyright (c) 1982, 2016, Oracle. ALL rights reserved. Enter password: LAST Successful login TIME: Fri Sep 17 2021 08:57:53 +02:00 Connected TO: Oracle DATABASE 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> CREATE USER keepalive IDENTIFIED BY "<aVeRySeCReTPa$$W0RD>" 2 / USER created. SQL> GRANT CONNECT, resource TO keepalive 2 / GRANT succeeded. SQL> GRANT unlimited tablespace TO keepalive 2 / GRANT succeeded. SQL>

Now that I had a user to connect to from my Python code, I wanted to make sure I could check what was going on. Preferably from the environment where I am the most comfortable, which is the Oracle database. I therefore created a simple table where my Python code can insert a record and I can check if it worked.

SQL> CONNECT keepalive@sandbox_low Enter password: Connected. SQL> SQL> CREATE TABLE keepalive 2 ( id NUMBER generated always AS IDENTITY 3 , the_timestamp TIMESTAMP DEFAULT systimestamp 4 , the_text varchar2(256) 5 ) 6 / TABLE created. SQL>

To make sure this table doesn’t consume all of the 20Gb storage I have on the Free Tier database, I also created a trigger to delete data that is older than a week. This period is chosen arbitrarily.

SQL> CREATE OR REPLACE TRIGGER tr_keepalive_asi 2 after INSERT ON keepalive 3 BEGIN 4 DELETE FROM keepalive k 5 WHERE k.the_timestamp < systimestamp - 7; 6 END tr_keepalive_as; 7 / TRIGGER created. SQL>

And that’s the end of working in my comfort zone. Now, I have to create a working Python script.

Python

As said, I am a Python newbie, so I looked up lots of examples on the internet. I know a little bit about programming, so making things modular seemed like a good idea. I used this blog post on Connecting to Oracle Database in Python as a starting point.

First, a config file with all the connection settings. I put it in a separate file, because then I could copy the rest of the code and only had to change this settings file:

[config.py]

username = 'keepalive'
password = '<aVeRySeCReTPa$$W0RD>'
dsn = 'sandbox_low'
port = 1512
encoding = 'UTF-8'

Then, a ‘module’ to perform the connection to the database:

[connect.py]

import cx_Oracle
import config
# connection = None
def connectme():
try:
connection = cx_Oracle.connect(
config.username,
config.password,
config.dsn,
encoding=config.encoding)
except cx_Oracle.Error as error:
print(error)
finally:
# return the connection
if connection:
return connection

And now for the real program. At least, the one that will get executed.

[keepalive.py]

import connect
def keepmealive(connection):
with connection:
cursor = connection.cursor()
result = cursor.execute('''insert into keepalive(the_text) values (to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))''')
connection.commit()
if __name__ == '__main__':
connection = connect.connectme()
keepmealive(connection)

Now that everything is in place, I could test it:

[oracle@rpi ~]$ python3 keepalive.py [oracle@rpi ~]$

No output. But also no errors, so I was hopeful. Back to the SQL*Plus session:

SQL> COLUMN id format 9999 SQL> COLUMN the_timestamp format a30 SQL> COLUMN the_text format a30 SQL> SELECT * 2 FROM keepalive 3 / ID THE_TIMESTAMP THE_TEXT ----- ------------------------------ ------------------------------ 1 17-SEP-21 10.54.44.929540 2021-09-17 10:54:44 SQL>

This looks like how I want it to work. Next stop: run this using a scheduler, or cron in *nix terms.

Cron

To execute a program using cron, I needed to make a shell script that will execute the Python script. I hadn’t done that before either, but in this case, I found an example in the /etc/cron.daily directory. I adjusted this file a little to come up with:

[keeppatchalive] #!/bin/sh python3 keepalive.py exit 0 [oracle@rpi ~]$ chmod 777 keeppatchalive [oracle@rpi ~]$ ./keeppatchalive

Again, no output, but checking the table in SQL*Plus showed me that I have another record present.
Next, I installed cron, which must be done through the root user again.

[root@rpi ~]# yum install cronie

After this, we can add the job to the cron table. For testing purposes, I wanted to run it every 5 minutes:

[oracle@rpi ~]$ crontab -e # This command keeps the connection to patch72 alive */5 * * * * /home/oracle/keeppatchalive

Now all I had to do was the start cron:

[oracle@rpi ~]$ service crond start Redirecting to /bin/systemctl start crond.service ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to manage system services or units. Authenticating as: root Password: ==== AUTHENTICATION COMPLETE === [oracle@rpi ~]$

If things went right, I should see a new record appear in the table every 5 minutes on the minute.

To make sure the cron jobs get run, even after a reboot of the Raspberry Pi, there was one more thing to do:

[oracle@rpi ~]$ chkconfig crond on Note: Forwarding request to 'systemctl enable crond.service'. ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-unit-files === Authentication is required to manage system service or unit files. Authenticating as: root Password: ==== AUTHENTICATION COMPLETE === ==== AUTHENTICATING FOR org.freedesktop.systemd1.reload-daemon === Authentication is required to reload the systemd state. Authenticating as: root Password: ==== AUTHENTICATION COMPLETE === [oracle@rpi ~]$

Multiple connections

It’s possible to have more than one database on the Free Tier. You can even have multiple Free Tier accounts (I got an extra one when I signed up for a hands-on-lab). That’s why it might be a good idea to put the files into separate directories so you can have multiple configurations.

[oracle@rpi ~]$ mkdir patch72 [oracle@rpi ~]$ mv *.py patch72

Change the shell script:

[oracle@rpi ~]$ vi keeppatchalive #!/bin/sh python3 patch72/keepalive.py exit 0

Maybe you’re wondering: how can it connect to multiple cloud environments? If I unzip the wallet for a second environment, the first one gets overwritten. If you want to know how I solved this problem, be sure to read my next blog.