Keeping the cloud alive – the real workGepubliceerd: Auteur: Patrick Barel Categorie: Oracle
Deze blog is alleen beschikbaar in het Engels
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 22.214.171.124.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 126.96.36.199.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.
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:
- username = 'keepalive'
- password = '<aVeRySeCReTPa$$W0RD>'
- dsn = 'sandbox_low'
- port = 1512
- encoding = 'UTF-8'
Then, a ‘module’ to perform the connection to the database:
- import cx_Oracle
- import config
- # connection = None
- def connectme():
- connection = cx_Oracle.connect(
- except cx_Oracle.Error as error:
- # return the connection
- if connection:
- return connection
And now for the real program. At least, the one that will get executed.
- 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'))''')
- if __name__ == '__main__':
- connection = connect.connectme()
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.
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 ~]$
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.