Common Oracle UNIX Administration Commands Oracle UNIX/Linux Tips by Burleson Consulting

Common Oracle UNIX commands

Now that we understand the fundamentals of UNIX commands, let?s begin examining common UNIX commands that are used by the Oracle DBA.  Many of these commands are used to automate Oracle reporting and make Oracle management simple.

Capturing server information with UNIX

For the Oracle DBA who performs reporting functions on multiple servers, capturing the server name is very important.  This is especially true in cases where several databases with the same name exist in several servers, and the report must show the server name to properly identify the database.

The most common command to gather server information is the uname ?a command.  Here is an example of the output from this command in Solaris UNIX.

root> uname -a
SunOS goofy 5.8 Generic_103634-03 sun4u sparc SUNW,Ultra-80
Here is the uname ?a command on a HP/UX server:
root> uname -a
HP-UX penguin B.10.20 A 9000/871 2639229148 two-user license

Here we see that the first column is the UNIX dialect (SonOS, HP-UX), the server name (goofy, penguin), the version of the OS, the serial number of the server, and miscellaneous server information.

If we just need the server name for an Oracle report, we can extend the uname ?a command to capture the server name by using the awk utility and parsing-out the first column of output:

root> uname -a|awk '{ print $2 }'
goofy

UNIX also provides the hostname command to display the server name.

root> hostname
goofy

Next, let?s look at how the wc command can be used to monitor the frequency of events in your Oracle database.

The UNIX wc command

You can use the wordcount command (wc) to display the number of words or lines in a UNIX file.  The wc command is very useful for the Oracle DBA who is looking to monitor the contents of specific messages with the Oracle alert log.

To illustrate, let?s count the number of words in out Oracle alert log

root> cat alert_envtest.log|wc
  108313  741411 5334959

Here we see that our Oracle alert log has 180,313 lines, 741,411 words and 5,334,959 characters.  We can use the wc ?l option to only display the number of lines on a file.

root> cat alert_envtest.log|wc -l
  108313

We can also use wc ?l command to count the number of specific messages in the Oracle alert log. Here we display the text of all ORA-600 errors in our alert .log using the cat and grep commands.

root> cat alert_envtest.log|grep ORA-00600
ORA-00600: internal error code, arguments: [2655], [0], [1], [], [], []
ORA-00600: internal error code, arguments: [16365], [2208470888], [1], [4]
ORA-00600: internal error code, arguments: [16365], [2209886568], [0], [4]
Now, let?s add the wc ?l command to count the number of ORA-600 errors:
root> cat alert_envtest.log|grep ORA-00600|wc -l
       3

Capturing date information in UNIX

It is also a common practice to capture the date from the UNIX server.  Just like Oracle, UNIX dates have a default date format that can be modified according to your needs.

Let?s begin by displaying the default date in UNIX.

root> date
Tue Sep  4 10:29:40 EDT 2001

Changing the date display format involves invoking the date command with a date format mask, very similar to using the Oracle nls_date_format to change Oracle dates (Table 3).

Format	Meaning
%d	Day of the month as a two-digit decimal number [01-31].
%e	Day of the month as a two-character decimal number
%E	Combined Emperor/Era name and year.
%H	Hour (24-hour clock) as a two-digit decimal number [00-23].
%I	Hour (12-hour clock) as a two-digit decimal number [01-12].
%j	Day of the year as a three-digit decimal number [001-366].
%m	Month as a decimal two-digit number [01-12].
%M	Minute as a decimal two-digit number [00-59].
%p	Equivalent of either AM or PM.  For example, PM.
%S	Second as a two-digit decimal number
%t	Tab character.
%u	Weekday as a one-digit decimal number [1-7].
%U	Week number of the year (Sunday as the first day of the week) as a two-digit decimal number [00-53].
%V	Week number of the year (Monday as the first day of the week) as a two-digit decimal number [01-53].
%w	Weekday as a one-digit decimal number [0-6]
%W	Week number of the year (Monday as the first day of the week) as a two-digit decimal number [00-53].
%x	Current date representation.  For example, 01/12/94.
%X	Current time representation.  For example, 19:45:58.
%y	Year without century as a two-digit decimal number [00-99].
%Y	Year with century as a four-digit decimal number [1970-2069].  For example, 1994.
%Z	Time zone name (or no characters if time zone cannot be determined).  For example, PST.

Table 5: Formats for the UNIX date command

As we can see, UNIX has far more date format options than Oracle, and the UNIX date can be displayed in a huge variety of ways.

root> date "+DATE: %m/%d/%y%nTIME: %H:%M:%S"
DATE: 09/04/01
TIME: 09:37:49   

You can also gather the UNIX date from inside a SQL*Plus script.  In the example below, we spool our SQL*Plus output to a file using the UNIX date function:

gen.ksh
#!/bin/ksh

# First, we must set the environment . . . .
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 -s /<<!

spool `hostname`_`date +%d_%m_%y`.lst
select count(*) from dba_data_files;
spool off;

exit
!

After running this script, we can issue the UNIX ls command, and see that the spool file contains the UNIX hostname and the UNIX date:

root> ls -t|head
cheopsdb-02_04_09_01.lst
temp.ksh
get_files.ksh

Next, let?s look at how we can use UNIX commands to manage connected users.

UNIX user identification

The UNIX who command can be used to show all users who are currently signed-on to the UNIX server.  Note that the who command does not show Oracle users who have connected via the Oracle listener:

root> who|head -20
root       ttyp1        Aug 31 19:09
tlmason    ttyp2        Sep  4 08:31
dbogstad   ttyp3        Sep  4 06:33
clarson    ttyp4        Sep  4 07:20
mgeske     ttyp5        Sep  4 06:35
vogden     ttyp6        Sep  4 06:45
crmoore    ttyp7        Sep  4 06:45
yliu       ttyp8        Sep  4 06:47
mbell      ttyp9        Sep  4 06:54
acook      ttypa        Sep  4 06:58
rwestman   ttypb        Sep  4 08:06
eboyd      ttypc        Sep  4 06:58
lhovey     ttypd        Sep  4 07:00
mepeter    ttype        Sep  4 07:10
klong      ttypf        Sep  4 07:02
ldoolitt   ttyq0        Sep  4 07:36
dwilken    ttyq1        Sep  4 08:16    

We can enhance the who command to include a count of all users on our Oracle server. In this example, we see that there are 145 UNIX users connected to this Oracle server.

root> who|wc -l
145

Locating files in UNIX

UNIX provides the which command for finding the location of executable code.  In the following example, we can easily find the location of the SQL*Plus executable:

root> which sqlplus
/u01/home/oracle/product/9.1.2/bin/sqlplus   

For non-executable files, you can use the UNIX find command to locate a particular file.  Please note that in chapter 8 we extend this command to search for all files that contain specific strings:

root> pwd
/
root> find . -print|grep ?i dbmspool.sql
./oracle/product/9.1.2/rdbms/admin/dbmspool.sql  

In the example above, we cd to the root directory (/) and issue a UNIX find command to display every file on the Oracle server.  Next, we pipe the output of the find command to grep, which searches for the dbmspool.sql file.

For more details on file management commands see Chapter 8. Next, let?s look at some common UNIX utility commands.

Additional UNIX utility commands

UNIX provides many extra utilities to help you execute UNIX command for Oracle:

* Grep - The grep command is short for Generalized Regular Expression parser.

* Awk ? The awk name is short for Aho, Weinberger, Kernighan, the folks who created the awk utility.

* Sed ? The sed utility is short for String Editor.  The sed utility is used to replace strings in UNIX files.

Let?s take some brief example of each of these utilities.

Using grep in UNIX

 The grep utility is a great way to find UNIX file that contain specific strings.  It is always a good idea to invoke grep with the ?i option because grep will then find your string regarding of the case of the string.

For example, suppose that you want to find an SQL script that recompiles invalid objects.

root> grep -i invalid *.sql
MKSTDROL.sql:/* create role for 'invalid' users    */
RUNTHEM.sql:  4  WHERE STATUS = 'INVALID'
add_view.sql:         'IV', 'Library Cache
Invalidation',                                   
invalid.sql:Spool run_invalid.sql
invalid.sql:   status = 'INVALID'
invalid.sql:@run_invalid.sql
locks.sql:                'IV', 'Library Cache Invalidation', 

Using awk in UNIX

The awk utility is especially useful for removing a specific column of output from a UNIX command.  For example, suppose we need to create a list of UNIX process ID?s for all Oracle processes on our server.

root> ps -ef|grep -i oracle|awk '{ print $2 }'
23308
25167
12193
25163
12155
24065
24073

Here we start by issuing the ps ?ef command to get a list of all UNIX processes, and then use grep to filter out all processes except this that contain the string ?oracle. 

Finally, we use the awk utility to extract the second column of output.

root> ps -ef|grep -i oracle
  oracle 23308     1  0   May 14 ?        0:06 ora_lgwr_prodb1
  oracle 25167     1  0   Apr 30 ?        0:26 ora_smon_prodc1
  oracle 25163     1  0   Apr 30 ?       41:27 ora_lgwr_prodc1
  oracle 12155     1  0 11:30:43 ?        0:01 oracleprodcars (LOCAL=NO)
  oracle 24065     1  0   Apr 30 ?        0:02 ora_pmon_rman
  oracle 24073     1  0   Apr 30 ?       10:39 ora_ckpt_rman
  oracle 24846     1  0   May 11 ?        0:48 oracleprodc1 (LOCAL=NO)

root> ps -ef|grep -i oracle|awk '{ print $2 }'

23308
25167
12193
25163
12155
24065
24073

Using sed in UNIX

The sed command is used to make global changes to strings in UNIX files.  For example, below we have a utility that will change all strings in a directory from one string to another.  This is sort of a ?change all? utility within UNIX. The format of the sed command is:

sed/oldstring/newstring > new file location

The sed utility always makes a new copy of every file it changes, so special care is required to make an in-place change. Note the sed line in this script where sed changes the old string to the new string in all files in the directory.  Note that this script makes a backup of the files in a tmp sub-directory before issuing the change.

chg_all.sh
#!/bin/ksh

tmpdir=tmp.$$

mkdir $tmpdir.new

for f in $*
do
  sed -e 's/oldstring/newstring/g' < $f > $tmpdir.new/$f
done

# Make a backup first!
mkdir $tmpdir.old
mv $* $tmpdir.old/

cd $tmpdir.new
mv $* ../

cd ..
rmdir $tmpdir.new

Next, let?s take a quick look at various UNIX tools to allow you to view and edit UNIX files.

 

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.