Back to overview

Keeping the cloud alive – the real work

Published on: 29 September 2021 Author: Patrick Barel 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 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.

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 (http://www.allroundautomations.com/). Patrick als publishes articles on his own blog (http://blog.bar-solutions.com/) 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.