Scheduling cronjobs to run commands on Oracle ADBs with audit logging

I love using the Autonomous Databases (ADBs) on OCI. I manage several ADBs across multiple tenancies. Occasionally, I simply need to schedule certain external jobs that connect to each of these ADBs in sequence and execute commands on a daily basis.

There are several ways you can schedule commands both internally to the DB (e.g. DBMS_SCHEDULER) and Job Scheduler Software that runs externally to the DB. I'll just describe one basic approach. you can substitute the commands in here for commands of your own.

The steps involved are:

Creation of a daily cronjob to run a ADBCommandRunner.sh script
This script:
1. Parses a ADBCommandRunner.json file which holds connection strings and wallet locations
2. Connects to each record in the JSON array using SQLcl
3. Runs the command. In my example it simply selects the connection name and timestamp from dual
4. Disconnects & writes an audit log to ADBCommandRunner.log

Scripts are provided for entertainment purposes. Run at your own risk.

Steps

Create a Compute box on Oracle Cloud
Create a Terminal/Putty session to your Compute Box (Instructions are at the end of the link in step 1)
Create a wallets folder
```
 mkdir wallets
```
Download your Wallet for your Autonomous Database(s). There are several ways to do this, including the Cloud OCI Tools method, however the easiest way is to smash the Download Wallet button manually on OCI - see picture below.
Shuttle the wallet zip file over to your Compute box into the new /home/opc/wallets folder using your preferred method. I use Windows, so I like to use WinSCP to perform this. WinSCP can also re-use your exiting Putty connections so its quite easy to use.
Install Java
```
 sudo dnf install java-17-openjdk -y
```
Install SQLcl
```
  sudo yum install sqlcl -y
```
Make a JSON config file to hold your connections.
```
 nano ADBCommandRunner.json
```

Paste in a version of the following JSON which represents your connection strings. I'm demonstrating two connection strings here.

 [
     {
         "name": "DEV",
         "wallet_path": "wallets/Wallet_DEV.zip",
         "connection_string": "User/Password$@dev_low"
     },
     {
         "name": "TEST",
         "wallet_path": "wallets/Wallet_TEST.zip",
         "connection_string": "User/Password@test_low"
     }
 ]

Ctrl+X to save the file & exit nano
Test the file to see if it is valid JSON
```
jq empty ADBCommandRunner.json
```
If the JSON file is valid, this command will produce no output. If there's a syntax error, it will display an error message indicating the issue. For example
```
parse error: Expected separator between values at line 4, column 21
```
Create a script to parse the JSON
```
nano ADBCommandRunner.sh
```

Paste in the following.

#!/bin/bash

# Define the log file
LOG_FILE="ADBCommandRunner.log"

# Read the JSON file and loop through each object
while IFS= read -r line; do
    name=$(echo "$line" | jq -r '.name')
    wallet_path=$(echo "$line" | jq -r '.wallet_path')
    connection_string=$(echo "$line" | jq -r '.connection_string')

    echo "Running SQL commands for Wallet: $wallet_path"

    # Execute SQL commands using SQLcl and append output to log file with timestamp
    {
        echo "$(date): Running SQL commands for Wallet: $wallet_path"
        sql /nolog <<EOF
            SET CLOUDCONFIG $wallet_path
            CONNECT $connection_string
            SELECT '$name' AS msg, CURRENT_TIMESTAMP AS execution_time FROM DUAL;
            EXIT
EOF
    } >> "$LOG_FILE"  # Appends output to log file

done < <(jq -c '.[]' ADBCommandRunner.json)

This just runs a simple select statement

SELECT '$name' AS msg, CURRENT_TIMESTAMP AS execution_time FROM DUAL;

You can replace this line to run any commands you like.

Ctrl+X to save the file & exit nano
Give it execute rights
```
chmod +x ADBCommandRunner.sh
```

Give it a run

 ./ADBCommandRunner.sh

It'll look like this - a bit boring 🥱

Running SQL commands for Wallet: wallets/Wallet_DEV.zip

Running SQL commands for Wallet: wallets/Wallet_TEST.zip

You can even review detailed output

nano ADBCommandRunner.log

Now lets hook this up to run every day
```
 export EDITOR=nano; crontab -e
```
Paste this on the last line to run it daily
```
 0 0 * * * /bin/bash /home/opc/ADBCommandRunner.sh
```
CTRL+X to save the file and exit
If you want to add remove/connections in the future, just modify the JSON file you created earlier.

Here is a preview of the log file, running at midnight, in case you were interested.

Fri Dec 22 00:00:01 GMT 2023: Running SQL commands for Wallet: wallets/Wallet_DEV.zip

SQLcl: Release 23.3 Production on Fri Dec 22 00:00:04 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected.

MSG        EXECUTION_TIME
__________ ______________________________________
DEV        22-DEC-23 12.00.07.900757000 AM GMT

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0
Fri Dec 22 00:00:11 GMT 2023: Running SQL commands for Wallet: wallets/Wallet_TEST.zip

SQLcl: Release 23.3 Production on Fri Dec 22 00:00:12 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected.

MSG        EXECUTION_TIME
__________ ______________________________________
TEST       22-DEC-23 12.00.16.048198000 AM GMT

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0

ENJOY!

Whats the picture? a cloud-free day on The Stray. Visit Yorkshire!