Keeping the cloud alive – the real work

Keeping the cloud alive – the real work

Published on: Category: Oracle

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 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 - 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.                                                                    

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:
  2  ( id NUMBER generated always AS IDENTITY
  3  , the_timestamp TIMESTAMP DEFAULT systimestamp
  4  , the_text varchar2(256)
  5  )
  6  /
TABLE created.

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.

  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.

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


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:

  1. username = 'keepalive'
  2. password = '<aVeRySeCReTPa$$W0RD>'
  3. dsn = 'sandbox_low'
  4. port = 1512
  5. encoding = 'UTF-8'

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

  1. import cx_Oracle
  2. import config
  4. # connection = None
  5. def connectme():
  6. try:
  7. connection = cx_Oracle.connect(
  8. config.username,
  9. config.password,
  10. config.dsn,
  11. encoding=config.encoding)
  13. except cx_Oracle.Error as error:
  14. print(error)
  15. finally:
  16. # return the connection
  17. if connection:
  18. return connection

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

  1. import connect
  3. def keepmealive(connection):
  4. with connection:
  5. cursor = connection.cursor()
  6. result = cursor.execute('''insert into keepalive(the_text) values (to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))''')
  7. connection.commit()
  9. if __name__ == '__main__':
  10. connection = connect.connectme()
  11. keepmealive(connection)

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

[oracle@rpi ~]$ python3
[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
  2    FROM keepalive
  3  /
   ID THE_TIMESTAMP                  THE_TEXT
----- ------------------------------ ------------------------------
    1 17-SEP-21      2021-09-17 10:54:44

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


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:

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
[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
==== AUTHENTICATING FOR org.freedesktop.systemd1.reload-daemon ===
Authentication is required to reload the systemd state.
Authenticating as: root
[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
python3 patch72/
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.

Patrick Barel
About the author Patrick Barel

Patrick studied Telematics and has worked as a full-time Oracle Developer since 1999. After his study, he followed several PL/SQL courses, including seminars by Steven Feuerstein. He also completed Oracle's OCA and OCP programme in the field of SQL and PL/SQL. Besides working with SQL and PL/SQL, he wrote different plug-ins for PL/SQL Developer ( Patrick als publishes articles on his own blog ( In 2011, Patrick was appointed Oracle ACE for his contribution to the field of SQL and PL/SQL. In 2015, Patrick was awarded the Developer Choice Award. In 2019, Patrick was appointed Oracle ACE Director for his contributions to the Oracle community.

More posts by Patrick Barel