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.
Auteur: Patrick Barel
