shell script fordba
TRANSCRIPT
-
8/12/2019 Shell Script ForDBA
1/115
Jon EmmonsShell Scripting for the Oracle Professional
Shell Scripting for the
Oracle Professional
Jon Emmons
Oracle Consultant
Author
-
8/12/2019 Shell Script ForDBA
2/115
Jon EmmonsShell Scripting for the Oracle Professional
My Background
Undergraduate Computer Science
coursework. Extensive experience in Solaris and Linux
system administration.
Oracle Database Administration onversions 8.0 through 10gR2.
As a consultant I must build efficient, low-to no-maintenance scripts for a variaety ofpurposes.
-
8/12/2019 Shell Script ForDBA
3/115
Jon EmmonsShell Scripting for the Oracle Professional
Books by Jon Emmons
Oracle Shell Scripting: Linux &
Unix Programming for Oracle
On shelves this summer
Pre-order at rampant-books.com
Easy Linux Commands: Working
Examples of Linux Command Syntax
Available Today at Bookstores
and at rampant-books.com
-
8/12/2019 Shell Script ForDBA
4/115
Jon EmmonsShell Scripting for the Oracle Professional
LifeAfterCoffee.com
-
8/12/2019 Shell Script ForDBA
5/115
Jon EmmonsShell Scripting for the Oracle Professional
WARNING: There are no one size fits all solutions. You must
evaluate any techniques or solutions based on yourenvironments and goals
Always test any solution in a non-production
environment before applying it to a production system.
Make sure you completely understand new commandsand techniques before applying them in your
environment.
You have been warned!
-
8/12/2019 Shell Script ForDBA
6/115
Jon EmmonsShell Scripting for the Oracle Professional
Topics When to script
Scripting Basics
The Oracle connection
Some useful tricks
Troubleshooting
Some Scripts
-
8/12/2019 Shell Script ForDBA
7/115
Jon EmmonsShell Scripting for the Oracle Professional
When to Script
-
8/12/2019 Shell Script ForDBA
8/115
Jon EmmonsShell Scripting for the Oracle Professional
When To Scripts
Shell scripting can be applied to a wide
variety of system and database tasks.
Though called scripting this isprogramming, but dont let that scare you.
Be careful of script bloat. Be sensitive toyour coworkers and your possiblereplacement.
-
8/12/2019 Shell Script ForDBA
9/115
Jon EmmonsShell Scripting for the Oracle Professional
Repeated Tasks
Necessity isthe mother of invention. The
first candidates for shell scripts will bemanual tasks which are done on a regular
basis.
Backups
Log monitoring
Check disk space
-
8/12/2019 Shell Script ForDBA
10/115
Jon EmmonsShell Scripting for the Oracle Professional
Occasional Tasks
Tasks which are performed rarely enough
that their method, or even their need maybe forgotten.
Periodic business related reports
(monthly/quarterly/yearly)
Offsite backups
Purging old data
-
8/12/2019 Shell Script ForDBA
11/115
Jon EmmonsShell Scripting for the Oracle Professional
Complex Manual Tasks
Some tasks must be performed manually
but may be aided by scripting.
Checking for database locks
Killing runaway processes
These tasks may evolve into repeated tasks
-
8/12/2019 Shell Script ForDBA
12/115
Jon EmmonsShell Scripting for the Oracle Professional
Helper Scripts
Dont ignore the usefulness of helper
scripts. Perhaps a system administratorreally does need to look over the log for a
system daily, but a script can help by
automatically sending it on to him!
-
8/12/2019 Shell Script ForDBA
13/115
Jon EmmonsShell Scripting for the Oracle Professional
Special Tasks
These are tasks which would not be
possible without a programming language.
Storing OS information (performancestats, disk usage, etc.) into the database
High frequency monitoring (several times
a day or more)
-
8/12/2019 Shell Script ForDBA
14/115
Jon EmmonsShell Scripting for the Oracle Professional
Scripting Basics
-
8/12/2019 Shell Script ForDBA
15/115
Jon EmmonsShell Scripting for the Oracle Professional
Before You Start ScriptingYou will find shell scripting an iterative process, but it is
best to have a good idea of your goals when you start.
What are you trying to accomplish
What are the dependencies Which dependencies can we check first
Which dependencies cannot be checked How broad will the effects of this script be
What happens if any step fails Should the script continue or be halted
What results or output do we want from the script
Who should be notified of the results and how What cleanup should be done when the script is complete
What if two copies of the script get executed simultaneously
-
8/12/2019 Shell Script ForDBA
16/115
Jon EmmonsShell Scripting for the Oracle Professional
Scripting ToolsAny plain texteditor will work.
vi (Command line UNIX)
Notepad (Windows)
TextEdit (Mac OSX) EditPlus (Windows, shareware, $30)
editplus.com
-
8/12/2019 Shell Script ForDBA
17/115
Jon EmmonsShell Scripting for the Oracle Professional
The Shell
Shell scripting allows us to use commands
we already use at the command line.This considerably eases the learning
curve.
We are familiar with the interactivemode of
the shell. Almost anything can be done ina script which can be done at the
command line.
-
8/12/2019 Shell Script ForDBA
18/115
Jon EmmonsShell Scripting for the Oracle Professional
Which Shell to UseMy preference is Bash (bash) because of its
ubiquity and compatibility with Bourne (sh).
Other common shells include:
C shell (csh)
Korn shell (ksh)
Z Shell (zsh)
It is important to pick a shell and stick with it. Thedifferences between shells are often small butinfuriating.
-
8/12/2019 Shell Script ForDBA
19/115
Jon EmmonsShell Scripting for the Oracle Professional
The Anatomy of a Commandgr ep i l ocal host / et c/ host s
Command Option Arguments
Optionschange the behavior of a command
Argumentscontrol what the command acts upon
-
8/12/2019 Shell Script ForDBA
20/115
Jon EmmonsShell Scripting for the Oracle Professional
VariablesVariables are set using the = sign
ORACLE_SI D=oss
Variables and their contentsare
case sensitive, sothe variable ORACLE_SI D is different from thevariable or acl e_si d.
Shell variables are un-typed and may contain
integers or text.Numbers with a decimal point will be treated as
text. (e.g. 3. 14)
-
8/12/2019 Shell Script ForDBA
21/115
Jon EmmonsShell Scripting for the Oracle Professional
Variable Naming
Variables should have meaningful names
Variable names do not need to be short
All UPPER CASE typically indicates an
environmental variable Local (script) variables are conventionally
all lowercase
Underscores (_) are best for separating
words in variable names
-
8/12/2019 Shell Script ForDBA
22/115
Jon EmmonsShell Scripting for the Oracle Professional
Variable Scope Variables will be available within the script
(or shell session) which sets them By exporting variables they can be made
available to subsequently called scripts.
This is why we typically perform anexpor t ORACLE_SI D
after setting the variable.Exporting is not necessary when variableswill only be used within the current script.
-
8/12/2019 Shell Script ForDBA
23/115
Jon EmmonsShell Scripting for the Oracle Professional
Using VariablesThe dollar sing ($) is used to retrieve the contents
of a variable.$ echo $ORACLE_SID
oss
If you are trying to use a variable where it may besurrounded by other letters you may need toadd curly braces {}around the name.
$ echo ${ORACLE_SID}_sid
oss_si d
-
8/12/2019 Shell Script ForDBA
24/115
Jon EmmonsShell Scripting for the Oracle Professional
Comments and Whitespace Anything appearing after a pound symbol
(#) on a line will be ignored. Adding comments can aid troubleshooting
and future editing of the script.
Blank lines are ignored when a script isexecuted.
Blank lines and other whitespace (tabs,spaces) can be used to improve scriptreadability.
-
8/12/2019 Shell Script ForDBA
25/115
Jon EmmonsShell Scripting for the Oracle Professional
A basic script#! / bi n/ bash
echo "The cur r ent dat abase i s $ORACLE_SI D"
echo "The cur r ent r unni ng pr ocesses f or$ORACLE_SI D ar e"
ps ef | gr ep $ORACLE_SI D
-
8/12/2019 Shell Script ForDBA
26/115
Jon EmmonsShell Scripting for the Oracle Professional
A basic script#! / bi n/ bash
echo "The cur r ent dat abase i s $ORACLE_SI D"
echo "The cur r ent r unni ng pr ocesses f or$ORACLE_SI D ar e"
ps ef | gr ep $ORACLE_SI D
This first line indicates what
interpreter to use when running
this script
-
8/12/2019 Shell Script ForDBA
27/115
Jon EmmonsShell Scripting for the Oracle Professional
A basic script#! / bi n/ bash
echo "The cur r ent dat abase i s $ORACLE_SI D"
echo "The cur r ent r unni ng pr ocesses f or$ORACLE_SI D ar e"
ps ef | gr ep $ORACLE_SI D
Whitespace is used to
separate commands to
improve readability.
-
8/12/2019 Shell Script ForDBA
28/115
Jon EmmonsShell Scripting for the Oracle Professional
A basic script#! / bi n/ bash
echo "The cur r ent dat abase i s $ORACLE_SI D"
echo "The cur r ent r unni ng pr ocesses f or$ORACLE_SI D ar e"
ps ef | gr ep $ORACLE_SI D
Variables referenced here
must have already been
set and exported.
-
8/12/2019 Shell Script ForDBA
29/115
Jon EmmonsShell Scripting for the Oracle Professional
A basic script#! / bi n/ bash
echo "The cur r ent dat abase i s $ORACLE_SI D"
echo "The cur r ent r unni ng pr ocesses f or$ORACLE_SI D ar e"
ps ef | gr ep $ORACLE_SI D
Note the variable being
used as an argument.
We'll see a lot of this.
-
8/12/2019 Shell Script ForDBA
30/115
Jon EmmonsShell Scripting for the Oracle Professional
The Shebang ( #! )The "shebang" is a special comment. Since
it is a comment it will not be executedwhen the script is run. Instead beforethe
script is run, the shell calling the script will
check for the #! pattern. If found it willinvoke the script using that interpreter.
If no#!
is found most shells will use thecurrent shell to run the script.
-
8/12/2019 Shell Script ForDBA
31/115
Jon EmmonsShell Scripting for the Oracle Professional
The Shebang (cont)Since the shells are installed in different locations
on different systems you may have to alter the#! line. For example, the bashshell may be in
/ bi n/ bash, / usr / bi n/ bashor
/ usr / l ocal / bi n/ bash.
Setting the shell explicitly like this assures that the
script will be run with the same interpreter
regardless of who executes it (or what theirdefault shell may be.)
-
8/12/2019 Shell Script ForDBA
32/115
Jon EmmonsShell Scripting for the Oracle Professional
Script NamingDescriptive names are important.
Use full words Separate words with underscores
Avoid using spaces or other unusual characters
There is no requirement for script names, buttypically they will end in . s h
Talk with others at your site who are doing shell
scripting and try to agree on a convention.
-
8/12/2019 Shell Script ForDBA
33/115
-
8/12/2019 Shell Script ForDBA
34/115
Jon EmmonsShell Scripting for the Oracle Professional
status.sh#! / bi n/ sh
# Show t he user and host name
echo "Hel l o $USER! "echo " Wel come t o `host name`"echo " - - - Cur r ent Di sk Usage - - - "d f - h# On some syst ems t he - h ( human r eadabl e) opt i on wi l l not work wi t h df# I n t hat case you can use t he - k opt i on t o di spl ay out put i n ki l l obyt es
echo " - - - Cur r ent upt i me, user s and l oad aver ages - - - "upt i me
echo "- - - Load aver age numbers r epr esent t he 1, 5 and 15 mi nut e l oadaver ages - - - "
echo " - - - Lower number s are bet t er f or l oad aver ages - - - "
# These ar e t he f i r st t wo t hi ngs I check when I t hi nk t her e i s a pr obl em# wi t h a syst em, but I ' m sur e you can t hi nk of some ot her t hi ngs t o addher e
-
8/12/2019 Shell Script ForDBA
35/115
Jon EmmonsShell Scripting for the Oracle Professional
status.sh#! / bi n/ sh
# Show t he user and host name
echo "Hel l o $USER! "echo " Wel come t o `host name`"echo " - - - Cur r ent Di sk Usage - - - "d f - h# On some syst ems t he - h ( human r eadabl e) opt i on wi l l not work wi t h df# I n t hat case you can use t he - k opt i on t o di spl ay out put i n ki l l obyt es
echo " - - - Cur r ent upt i me, user s and l oad aver ages - - - "upt i me
echo "- - - Load aver age numbers r epr esent t he 1, 5 and 15 mi nut e l oadaver ages - - - "
echo " - - - Lower number s are bet t er f or l oad aver ages - - - "
# These ar e t he f i r st t wo t hi ngs I check when I t hi nk t her e i s a pr obl em# wi t h a syst em, but I ' m sur e you can t hi nk of some ot her t hi ngs t o addher e
This output will help
the user identify what
they are looking at.
This comment explains the
command option used and
how it may need to bechanged on some systems.
-
8/12/2019 Shell Script ForDBA
36/115
Jon EmmonsShell Scripting for the Oracle Professional
status.sh Usage$ . / st at us. shHel l o or acl e!
Wel come t o gl onk- - - Cur r ent Di sk Usage - - -Fi l esyst em Si ze Used Avai l Use% Mount ed on/ dev/ mapper / Vol Gr oup00- LogVol 00 72G 6. 5G 61G 10% /
/ dev/ hda1 99M 9. 8M 84M 11% / boot/ dev/ shm 252M 0 252M 0% / dev/ shm- - - Cur r ent upt i me, user s and l oad aver ages - - - 19: 17: 41 up 10 days, 6: 02, 2 user s, l oad aver age:
0. 00, 0. 02, 0. 00- - - Load aver age number s r epr esent t he 1, 5 and 15 mi nut e
l oad aver ages - - -- - - Lower number s ar e bet t er f or l oad aver ages - - -
This additional output provides
very useful information on the
results we're looking at.
-
8/12/2019 Shell Script ForDBA
37/115
Jon EmmonsShell Scripting for the Oracle Professional
Basic Script Setup Make a plan!
Create a new text file Specify the interpreter to be used (#! )
Set variables using =
Retrieve variable contents using $
Add {}around variable name if necessary
Use comments (#) and whitespace (blank lines,spaces and tabs) to improve readability
Grant execute permissions to the appropriateusers with chmod
-
8/12/2019 Shell Script ForDBA
38/115
Jon EmmonsShell Scripting for the Oracle Professional
Running Your ScriptIf the proper execute permissions have
been applied:. / t est _scr i pt . sh
/ home/ or acl e/ t est _ scr i pt . sh
If . is in your $PATHvariable
t est _scr i pt . sh
-
8/12/2019 Shell Script ForDBA
39/115
Jon EmmonsShell Scripting for the Oracle Professional
Keeping Your Scripts Organized Work with sysadmins and DBAs to come
up with a convention Development should be done in an area
away from production scripts
Scripts for a specific database in/ u01/ app/ or acl e/ admi n/ sid/ scr i pt s
Scripts used on multiple databases in/ u01/ app/ or acl e/ admi n/ common/ scr i pt s
-
8/12/2019 Shell Script ForDBA
40/115
-
8/12/2019 Shell Script ForDBA
41/115
Jon EmmonsShell Scripting for the Oracle Professional
Decisions and Loops
-
8/12/2019 Shell Script ForDBA
42/115
Jon EmmonsShell Scripting for the Oracle Professional
The if StatementThe simplest flow control statement is the
i fstatement.$ age=29
$ if [ $age -lt 30 ]
> then
> echo "You're still under 30"
>fi
You' r e st i l l under 30
-
8/12/2019 Shell Script ForDBA
43/115
Jon EmmonsShell Scripting for the Oracle Professional
The if StatementThe simplest flow control statement is the
i fstatement.$ age=29
$ if [ $age -lt 30 ]
> then
> echo "You're still under 30"
>fi
You' r e st i l l under 30
Note that the end of an ifstatement is indicated by
the keyword fi
-
8/12/2019 Shell Script ForDBA
44/115
Jon EmmonsShell Scripting for the Oracle Professional
if, elseif and else#! / bi n/ sh
age=39
i f [ $age - l t 30 ]
t hen
echo "You' r e st i l l under 30"
el i f [ $age - ge 30 - a $age - l e 40 ]t hen
echo "You' r e i n your 30s"
el seecho "You' r e 40 or over "
f i
-
8/12/2019 Shell Script ForDBA
45/115
Jon EmmonsShell Scripting for the Oracle Professional
if, elseif and else#! / bi n/ sh
age=39
i f [ $age - l t 30 ]
t hen
echo "You' r e st i l l under 30"
el i f [ $age - ge 30 - a $age - l e 40 ]t hen
echo "You' r e i n your 30s"
el seecho "You' r e 40 or over "
f i
Initially this condition is
checked and, if true, the codein the t hensection executed
-
8/12/2019 Shell Script ForDBA
46/115
-
8/12/2019 Shell Script ForDBA
47/115
Jon EmmonsShell Scripting for the Oracle Professional
if, elif and else#! / bi n/ sh
age=39
i f [ $age - l t 30 ]
t hen
echo "You' r e st i l l under 30"
el i f [ $age - ge 30 - a $age - l e 40 ]t hen
echo "You' r e i n your 30s"
el seecho "You' r e 40 or over "
f iFinally if the if condition and
all elif conditions have failed
the else, if present, will be
executed
-
8/12/2019 Shell Script ForDBA
48/115
Jon EmmonsShell Scripting for the Oracle Professional
if, elif and else Conditional statements can compare numbers
or text An i f statement will need to have a t henand
an f i to indicate the end of the statement
An i f statement can have one or more el i fstatements or may have none
An i f statement may have one el sestatement
but may have no el sestatement Only one section of code will be executed
-
8/12/2019 Shell Script ForDBA
49/115
Jon EmmonsShell Scripting for the Oracle Professional
Mathematical Comparators
-
8/12/2019 Shell Script ForDBA
50/115
Jon EmmonsShell Scripting for the Oracle Professional
String Comparators
-
8/12/2019 Shell Script ForDBA
51/115
Jon EmmonsShell Scripting for the Oracle Professional
Comparing Strings$ if [ $ORACLE_SID = "oss" ]
> then
> echo "Using the sid for the Oracle ShellScripting database"
> fi
Usi ng t he si d f or t he Or acl e Shel l Scr i pt i ng
dat abase
-
8/12/2019 Shell Script ForDBA
52/115
Jon EmmonsShell Scripting for the Oracle Professional
Checking Variables$ if [ $ORACLE_SID ]
> then
> echo "ORACLE_SID variable is set to $ORACLE_SID"> fi
ORACLE_SI D var i abl e i s set t o oss
This statement checks to see if the variable$ORACLE_SI Dhas been set.
The statement will fail if the variable has notbeen set, or if it is set to a null value.
-
8/12/2019 Shell Script ForDBA
53/115
Jon EmmonsShell Scripting for the Oracle Professional
File Comparators
-
8/12/2019 Shell Script ForDBA
54/115
Jon EmmonsShell Scripting for the Oracle Professional
Checking Files$ if [ -e
$ORACLE_HOME/dbs/init$ORACLE_SID.ora ]
> then
> echo "An init file exists for the
database $ORACLE_SID"
> fi
An i ni t f i l e exi st s f or t he dat abase oss
-
8/12/2019 Shell Script ForDBA
55/115
Jon EmmonsShell Scripting for the Oracle Professional
Complex Comparisons
-
8/12/2019 Shell Script ForDBA
56/115
Jon EmmonsShell Scripting for the Oracle Professional
Checking Multiple Files$ if [ -e $ORACLE_HOME/dbs/init$ORACLE_SID.ora -a -e \
> $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora ]
> then> echo "We seem to have both an spfile and an init file"
> fi
We seem t o have bot h an spf i l e and an i ni t f i l e
-
8/12/2019 Shell Script ForDBA
57/115
Jon EmmonsShell Scripting for the Oracle Professional
Case Statement#! / bi n/ shcase $ORACLE_SI D
i noss)echo "Usi ng t he si d f or t he Or acl e Shel l
Scr i pt i ng dat abase"; ;
db1)echo "Usi ng t he def aul t Or acl e dat abase"; ;
*)echo " I don' t have a descr i pt i on f or t hi s
dat abase"; ;
esac
-
8/12/2019 Shell Script ForDBA
58/115
Jon EmmonsShell Scripting for the Oracle Professional
Case Statement#! / bi n/ shcase $ORACLE_SI D
i noss)echo "Usi ng t he si d f or t he Or acl e Shel l
Scr i pt i ng dat abase"; ;
db1)echo "Usi ng t he def aul t Or acl e dat abase"; ;
*)echo " I don' t have a descr i pt i on f or t hi s
dat abase"; ;
esac
The beginning of a casestatement is indicated by the
case keyword. The end is
indicated by case spelled
backwards
-
8/12/2019 Shell Script ForDBA
59/115
Jon EmmonsShell Scripting for the Oracle Professional
Case Statement#! / bi n/ shcase $ORACLE_SI D
i noss)echo "Usi ng t he si d f or t he Or acl e Shel l
Scr i pt i ng dat abase"; ;
db1)echo "Usi ng t he def aul t Or acl e dat abase"; ;
*)echo " I don' t have a descr i pt i on f or t hi s
dat abase"; ;
esac
The input given at the
beginning will be compared to
each value in the list
The asterisk is a
wildcard and will
match any string
The code to be executed for each option is
terminated by a double semicolon.
-
8/12/2019 Shell Script ForDBA
60/115
Jon EmmonsShell Scripting for the Oracle Professional
Case Statement The code following the firstmatching option will
be executed.
If no match is found the script will continue onafter the esacstatement without executing anycode.
Some wildcards and regular expressions can beused.
A casecould be rewritten as a series of el i f
statements but a caseis typically more easilyunderstood.
-
8/12/2019 Shell Script ForDBA
61/115
-
8/12/2019 Shell Script ForDBA
62/115
Jon EmmonsShell Scripting for the Oracle Professional
The while Loop#! / bi n/ sh
i =1
whi l e [ $i - l e 10 ]
do
echo "The cur r ent val ue of i i s $i "i = expr $i + 1`
done
Make sure your loop variable is
initialized before the loop starts
Also makes sure that something will
eventually cause the while condition
to fail, otherwise you may end up in
an infinite loop!
-
8/12/2019 Shell Script ForDBA
63/115
-
8/12/2019 Shell Script ForDBA
64/115
Jon EmmonsShell Scripting for the Oracle Professional
The for Loop#! / bi n/ sh
count =0
f or i i n 2 4 6
do
echo "i i s $i "count = expr $count + 1`
done
echo "The l oop was execut ed $countt i mes"
This for loop will be executed threetimes, once with i=2, once with i=4
and once with i=6
-
8/12/2019 Shell Script ForDBA
65/115
Jon EmmonsShell Scripting for the Oracle Professional
Breaking Out of the Current LoopThe br eakstatement will cause the shell to stop executing the current loop
and continue on after its end.
#! / bi n/ shf i l es= l s`count =0f or i i n $f i l esdo
count = expr $count + 1`i f [ $count - gt 100 ]t hen
echo "Ther e ar e mor e t han 100 f i l es i n t he cur r entdi r ect or y"
breakf i
done
-
8/12/2019 Shell Script ForDBA
66/115
Jon EmmonsShell Scripting for the Oracle Professional
Prompting for User InputFor scripts which will be run interactively we
can prompt the user to give us input.The r eadcommand can be used to set a
variable with a value read from user input.
#! / bi n/ sh
echo "Ent er your name"
r ead nameecho "Hi $name. I hope you l i ke t hi s scr i pt "
-
8/12/2019 Shell Script ForDBA
67/115
Jon EmmonsShell Scripting for the Oracle Professional
Prompting for User Input$ ./welcome.sh
Ent er your name
Jon
Hi J on. I hope you l i ke t hi s scr i pt
Note that the text input will be displayed on the
screen. If you do not want the input displayed(like when accepting a password) use the - s
option for the r eadcommand.
-
8/12/2019 Shell Script ForDBA
68/115
Jon EmmonsShell Scripting for the Oracle Professional
Using ArgumentsAccepting arguments to your script can allow you
to make a script more flexible.
The variables $1, $2, $3 etc. refer to thearguments given in order.
The variable $@ refers to the complete string ofarguments.
The variable $# will give the number of argumentsgiven.
-
8/12/2019 Shell Script ForDBA
69/115
-
8/12/2019 Shell Script ForDBA
70/115
Jon EmmonsShell Scripting for the Oracle Professional
Using Argumentsi f [ $1 ]t hen
ORACLE_SI D=$1ORAENV_ASK=NO. or aenv
el sei f [ ! $ORACLE_SI D ]
t henecho "Er r or : No ORACLE_SI D set or pr ovi ded as
an ar gument "exi t 1
f if i
Check to see if an argument was given
If it was, we will use it to set the
ORACLE_SID variable then
execute oraenv
-
8/12/2019 Shell Script ForDBA
71/115
Jon EmmonsShell Scripting for the Oracle Professional
The Oracle Connection
-
8/12/2019 Shell Script ForDBA
72/115
-
8/12/2019 Shell Script ForDBA
73/115
Jon EmmonsShell Scripting for the Oracle Professional
Calling a SQL Script#! / bi n/ sh
sql pl us - S syst em/ manager @dat abase_st at us. sql
This short script will allow you to easily execute a
SQL script with given permissions
Warning: Calling sqlplus in this manner may
expose your username and password to
others on the system!
Warning: Files which contain usernamesand passwords must be properly secured to
avoid exposing the passwords!
Getting Past the
-
8/12/2019 Shell Script ForDBA
74/115
Jon EmmonsShell Scripting for the Oracle Professional
Password ProblemsA combination of two methods can be used to get
around the password problems with the
previous method.
Place the password in a variable so it will notdisplay in a process listing.
Rather than placing the password in the shellscript store it in a separate, secure file.
Placing the Password in a Variable
-
8/12/2019 Shell Script ForDBA
75/115
Jon EmmonsShell Scripting for the Oracle Professional
Placing the Password in a Variable
#! / bi n/ sh
syst em_pw=manager
sql pl us - S syst em/ $syst em_pw @dat abase_st at us. sql
When this command is running a process listing
(ps) will show the variable name ($syst em_pw)instead of the password.
Reading the Password from a
-
8/12/2019 Shell Script ForDBA
76/115
Jon EmmonsShell Scripting for the Oracle Professional
Secure File#! / bi n/ sh
syst em_pw= cat/ u01/ app/ or acl e/ admi n/ oss/ pw/ syst em. pw
sql pl us - S syst em/ $syst em_pw @dat abase_st at us. sql
By reading the password from a text file the scriptis no longer required to have the passwordembedded in it.
This has the added advantage of providing a
single location where passwords can bechanged for all scripts at once.
-
8/12/2019 Shell Script ForDBA
77/115
Using SQL Directly In Shell Scripts
-
8/12/2019 Shell Script ForDBA
78/115
Jon EmmonsShell Scripting for the Oracle Professional
Us g SQ ect y S e Sc pts
By using file markers we can call SQL
directly from our shell scripts. This canmake scripts easier to move and maintain
as there is only one file to work with.
-
8/12/2019 Shell Script ForDBA
79/115
Jon EmmonsShell Scripting for the Oracle Professional
#! / bi n/ shsql pl us - S syst em/ manager
-
8/12/2019 Shell Script ForDBA
80/115
Jon EmmonsShell Scripting for the Oracle Professional
g
The shell will interpret everything that follows
-
8/12/2019 Shell Script ForDBA
81/115
-
8/12/2019 Shell Script ForDBA
82/115
Getting Information Out of
-
8/12/2019 Shell Script ForDBA
83/115
Jon EmmonsShell Scripting for the Oracle Professional
SQL*PlusThe output of sqlplus can be sent to a file on the system
for further processing.
Output is redirected with the >symbol.When redirecting both input and output things can getconfusing.
sql pl us - S " / as sysdba" $t empf i l e
It is easiest to look at this as two separate statements, theredirection of input ( $t empf i l e).
Note that the output is being redirected to the file locationdescribed by the t empf i l evariable.
Getting Information Out of
-
8/12/2019 Shell Script ForDBA
84/115
Jon EmmonsShell Scripting for the Oracle Professional
SQL*Plus#! / bi n/ basht empf i l e=/ t mp/ audi t _l ocked_account s_$ORACLE_SI D. t xt# St ar t sql pl us and check f or l ocked account ssql pl us - S " / as sysdba" $t empf i l e
set pagesi zesel ect ' The f ol l owi ng account s wer e f ound t o be unl ocked and shoul d notbe'f r om dual ;def i ne exi t _st at us = 0col umn xs new_val ue exi t _st at ussel ect user name, account _st at us, 1 as xs f r om dba_user swher e account _st at us ! = ' LOCKED'and user name i n ( ' HR' , ' SCOTT' , ' OUTLN' , ' MDSYS' , ' CTXSYS' ) ;exi t &exi t _st at us
EOF# I f t he exi t st at us of sql pl us was not 0 t hen we wi l l send an emai l
i f [ $? ! = 0 ]t hen
mai l - s " Account s Unl ocked i n $ORACLE_SI D" oracl e < $t empf i l ef i
Manipulating Other Commands
-
8/12/2019 Shell Script ForDBA
85/115
Jon EmmonsShell Scripting for the Oracle Professional
These methods can also be applied with
RMAN for backup and recovery.
File markers can be used to emulate user
input for many (but not all) commands.
Break
-
8/12/2019 Shell Script ForDBA
86/115
Jon EmmonsShell Scripting for the Oracle Professional
-
8/12/2019 Shell Script ForDBA
87/115
Jon EmmonsShell Scripting for the Oracle Professional
Some Useful Tricks
Escape Character
-
8/12/2019 Shell Script ForDBA
88/115
Jon EmmonsShell Scripting for the Oracle Professional
The escape character will prevent the shell from
interpreting the following character as anything
other than text.
Backslash (\ ) is the escape character in the Bash
shell.
Escaping special characters (such as * ' $ ;
and space) can help you get the output you
want and to handle special characters in file
names.$ echo "The escape character in Bash is \"\\\""
The escape char act er i n Bash i s " \ "
Single Quotes
-
8/12/2019 Shell Script ForDBA
89/115
Jon EmmonsShell Scripting for the Oracle Professional
Single quotes will cause all special
characters (except the single quote) to beignored.
$ echo 'In single quotes "double quotes",$ and even ; are all safe'
I n si ngl e quot es "doubl e quot es" , $ andeven ; ar e al l saf e
Double Quotes
-
8/12/2019 Shell Script ForDBA
90/115
Jon EmmonsShell Scripting for the Oracle Professional
Double quotes will cause most specialcharacters to be ignored.
Variables and back quotes will be expandedand backslashes are interpreted as anescape character.
$ echo "In double quotes we can usevariables like $ORACLE_SID"
I n doubl e quot es we can use var i abl es l i keoss
-
8/12/2019 Shell Script ForDBA
91/115
Redirecting Output to a File
-
8/12/2019 Shell Script ForDBA
92/115
Jon EmmonsShell Scripting for the Oracle Professional
Output from commands can easily be
sent to a file instead of the display with a>or >>
The >will replace the given file if it exists
but the >>will concatenate the output onthe end of the given file
Both the standard output and the error
output can be redirected to a file
Redirecting Standard Output
-
8/12/2019 Shell Script ForDBA
93/115
Jon EmmonsShell Scripting for the Oracle Professional
$ lsl og1. l og l og3. l og myf i l e. t xt sampl e. t xt
t ypes_of _uni x. t xt
l og2. l og mar x. t xt out put . t xt t est _scr i pt . sh$ ls > listing.txt$more listing.txtl i st i ng. t xtl og1. l og
l og2. l ogl og3. l ogmar x. t xtmyf i l e. t xtout put . t xt
sampl e. t xtt est _scr i pt . sht ypes_of _uni x. t xt
Redirecting Error Output
-
8/12/2019 Shell Script ForDBA
94/115
Jon EmmonsShell Scripting for the Oracle Professional
$ f i nd . / - name "*. t xt " >t ext _f i l es. t xt 2>er r or s. t xt
While >or >>redirect standard output 2>or
2>>will redirect error output.
Standard or error output can be redirectedto /dev/null (2>/dev/null) to discard the
output
Linking Output to Input
-
8/12/2019 Shell Script ForDBA
95/115
Jon EmmonsShell Scripting for the Oracle Professional
The pipe (| ) can be used to link the output
of one command to the input of another.
$ps -ef | grep oss
oracl e 2684 1 0 14: 02 ? 00: 00: 00 ora_pmon_oss
oracl e 2686 1 0 14: 02 ? 00: 00: 00 ora_psp0_ossor acl e 2688 1 0 14: 02 ? 00: 00: 00 ora_mman_oss
oracl e 2690 1 0 14: 02 ? 00: 00: 02 ora_dbw0_oss
or acl e 2692 1 0 14: 02 ? 00: 00: 03 or a_l gwr _oss
or acl e 2694 1 0 14: 02 ? 00: 00: 01 or a_ckpt _oss
oracl e 2696 1 0 14: 02 ? 00: 00: 06 ora_smon_oss
or acl e 2698 1 0 14: 02 ? 00: 00: 00 or a_r eco_oss
Performing Math in the Shell
-
8/12/2019 Shell Script ForDBA
96/115
Jon EmmonsShell Scripting for the Oracle Professional
The expr command can be used to perform simple math in theshell.
$ expr 2 + 79$ expr 4 + 3 \* 313$ expr 13 / 27
The asterisk is used for multiplication but must be escaped by abackslash.
Results will be truncated to whole numbers.
Sending Email
-
8/12/2019 Shell Script ForDBA
97/115
Jon EmmonsShell Scripting for the Oracle Professional
Sending email is simple!
Use -s to specify a subject line, give anaddress as an argument (or list multiple
addresses in quotes and separated by
commas) and redirect a file intothecommand.
mai l - s " Al er t l og f r om $ORACLE_SI D `host name`"or acl e