UNIX Scripting for the Oracle DBA
Everything an Oracle DBA will ever need to know about UNIX
scripting.
* Work smarter, not harder.
* Lean only what you need know and leave the rest for later.
Rules
- Go vanilla. Use what is available. Don't install any
utilities that have to be maintained.
- Keeping it simple means that your scripts will run on any
version of SUN, HP, AIX, Linux, etc with minimal or no
modifications.
- Pick the shell, Korn, C, etc that the UNIX Admin use because
your going to ask them for help.
The examples below are AIX, Korn shell.
Must Know Commands
These are basic commands you need to know. Skim the list if you
already have some UNIX skills.
| ls |
List files and directories |
| ls -ltr |
List files and directories by last time
reverse order. |
| mkdir |
Make a directory. |
| rmdir |
Remove a directory. |
| cd |
Change directory to users home directory. |
| cd .. |
Change to the parent directory |
| cd ../../admin |
Go two directories back up the tree and then into the
"admin" directory |
| cd - |
Change directory to the previous directory I was in. |
| cd /u01/app/oracle/admin/scripts |
Change to the /u01/app/oracle/admin/scripts director |
| ps -ef |
List processes |
| grep |
Look in a file or string for the presence or absence of
a string. |
| egrep |
Same as grep, but can check for multiple sub strings. |
| cut |
Parse a line into fields by column position or by a
single special character. |
| sort |
Sort the results |
| uniq |
Remove duplicates from the results |
| wc -l |
Word count, or line count with "-l" |
| # |
The rest of the line is a comment. |
| |
|
| . /path/script_lib.txt |
Source a file so you can use functions inside it. |
Calling SQLplus
I have 211 shell scripts on my development box that are not for
to restoring or cloning the databases on that host. Of those 211
scripts, 89 of them make a call to SQLplus in order to do something
in the database.
Side Note Example:
lsnr:/u01/app/oracle/admin/scripts>grep
-i sqlplus *.sh | grep -v RESTORE | grep -v CLONE |
cut -d: -f1 | sort | uniq | wc -l
89 |
Explanation:
grep -i sqlplus *.sh |
Look in (grep) any shell file (*.sh) for "sqlplus"
regardless of case (-i) |
| | grep -v
RESTORE | grep -v CLONE |
Take the results and remove (-v) any line that
contains (grep) RESTORE or CLONE. |
| | cut -d: -f1 |
Cut (cut) each resulting line into fields at the
semi-colons ":" (-d:) and return the 1st field (-f1) |
| | sort | uniq
| wc -l |
Sort (sort) the results into ascending order,
remove any duplicates (uniq), and count the number
of remaining lines (wc -l) |
|
The monitor_heart_beat.sh uses the results of the backup.sh
script to get a list of every production Oracle database from the
backup_configuration directory. It then runs the following
code to check the status of each database. You only need to
try to connect to the database in order to get the status because
the error message will tell you everything you need to know.
In the following example we attempt to connect to the TIPS database
with the username "bogus" and password "bogus". If we get
"ERROR: ORA-01017: invalid username/password; logon denied" then
ldap is working, the host is up, the listener is working, and the
database is up, otherwise we would get some other error message.
SID=TIPS
# Assign the variable SID with the database name
"TIPS"
STATUS=`sqlplus -s /nolog << ENDIT
set pause off head off pagesize 0 feedback off
linesize 200
connect bogus/bogus@$SID
exit
ENDIT` # End the SQLPlus Call
if [ ! -z "`echo $STATUS | grep ORA-01017`" ]
then
STATUS_MSG=UP
else
if [ ! -z "`echo $STATUS | grep ORA-01090`" ]
then
STATUS_MSG="shutting down."
else
if [ ! -z "`echo $STATUS | grep ORA-01033`" ]
then
STATUS_MSG="starting up."
else
if [ ! -z "`echo $STATUS | grep ORA-12154`" ]
then
STATUS_MSG="not found by TNS."
else
if [! -z "`echo $STATUS | grep
ORA-27101`" ] || [! -z "`echo $STATUS|grep
ORA-12537`"]
then
STATUS_MSG="down."
else
STATUS_MSG="not
responding."
fi
fi
fi
fi
fi
|
|
Loops
The oratab file is a
|
