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
#/bin/bash
ORACLE_SID=orcl # your Oracle SID
ORAENV_ASK=NO
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
#/bin/bash
ORACLE_SID=$(grep -wo orcl /etc/oratab) # your Oracle SID
if [ "$ORACLE_SID" ]; then
. /usr/local/bin/oraenv <<<${ORACLE_SID} >\dev\null
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
else
echo "ERROR: Unknown Oracle SID $ORACLE_SID"
fi
2.3 Method 3
#/bin/bash
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 LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
else
echo "ERROR: Unknown Oracle SID $ORACLE_SID"
fi
Note:
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.
#/bin/bash
# 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;
EOI
# 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.
#/bin/bash
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';
EOI
)
echo $dump_loc
Executing a simply command, using sqlplus -S to suppress the SQL banner, etc.
#/bin/bash
echo "shutdown immediate" | sqlplus -S / as sysdba
An alternative to using "here document" marker by creating a variable.
#/bin/bash
cmd="
backup incremental level 1 database plus archivelog delete input;
delete noprompt obsolete;
"
rman target / <<<${cmd}