Keeping the cloud alive - setup

If your Always Free Autonomous Database has no activity for 7 consecutive days, the database will be automatically stopped. Your data will be preserved, and you can restart the database to continue using it. If the database remains stopped for 3 months, it will be reclaimed. 

The easy solution to this problem is to just access them every day, or at least once a week. But you know how things go. Work gets in the way, you have a couple of days off, or you just forget. And when you need to access the database again, it has been stopped.

Luckily, this isn’t that much of a problem. You just log into the Oracle Cloud website, supply your credentials and start it up again. But as a developer, I figured there must be an easier way to do this. In fact, I shouldn’t be doing this at all. What if it was done automagically?

Raspberry Pi

Since I had a little bit of time, I figured: why not try to do this, using stuff I don’t know enough about (yet)? The plan is to use a Raspberry Pi and Python code to set up a connection to my cloud database every day or so. Sounds easy, but for someone who knows just enough of Linux to start and stop SQL*Plus, this seemed like quite a challenge.

I decided I wanted to use Oracle Linux for Raspberry Pi, since I came across this download link by coincidence. Because I am an Oracle Developer, it seemed like the most logical choice.

I bought a new Raspberry Pi 4 and tried to run the image there. Somehow, this didn’t work (I don’t know why, yet). Luckily, I have another Raspberry Pi, type 3B, so I tried to start up the image there. It worked. However, since this Pi is already doing other stuff, I decided to buy a new Raspberry Pi 3B to run the image on. I chose to use the Oracle Linux 7.9 image. It may also work with the 8.4 version, but I haven’t tried that yet.

Using Raspberry Pi Imager, you can write the image to an SD card. When that is done, just put the SD card into the Pi, start it up and log in.

λ ssh root@oraclepi
root@oraclepi's password:

First thing I needed to do: change the default password from ‘oracle’ to something I can remember.

You are required to change your password immediately (root enforced)
Changing password for root.
(current) UNIX password:
New password:
Retype new password:
[root@rpi ~]#

Now that I know which partition to grow, I can issue this command:

[root@rpi ~]# growpart /dev/mmcblk0 4
CHANGED: partition=4 start=2074624 old: size=8388608 end=10463232 new: size=13449183 end=15523807

To make the changes persistent, the following command is needed:

[root@rpi ~]# btrfs filesystem resize max /
Resize '/' of 'max'

Then I wanted (needed) to set the time zone.

[root@rpi ~]# timedatectl set-timezone Europe/Amsterdam

Next, I updated the entire system with the following command:

[root@rpi ~]# yum update -y

This process took some time, so grab something to drink while you wait. In my case, it took about an hour to update.

Python

I am of course not the first who wants to connect a Linux environment to the cloud to run some Python code, so I searched the internet and came across this page: Quick Start: Developing Python Applications for Oracle Autonomous Database.

I don’t have to provision an ADB instance because I already have one. It’s the reason why I’m doing all of this in the first place. So, I started at step 2: obtaining client credentials. I will save these to my local computer and upload them to the Pi later.

Step 3 is to install Python:

[root@rpi ~]# yum install -y python3

On a Raspberry Pi, you also have to install the Python development environment.

[root@rpi ~]# yum install python3-devel

From this point on, I performed the steps in a different order than described in the Quick Start guide. I don’t know why but when I followed the guide, I got all kinds of errors.

First, I configured the Instant Client repository:

[root@rpi ~]# yum install oracle-release-el7

Then, I installed the Basic Package:

[root@rpi ~]# yum install oracle-instantclient19.10-basic

Then, to prevent getting a lot of errors, I needed to install the gcc compiler before I could perform the next steps.

[root@rpi ~]# yum install gcc

Now it’s time to install the Python3 environment:

[root@rpi ~]# python3 -m pip install --upgrade setuptools
[root@rpi ~]# python3 -m pip install ez-setup

And of course, the Oracle connector (at least that is how I see it):

[root@rpi ~]# python3 -m pip install cx_Oracle --upgrade --user

Next, I uploaded the Wallet to the Pi so the Oracle connector can actually connect to the cloud. I downloaded the Wallet to my local machine, so I can upload it to the Pi using ftp. Then, on the Pi, I needed to move or copy the file to the correct folder.

[root@rpi ~]# cp Wallet_*.zip /usr/lib/oracle/19.10/client64/lib/network/admin/
[root@rpi ~]# sh -c 'cd /usr/lib/oracle/19.10/client64/lib/network/admin/ && unzip -B Wallet_*.zip'

If this doesn’t work (which of course it didn’t), you can also upload the separate files from the zip to the location (/usr/lib/oracle/19.10/client64/lib/network/admin/) directly.

Check if it works

Let’s see if everything works. Step 7 in the Quick Start guide is a simple Python program. The program creates a table, adds some data to it, then reads it back to display the rows. Pretty simple.  But it’s a good test to see if everything works.

import cx_Oracle
 
connection = cx_Oracle.connect(user="admin", password="XXXX", dsn="XXX_dbhigh")
 
cursor = connection.cursor()
 
# Create a table
 
cursor.execute("""begin
                     execute immediate 'drop table pytab';
                     exception when others then if sqlcode <> -942 then raise; end if;
                  end;""")
cursor.execute("create table pytab (id number, data varchar2(20))")
 
# Insert some rows
 
rows = [ (1, "First" ),
         (2, "Second" ),
         (3, "Third" ),
         (4, "Fourth" ),
         (5, "Fifth" ),
         (6, "Sixth" ),
         (7, "Seventh" ) ]
 
cursor.executemany("insert into pytab(id, data) values (:1, :2)", rows)
 
connection.commit()  # comment to rollback automagically
 
# Now query the rows back
for row in cursor.execute('select * from pytab'):
    print(row)

Python depends on indentation, so make sure you keep the indentation correct. Save this file as example.py so you can run it.

From the command prompt, issue this command:

[root@rpi ~]# python3 example.py

If everything worked as expected, the output will look like this:

(1, 'First')
(2, 'Second')
(3, 'Third')
(4, 'Fourth')
(5, 'Fifth')
(6, 'Sixth')
(7, 'Seventh')

So far so good. I have a Raspberry Pi that can connect to the Oracle database on the Always Free Tier. Next: create my own code and make sure it runs automagically every day or week. If you’re interested to see how that went, be sure to read my next blog.

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