Reasons for Restrictions:
- 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
- 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