1 april 2023
Do you have an Oracle Free Tier cloud account? Then you know it’s important to keep your databases alive if you want to be able to access them at any time. But this is not as easy as it sounds. Of course, you know that:
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?
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
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:
Retype new password:
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.
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
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.
connection = cx_Oracle.connect(user="admin", password="XXXX", dsn="XXX_dbhigh")
cursor = connection.cursor()
# Create a table
execute immediate 'drop table pytab';
exception when others then if sqlcode <> -942 then raise; end if;
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'):
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:
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.