Oracle Job Management in UNIX Administration Oracle UNIX/Linux Tips by Burleson Consulting

Oracle job management in UNIX

This section describes techniques for submitting and monitoring Oracle tasks that have been placed inside UNIX shell scripts. Let?s start by showing how a UNIX shell script is submitted, and then move on to more advanced topics.

Here is an example of a UNIX script that contains SQL*Plus commands to analyze all tables and indexes for an Oracle database. The script accepts the $ORACLE_SID as an input argument.

analyze.ksh
#!/bin/ksh

# Validate the Oracle database name with
# lookup in /var/opt/oracle/oratab
TEMP=`cat /var/opt/oracle/oratab|grep \^$1:|\
cut -f1 -d':'|wc -l`
tmp=`expr TEMP`     # Convert string to number
if [ $tmp -ne 1 ]
then
   echo "Your input $1 is not a valid ORACLE_SID."
   exit 99
fi

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

ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/obj_stat`
export MON

# Get the server name
host=`uname -a|awk '{ print $2 }'`

$ORACLE_HOME/bin/sqlplus ?s perfstat/perfstat<<!

set heading off;
set feedback off;
set echo off;
set pages 999;
set lines 120;

--****************************************************************
-- First, let's get the latest statistics for each table
--****************************************************************

spool $MON/run_analyze.sql
select 'analyze table '||owner||'.'||table_name||' estimate statistics
sample 5000 rows;'
from
   dba_tables
where
   owner not in ('SYS','SYSTEM','PERFSTAT');
--  ******************************
--  Analyze all indexes for statistics
--  ******************************
select 'analyze index '||owner||'.'||table_name||' compute statistics;'
from
   dba_indexes
where
   owner not in ('SYS','SYSTEM','PERFSTAT');
spool off;

set echo on;
set feedback on;

@$MON/run_analyze

exit

!

Here we see that our SQL*Plus script is encapsulated inside a UNIX script.  We check to ensure that a valid $ORACLE_SID is passed to this script as a parameter, and we then execute SQL*Plus from inside the UNIX script.

Now, let?s see how we might submit this task in UNIX as a background job.

Submitting Oracle jobs in the background

UNIX provides the nohup command to submit a task in the background. This technique is used for long-running Oracle jobs, and is used to free-up your command line prompt, which is especially useful if you are dialed-in to the UNIX server.  In the example below, we have a script that executes a SQL*Plus command, and we submit it for background processing.

nohup run_me.ksh > outfile.lst 2>&1 &

This command has the following components:

1. nohup          This directs UNIX to submit the job

2. run_me.ksh     This is the name of the UNIX executable script

3. >              This re-directs the standard output to a UNIX file

4. outfile.lst    This is the UNIX file location for the script output

5. 2>&1           This re-directs standard error to standard out, showing any error                               messages

6. &              This submit the job as a background task

Watch the execution of a background process

From the example above, we directed the script output to a file called outfile.lst.  We can monitor the execution of the background process by using the UNIX tail ?f command, and see each line of output as it is written to the file.

>tail ?f longfile.lst

To exit the tail ?f command, you enter <crtl> c at any time to return to the UNIX prompt.

UNIX task management techniques

Now that we understand how to submit a single task, let?s look at some more sophisticated UNIX techniques for managing multiple tasks.  Here we will look at techniques for verifying the number of input parameters to a UNIX script and also examine techniques for submitting multiple UNIX tasks at the same time.

parameter checking for Oracle shell scripts

The following code snippet will end a UNIX script with the exit command if the appropriate arguments have not been passed to the script.  In this example, the run_purge.ksh script requires two parameters, a valid ORACLE_SID and a numeric value, specifying the number of days back to purge. For example:

root> run_purge.ksh MYSID 200

Below we see that the script will terminate if the appropriate parameters are not passed to the script.

# Exit if no first parameter $1
if [ -z "$1" ]
then
   echo "Usage: run_purge.ksh <ORACLE_SID> <#_days> (where value is > 100)"
   exit 99
fi

# Exit if no second parameter $2
if [ -z "$2" ]
then
   echo "Usage: run_purge.ksh <ORACLE_SID> <#_days> (where value is > 100)"
   exit 99
fi

# Exit is parm is not greater than 100
tmp=`expr $2`            # Convert string to number
if [ $tmp -lt 100 ]
then
   echo
   echo "Argument two is less than 100.  Aborting Script."
   echo
   exit 99
fi

Make sure that the UNIX user is oracle

This statement will ensure that all UNIX scripts are only executed by the Oracle UNIX user.  This technique ensures that only oracle executes the script and offers extra security and protection against unauthorized execution.  Note the use of the UNIX whoami command to capture the current user ID.

if [ `whoami` != 'oracle' ]
then
   echo "Error: You must be oracle to execute.  Exiting."
   Exit 99
fi

Validate an $ORACLE_SID being passed to a UNIX script

This code snippet is useful when you want to ensure that a valid database name is passed to a UNIX script.  Note that the /etc/oratab file is for HPUX and AIX, and you may need to change it to /var/opt/oratab if you are using Solaris.

#!/bin/ksh

# Exit if no first parameter $1 passed
if [ -z "$1" ]
then
   echo "Please pass a valid ORACLE_SID to this script"
   exit 99
fi

# Validate the Oracle database name with lookup in /etc/oratab
TEMP=`cat /etc/oratab|grep \^$1:|cut -f1 -d':'|wc -l`
tmp=`expr TEMP`            # Convert string to number
if [ $tmp -ne 1 ]
then
   echo "Your input $1 is not a valid ORACLE_SID.  Retry."
   exit 99
fi

Multiplexing Oracle tasks in UNIX

If you have a large number of tasks to perform in a short amount of time, you can save time by running the jobs simultaneously in UNIX.

A great example is Oracle DBA table reorganizations. When you process table reorganizations in parallel, the total time required to reorganize all the tables is no more than the time required for the largest table. For example, if you need to reorganize 100 gigabytes of table data in a single weekend, the parallel job submission approach is the only way to go.

Below is a Korn shell script you can use to execute the reorganization. The script uses the UNIX nohup command to submit simultaneous CTAS reorganizations at the same time.

master_reorg.ksh
#!/bin/ksh
# Written by Donald Keith Burleson
# usage: nohup don_reorg.ksh > don_reorg.lst 2>&1 &

# Ensure that running user is oracle . . . . .
oracle_user=`whoami|grep oracle|grep -v grep|wc -l`;
oracle_num=`expr $oracle_user`
if [ $oracle_num -lt 1 ]
 then echo "Current user is not oracle. Please su to oracle and retry."
 exit
fi

# Ensure that Oracle is running . . . . .
oracle_up=`ps -ef|grep pmon|grep -v grep|wc -l`;
oracle_num=`expr $oracle_up`
if [ $oracle_num -lt 1 ]
 then echo "ORACLE instance is NOT up. Please start Oracle and retry."
 exit
fi

#************************************************************
# Submit parallel CTAS reorganizations of important tables
#************************************************************
nohup reorg.ksh CUSTOMER  >customer.lst  2>&1 &
nohup reorg.ksh ORDER     >order.lst     2>&1 &
nohup reorg.ksh ITEM      >item.lst      2>&1 &
nohup reorg.ksh LINE_ITEM >line_item.lst 2>&1 &
nohup reorg.ksh PRODUCT   >product.lst   2>&1 &

A UNIX script to ensure than a daemon is running

The following script is used to ensure that a UNIX vmstat monitor is always running.  This script can be scheduled via cron every 15 minutes.  The scripts checks to see if the UNIX daemon is running, and re-starts it if it has failed.

In this example, we have a UNIX daemon called get_vmstat_linus.ksh that collects vmstat information from UNIX and places it into a STATSPACK extension table called stats$vmstat.  We always want to make sure that this script is running, and the following script verifies that it is executing.

run_vmstat.ksh
#!/bin/ksh

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

vmstat=`echo ~oracle/vmstat`
export vmstat
ORACLE_SID=`cat ${vmstat}/mysid`
export ORACLE_SID

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

#----------------------------------------
# If it is not running, then start it . . .
#----------------------------------------
check_stat=`ps -ef|grep get_vmstat|grep -v grep|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -le 0 ]
 then nohup $vmstat/get_vmstat_linux.ksh > /dev/null 2>&1 &
fi

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.