Oracle UNIX Administration Dissecting Complex Commands Oracle UNIX/Linux Tips by Burleson Consulting

Dissecting Complex UNIX commands

UNIX neophytes are often frightened when they see some of the cryptic commands used in UNIX.  However, once you learn to use the pipe command to joining together UNIX commands you will have a very powerful tool for creating one-line commands.  For example, below we see a one-line UNIX command that locates and kills all Oracle processes for a specific Oracle database on a server.

ps -ef|grep "ora_"|grep $ORACLE_SID|-v grep| \
awk '{ print $2 }'|-exec rm –f {} \;

At first glance, this powerful UNIX command appears to be a conglomeration of cryptic letters.  However, upon closer examination we see that this UNIX command is actually a series of commands that are joined together with the “pipe” operator “|”.  When viewed this way, our command can be viewed as a connected list of commands:

ps –ef
|
grep "ora_"
|
grep $ORACLE_SID
|
grep -v grep
|
awk '{ print $2 }'
|
-exec rm –f {} \;

By expanding the command onto separate lines (using the || characters as a delimiter), we can examine each sub-command and see how each successive command refines the output from the prior UNIX command (Figure 5).  Once we see the individual commands that comprise the whole UNIX script, we are ready to begin understanding each component.  From Figure 5 we also see that the result set becomes smaller and more refined with each subsequent command.

Figure 5: Dissecting a complex UNIX command

Deciphering a complex UNIX Command

In this example, we will examine a one-line UNIX command that is used to kill all Oracle background processes for a specified database on the UNIX server.  As an Oracle DBA, there are times when it is necessary to kill all Oracle processes, or a selected sub-set of Oracle processes.  This is a common UNIX script used by an Oracle DBA who wants to kill all Oracle processes when the Oracle database is “locked-up” and the database cannot be stopped with the standard Oracle utilities.

To start, let’s look at the syntax of the UNIX kill command. The basic format of the UNIX kill command looks as shown below, and a single kill command can be used to kill many UNIX processes (PIDs):

root> kill –9 process1 process2 process3

Since we see that the kill command can accept a list of processes, our goal is to gather a list of processes from UNIX and send them as arguments to the kill command. To continue the example, the following command will kill all Oracle processes for your server because of the –exec syntax.

      ps -ef|grep "ora_"|grep -v grep|awk '{print $2}'|-exec kill -9 {} \;  

Let’s take a closer look at the steps within this command:

1- The ps –ef UNIX command displays all active processes on the server.  However, we want to limit our command to only those processes that are related to the Oracle database.

2 – The grep “ora_” command removes all but the Oracle background processes:

root> ps -ef|grep "ora_"

  oracle 13022     1   0   Sep 30      -  0:18 ora_db02_vald
  oracle 14796 42726   0 09:00:46  pts/0  0:00 grep ora_
  oracle 17778     1   0   Sep 30      -  0:14 ora_smon_devp
  oracle 18134     1   0   Sep 30      -  0:37 ora_snp1_vald
  oracle 19516     1   0   Sep 30      -  0:24 ora_db04_prod
  oracle 21114     1   0   Sep 30      -  0:37 ora_snp0_devp
  oracle 28436     1   0   Sep 30      -  0:18 ora_arch_prod

3 – The grep –v grep is used to remove the second line from the above output.  If we don’t specify grep –v grep, the subsequent kill command will kill our own process.  The grep –v is the opposite of grep.  Where grep is used to find strings, the grep –v command is used to exclude lines with the specified string.  In the output below, note that the grep line is now missing from our output:

root> ps -ef|grep "ora_"|grep -v grep

  oracle 13022     1   0   Sep 30      -  0:18 ora_db02_vald
  oracle 17778     1   0   Sep 30      -  0:14 ora_smon_devp
  oracle 18134     1   0   Sep 30      -  0:37 ora_snp1_vald
  oracle 19516     1   0   Sep 30      -  0:24 ora_db04_prod
  oracle 21114     1   0   Sep 30      -  0:37 ora_snp0_devp
  oracle 28436     1   0   Sep 30      -  0:18 ora_arch_prod

4 – We now use the UNIX awk command.  As we have discussed, the awk or the cut commands can be used to extract specific columns from a result set.  In our case, we use awk ‘{ print $2 }’ to get the second column which is the Process ID for these processes.  We now have a list of process ID’s to send to the kill command.

root> ps -ef|grep "ora_"|grep -v grep|awk '{ print $2 }'

13022
17778
18134
19516
21114
28436
28956

5 – Now we have a clean list of process ID’s for the Oracle background processes and we are ready to ship this list to yet another UNIX command.  To ship the list to the next command we pipe the list of PID’s to the UNIX kill command by using the –exec UNIX command.  The –exec command accepts a list as an argument and performs any UNIX command on the input set.  Note: If you are using HP/UX or AIX you can also use the xargs command for this purpose.

ps -ef|grep "ora_"|grep -v grep|awk '{ print $2 }'|-exec kill -9 {} \;

6 – Once we have built the complex command, we can encapsulate the command into a single UNIX alias.

alias nukem = “ps -ef|grep "ora_"|grep -v grep| \
   awk '{ print $2 }'|-exec rm –f {} \;”

Now, entering the alias nukem alias at the UNIX command prompt will invoke your complex command to kill all Oracle background processes.  Of course, the example is for illustration purposes only since a prudent Oracle DBA would never risk assigning such a dangerous command to an alias.

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.