Five Timesaving Unix Commands for Oracle DBAs
by Donald K. Burleson01/15/2001
My book Unix for Oracle DBAs Pocket Reference is the result of my trying for more than 20 years to memorize all of the different Unix commands that a DBA (database administrator) is required to know. As a consultant, I am asked to tune databases on every dialect of Unix, and it is a constant challenge to remember the commands that I need to perform my work. This article presents a sampling of my favorite scripts from the book.
A "Change All" Command for Unix
The script shown in this section does a search and replace in all files in a directory, replacing one string with another. If I have a Unix directory with hundreds of files and I need to change the ORACLE_SID in each file, this script can make all the changes in seconds. It also creates a backup directory with the original contents of every file that it changed. This script has saved me hundred of hours of tedious editing.
#!/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
The for loop that you see in the script causes the sed command to be executed for each file in the current directory. The sed command does the actual search and replace work, and at the same time writes the new versions of any affected files to a temporary directory.
To use this script, place the code shown here into a file named chg_all.sh. Whenever you want to make a global change, start by editing the old and new strings in the script file. Then, when executing the script, pass in a file mask as an argument. For example, to only change SQL files, you would execute the command like this:
root> chg_all.sh *.sql
When the script completes, the string substitution that you requested will be done, and you'll be left with a directory named tmp.old, which contains the original versions of any modified files.
A Script to Check Oracle Values on Hundreds of Databases
One thing I always needed for Unix was a way to run the same SQL*Plus command on every database, and even databases on other servers. I had a manager who wanted to know the default optimizer mode for every database at a shop that had over 150 databases on 30 database servers. The manager allotted me two days for this task, and he was quite surprised when I provided the correct answer in ten minutes. I did it using the following script:
|
Related Reading 
Unix for Oracle DBAs Pocket Reference |
# 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 -d':'"`
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
This script requires the Unix remote shell (rsh) privilege so that it can bounce quickly between servers. You do this by making entries into your .rhosts file. The script will loop though all of the server names defined in the .rhosts file on your system, and will then loop through each database listed in each server's /etc/oratab file.
You can use this script to check any database values or to run any SQL*Plus script. You quickly can get user reports, performance statistics, and a wealth of information on every database in your enterprise. I have also used variations on this script to delete old trace files from the Oracle directories and to check free space in archived redo log filesystems. This script has saved me many hours of repetitive work executing the same command against many databases.
A Fast Way to Change Oracle Environments
One of the most confounding problems when working in a large shop is the need to change between Oracle environments quickly. It seems that everyone has a different method for doing this, and it is always hard to remember the differences between servers. It's even harder when a server is running different versions of Oracle.
My solution is to install a standard .profile script on every server. This .profile is executed when I sign on to the server, and it automatically makes an alias for each database instance, which is the same as the Oracle SID [spell out?] name. When I enter the Oracle SID name at the Unix prompt, my entire Unix environment is reset for the new database. The following code is what I place in my .profile file:
for DB in `cat /etc/oratab|grep -v \#|grep -v \*|cut -d":" -f1`
do
alias $DB='export ORAENV_ASK=NO; \
export ORACLE_SID='$DB';\
. $TEMPHOME/bin/oraenv; \
export ORACLE_HOME;\
export ORACLE_BASE=\
`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`;\
export DBA=$ORACLE_BASE/admin;\
export SCRIPT_HOME=$DBA/scripts;\
export PATH=$PATH:$SCRIPT_HOME;\
export LIB_PATH=$ORACLE_HOME/lib64:$ORACLE_HOME/lib '
done
Now, if I want to switch my environment to the PROD database, I simply type in PROD as a command at the Unix command prompt.
Note that for Solaris you need to change the oratab directory name from /etc to /var/opt/oracle.
A Package of Valuable Unix Aliases
When I am paged at 3:00 A.M. to fix a production problem, I am not in a position to remember the locations of all the Oracle alert log files and trace file directories. To make things simple and uniform, I always include a list of standard aliases in my Unix .profile file. For example:
# Aliases # alias alert='tail -100\ $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more' alias arch='cd $DBA/$ORACLE_SID/arch' alias bdump='cd $DBA/$ORACLE_SID/bdump' alias cdump='cd $DBA/$ORACLE_SID/cdump' alias pfile='cd $DBA/$ORACLE_SID/pfile' alias rm='rm -i' alias sid='env|grep ORACLE_SID' alias admin='cd $DBA/admin'
All these aliases provide easily remembered shortcuts to longer commands. The alert alias, for example, equates to the following much longer command:
tail -100 $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more
With these aliases, I can always see the most recent entries in the Oracle alert log by simply typing alert at the Unix prompt. To go to the location of the Oracle archived redo log directory, I just type arch.
A Script to Capture Server Statistics into an Oracle Table
One of the most problematic issues when tuning an Oracle database is knowing what is happening on the database server when a performance problem occurs. To remedy this issue I created a script that will capture the output from the Unix vmstat command and store the server metrics into an Oracle table named mon_vmstats. Here is the script:
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=BURLESON
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|\
grep \^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/mon`
export MON
SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME
# sample every five minutes (300 seconds) . . . .
SAMPLE_TIME=300
while true
do
vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$
# This script is intended to run starting at
# 7:00 AM EST Until midnight EST
cat /tmp/msg$$|sed 1,4d | awk '{ \
printf("%s %s %s %s %s %s %s\n", $1, $6, $7,\
$14, $15, $16, $17) }' | while read RUNQUE\
PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU\
IDLE_CPU WAIT_CPU
do
$ORACLE_HOME/bin/sqlplus -s / <<EOF
insert into mon_vmstats values (
sysdate,
$SAMPLE_TIME,
'$SERVER_NAME',
$RUNQUE,
$PAGE_IN,
$PAGE_OUT,
$USER_CPU,
$SYSTEM_CPU,
$IDLE_CPU,
$WAIT_CPU
);
EXIT
EOF
done
done
rm /tmp/msg$$
This script runs the vmstat utility for five-minute elapsed periods, and stores the information in the mon_vmstat table. From the data in this table, I can extract server performance statistics and create wonderful server performance graphs. For example, I created the following graph of page-in activity by copying and pasting the data into Microsoft Excel. The graph shows page-in activity during three different time intervals over a period of several months.
These are just some of the timesaving scripts I have included in my book, Unix for Oracle DBAs Pocket Reference. There's a lot of power in Unix, and it's power that you can leverage to make your work easier.
Donald K. Burleson is one of the world's leading database authors with 9 books on database management and more than 50 articles in national magazines. He has more than 20 years of experience as a database administrator, and currently is the editor in chief of Oracle Internals. Don's Web site is www.dba-oracle.com, and he can be reached at burleson@frontiernet.net.
In addition to being an Oracle DBA and author, Don Burleson is also a pioneer in use of miniature horses to guide the blind. Don and his wife, Janet, have more than 30 years of experience training horses and have developed a technique for training miniature horses to lead visually impaired people. Together, Don and Janet have founded the Guide Horse Foundation as a charity to provide these animals to blind people across North America.
O'Reilly & Associates will soon release (January 2001) Unix for Oracle DBAs Pocket Reference.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
-
For more information, or to order the book, click here.
Return to: oracle.oreilly.com
