As Published In Special Edition for Linux 2006

Delve into Shell Scripts
By Casimir Saternos

Whether you're an experienced Linux DBA or just learning, scripts can be your best friends.

About eight years ago, Oracle released the first commercial database on Linux. Since then, Oracle, Red Hat, and Novell/SUSE have been steadily collaborating on changes to the Linux kernel as they relate to database and application performance. For that reason, Oracle Database 10g for Linux includes enhancements that are closely related to the operating system. Now more than ever, DBAs need to have knowledge of and experience on this platform to best administer the systems under their watch.

There is a traditional division of responsibilities between sysadmins and DBAs. In practice, however, the distinction is not always clear. Many IT shops employ individuals who address concerns at both the database and operating system levels. And of course the Oracle Database itself uses operating system resources and is designed to interact closely with its environment.

Furthermore, many sysadmins and DBAs find it necessary or convenient to automate tasks related to their work. The installation of software, monitoring of system resources, and management of systems involve repetitive and error-prone tasks that are better addressed through automated processes than manual procedures.

One method that is used to automate such tasks is shell scripting. Shell scripts play a significant role in the Linux system from the time it is installed. Various scripts are called when the system is started up and shut down. Utilities from Oracle and third-party vendors are invoked through shell scripts. Because they can be developed quickly, they have historically been used for prototyping applications. Sysadmins have taken advantage of the functionality available through shell scripting to provide solutions that are tailored for the particular requirements and idiosyncrasies of the systems under their watch.

What Is a Shell Script?

A shell script is simply a text file containing a sequence of commands. When you run the file—or script—it executes the commands contained in the file. The term shell simply refers to the particular command-line user interface you use to communicate with the Linux kernel. Several different shells are available, including the C shell (csh), Korn shell (ksh), Bourne shell (sh), and Bourne-Again shell (bash). The shell itself is a command that reads lines from either a file or the terminal, interprets them, and generally executes other commands. The Bourne-Again shell incorporates features of the other shells mentioned and is the one that was used for the scripts this article demonstrates.

The first line in the script file can be used to dictate which shell will be used to run the script. This is the purpose of the first line in all of the script examples:

#!/bin/bash

Why Use Shell Scripts?

Depending on your background, you may not see any immediate value to shell scripting as it relates to the DBA's work. If you do not have experience with UNIX or UNIX-like systems, the myriad of cryptic commands might be cause for concern. Besides, in addition to being a relational database, Oracle Database 10g provides a robust platform for processing data within the database as well as several methods of interacting with the OS outside of the database.

However, there are several reasons you might find yourself delving into the world of shell scripting, including the following:

• You are in a situation in which you must support already-existing scripts.
• You want to automate system setup that occurs prior to the installation of the Oracle software. For instance, you could write a script to check the initial state of the OS and report any prerequisites that must be met before installation of the software. The script might also create relevant OS users and groups and set environmental variables for the users.
• A running Oracle database can be used to execute manual or scheduled tasks. However, some tasks need to be run when the database is not running. You can use a script to stop or start a database (as well as a listener or related database process). Such an action cannot be initiated from within the database itself.
• You need a mechanism for monitoring the state of a database (in other words, that it is running and available to process queries).
• You can call Oracle Recovery Manager from a shell script and use it to perform a wide range of backup and recovery activities.
• You might have a requirement that is not specific to a single database. Perhaps you have several databases on a single machine. It may not be advisable to fulfill the requirement by using a single database, and it introduces potential security issues as well. Under such circumstances, shell scripting provides a means of fulfilling the requirement in a manner that does not associate the process with a single database.

Oracle Database includes functionality that extends beyond the traditional definition of an RDBMS. Like any other piece of software, it uses resources that are provided by the operating system, but it can "see" and "change" its environment to a much greater degree than other software. SQL and Oracle's fixed views provide a picture of the system from inside the database, whereas shell scripting provides a view of the system from outside of the database. Shell scripting is not the solution for every problem.

It is important to recognize that many aspects of the operating system can be monitored and modified from within the database. Oracle's fixed views (the views with a v$ prefix) can be used to determine the host name of the machine (v$instance) or the platform name on which the database is running (v$database). The location and other attributes of the files associated with the database can be determined in this manner as well. The location and other attributes of datafiles (v$datafile, dba_data_files), temp files (v$tempfile, dba_temp_files), redo logs (v$logfile), archive logs (v$archived_log), and control files (v$controlfile) can be queried directly from the database. You can determine information about the flash recovery area ($recovery_file_dest) from this view, as well as by looking at some init.ora parameters (db_recovery_file_dest, db_recovery_file_dest_size). The status of processes (v$process) and memory (v$sga, v$sgastat, and so on) can be queried as well. There are various built-in PL/SQL packages as well as the ability to create Java and C database objects that allow for additional access to the underlying OS.

If you are considering scripting for a task that requires a good deal of database access, scripting is probably not your best option. In that case, you are better off approaching the problem by using another language.

Now, let's look at some examples of bash and Oracle scripting.

Bash Scripting

Scripts are either called as part of an automated process (with no human intervention) or run interactively, with a user responding to prompts. As long as you have executable permission for a file, you can run it from the command line by typing its name. If you do not have executable permission for the file but do have read permission, you can run the script by preceding it with sh.

If a script is designed to be run without user input, several options are available for calling it. You can run a script in the background, and continue even if you disconnect, by entering a command in the following form:

nohup /path_to_dir/myscript_here.sh &

This can be useful for running scripts that take a long time to complete. The at command can be used to execute a script in the future, and cron can be used to schedule scripts to execute on a recurring basis.

The following scripts cover some essential aspects of providing output to view (using echo), looping, conditional logic, and variable assignment.

print_args.sh. Arguments are words to the right of the command name that are passed into the script. To access the first parameter, you use the $1 variable. The $0 variable contains the name of the script itself. The $# variable contains the number of arguments in the script. A handy way to iterate through all of the parameters passed involves the use of a while loop and the shift command. This command is what lets you iterate through all the arguments in the argument list (rather than remaining in an infinite loop):

while [ $# -ne 0  ]
do
        echo $1
        shift
done

If a script takes a filename as an argument (or prompts a user for a filename) and the file will be read later in the script, it is advisable to check whether it is accessible and readable. For example, a recovery script that involves the selection of a backed-up control file might prompt the user to make a selection that will be used later in the script to restore the file:

if [ ! -r $1 ]; then # not exists and is 
readable
echo "File $1 does not exist or is not 
readable."
exit;
fi      

This sequence of characters actually performs the test: if [ ! -r $1 ];. If the contents between the brackets evaluate to TRUE, the commands that appear between if and fi will be executed. The actual test appears between the brackets. The exclamation point serves to negate the test you are doing. The -r option checks to see if the file is readable. What is being tested in this particular case is the first argument being passed to the script. By using a different test (-d), you can check to find out if a given entry is a directory.

do_continue.sh. This script is a simple representative sequence of commands that can be used to read user input for various purposes. Before running a process that can result in data loss or other undesirable results under certain conditions that are not determinable from within the script, it is advisable to include a prompt asking if the user actually wants the script to execute the next command or commands.

The following example asks if the user wants to continue, reads a variable named doContinue from the command line, and evaluates what the user entered. If the user enters anything other than "y", that person is informed that the script is "quitting" and it exits without executing the remainder of the script following the end of the if block (fi):

doContinue=n
echo -n "Do you really want to 
continue? (y/n)" 
read doContinue

if [ "$doContinue" != "y" ]; then
      echo "Quitting..."
      exit
fi

It is imperative that only users with the correct permissions and environment run a given script. A useful check in a script tests the user who is attempting to run the script. If you enclose a command within back-quote (') characters, the results of the command can be returned to the script. The following example retrieves the currently logged-on user, by using whoami, and displays the date, by using the date command later in the script:

echo "You are logged in as 'whoami'";

if [ 'whoami' != "oracle" ]; then
  echo "Must be logged on as oracle to run this script."
  exit
fi

echo "Running script at 'date'"

Scripts written to interact with Oracle Database sometimes require the entry of sensitive information such as a database password. The stty -echo command turns off the screen echo, so that the information entered for the subsequent read command will not appear on the screen. After the sensitive information has been read and stored in a variable (pw in the example below), the display can be turned back on with stty echo:

stty -echo    
        echo -n "Enter the database system password:  "
        read pw
stty echo

Oracle Scripting

Some files affected by these scripts reside in a fixed location for a given Oracle install. You can determine the Oracle inventory by viewing the /etc/oraInst.loc file. The /etc/oratab file identifies databases (and other Oracle programs) installed on the server.

search_log.sh. A variety of logs are generated by Oracle products, and you might be interested in monitoring them. The database alert log contains messages that are critical to database operations. Log files are also generated when products are installed or deinstalled and when patches are applied.

The following script iterates over a file passed to it as an argument. If any lines are found that contain ORA-, an e-mail message is sent to a designated recipient:

cat $1 | grep ORA- > alert.err

if [ 'cat alert.err|wc -l' -gt 0 ]
then
        mail -s "$0 $1 Errors" administrator
@yourcompany.com < alert.err
fi      

Next Steps READ an expanded version of this article including examples and sample scripts oracle.com/technology/pub/articles/saternos_scripting.html

The specific test being performed is a count of the number of words that exist in the file alert.err, which is written when you redirect to alert.err. If the word count (wc) is greater than (-gt) zero, the contents of the if block will execute. In this case, you are using mail (send mail might also be used) to send a message. The title of the message contains the script being executed ($0), the name of the log being searched ($1), and the lines that matched our initial search (ORA-) as the body of the message.

Environmental variables such as ORACLE_HOME, ORACLE_BASE, and ORACLE_SID can be used to locate resources that are not in a fixed location in the Linux environment. If you are administering an Oracle E-Business Suite 11i application instance, you have numerous other environmental variables that can be used to locate resources. These include APPL_TOP, TWO_TASK, CONTEXT_NAME, and CONTEXT_FILE, to name a few. To see a complete list in your environment, execute the following command:

env > myenv.txt

Various combinations of these environmental variables can be used as the location of a file being searched. For example, an alert log location might be designated as $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log.

Based on the principles introduced in this script, a larger one can be written and scheduled to execute at periodic intervals that will search the contents of the alert log (or another file of interest) and send an e-mail if any errors exist. Then the contents of the log can be moved to another file, so that only the most-recent error messages will be sent via e-mail.

Database installation. Many of the steps involved in the setup of a database can be automated. Before you install Oracle Database 10g on Linux, you need to run various tests to verify the minimum required version of packages and the settings of kernel parameters. You can query the version of a package by using the rpm command with the -q option:

rpm -q compat-libstdc++

You can determine various aspects of the system by looking at the /proc "virtual" or "pseudo" file system. It contains not real files but, rather, runtime system information that can be viewed as if it resided in files. For instance, /proc/meminfo contains memory information for the system, and grep MemTotal /proc/meminfo displays the total memory of the system. By using awk, you could isolate the amount of memory in kilobytes, as follows:

grep MemTotal /proc/meminfo | awk '{print $2}'

Such a command could be used in the context of a script that would do comparisons and respond accordingly (even updating the system itself).

Conclusion

Shell scripting can be an effective tool for quickly automating repetitive and error-prone administration tasks. However, every system has distinct quirks and foibles and a unique configuration. An administrator must develop unique solutions to meet the needs of the particular system.