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 deleting 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 created 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