Automating Archive Log Cleanup in DBCS Standby Databases: A Step-by-Step Guide

In Oracle Cloud Infrastructure (OCI) Database Cloud Service (DBCS), the ability to use crontab or similar utilities to schedule jobs for the oracle user is restricted by default due to security and operational best practices. Here's why this restriction exists and alternative approaches you can use:

Reasons for Restrictions:

1. Security Policies: OCI and DBCS environments typically have strict security policies to protect the infrastructure and databases. Allowing users to schedule cron jobs directly as the oracle user could introduce security risks if not properly managed.
2. Operational Control: To maintain operational control and ensure the stability of the environment, access to system-level utilities like crontab is often restricted to administrative users such as root.

Therefore, I wrote the script to be executed by the root user.

Step 1: Create the folder for maintaining the scripts and logs. 

[oracle@DBCS-stadby ~]$ cd  /u01/app/oracle/admin
[oracle@DBCS-stadby ~]$ mkdir -p /u01/app/oracle/admin/Eclipsys/
[oracle@DBCS-stadby ~]$ mkdir -p /u01/app/oracle/admin/Eclipsys/logs
[oracle@DBCS-stadby ~]$ chmod 775 -R Eclipsys/

Step 2: write the scripts

2.1. delete_archivelogs.sh is the main Linux shell script, and I will add it to the root user’s crontab.

[oracle@DBCS-stadby Eclipsys]$ cat  delete_archivelogs.sh
#!/bin/bash

# Oracle environment variables
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=base
export PATH=$ORACLE_HOME/bin:$PATH
CMD_ROOT_PATH=/u01/app/oracle/admin/Eclipsys
# Define RMAN script path
CMD_FILE=$CMD_ROOT_PATH/rmanarch.rcv

# Define log file path with the current date
LOG_FILE=$CMD_ROOT_PATH/logs/delete_archivelogs_$(date +%Y%m%d_%H%M).log

# Switch to the Oracle user and execute the RMAN script
sudo -u oracle -i <<EOF
export ORACLE_HOME=$ORACLE_HOME
export ORACLE_SID=$ORACLE_SID
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba @$CMD_ROOT_PATH/delete_archivelogs.sql> /dev/null 2>&1
rman target / cmdfile=$CMD_FILE log=$LOG_FILE
EOF

2.2. delete_archivelogs.sql is an Oracle query used to produce the RMAN command for deleting old archived logs. In this query, I specify to delete archived logs until the sequence equals the maximum applied sequence minus 5 on the standby.

[oracle@DBCS-stadby Eclipsys]$ cat delete_archivelogs.sql
set heading off
spool /u01/app/oracle/admin/Eclipsys/rmanarch.rcv
select 'delete archivelog until sequence ' || to_char(max(sequence#) - 5) || ' thread=1;'
from v$archived_log
where applied='YES' and thread#=1;
spool off;
exit;

2.3. rmanarch.rcv is a file that contains the RMAN delete command and is used as a cmdfile in RMAN. Initially, I create it as an empty file.

[oracle@DBCS-stadby Eclipsys]$ touch rmanarch.rcv

Step 3: Scheduling in the root user's crontab

Once you've verified that the SQL*Plus command works correctly, you can schedule it in the root user's crontab. Add the following line to schedule the script to run daily at 2:00 AM:

[opc@DBCS-stadby ~]$ sudo su -
[root@DBCS-stadby ~]# crontab -l
0 2 * * * /u01/app/oracle/admin/Eclipsys/delete_archivelogs.sh > /dev/null 2>&1