The following scripts are a collection of useful scripts that will make Oracle administration easier in a UNIX environment. Once the Oracle DBA is comfortable with UNIX programming, there are many great DBA utilities that can be made by combining UNIX commands into scripts. In a large UNIX environment, it is critical that all UNIX servers use the same environmental setting, and the same .profile file for the UNIX oracle user. This chapter covers the following topics.

* Managing the UNIX environment for the oracle user

* File management commands in UNIX

Managing the UNIX environment for the Oracle user

The Oracle DBA will commonly perform all of their UNIX work when signed-on to the UNIX server as the oracle user.  In large UNIX environments with hundreds of Oracle servers and dozens of Oracle DBA’s, it is critical that all UNIX servers have a common look-and-feel.  This is done by creating the following constructs.

* Standard UNIX prompt for the Oracle user

* Standard command editor

* Standard alias for moving between Oracle directories

* Standard alias name for each $ORACLE_SID

Let’s take a look at commands that can be placed into the UNIX .profile script for the oracle user.  Most large UNIX shops create a standard .profile scripts for all servers and use the UNIX rcp command to distribute the .profile to every Oracle server.

Here is an example of a UNIX script to distribute a standard .profile to every Oracle server.

distr_profile.ksh
#!/bin/ksh 

echo 'starting distribution of .profile file' 

#*******************************************************************
# We reply on the UNIX /etc/hosts file for a list of Oracle servers
#******************************************************************* 

for host in `cat /etc/hosts|awk '{ print $2 }'`
do
   echo  starting distribution to $host
   rcp -p .profile $host:~oracle/.profile
   rsh $host ls –al ~oracle/.profile
done

In this script we look up each Oracle server name from the /etc/hosts files.  Then we loop between each server, using the remote copy command (rsh) to copy our standard .profile file to every server.  We verify that the copy was successful by using the UNIX rsh command to verify the UNIX time and file size for the .profile file.  Note that you may need to get your UNIX systems administrator to configure the .rhosts files to allow the rsh and rcp commands to work properly.

A Standard UNIX Prompt

Placing the following setting in your .profile will give you a UNIX prompt that identifies your current server name, the current database name and current working directory.

This important setting ensures that your Oracle DBA always knows what server and database to which they are connected.  More important, the Oracle UNIX DBA can easily see their current working directory while still giving the use a full line to enter UNIX commands.

Useful UNIX Aliases for Oracle

This is a list on common UNIX aliases that can be added to the .profile of the UNIX Oracle user.  There are two compelling reasons to use these standard aliases.

In large environments, you may find Oracle file systems that were not installed according to the Oracle Optimal Flexible Architecture (OFA) standard.  This is especially true for vendor-installed Oracle systems.  The use of standard aliases eliminates the file hunting that commonly occurs on a foreign Oracle server.

For example, one installed the alert alias will reliably display the most recent 100 lines of the alert log file, regardless of the actual location of the file.

To illustrate, here we use the pfile alias to quickly change to our UNIX directory that contains the init.ora file:

Standard aliases for changing the ORACLE_SID

In environments with older copies of Oracle, we cannot always rely on the setenv utility to properly change the $ORACLE_SID.  To get around this issue, we can place a loop in our .profile file to loop through every database on our server, creating a UNIX alias with the same name as the $ORACLE_SID.

#*************************************************************
# Create an alias for every $ORACLE_SID on the UNIX server  
#*************************************************************
for DB in `cat /var/opt/oracle/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/lib:$ORACLE_HOME/lib:/usr/lib '
   done

Once this code is executed, you can use the alias command to quickly see the alias for all of the $ORACLE_SID values on your server.

Once these values are set, we can quickly change our UNIX environment to another $ORACLE_SID.  In the example below we change to the test9i database:

Standard command history

It is very important to use a common set of UNIX command history and command editing keystrokes.  By adding the following command to your .profile, you instruct UNIX to use the vi command editor from your UNIX prompt

Having a common editor between Oracle servers ensures the common look-and-feel regardless of the current Oracle server.

Next, let’s move on a take a closer look at managing UNIX files in the Oracle environment.

File Management Commands in UNIX

There are numerous commands that are required by the Oracle DBA when managing the Oracle files in UNIX.  While we reviewed the basic UNIX file management commands in Chapter 1, we are now ready to look at advanced UNIX commands for the Oracle DBA.