Keeping the cloud alive – the real work

Keeping the cloud alive – the real work

Gepubliceerd: 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 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]
  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:

[connect.py]
  1. import cx_Oracle
  2. import config
  3.  
  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)
  12.  
  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.

[keepalive.py]
  1. import connect
  2.  
  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()
  8.  
  9. if __name__ == '__main__':
  10. connection = connect.connectme()
  11. 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.

Patrick Barel
Over auteur Patrick Barel

Patrick heeft Telematica gestudeerd en is sinds 1999 fulltime Oracle Developer. Na zijn studie heeft hij meerdere PL/SQL-cursussen gevolgd (o.a. van Steven Feuerstein) Ook heeft hij Oracle's OCA- en OCP-programma afgerond op het gebied van SQL en PL/SQL. Patrick heeft naast zijn werk met SQL en PL/SQL verschillende plug-ins geschreven voor PL/SQL Developer (http://www.allroundautomations.com/). Patrick publiceert ook regelmatig op zijn eigen blog (http://blog.bar-solutions.com/). In 2011 heeft Patrick de titel Oracle ACE toegewezen gekregen voor zijn bijdragen aan het vakgebied SQL en PL/SQL. In 2015 heeft Patrick de Developer Choice Award toegewezen gekregen. In 2019 heeft Patrick de titel Oracle ACE Director toegewezen gekregen voor zijn bijdragen aan de Oracle-community.

Meer posts van Patrick Barel
Reacties
Reactie plaatsen