1. About


The following demonstrates several ways how to run Oracle database tasks using cron, for example, to use sqlplus or rman.


OS: Oracle Linux


2. Configuring the Oracle Environment


Keep in mind that cron does not perform a user login and will not process the user login profile, such as .profile or .bash_profile, or execute .bashrc for interactive sessions.


It is necessary to define the Oracle command shell environment to run any Oracle Database applications. You can either define required environment variables by hand or use oraenv provided by Oracle. The oraenv script is typically located in /usr/local/bin unless specified otherwise.


You can define the Oracle database shell environment in a crontab file or shell script in several ways. Method 1 or 2 are probably the safest, but there is no real advantage one way or the other.



2.1 Method 1



ORACLE_SID=orcl  # your Oracle SID


grep -wo "$ORACLE_SID" /etc/oratab >/dev/null \

&& { source /usr/local/bin/oraenv >/dev/null

     export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; } \

|| { echo "ERROR: Unknown Oracle SID $ORACLE_SID"; }



2.2 Method 2



ORACLE_SID=$(grep -wo orcl /etc/oratab) # your Oracle SID

if [ "$ORACLE_SID" ]; then

  . /usr/local/bin/oraenv <<<${ORACLE_SID} >\dev\null



  echo "ERROR: Unknown Oracle SID $ORACLE_SID"




2.3 Method 3



ORACLE_SID=orcl # your Oracle SID

if [ "$(grep -wo $ORACLE_SID /etc/oratab)" ]; then

  export ORACLE_SID

  export ORACLE_BASE=/u01/app/oracle

  export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1


  export PATH=$ORACLE_HOME/bin:$PATH



  echo "ERROR: Unknown Oracle SID $ORACLE_SID"





Any script that defines Oracle environment variables, as well as oraenv, needs to be source executed, so it applies to the current shell process. You can source execute a script using source or [dot space].




3. Scripting RMAN and SQLPLUS Examples



The below uses a simple EOI as the "here document" marker. This allows to use shell variables, e.g.: $spoolfile, but $ signs in SQL need to be escaped with a forward slash.



# This procedure uses a temporary SQL spoolfile.

spoolfile=/tmp/orafra_$(date +%N).tmp

sqlplus -s /nolog 1>/dev/null 2>&1 <<EOI

connect system/oracle_4U

set pages 0 feed off

spool ${spoolfile}

select * from v\$recovery_file_dest;



# Get the data and convert free_space to human readable.

values=$( cat ${spoolfile} )

values=$( echo $values ) # convert CR and blanks to blank.

fra_dir=$( cut -d' ' -f1 <<<${values} )

free_space=$( cut -d' ' -f3 <<<${values} )

free_space=$( numfmt --from=si --to=iec $free_space )


# Mail a message

mail_msg="Free space in $fra_dir: $free_space"

echo $mail_msg

mailx -s "FRA free" root@localhost <<<${mail_msg}


The next example uses "EOI" between double quotes, which does not require to escape $ signs in SQL, however, it won't translate shell variables. The example also redirects all sqlplus output into variable dump_loc.




dump_loc=$( sqlplus -s /nolog <<"EOI"

connect system/oracle_4U

set pages 0 feed off

select value from v$parameter

where name='background_dump_dest';



echo $dump_loc


Executing a simply command, using sqlplus -S to suppress the SQL banner, etc.



echo "shutdown immediate" | sqlplus -S / as sysdba


An alternative to using "here document" marker by creating a variable.




backup incremental level 1 database plus archivelog delete input;

delete noprompt obsolete;


rman target / <<<${cmd}