Keeping the cloud alive – multiple clouds
Gepubliceerd: Auteur: Patrick Barel Categorie: OracleDeze 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]
#!/usr/bin/env python ####################################################################### # Author : Patrick Barel - # Version : 0.1 # Date : 2021-09-14 # Filename : KeepMeAlive.py # Description : A python script that connects to multiple oracle # always free tier to keep them alive/awake # Dependencies : ####################################################################### import cx_Oracle import logging import json def connect(connection_in): try: logging.info("Connect to %s@%s", connection_in["username"], connection_in["dsn"]) connection = cx_Oracle.connect( connection_in['username'], connection_in['password'], connection_in['dsn'], encoding=connection_in['encoding']) except cx_Oracle.Error as error: logging.error(error) print(error) finally: # return the connection if connection: return connection if __name__ == '__main__': ####################################################################### # start - setup logging # ####################################################################### logging.basicConfig( filename='KeepMeAlive.log' , level=logging.DEBUG , format='%(asctime)s %(message)s') logging.info("Read the JSON file with the defined connections") # Opening JSON file file = open('/home/oracle/KeepMeAlive/connections.json',) # returns JSON object as # a dictionary connections = json.load(file) # Iterating through the json list logging.info("Loop through all the defined connections") for conndata in connections['connections']: connection = connect(conndata) logging.info("Close the connection (Just connecting should be enough)") connection.close() # Closing file 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]
{ "connections": [ { "username" : "keepalive" , "password" : "<aVeRySeCReTPa$$W0RD>" , "dsn" : "orcldev_low" , "port" : 1512 , "encoding" : "UTF-8" } , { "username" : "keepalive" , "password" : "<aVeRySeCReTPa$$W0RD>" , "dsn" : "sandbox_low" , "port" : 1512 , “encoding” : “UTF-8” } ] }
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.