Oracle batch shell scripting

It's not uncommon to use Linux shell scripts (ksh or bash) scripts to invoke Oracle processes remotely, usually using a crontab (on in Windows with the AT command).  It's easy to embed Oracle commands into a batch shell script and use the shell command language to analyze and/or format the output from Oracle.  I also recommend getting the free Oracle Linux command reference poster.

Here are some of my Oracle shell scripts that can be executed batch:

UNIX ksh Scripts for the Oracle DBA

Disk I/O scripts in Oracle

Oracle to capture UNIX/Linux vmstat information
Setting Oracle script variables in Linux/UNIX

Scheduled job completion scripts

Oracle Streams Replication scripts

Linux, The script commands

Oracle - The UNIX ls command - list files

Linux, Invoking Crontab with various options

STATSPACK monitor disk IO shell script

In my O'Reilly book "Oracle UNIX for DBA pocket reference" and my Oracle Press book "Oracle UNIX Administration handbook" I have loads of sample Oracle shell scripts, but the principle is quite simple. 

Here is a simple example of a batch shell script that accesses Oracle and writes the output to a file named myfile.txt:

#!/bin/ksh

 

# First, we must set the environment . . . .

ORACLE_SID=mon1

export ORACLE_SID

ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

 

$ORACLE_HOME/bin/sqlplus system/manager<<!

spool myfile.txt
select * from v\$database;

exit

!

You can also execute external OS batch shell scripts to collect server metrics (vmstat, iostat) and store the results in Oracle tables.

 

#!/bin/ksh

 

while true

do

   iostat -x  300 1|\

      sed 1,2d|\

      awk  '{ printf("%s %s %s\n", $1, $4, $5) }' |\

   while read HDISK VMSTAT_IO_R VMSTAT_IO_W

   do

      echo $HDISK

      echo $VMSTAT_IO_R

      echo $VMSTAT_IO_W

 

      sqlplus -s / <<!

      insert into

         perfstat.stats\$iostat

      values

         (SYSDATE, 300, '$HDISK', $VMSTAT_IO_R,$VMSTAT_IO_W);

      exit

!

 

   done

   sleep 300

done

If you have external scripts, you can also write scripts to ensure that they are always collecting data:

 

#!/bin/ksh

 

# First, we must set the environment . . . .

ORACLE_SID=prodb1

ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'`

PATH=$ORACLE_HOME/bin:$PATH

MON=`echo ~oracle/iostat`

 

#----------------------------------------

# If it is not running, then start it . . . 

#----------------------------------------

check_stat=`ps -ef|grep get_iostat|grep -v grep|wc -l`;

oracle_num=`expr $check_stat`

if [ $oracle_num -lt 1 ]

 then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 &

fi

You can also use batch scripts to loop from database to database, to perform system-wide functions, like this one that deletes from current_logons table:

 

#!/bin/ksh

 

MYDATE=`date +"%Y%m%d"`

SERVER=`uname -a|awk '{print $2}'`

 

if [ -f /usr/local/src/rsp_server-0.01/inetd_copy/core ]

then

 

   # Remove all connections for this WebServer from the database

   su - oracle -c "/usr/app/oracle/admin/product/bin/sqlplus reader/reader@testb1<<!

   select count(*) from current_logons;

   delete from current_logons where webserver_name = '$SERVER';

   select count(*) from current_logons;

   exit

!"

 

fi