Bash for DBAs – Part 1

Many Oracle databases are running on Linux/UNIX systems, and many DBAs need to write all kind of scripts. Over the years I’ve written probably tens of thousands of script lines (the longest script I remember was about 3000 lines), and almost every time I had the chance to learn something new. Bash is really really sophisticated and complex. One thing I will never forget is a sysadmin at one of my customers who taught me something new. When I asked him how he knows all of this bash stuff, he simply pointed at a book and said “read this and you’ll know everything as well”. It was a book containing the entire bash manual (the same as running “man bash”), and it was much larger than I would have expected. I have to admit that I haven’t read the entire manual yet…

I decided to write this series of posts for those DBAs who use bash but want to learn some more options and functionality of this wonderful shell. I’ll write some tips and tricks, some explanations and some script samples. I hope you’ll find it useful. Please note that this is targeted to people who already write bash, and is not an introduction to bash. I will include database specific issues as well as non-database specific issues that I find useful.

Parameters

We all use parameters in bash, but many don’t know that bash has many different options when using parameters. Default values, search and replace and case change are some of the options. Everything can be done in other ways as well, but using the parameter expansion options is very quick and done at the bash level. In order to use these features, we have to enclose the parameter name in curly brackets “{}”. I use these brackets every time I use a parameter because this is a best practice. some things won’t work when you don’t use them.
Here are some examples:

# the importance of curly brackets
$ param=myfile
$ echo $param
myfile
$ echo ${param}
myfile
$ echo $param_name
$ echo ${param}_name
myfile_name
# default value
$ input=filename
$ myfile=${input:=def_filename}
$ echo ${myfile}
filename
$ unset input
$ myfile=${input:=def_filename}
$ echo ${myfile}
def_filename
# substring
$ mydate=01-JAN-2016
$ echo "month is ${mydate:3:3}, year is ${mydate:7:4}"
month is JAN, year is 2016
# substring substitution
# (the % means replace only at the end of the string)
$ filename=myfile.txt
$ echo "Logfile is ${filename/%txt/log}"
Logfile is myfile.log
# change to upper case
$ user=public
$ echo ${user^^}
PUBLIC

There are many other things as well, but I don’t want to list all of them. If you go to the manual, simply search for the part titled “Parameter Expansion”.

Exit Code

Exit code is a very important thing in scripting. Every command we execute returns an exit code, and we should use this exit code to understand if the command was successful or not and continue accordingly. After every command I use, even sqlplus (which I’ll discuss later), I check the exit code and stop with a message if something is wrong.
To check the exit code, simply use the parameter $? (dollar sign, question mark). The parameter returns the exit code of the last command, a value of zero means a successful execution, any other value means failure. The value sometimes helps to understand what the problem was, depends on the command. We should also use exit code from functions (which is actually return code, as exit ends the script).
Note that when using pipes (comm1 | comm2), $? returns the exit code of the last command in the pipe. To check exit code for other commands, we should use the PIPESTATUS bash array.
Examples:

# exit code
$ ls -l exist
-rw-r--r-- 1 root root 0 Jun  9 17:51 exist
$ echo $?
$ ls -l non_exist
ls: cannot access non_exist: No such file or directory
$ echo $?
2
# using pipe, no errors
$ ls -l exist | sed -e 's/exist/replace/g'
-rw-r--r-- 1 root root 0 Jun  9 17:51 replace
# error from the ls command, but the exit code is from the sed
$ ls -l non_exist | sed -e 's/exist/replace/g'
ls: cannot access non_exist: No such file or directory
$ echo $?
# get the exit code of the first command, the ls in this case
$ ls -l non_exist | sed -e 's/exist/replace/g'
ls: cannot access non_exist: No such file or directory
$ echo ${PIPESTATUS[0]}
2

Printing information

When we write a script, sometimes we wish to print status or output from the script. What I like to do is to print only some informative information to the screen and/or log file, and if I want to have more information, print it separately into a trace file.
This is how it looks on the screen:

Doing something... Done

In order to do that I use ‘echo -n “Doing something… “‘ which prints “Doing something… ” but doesn’t start a new line, then I run the commands (with redirection to a log file or trace file so they won’t be printed to the screen) and then use ‘echo “Done”‘ which prints “Done” and start a new line.
Another thing I use is the “tee” command, which prints to the screen (standard output) and to a log file at the same time (use tee -a for append). That way I print the basic stuff to the screen and log file and the other stuff to a trace file.
Example:

echo "Print to both screen and logfile" | tee $logfile

Running commands within commands

Sometimes we want to run commands within other commands. To do that we will enclose the inner command in either backticks ““” or dollar sign and parentheses “$()”. The latter is the best practice (mainly because the open and close characters are different so it’s easier to follow and sometimes even works differently).
Examples:

$ logdir=/scripts/log
$ logfile=${logdir}/log_$(date +%Y%m%d_%H%M%S).log
$ echo ${logfile}
/scripts/log/log_20160615_003528.log
$ echo "$(echo $(echo a))"
a
$ echo "`echo `echo a``"
echo a

Note that the difference in the second example is because of the backtick use. The bash thinks that the first command is “echo” and the second is empty. The second backtick closes the first one and the forth closes the third one, but what we did in the first command is that the third closes the second and the forth closes the first.

Handling passwords

Another important issue, and the last for this post, is passwords. It is very important not to reveal passwords in script, so there are several things here.

Reading a password from the user

When reading a password from the user, we don’t want the password to be printed, so we can simply use the read command:

$ read -s -p "Please enter your password: " pwd
Please enter your password:
$ echo ${pwd}
password

Executing a command using a password

In scripts we want to run all kind of commands, including some that require username and password (like sqlplus, rman, etc.). We should make sure that other users won’t be able to see these passwords. Because of that I prefer not to have password in the command line itself (i.e. sqlplus system/manager), but provide the password inside (i.e. sqlplus /nolog and then connect system/manager).

Running a script without user intervention

When running the script in the background, one of the problems is providing passwords. The main issue here is that we sometimes don’t have control on the script permissions, so everybody can read it and see the database password, which is very bad!
That is why I prefer to simply use “/ as sysdba” whenever I can, so I don’t care who sees the script. If I cannot do that, another good option is to use Oracle wallet to allow local connection without a password. If none of these can be used, the last option I use is to place a file containing the password in oracle user’s home directory with read permissions only to oracle, then have the password there and the script only reads it and use it. This is not the best, but it’s better than the alternatives. The benefit here is that I can share the script and have different password for different databases.

Summary

This was the first bash post, a bit long but hopefully useful. In the next post I’ll talk about running sqlplus from bash with all kind of options.