Scripting Oracle SQLPLUS and RMAN

• • 1. About
  • 2. Configuring the Oracle Environment
    • 2.1 Method 1
    • 2.2 Method 2
    • 2.3 Method 3
  • 3. Scripting RMAN and SQLPLUS Examples

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}