Keeping the cloud alive – multiple clouds
Terug naar overzicht

Keeping the cloud alive – multiple clouds

Gepubliceerd: Categorie: Oracle

Deze blog is alleen beschikbaar in het Engels

In the previous blog, I explained how I configured the Raspberry Pi and created a Python script to connect to the cloud. Now, I want to be able to keep multiple cloud databases alive using a single Raspberry Pi. In this blog, I will show you how I handled this problem.

Connecting more than one cloud environment sounds impossible. After all, a new Wallet overwrites the existing one. So how can you work around this? Luckily, I already solved this issue on my laptop, using a solution that I found in this blogpost. This is also what I did on the Raspberry Pi.

First, I created a separate Wallet folder and moved all the files here:

[root@rpi ~]#  cd /usr/lib/oracle/19.10/client64/lib/network/admin/
[root@rpi admin]# mkdir Wallet_Sandbox
[root@rpi admin]# mv *.* Wallet_Sandbox/

Then I created another folder that consists of different Wallet files.

[root@rpi admin]# mkdir Wallet_orcldev

Using my FTP tool, I uploaded the files from the unzipped Wallet to this folder. To make sure that the Oracle Client can find the correct Wallet folder for the different connection strings (tnsnames entries), I created a new tnsnames.ora file in the admin folder.

I used tnsnames.ora files from the Wallets and combined them into one big file. However, for this to work, I had to add a minor change to each entry.

sandbox_low = 
  (description = 
    (retry_count = 20)
    (retry_delay = 3)
    (address     = 
      (protocol = tcps)
      (port     = 1522)
      (host     = adb.eu-amsterdam-1.oraclecloud.com)
    )
    (connect_data = (service_name = **********_sandbox_low.adb.oraclecloud.com))
    (security = 
      (MY_WALLET_DIRECTORY = 
                 "/usr/lib/oracle/19.10/client64/lib/network/admin/Wallet_Sandbox"
      )
      (ssl_server_cert_dn="CN=adb.eu-amsterdam-1.oraclecloud.com
                         ,OU=Oracle ADB AMSTERDAM,O=Oracle Corporation
                         ,L=Redwood City,ST=California,C=US")
    )
  )

In the security section of the entry, I told the client where to find the Wallet files. I had to do this for every entry in the tnsnames.ora file, making sure I pointed to the correct folder for the correct entry.

Now that this part of the administration was in place, I could copy the directory with the python files. In the new directory, all I had to do was update the config.py file. Since I am using scripts to create the user, table and trigger, the password was the same. So, it was a matter of simply changing the DSN entry.

[oracle@rpi ~]$ mkdir oradev
[oracle@rpi ~]$ cp patch72/*.* oradev/
[oracle@rpi ~]$ cd oradev/
[oracle@rpi oradev]$ vi config.py
dsn = 'orcldev_low'

Next, I copied the shell script and altered this to call the Python script from the new folder:

[oracle@rpi ~]$ cp keeppatchalive keeporadevalive
[oracle@rpi ~]$ vi keeporadevalive
python3 oradev/keepalive.py

Checked if it worked by calling the shell script:

[oracle@rpi ~]$ ./keeporadevalive

To make sure this script gets called by the cron scheduler, I needed to update the crontab (while I was at it, I changed the schedule from every 5 minutes to every hour, because we know it works now):

[oracle@rpi ~]$ crontab -e
# This command keeps the connection to patch72 alive
0 * * * * /home/oracle/keeppatchalive
# This command keeps the connection to oradev alive
0 * * * * /home/oracle/keeporadevalive

Minimizing the work

This was all pretty easy, but adding another database to the set was still a lot of work. More work than I would like to do. What if I just have to set up a user in the database that I want to keep alive, then add a couple of lines to a configuration file to point to this database? That would be a lot easier. So, with the internet as my helper, I came up with the following Python script. I decided to put procedure/function in the same file, since I thought it was a bit of an overkill to put a single program into a single file. Maybe I am wrong. If so, please let me know in the comments.

[KeepMeAlive.py]
  1. #!/usr/bin/env python
  2.  
  3. #######################################################################
  4. # Author       : Patrick Barel - 
  5. # Version      : 0.1
  6. # Date         : 2021-09-14
  7. # Filename     : KeepMeAlive.py
  8. # Description  : A python script that connects to multiple oracle 
  9. #                always free tier to keep them alive/awake
  10. # Dependencies :
  11. #######################################################################
  12. import cx_Oracle
  13. import logging
  14. import json
  15.  
  16. def connect(connection_in):
  17.     try:
  18.         logging.info("Connect to %s@%s", connection_in["username"], connection_in["dsn"])
  19.         connection = cx_Oracle.connect(
  20.             connection_in['username'],
  21.             connection_in['password'],
  22.             connection_in['dsn'],
  23.             encoding=connection_in['encoding'])
  24.  
  25.     except cx_Oracle.Error as error:
  26.         logging.error(error)
  27.         print(error)
  28.     finally:
  29.         # return the connection
  30.         if connection:
  31.             return connection
  32.  
  33. if __name__ == '__main__':
  34.     #######################################################################
  35.     #                        start - setup logging                        #
  36.     #######################################################################
  37.     logging.basicConfig( filename='KeepMeAlive.log'
  38.                        , level=logging.DEBUG
  39.                        , format='%(asctime)s %(message)s')
  40.  
  41.     logging.info("Read the JSON file with the defined connections")
  42.     # Opening JSON file
  43.     file = open('/home/oracle/KeepMeAlive/connections.json',)
  44.     # returns JSON object as
  45.     # a dictionary
  46.     connections = json.load(file)
  47.     # Iterating through the json list
  48.     logging.info("Loop through all the defined connections")
  49.     for conndata in connections['connections']:
  50.         connection = connect(conndata)
  51.         logging.info("Close the connection (Just connecting should be enough)")
  52.         connection.close()
  53.     # Closing file
  54.     file.close()

This program reads the connection information from a JSON file, then simply connects and disconnects to the database. However, I think just connecting should be enough to keep the databases up and running.

The JSON file looks like this:

[connections.json]
  1. {
  2.     "connections": [
  3.         { "username" : "keepalive"
  4.         , "password" : "<aVeRySeCReTPa$$W0RD>"
  5.         , "dsn"      : "orcldev_low"
  6.         , "port"     : 1512
  7.         , "encoding" : "UTF-8"
  8.         }
  9.         ,
  10.         { "username" : "keepalive"
  11.         , "password" : "<aVeRySeCReTPa$$W0RD>"
  12.         , "dsn"      : "sandbox_low"
  13.         , "port"     : 1512
  14.         , “encoding” : “UTF-8”
  15.         }
  16.     ]
  17. }

I created a shell script to call this Python program:

[oracle@rpi ~]$ cp keeporadevalive KeepDBAlive
[oracle@rpi ~]$ vi KeepDBAlive
#!/bin/sh
 
python3 KeepMeAlive/KeepMeAlive.py
exit 0

And added the following line to the crontab to add it to the cron schedule:

[oracle@rpi ~]$ crontab -e
# This command keeps the cloud databases alive
0 * * * * /home/oracle/KeepDBAlive

If I provision a new database in the cloud, all I have to do now is create a user that can connect and adjust the connections.json file to add the new credentials. And this program will automagically keep the new database alive. I know this code needs more error handling and better logging, but for now it works. As far as I can tell, it gets the job done and saves me (and hopefully you) time.

More information?

Qualogy has a lot of expertise and experience with Oracle, cloud environments and cloud databases. We are happy to share this knowledge. For more information, please contact info@qualogy.com or +31 70 319 5000.

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