Thanks to Oracle’s Cloud Always Free services, now we can have a free VM and a free Oracle Autonomous Database.

Isn’t it excited? And then you want to do some thing with it.

1 constraint of the Always Free Oracle Autonomous Database is that, you need to use it.

If you don’t use the database within 7 days, Oracle will shut the database down, you need to start it manually later. And if you don’t use it for 3 months after that, Oracle deletes the whole instance.

So, how to avoid that? Or, how to auto do something in the database to keep it alive?

An easy way to do this is to insert some data into the database periodically.

We got a VM, and we got the database, so why don’t use the VM to insert some data into the database?

So here I come up with the “ping” groovy script, which get triggered by cron job, and inserts a dummy record every hour to the Database.

Here’s how it works.

Firstly, we install Java and Groovy into the database

How to install Java and Groovy in Oracle Always free Ubuntu

Secondly, we download the Oracle Wallet, and then, referring to Oracle document on how to use JDBC thin to connect to Database, we can download all the required jar files from Oracle Database 18c (18.3) JDBC Driver and UCP Downloads. (We will need ojdbc8.jar, ucp.jar, oraclepki.jar, osdt_core.jar and osdt_cert.jar)

Put the downloaded Jar file to your Groovy /lib directory
And put the wallet to an folder, unzipped it, we need the unzipped path later in the Groovy script.

Create a table using following script

create table ping(creation_date date)

Thirdly, we create the Groovy Script as following:

//Please note the wallet path is the unzipped path
//Replace DBNAME, USERNAME, PASSWORD to your own ones
url = "jdbc:oracle:thin:@DBNAME_high?TNS_ADMIN=PATH_TO_THE_Wallet/Wallet_DBNAME"
username = "USERNAME"
password = "PASSWORD"

println url

driver = "oracle.jdbc.driver.OracleDriver"

import groovy.sql.*
sql = Sql.newInstance(url,username,password,driver)
try{
  sql.execute("insert into ping(creation_date) values (sysdate)")
} finally{
  sql.close()
}

Finally, we create a Cron job in the crontab, and let Linux schedule and trigger the Groovy script every hour

 0 */1 * * * /home/ubuntu/.sdkman/candidates/groovy/current/bin/groovy /home/ubuntu/groovy/PingDB.groovy 

If everything works fine, you should see the Ping table been populated every hour.

Now you can enjoy a none turned off Free database and VM.