The title says it all, except these are some of my personal
favorites.
Use a standard setup in shell scripts
Why re-invent the wheel? Any script that relies on access to
SQL*Plus or SQL*LDR (on the same machine) can be easily initialized. Take the
following code snippet and put it into its own file:
#! /usr/bin/ksh
#<———————————————————–>
# Check Oracle environment
#<———————————————————–>
if [ -z “${ORACLE_HOME}” ]
then
echo “”
echo “ORACLE_HOME is not set.”
echo “ORACLE_HOME is needed for SQL*Plus and SQL*LDR.”
echo “”
echo “Exiting $0”
echo “”
exit 1
fi
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “”
echo “Can not find sqlplus.”
echo “”
echo “Exiting $0”
echo “”
exit 1
fi
Let’s say you named the file as shell.txt. Save shell.txt in
the user’s $HOME directory. If I am logged in as oracle and my home directory
is /opt/oracle, then $HOME refers to /opt/oracle. When starting a new script,
you have environment checking already added in, you also have the new script
file created in the current working directory. The example below checks for
ORACLE_HOME and the sqlplus executable. Other executables can be included/checked,
but chances are, that if sqlplus exists under ORACLE_HOME, so does sqlldr.
linux.box.net > cat ~/shell.txt > my_new_script.ksh
linux.box.net > more my_new_script.ksh
#! /usr/bin/ksh
#<———————————————————–>
# Check Oracle environment
#<———————————————————–>
if [ -z “${ORACLE_HOME}” ]
then
echo “”
echo “ORACLE_HOME is not set.”
echo “ORACLE_HOME is needed for SQL*Plus and SQL*LDR.”
echo “”
echo “Exiting $0”
echo “”
exit 1
fi
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo “”
echo “Can not find sqlplus.”
echo “”
echo “Exiting $0”
echo “”
exit 1
fi
While you are at it, especially if the new script is going to interview the user, add in a “yes or no” block. The script can then test SOME_VARIABLE’s value and branch appropriately.
#<———————————————————–>
# Variable assignment
#<———————————————————–>
YN_ANSWER=””
echo “Are you doing <fill in the blank> (y/n) [Y]: ”
read YN_ANSWER
while [ “$YN_ANSWER” != “Y” -o “$YN_ANSWER” != “N” ]
do
case “$YN_ANSWER”
in
y | Y | yes | YES | Yes | “” ) YN_ANSWER=”Y”
break;;
n | N | no | NO | No ) YN_ANSWER=”N”
break;;
* ) echo “”
echo “Please enter y or n (Ctrl-c to quit): ”
read YN_ANSWER;;
esac;
done
if [ “${YN_ANSWER}” = “Y” ]
then
SOME_VARIABLE=Y
else
SOME_VARIABLE=Y
fi
Get output from a query returned into a shell script variable
The utility of this is endless as most anything from a query
in SQL*Plus can be returned into a shell variable. Although there are other
ways of adding a timestamp as part of a file’s name, this example uses the date
returned from a query and places it into a file’s name (e.g., a spool file).
TS=`sqlplus -s username/password@SID <<EOF
set heading off feedback off verify off
select to_char(sysdate, ‘YYYYMMDD_SSSSS’) from dual;
exit
EOF
`
TS=`echo $TS | tr “[a-z]” “[A-Z]”`
echo “The timestamp is $TS”
As a test, put this into my_new_script.ksh and run it.
linux.box.net> my_new_script.ksh The timestamp is 20060720_18200
If you are familiar or comfortable using date
formatting/masking in UNIX, this approach does everything UNIX does and more
because of the numerous ways in which dates can be formatted. You can also
include a random number (concatenated with the date query) if the 86,400
seconds in a day in combination with the date is not enough to make a file name
unique.
Two things to take note of include the back tick single
quotation mark and the use of the tr command. The "`" character is
hard to see, especially if your editor allows color mode, so don’t forget to
include it (or check for it during troubleshooting). The other item is the "tr"
(translate) command. This helps to stop carriage/line return when referencing
the variable. If your output looks like what is shown below, the tr command’s
conditioning of the variable’s value will make the variable more "normal."
The timestamp is 20060720_18200
Use mail as a file transfer utility
Let’s say you just spooled the output of a query into a
file. If you work in a PC desktop/UNIX server environment, as is very common,
how do you get the spool file to your desktop (or other users, for that
matter)? Are you one of those who copies the file to your home directory, starts
up PuTTY/WinSCP3/Tectia/etc., copies the file onto your PC, then attached it to
an email? If the file is small enough (relatively speaking), send it via mail
using:
mail –s "Your subject line" your_name@your_domain.com < file_to_send
The exact name of "mail" can vary across
platforms/vendors, but either mail or mailx typically does the trick. Of
course, your environment has to be configured to allow mail to be sent from
UNIX-land to PC-world.
Armed with this functionality, you can create an alert log
checker and have it send mail when it discovers problems or alertable
conditions in the end of the alert log. Add a cron job to check every five minutes
(as an example). Use the tail command to output the bottom however many lines
you want from the alert log into a temp file, grep for ORA-xxxxx or whatever
else is of interest, and if you get a hit, send the results or temp file via
email.
This method isn’t bulletproof in that a lot can happen in
five minutes and the scan (using
tail -#_of_lines) may miss the event you were hoping to be
alerted on. Not to knock the improvements in OEM/Database Control/whatever, but
a cron on UNIX is quite a bit more trustworthy than relying on an Oracle agent.
Keep in mind that this isn’t an either-or choice. The cron can backup the
agent, and vice versa.
Run a script right now
Related to cron jobs is the "at" command. On
Linux, running a script is as simple as:
at –f alert_log_checker.ksh now
This is much easier (and safer) to perform when compared to
waiting for the scheduled cron job to start, editing the crontab and
introducing errors/typos, and preventing output from scrolling in your window.
The "now" part can include noon, midnight, 0600, and a variety of
other named time formats.
Pick one shell and become proficient in it (for beginners)
Bash, Korn, Bourne, C, and the list goes on and on. Each
shell has it own nuances, so instead of having your scripts spread over several
shells, pick one and stick with it. The payoff is consistency. You can almost
always switch between shells, so if your personal preference is the C-shell
(hard to beat the tab key for file name completion), but all "real"
work is done the Korn shell, learn how to switch between them.
In Closing
There are scores of books (and bibles) on tips, tricks,
techniques, utilities, insider secrets, internals, power tools, complete (but
not really, depending on the publisher) reference, essentials, nutshells, and
well, you must get the idea by now. They all complement one another because if
there were one definitive guide (add that one to the list as well), the market wouldn’t
have that many books to choose from. You have the freedom to pick and choose
the ones you like, so happy hunting. Perhaps some of the ones in this article
will make your life and job easier.
