Automate Amazon Lightsail Maintenance Activities Using Bash Script

With Amazon Lightsail, like most other virtual private servers (VPS), you are responsible for performing server maintenance activities, e.g. applying patches, once the instance is running. While it’s easy to perform this manually based on a calendar reminder, it’s also easy to forget to do it periodically. When added to the user crontab, the following bash script automatically performs OS patches on a schedule that you define. Please be aware that most Lightsail images are Bitnami based and this method will not apply upgrades/patches on its packaged applications. Updating Apache, for example, requires moving to a new instance with the latest Bitnami image.

Since this example assumes a Lightsail environment based on a Bitnami stack (Debian), user and path specifics may need change for your specific environment.

As a personal preference, I write user scripts to a “scripts” directory in my home directory. This first step creates a new “scripts” directory in my home directory with 700 permissions (read, write, execute). Then, I create a blank file named “maintenance.sh” to contain the script. I prefer to use vim to edit files, but please feel free to adjust to your preferred editor.

mkdir -m 700 ~/scripts

touch ~/scripts/maintenance.sh
chmod 700 ~/scripts/maintenance.sh
vim ~/scripts/maintenance.sh

The next step is to add the script to the blank maintenance.sh file. The script performs only a few actions: “apt update” retrieves the latest list of available packages and versions, but it does not upgrade any packages; “apt upgrade” upgrades existing/installed packages to the latest version. Finally, “apt autoclean” removes package files from the local repository that have been uninstalled or they are no longer available for downloaded.

The script also deletes older log files in the Apache, MySQL, and PHP directories. The rm commands remove all log files older than the latest 5 based on the date based file naming conventions.

#!/bin/bash

sudo apt update && sudo apt upgrade -y
sudo apt autoclean

sudo rm -f $( find /opt/bitnami/apache/logs -maxdepth 1 -iname '*access_log-*' -type f | sort -r | tail -n +6 )
sudo rm -f $( find /opt/bitnami/apache/logs -maxdepth 1 -iname '*error_log-*' -type f | sort -r | tail -n +6 )
sudo rm -f $( find /opt/bitnami/mysql/logs -maxdepth 1 -iname '*mysqld.log-*' -type f | sort -r | tail -n +6 )
sudo rm -f $( find /opt/bitnami/php/logs -maxdepth 1 -iname '*php-fpm.log-*' -type f | sort -r | tail -n +6 )

The last step is to schedule the job using cron. The next command allows you to edit the user crontab.

crontab -e

As an example, add the following line to the user crontab to schedule the script to run at 3:00 AM every Sunday.

0 3 * * 0 /home/bitnami/scripts/maintenance.sh

Exit the editor and the your maintenance activities will be performed automatically as scheduled.

Setting Complex Oracle Job Intervals

It may not be immediately apparent, but DBMS_JOB.SUBMIT permits the use of function calls to set the time interval for executing an Oracle job. This allows for more complex control over the interval rather than attempting to calculate the appropriate interval using SYSDATE math.

Here we have a simple function which returns either the 15th day of the current month or the 1st of the subsequent month (whichever comes first from the current date). This function is intended to set an interval where the job is executed on the first and fifteenth of each month at Noon. I think this would be relatively complicated and unreadable to try to set using just SYSDATE math as an argument in the DBMS_JOB.SUBMIT call.

CREATE OR REPLACE FUNCTION SET_JOB_INTERVAL
  RETURN DATE
  IS
    mydate DATE := NULL;
   
BEGIN
  IF TO_CHAR(SYSDATE, 'DD') BETWEEN 1 AND 14 THEN
    mydate := TO_DATE(TO_CHAR(SYSDATE, 'MM') || '/15/' || TO_CHAR(SYSDATE, 'YYYY') ||
              ' 12:00:00', 'MM/DD/YYYY HH24:MI:SS');
  ELSE
    mydate := TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'MM') || '/01/' ||
              TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY') || ' 12:00:00', 'MM/DD/YYYY HH24:MI:SS');
  END IF;  
  
  RETURN mydate;
END;
/

The following code submits a new job for execution. Note that the last two arguments in the call refer to the function defined above.

VARIABLE job_number NUMBER
BEGIN
  DBMS_JOB.SUBMIT(:job_number, 'EXAMPLE_PROCEDURE;', SET_JOB_INTERVAL, 'SET_JOB_INTERVAL');
END;
/

Maintaining Data Change History in Oracle

For some software applications, it’s necessary to track changes made to data in order to comply with various regulatory or legal requirements. As an example, let’s say our application has an Oracle database with a table named WIDGET and the application needs to maintain a history of changes made to each record in the WIDGET table. The WIDGET table contains a simple ID key (WIDGET_ID) and a name attribute (WIDGET_NAME). It also has attributes to capture when the row is created/modified and by whom along with an INTERNAL_STATUS field which indicates whether or not the row is active (1) or deleted (0).

CREATE TABLE WIDGET
(
  WIDGET_ID           NUMBER(10),
  WIDGET_NAME         VARCHAR2(150),
  INTERNAL_STATUS     NUMBER(10),
  ROW_ENTERED         DATE,
  ROW_ENTERED_BY      VARCHAR2(15),
  ROW_MODIFIED        DATE,
  ROW_MODIFIED_BY     VARCHAR2(15)
)
/

Next, we’ll create a history table to capture each version of each record. For the purposes of this example, the current active records always reside in the WIDGET table (INTERNAL_STATUS is set to 1). Notice that the table structure closely follows the table structure of the WIDGET table except that a new HISTORY_ID attribute has been added.

CREATE TABLE HISTORY_WIDGET
(
  HISTORY_ID          NUMBER(10),
  WIDGET_ID           NUMBER(10),
  WIDGET_NAME         VARCHAR2(150),
  INTERNAL_STATUS     NUMBER(10),
  ROW_ENTERED         DATE,
  ROW_ENTERED_BY      VARCHAR2(15),
  ROW_MODIFIED        DATE,
  ROW_MODIFIED_BY     VARCHAR2(15)
)
/

We’ll need a unique key for the HISTORY_ID field value in the HISTORY_WIDGET table, so let’s create a new sequence named HISTORY_WIDGET_SEQ.

CREATE SEQUENCE HISTORY_WIDGET_SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER
/

Finally, we’ll need to create two triggers responsible for populating the HISTORY_WIDGET table whenever changes are made to the records in the WIDGET table. The trigger POSTDEL_WIDGET is executed after a record is deleted while the trigger POSTUPD_WIDGET is executed after a record is updated. With each data manipulation, the triggers create new records in the history table using the field values from the record that was deleted or updated by using the :OLD reference.

CREATE OR REPLACE TRIGGER POSTDEL_WIDGET
  AFTER DELETE ON WIDGET
  FOR EACH ROW
BEGIN
  INSERT INTO HISTORY_WIDGET
             (HISTORY_ID,
              WIDGET_ID,
              WIDGET_NAME,
              INTERNAL_STATUS,
              ROW_ENTERED,
              ROW_ENTERED_BY,
              ROW_MODIFIED,
              ROW_MODIFIED_BY)
  VALUES     (HISTORY_WIDGET_SEQ.NEXTVAL,
              :OLD.WIDGET_ID,
              :OLD.WIDGET_NAME,
              :OLD.INTERNAL_STATUS,
              :OLD.ROW_ENTERED,
              :OLD.ROW_ENTERED_BY,
              :OLD.ROW_MODIFIED,
              :OLD.ROW_MODIFIED_BY);
END;
/

CREATE OR REPLACE TRIGGER POSTUPD_WIDGET
  AFTER UPDATE ON WIDGET
  FOR EACH ROW
BEGIN
  INSERT INTO HISTORY_WIDGET
             (HISTORY_ID,
              WIDGET_ID,
              WIDGET_NAME,
              INTERNAL_STATUS,
              ROW_ENTERED,
              ROW_ENTERED_BY,
              ROW_MODIFIED,
              ROW_MODIFIED_BY)
  VALUES     (HISTORY_WIDGET_SEQ.NEXTVAL,
              :OLD.WIDGET_ID,
              :OLD.WIDGET_NAME,
              :OLD.INTERNAL_STATUS,
              :OLD.ROW_ENTERED,
              :OLD.ROW_ENTERED_BY,
              :OLD.ROW_MODIFIED,
              :OLD.ROW_MODIFIED_BY);
END;
/

Since this is a very simple way of maintaining data history, it is important to note how the application should manipulate the data. It is the responsibility of the application to populate the ROW_MODIFIED and ROW_MODIFIED_BY attributes when updating records. More importantly, when deleting records, the application should first perform an update on the record to set the INTERNAL_STATUS_ID equal to 0 and to set the ROW_MODIFIED and ROW_MODIFIED_BY attributes. If this crucial step is not performed, then the history will not include a record capturing when a record was deleted and by whom.