17 maart 2023

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.