Building Oracle UNIX Commands into Scripts Administration Oracle UNIX/Linux Tips by Burleson Consulting

How to build UNIX Commands into Scripts

We know that the UNIX -exec and xargs commands will apply a single UNIX command to a list of inputs, let?s look at how we can quickly generate a UNIX script to perform a powerful task. Suppose we need a UNIX script that searches every file on the UNIX server for a file that contains a reference to the dba_2pc_pending view. 

STEP 1 ? We begin by writing a command that will display all file names on the server.  This is quite simple in UNIX. And returns a list of every file on the server:

root> find . -print
./sql/pl2.sql
./sql/plcur.sql
./sql/check.ksh
./sql/mts.lst
./sql/fix_ts.sql
./sql/d.sql
./tools/sessions.sql
./tools/connections.sql
./tools/flush_shared_pool.sql
./tools/currently_running.sql
./tools/inval_obj.sql
./tools/recomp.sql
./tools/sqlnet.log
./statspack/r.ksh
./statspack/rpt_8i_bhr_dy.sql
./tmp/t1.sql
./tmp/t2.sql
./tmp/t3.sql
./tmp/n3.sql
./tmp/i2.sql
./ext_routines/common.mk
./ext_routines/common_OCI.c
./ext_routines/common_PROC.pc

STEP 2 ? We now have a complete list of all of the UNIX files.  Because we know that the grep commands accepts file names as an argument, we can use UNIX xargs command to search each file for our Oracle table name in all sql files.  In this case, we search the whole Oracle UNIX directory structure and quickly find all scripts that reference the dba_2pc_pending view.

root> find . -print|grep sql|xargs grep -i dba_2pc_pending
./sql/PENDING.sql:from   dba_2pc_pending

This ability to take a basic UNIX command and chain it with other commands is a fundamental principal in UNIX shell programming for Oracle.

Miscellaneous UNIX tips and techniques

This section covers miscellaneous tips and techniques for improving the reliability and functionality of your UNIX scripts for Oracle.

Add the UNIX date to an Oracle file name

In UNIX you can simple enter the date command to see the current date.  You can capture this command inside a UNIX script and use the date as part of a UNIX file name. The following example shows how you can call the UNIX date function with date masks to change the display values according to your needs.

      YEAR=`date +"%Y"`
      MONTH=`date +"%m"`
      DAY=`date +"%d"`
      HOUR=`date +"%H"`
      MINUTE=`date +"%M"`

In some cases we want to record the current UNIX date inside an Oracle log file. Here we place the date into a log file, indicating the exact time that a UNIX task ended.

echo End `date` > /usr/local/bin/scripts/logs/tar_end.lst

Below is a cleaver command that uses the UNIX tee command in conjunction with the UNIX date command.  Note that it appends the current UNIX date and also uses the tee command to direct the output both to standard out as well as the progress.log file.

echo "Rebuild start for ${TABLESPACE}. . "\
     `date +"%H:%M "`|tee -a progress.log

Here is another handy UNIX script for check files with specific dates embedded in the UNIX file name. Once you write files with the UNIX date, you can check UNIX files based on date. The script below takes the current data and display all files in the /u01/backup/logs directory that have the current day and month.

mdate=`date`
mday=`echo $mdate|awk '{print $3}'`
mmonth=`echo $mdate|awk '{print $2}'`

echo  month $mmonth
echo  day $mday

ls -alt /u01/backup/logs|grep `echo $mmonth $mday`

Monitor the performance of Oracle parallel server

Here is a handy script to capture DLM performance in an OPS environment and place the output inside an Oracle table for tracking.  Note the sophisticated use of the UNIX date function in this script.

This script checks the OPS values for locks, resources and processes using the UNIX Oracle lkdump command.  Once we have gathered these values, we direct them to the $RPT_FILE UNIX directory and also write them to STATSPACK extension table stats$dlm_stats.

DAY_OF_WEEK=`date +"%A"`
MACHINE_NAME=`hostname`
RPT_FILE=/u01/MYSID/reports/dlm_monitor.${MACHINE_NAME}.${DAY_OF_WEEK}.log

# \
#   Set up the file to log the lock to:
# /
TIMESTAMP=`date +"%C%y.%m.%d-%H:%M:%S"`
DLM_RESOURCES=`/oracle/MYSID/bin/lkdump -a res | head -2 | awk 'getline'`
DLM_LOCKS=`/oracle/MYSID/bin/lkdump -a lock | head -2 | awk 'getline' `
DLM_PROCESS=`/oracle/MYSID/bin/lkdump -a proc | head -2 | awk 'getline'`
printf "$TIMESTAMP  $DLM_RESOURCES  $DLM_LOCKS  $DLM_PROCESS \n" >>
RPT_FILE

RES=`echo $DLM_RESOURCES|cut -f2 -d '='`
LOC=`echo $DLM_LOCKS|cut -f2 -d '='`
PRO=`echo $DLM_PROCESS|cut -f2 -d '='`

ORACLE_SID=MYSID; export ORACLE_SID;
PATH=$PATH:/oracle/MYSID/bin; export PATH;
ORACLE_HOME=/oracle/MYSID; export ORACLE_HOME;

$ORACLE_HOME/bin/sqlplus <<! >> /dev/null

connect system/manager;

insert into perfstat.stats$dlm_stats
 values (
   SYSDATE,
   $PRO,
   $RES,
   $LOC );

exit;
!

UNIX gotcha ? Clobber your entire UNIX server

When folks talk about UNIX security and the tools to prevent mishaps, I am often reminded just how fragile the UNIX environment can be. Most of the job of the UNIX Oracle DBA is keeping the database running, and it does not come as a surprise when they see how easy it is to clobber a server.

The script below cripples the UNIX server by an implosion of incoming jobs. This is known as a Denial Of Service (DOS) attack, and the Oracle DBA should be aware just how easy it is for a hacker to make a mess in UNIX.

Interestingly, the use of  a DOS attack is not new and mainframe programmers have known since the 1960?s how to clobber a mainframe job initiator. You can do the same kind of attack on IBM mainframes by creating a recursive IEFBR14 job that directs its SYSOUT to the mainframe internal reader. Here is a section of JCL that illustrates the recursive job submission technique.

//EXEC  IEFBR14
//DD SYSIN=IBM.MISC(CRASH),DISP-SHR
//DD SYSOUT=(,,INTRDR)

Beware that the following command will clobber any UNIX server in a matter in just a few seconds. 

WARNING - This script is provided for illustration purposes only and it should not be run in a production environment.

nohup /tmp/recursive.ksh > /dev/null 2>&1 &
The recursive.ksh file submits two of the same tasks.
nohup /tmp/recursive.ksh > /dev/null 2>&1 &
nohup /tmp/recursive.ksh > /dev/null 2>&1 &

Because the job submits itself, two jobs submit 4, four submit 16, and so on, until the entire server in unable to accept any work.

Again this script is described as a warning to any cocky Oracle DBA who assures management that their UNIX server is impervious to DOS attacks.

Looping between UNIX Servers

UNIX provides a for command that can be used to loop for a specific number of iterations. When the for command is used to read the /etc/hosts and /etc/oratab files, you can write a script that loops from server-to-server, and database-to-database.

This is especially useful where all UNIX servers are ?trusted?, by allowing remote shell (rsh) commands.  This is done by making an entry in the .rhosts file for your ORACLE UNIX user. This is called a nested loop construct, and is illustrated my the pseudocode below.

FOR every server defined in .rhosts
     FOR every database defined in the server?s /etc/oratab
            connect to SQL*Plus
     END
END

This is a very powerful construct, and it can be used to visit every database in your enterprise. In the following example, we perform a double loop, looping from server to server.  For each server, we loop from database to database. Here is the actual UNIX code that perform the loops.

# Loop through each host name . . .
for host in `cat ~oracle/.rhosts|cut -d"." -f1|awk '{print $1}'|sort -u`
do
  echo " "
  echo "************************"
  echo "$host"
  echo "************************"
  # Loop through each database name on the host /etc/oratab . . .
  for db in `rsh $host "cat /etc/oratab|egrep ':N|:Y'|grep -v \*|\
  cut -f1 -':'"`
  do
     # Get the ORACLE_HOME for each database
     home=`rsh $host "cat /etc/oratab|egrep ':N|:Y'|grep -v \*|\
     grep {db}|cut -f2 -d':'"`
     echo " "
     echo "database is $db"
     sqlplus system/manager@$db <<!
     select * from v\$database;
     exit;
!
  done
done

In the example above we connected to SQL*Plus using a Net8 connect string for the remote database.  However, we can also use the UNIX rsh command to directly connect to the SQL*Plus facility on the remote UNIX server.

Let?s take a look at how this works.

Executing SQL*Plus on all remote UNIX servers

We can expand upon this approach and write a script to visit SQL*Plus on every server and every database on each server.  In the example below, we select the name of every database on every server, and display the optimizer_mode for every database.

# Loop through each host name . . .
for host in `cat ~oracle/.rhosts|cut -d"." -f1|awk '{print $1}'|sort -u`
do
    echo " "
    echo "************************"
    echo "$host"
    echo "************************"
    # loop from database to database
    for db in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|\
    grep ${db}|cut ?f1 -':'"`
    do
        home=`rsh $host "cat /etc/oratab|egrep ':N|:Y'|\
        grep -v \*|grep {db}|cut -f2 -d':'
        "`echo "************************"
        echo "database is $db"
        echo "************************"
        rsh $host "
        ORACLE_SID=${db}; export ORACLE_SID;
        ORACLE_HOME=${home}; export ORACLE_HOME;
        ${home}/bin/sqlplus -s /<<!
        set pages 9999;
        set heading off;
        select value from v"\\""$"parameter where name='optimizer_mode';
        exit
        !"
    done
done

In this example, we can quickly get a report of the Oracle optimizer_mode value for all databases in out enterprise.  This can make tasks that might take hours into a simple task that takes just a few seconds.

 

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.