Unix Oracle Shell Scripting

UNIX SHELL SCRIPTING IN AN ORACLE ENVIRONMENT

By William A. Ducat Ambassador, Inc. 5299 DTC Boulevard Suite 290 Englewood, CO 80112 (888) 775-3778 x227 (Voice) (630) 839-5264 (Fax)

www.rmoug.org www.rmou g.org

RMOUG Training Days 2001

UNIX Shell Scripting for Oracle

Ducat

Table of Contents Introduction................................................................................................................................................. 3 Conventions ............................................................................................................................................ 3 Building Blocks .......................................................................................................................................... 4 Concepts.................................................................................................................................................. 4 STDOUT............................................................................................................................................. 4 STDERR ............................................................................................................................................. 4 STDIN ....................................................... ........................................................ .................................. 5 Environment Variables ....................................................................................................................... 6 Scripts.................................................................................................................................................. 6 Parameter Passing ........................................................ ....................................................... ................ 8 Flow Control ...................................................... ........................................................ ......................... 9 Identifying your process.................................................................................................................... 10 Practical Examples.................................................................................................................................... 11 Password Management ......................................................................................................................... 11 Embedded SQL..................................................................................................................................... 12 Embedded Subroutines (aka Functions) ............................................................................................... 13 Passing Parameters................................................................................................................................ 15 Global Functions .................................................... ........................................................ ....................... 18 Conclusion ................................................................................................................................................ 22

Sample Code Figure 1 - Redirecting STDERR to a file.................................................................................................... 4 Figure 2 - Redirecting STDERR to STDOUT............................................................................................ 4 Figure 3 - Redirecting STDIN in a script.................................................................................................... 5 Figure 4 - Basic script ................................................................................................................................. 7 Figure 5 – Sample setup_passwords.ksh script......................................................................................... 11 Figure 6 - PS results on HP or Sun ............................................................................................. .............. 12 Figure 7 - PS results on Compaq or Linux ............................................................................................... 12 Figure 8 - Embedding SQL in a KSH script .................................................... ......................................... 13 Figure 9 - Implementing a user-defined user-defined function in a KSH script ........................................................... 14

www.rmoug.org www.rmou g.org



Ducat

Introduction The primary purpose of this paper is to provide a starting point for your own scripting projects. To accomplish this, the paper is divided into two major sections. The “Building Blocks” section will describe some of t he required basic conce pts, and the “Practical Examples” section t akes these ba sic constru cts and steps t hrough the c onstructio n of a rather us eful script . The script starts o ut as a very basi c script , and by applying multiple new features, becomes a very flexible and useful tool. Developing scripts for the Oracle environment involves performing some rather complex tasks, and the KORN shell naturally lends itself to the task. Because of this, and its wide availability, we will use the KORN shell for all examples. In the UNIX environment, a users interactive environment or scripts always run in some sort of shell. A shell can be thought of as an interface to the underlying UNIX command set. Most tasks can be completed in any available shell, but each shell has various attributes, which tend to make it desirable for various applications. For instance, the Bourne shell (/bin/sh) is a very basic shell, which is common to most UNIX installations. Many install programs use the BOURNE shell since one script can be used, unchanged, on multipl e platforms. The C shell (/bin/csh) has various features to assist in the interactive environment, and many users make the C shell their default 1 shell for this reason. The KORN shell (/bin/ksh) lends its self to heavy scripting, and includes the ability to define functions that are local to a script, as well as functions that are available to the users interactive session. Another relative newcomer in the shell arena is the BASH shell (/bin/bash)(a.k.a Bourne Again Shell). BASH attempts to combine the best of the C and BOURNE shells into one. BASH is not widely used at this time, but has the potential to become very popular.

Conventions In this paper when a space is critical, the b symb ol will be used to denot e each space. When something is enclosed in a box, as is this paragraph, it contains commands to enter, the text of a UNIX script, or the results of a script or command.

1

The default shell for a user is specified on the users entry in the /etc/passwd file.

www.rmoug.org



Ducat

Building Blocks UNIX can be t hought of as a very thin operating system. Instead of providing a series of very powerful commands with numerous options, such as the VMS operating system, it provides a large number of very simple commands which can be strung together to obtain the des ired result. A simple script is nothing more than a series of commands or strings of commands executed in a sequential manner. A complex script uses flow control constructs and conditional operators to control the execution of commands much like a 3GL such as “C”, BASIC or Fortran.

Concepts Numerous texts cover the syntax and purpose of all of the UNIX commands, and we will not attempt to cover all of them here. We will however cover some of the more useful ones from a scripting standpoint, as well as some basic issues related to all commands. There are three things common to most commands. First, they need a source of input. Second, they need somewhere to send their output. Third, they need somewhere to send the results of any errors encountered. In the UNIX world, we have “standar d inpu t” (STDIN), “standard output” (STDOUT), and “standard error” (STDERR). The default values for each command differ, but as a rule, STDOUT and STDERR are sent to the screen, and STDIN will be requested interactively from the user, or from a file.

STDOUT When performing a command such as “ls”, the results of the li sting are displayed to the screen. This can be changed by redirecting STDOUT with the “>” redirector. For instance, if the “ls >listing.out” command is issued, the file “listing.out” will be created if it does not exist, or it will be replaced if it does exist. When complete, the file will contain the results of the ”ls” command. When the “>” is used, the results will not be shown on the screen. The “>>” redirector works like “>” except it will append the to a file if it already exists. STDERR If an “ls” command returns “no files found”, the resulting error message is shown on the screen. If the same command is performed except the results are redirected to a file, the resulting file will be empty and the error message will still be displayed on the screen. The reason is that the results are being sent to STDOUT, and the error is sent to STDERR. Take the following command as an example: ls myfile* >results.txt 2>errors.txt

Figure 1 - Redirecting STDERR to a file STDERR is referenced via the “2>” redirector. In this case, results are sent to the results.txt file, and errors are sent to the errors.txt file. In a given command, STDOUT can be referenced as &1. Consider the following command: ls myfile* >results.txt 2>&1

Figure 2 - Redirecting STDERR to STDOUT In this case, the results.txt file will contain both the results and the errors (if any), since STDERR is redirected to STDOUT.

www.rmoug.org



Ducat

STDIN Standard input typically comes from one of three sources. The first is interactively from the keyboard, the second is from an input file, and the third is from the output of another command. When writing a script to be run in batch mode, keyboard entry is usually no t an option. For instance, if a script is require to enter Sql*Plus and execute an update statement, how can it be done? One possibility would be to add an “@” command to the startup of Sql*Plus, but maybe a single file is desired. One answer is to have Sql*Plus take it’s input from the script instead of the keyboard. Redirecting STDIN can do this. sqlplus scott/tiger –s <
Figure 3 - Redirecting STDIN in a script In this example, the “<<” redirector indicates that an alternate STDIN follows. In this case, “!!” is used to bracket the starting and ending of the input. There is nothing special about the “!!” characters, and any bracketing characters can be used as long as they follow the following rules; The first set of bracketing characters must immediately follow the “<<” redirector, and nothing can follow the characters on the line. To end the standard input, the characters must exist on a line all by themselves. The most common problem when coding these brackets occ urs when spaces are entered before or after the bracketing characters. When this happens, the redirection does not happen properly, and the results can be very confusing.

!

I have never fo und a reason to end lines with spaces in a script, and the following command can be used to remove all trailing spaces from a file when using vi: :1,$s/ *$//

vi hint

The second method of changing standard input is to pipe “|” the results of one command into another command. For instance, the sort command will take a file name as input and display the sorted results to the screen, but other methods are possible. The following two commands accomplish the same results: sort myfile cat myfile | sort

In the first example, sort takes “myfile” as the input, but in the second example, the results of the cat command (which simply displays a file) are sent as input to the sort command. While the second example may seem more complicated, it opens up a whole world of possibilities. For instance, let’s assume we have a file containing the results of a series of Oracle queries, and we want to display a sorted list of “ORA” errors contained in the file. In addition, we want to align the output so each “ORA-“ string is indented in a manner that places the “O” in column 4. The following command would do this: grep “ORA-“ myfile | sed –e ‘s/^b*//;s/^/bbb/’ | sort

Let’s take this command apart. The first portion is the grep. Oracle errors generally start with “ORA-“, and w e use grep to extract all of these lines from “myfile”. When Oracle generates the error messages, the resulting lines can be indented all over the place. We want them to be consistent for display purposes. The sed command is used to take the results of the grep command and format them. Sed is a stream editor, which applies VI like commands to each line of input. In our case, we are going to apply two substitute commands (separated by the “;” cha racter) to each line. The first substitution, ( s/^ b*//), will find the beginning of line followed by any number of spaces, and remove the spaces. This will result in all the ORAlines being left justified. The second substitution will add 3 spaces to the beginning of each line. Next, the results are sent to sort where they a re displayed on t he screen in sorted order. Of course, we could pipe the results to a file using the “>” redirector, or the results could be displayed to the screen and sent to a file by piping the resu lts to the “tee” command. The options are really only bound by your imagination. In fact, UNIX commands can be thought of much like a box of

www.rmoug.org



Ducat

“Tinkertoys”. They can be put them toget her in many ways to solve the same p roblem, and there is no one correct answer. The best we can ho pe to accomplish h ere is to show vari ous contrapti ons others have built, and turn your imagination loose to invent your own!

Environment Variables Environment variables behave much like variables in any language except they are available throughout the shell environment. The re are t wo basic ways to view the contents of an environment variable. First, the UNIX command “env” will show all variables, and the echo command can be used to display a single variable. When referencing an individual variable, the variable name must be referenced starting with the “$” character, and optionally, the variable name can be enclosed in “{}” characters. For instance, to display the contents of the ORACLE_SID variable, either of the following commands could be used: echo $ORACLE_SID echo ${ORACLE_SID}

While both commands will work, the second method is desirable. The basic reason is consistency. Good programming techniques dictate a consistent s tyle, and the first method does not always w ork. For instance, let’s assume a script mu st display the contents of the ORACLE_SID variable followed by “_world”. echo $ORACLE_SID_world echo ${OACLE_SID}_world

The second example would give the desired result, but the first would be looking for a variable named ORACLE_SID_world. NOTE: Keep in mind that variable names, like most things in UNIX, are case sensitive.

One use of environment variables is to store username and password combinations. Consider the following: SYSTEM_CONNECT=system/manager sqlplus ${SYSTEM_CONNECT}

In this example, sqlplus uses the contents of the SYSTEM_CONNECT variable as the username and pas sword. T he Password Management section goes into this in more detail.

EXPORTING ENVIRONMENT VARIABLES The scope of environment variables can be expanded using the “export” command, but a great deal of care must be taken when u sing th is command. When a variable is exported, that variable is viable to other scripts called from the process where the export was performed. While this can be very useful, it does pose a security problem. The “ps aew” command will show all of the exported environment variables for all other processes on the system. If a variable that contains a password is exported, this command can be used to display those val ues. There are possible workarounds to this, but not exporti ng password variables is the safest optio n. Scripts If a series of commands are placed in a file, it is called a script. To run a script, first modify the permissions on the file to include execute permission, and then enter its name at the command line. For instance, consider the following script called s1.ksh: date pwd whoami

www.rmoug.org



Ducat

Figure 4 - Basic script

www.rmoug.org



Ducat

If this script were created using your favorite editor (which should be vi ☺), execute permission could be granted via the following command: chmod +x s1.ksh

Assuming the current path contains “.”, simply typing “s1.ksh” would execute the script. If “.” Is not in the path, the script could be executed by entering “./s1.ksh”. A run of this script might look something like: %s1.ksh Sat Dec 2 13:59:28 MST 2000 /common/bin wducat %

This is scripting at its most basic, and the remainder of this paper will focus on writing scripts that are more powerful. Throughout this paper, many scripts start with “#! /bin/ksh”. Even though it looks like a comment, it is not. By including this line, UNIX will use Korn shell syntax rules regardless of the default shell.

Parameter Passing Many scripts require parameters to be passed in to assist in processing. When calling a script from the UNIX command line, parameters can be included by simply including them on the same line as the command, separating then with spaces. If a parameter must contain spaces, simply enclose the parameter in double quotes. Once in side a KSH script, there are a few ways to obtain parameter information. The following is a list of built in variables that can be used: Variable

Alternate

Usage

$#

${#}

The number of parameters passed in

$0

${0}

The basic command without any parameters

$1

${1}

The first parameter passed in

$2

${2}

The second parameter passed in

. . .

Consider the following example: #! /bin/ksh echo $1 echo $2 echo $3 echo $# echo $0 echo Done

The following is a sample run of this script:

www.rmoug.org



Ducat

% s2.ksh this “is a” test this is a test 3 ./s2.ksh Done %

Reading the output, t he first parameter is “this”, the second is “is a”, and the third is “test”. Three parameters were sent, and the command s2.ksh was run from the current directory.

Flow Control One of the basic requirements of any language is that it provides the ability to control the flow of execution. Functions provide the ability to jump to another section of code, but what if code needs to be skipped? The Korn shell provides four basic constructs used in many scripts. In this paper, we use two of them. They are “If” and “for”. A brief overview of these constructs follows: IF PROCESSING The “if” command provides the ability to perform some task based on some expression. Frequently the expression involves comparing two numbers or two strings. The operators used to compare strings are “=” (equal to) and “!=” (not equal to). Numbers are compared using the “-gt” (greater than), “-ge” (greater than or equal to), “-eq” (equal to), “-ne” (not equal), “ le” (less than or equal to), and “-lt” (less than). When performing numeric comparisons, the opti onal “test” synt ax should be used. The following code is a sample of this: x=5 if [ test ${x} –gt 0 ] ;then echo ${x} is greater than 0 else echo ${x} is not greater than 0 fi

Please see a good Korn shell syntax text for more detail s on using the “if” c onstructs.

FOR PROCESSING The “for loop” is very useful for processing a list of values. Consider the following example: for x in a b c ;do echo ${x} done

www.rmoug.org



Ducat

This code will print out a, b, and c, each on their own line. The real power of the “for loop” comes in the definition of the list. The list can be a command that generates multiple lines of output. When this is the case, the loop variable takes on each line of output for the duration of the loop. Consider the following example: for x in `ls` ;do my_function ${x} done

This loop would call “my_function” once for each file in the directory, passing the file name as a parameter.

Identifying your process Many times a script will need to write out a temporary file. One technique used when specifying a temporary file name is to append the current process id to the name of the file, then remove the file when the task is completed. The current process id can be obtained via the “$$” variable. For instance, typing “echo $$” at the UNIX prompt will return the current process ID (a.k.a your PID). If the PID is 12345 and the command: “ls >/tmp/scrap$$.out” is issued, the file scrap12345.out will be created in the /tmp directory. If a script can be run from multiple windows simu ltaneously, this i s a v ery good technique for isolating results.

www.rmoug.org



Ducat

Practical Examples This section of the paper will use the basic concepts already discussed to build tools that are usef ul in an Oracle environment.

Password Management One of the class ic problems in scripting involves password management. Typically, passwords end u p getting hard coded, and this poses both security and logist ical problems. From a security standpoint, e very script, which contains a hard coded password, must be protected so only authorized persons can view the scripts. From a logistical standpoint, when passwords are changed, every script needs to be found and modified. One othe r logistical issue comes up whenever help is needed to debug a script. In this case, only those with t he passwords can help. Yet another comes up whenever a script is being worked on, anyone looking over your shoulder can see pas swords! By using environment variables, thes e problems can be avoided. One workable solution is the creation of a script, which sets environment variables for each password. A sa mple of this script, which we will call “setup_passwords.ksh”, follows: SYSTEM_CONNECT=system/manager SYS_CONNECT=sys/change_on_install

Figure 5 – Sample setup_passwords.ksh script If this script is run at the start of each script, then the variables defined can be referenced at both the UNIX and embedded SQL levels. This script should be placed in a directory common to all of those who n eed access, and privileges should b e set so only members of the group should be able to read the file. For instance, if only members of the UNIX dba group should have password access, then the file can be owned by the “oracle” account, with the group set to “dba”. The chmod command shou ld be used to set the privileges to “770”. When this is done, a “ls –l” command on the file should look something like: -rwxrwx---

1 oracle

dba

79 Nov 23 13:25 setup_passwords.ksh

Since the “other” group cannot see the file, the passwords are now secure from everyone except the ro ot account, or other users who have your password, but that is a perso nal problem! Once we create this file, it can be used many ways. First, if it is called from your “.profile” 2 , the variables are available at the command line. Second, if it is run at the beginning of each script, the variables are available within the script, and others can look at the scripts without being able to see the passwords. When callin g the script, always proceed the call with a dot and a space so vari ables are visible to the script. For instance, assuming the file is located in the /common/bin directory, the following could be added to each script:

b/common/bin/setup_passwords.ksh

.

If this is done in the .profile, aliases could be created to log into Ora cle. A sample alias follows: alias sqlplusS=’sqlplus ${SYSTEM_CONNECT}’

From that point on, typing “sqlplusS” would start sqlplus as the system account for the default instance. *** WARNING *** 2

The “.profile” file is automatically run when a new KSH session is started.

www.rmoug.org



Ducat

There is one very large wa rning here, depending on the version of UNIX being used. If a parameter password is passed to a UNIX application, and perform a “ps –aef” in another window, all of the parameters passed in the first session may be shown. This includes passwords! Sqlplus is simply a UNIX application, and is subject to the same issues. Oracle has seen fit to fix this problem in some, but not all UNIX environments. For instance, if “sqlplus system/manager” were typed at the UNIX prompt on a Sun or HP system, the following could be seen in another window: % ps -aef |grep sqlplus oracle 10828 10750 0 21:35:50 ttyp1 0:00 grep sqlplus oracle 10823 10808 0 21:35:44 ttyp3 0:00 sqlplus system/manager@devel %

Figure 6 - PS results on HP or Sun If the same thing is done on a Linux or Compaq/DEC environment, another “ps” command will show the parameters have been stripped off by Oracle: % ps -aef |grep sqlplus oracle 10979 22719 0.0 21:37:28 ttyp3 0:00.07 sqlplus oracle 32400 4008 0.5 21:37:50 ttyp4 0:00.01 grep sqlplus %

Figure 7 - PS results on Compaq or Linux This is generally a UNIX issue, and Oracle strips off the parameters to sqlplus in some implementations. Because of this, systems should be tested befo re setting up such aliases. Another related consideration involves “C” programs or other scripts that tak e passwords as parameters. Regardless of the UNIX implementation, the passwords will be available to anyone who does a “ps” command, and the technique shou ld be avoided. Scripts can be setup to get the passwords once the scripts start, and “C” programs can be setup to get environment variables as well, so this should not be a major problem. Simply be aware of the issue, and test accordingly.

Embedded SQL By redirecting STDIN, SQL or PL/SQL code can be directly coded into a KORN shell script. The main advantage of this technique is that UNIX environment variables can be referenced within the SQL portion of the script as well as the shell area. Consider the following example: #! /bin/ksh . /common/bin/setup_passwords.ksh $ORACLE_HOME/bin/sqlplus -s /nolog <
www.rmoug.org



Ducat

v\$license,global_name ; quit; !!

Figure 8 - Embedding SQL in a KSH script This script will connect to oracle using the default instance and perform a series of commands that show connectio n information. There are a few things to consider. o

The –s switch on the sqlplus command is included to remove the Oracle banner from the output.

o

The /nolog switch is used to avoid passing the password on the command line. We use this technique to avoid passing the password in case this is run on a version that does not trim off parameters as discussed in the Password Management section.

o

When embedded in a KSH script, references to v$ tables must always include a “\ ” in front of the “$” character. This is required in a KSH script since UNIX attempts to translate the $ into environment variables.

o

Within the SQL area we issue a “connect” statement which references the SYSTEM_CONNECT variable

When this script, named l1.ksh, was run on a system where the default instance is named devel, the following results were produced: %l1.ksh Connected. Instance Max Warning Current Highwater --------------- ----- ------- ------- --------DEVEL.WORLD 0 0 0 %

1

Notice the first line of the output is a line contain ing “Connected.” This line is the result of the “ connect” statement and, as of this time, there is no way to get sqlp lus to stop producing that output. Neve r fear, the Embedded Subroutines sectio n discusses sways of dealing with this issue.

Embedded Subroutines (aka Functions) When scripting, we do not alw ays want to start at the top of the script, and end at the bottom. As any programmer is well aware, the ability to create a subroutine is indispensable when creating complex code. Ksh provides this functionality in the form of functions embedded within the script. Consider the following script: #! /bin/ksh . /common/bin/setup_passwords.ksh __show_licenses() { $ORACLE_HOME/bin/sqlplus -s /nolog <
www.rmoug.org



Ducat

sessions_current-1 sessions_current, sessions_highwater from v\$license,global_name ; quit; !! } __show_licenses

Figure 9 - Implementing a user-defined function in a KSH script In this example, we have included the definition of a function called __show_licenses. The code for the function is bracketed by the “{}” characters. The () following the function name indicates this is a function. An alternative syntax of “function __show_licenses” could be used if des ired. When this script is run, the function is fir st defined, and then called by the code on the last line. This can be verified by commenting out the last line and running the script. If this were done, the script would produce no output. This script produces the same output as the script in the Em bedded SQL section, but with a slight change, the problem identified in that paragraph can be eliminated. When we call the fun ction on the last line, the results of the function are pushed back through STDOUT. Because of this, we can redirect them through any means we desire. For instance, assume we changed the last line to: __show_licenses >scrap.out

In this case, nothing w ould be sent t o the screen, but the file scrap.out would contain th e output. This still does not get rid of the “Connected.” line discussed earlier, but this can be accomplished by piping the result s through grep to eliminate the offending output. The following would accomplish this: __show_licenses | grep –v “^Connected\.$”

This grep command would eliminate any lines containing only the string “Connected.” so the results would look like: %l3.ksh Instance Max Warning Current Highwater --------------- ----- ------- ------- --------DEVEL.WORLD 0 0 0 %

1

One interesting thing to note is the existence of the backslash character prior to the period character. The grep command uses regular expression matching, and the period represents “any one character”. The backslas h character indicates that the following “special character” should be interpreted literally, not as a part of a regular expression. If the backslash were removed, the code would eliminate lines containing things such as “ConnectedX”, “Connected b”, etc. If this is what is desired, then the period character can be used, but if the desire is to specifically match the period character, the backslash is required. This example would work with or without the backslash, but if sloppy style is used, the issue may be missed in later code, resulting in some ve ry hard to debug issues. In short, m ean what you say, and code what you mea n. There is no “do as I mean, not what I say” mode in UNIX, yet.

www.rmoug.org



Ducat

Passing Parameters

Up until now, the script we have been building only works against the default instance 3. By using parameters, we can increase the usefulness of the script. Before writing this script, there are a few issues to be considered. Parameters passed into functions are handled the same way as parameters passed into scripts (as discussed in the Parameter Passing section), and variables passed to the script must be passed to the function if they are to be used by the function. Consider the following example: #! /bin/ksh __doit() { echo ${1} ${2} } echo ${1} ${2} __doit test ${1}

The two parameters on the second to the last line will refer to the parameters passed in at the command line, and the echo command inside the __doit function will refer to the parameters passed in on the last line. The following run illustrates this: %s3.ksh aaa bbb aaa bbb test aaa %

In this case, the first parameter passed into the script contains “aaa”, and that value is passed as the second parameter to the __doit function. Getting back to our main script, if we modify it to expect a SID as the first parameter, then we no longer need to set our default instance prior to running the script. The following shows this. Notice the parameter references on the connect line as well as on the call to __show_licenses. #! /bin/ksh . /common/bin/setup_passwords.ksh __show_licenses() { $ORACLE_HOME/bin/sqlplus -s /nolog <
The default instance is the one used if no SID is passed on a connect string

www.rmoug.org



Ducat

quit; !! } __show_licenses ${1}| grep –v “^Connected\.$”

One interesting note is that if a parameter is not passed in, it ends up connectin g as system/manager@. This results in a conne ction to the default instance. If this is not desired, a few lines of conditional logic could be added to the bottom in order to verify that a parameter was sent. The following script demonstrate: #! /bin/ksh . /common/bin/setup_passwords.ksh __show_licenses() { $ORACLE_HOME/bin/sqlplus -s /nolog <
If we did not want to pass in the names of our instances, we could hard code them. Consider the following example: #! /bin/ksh . /common/bin/setup_passwords.ksh __show_licenses() { $ORACLE_HOME/bin/sqlplus -s /nolog <
www.rmoug.org



Ducat

column global_name heading "Instance" format a15 column sessions_max heading "Max" format 9999 column sessions_warning heading "Warning" format 9999 column sessions_current heading "Current" format 9999 column sessions_highwater heading "Highwater" format 9999 select global_name,sessions_max,sessions_warning, sessions_current-1 sessions_current, sessions_highwater from v\$license,global_name ; quit; !! } __show_licenses prod|grep -v "^Connected\.$" __show_licenses devel|grep -v "^Connected\.$" __show_licenses systest|grep -v "^Connected\.$"

This script would result in the following: %l6.ksh Instance Max Warning Current Highwater --------------- ----- ------- ------- --------PROD.WORLD 0 0 0 1 Instance Max Warning Current Highwater --------------- ----- ------- ------- --------DEVEL.WORLD 0 0 0 1 Instance Max Warning Current Highwater --------------- ----- ------- ------- --------SYSTEST.WORLD 0 0 0 1 %

www.rmoug.org



Ducat

While it works, the results are not pretty. A few more modifications could correct the formatting issues: #! /bin/ksh . /common/bin/setup_passwords.ksh __show_licenses() { $ORACLE_HOME/bin/sqlplus -s /nolog <
In this case, a few changes were made. First, We added a “heading” environment variable and set it so only the first run would cause t he heading to be printed by sqlplus. Notice the “set heading” command now references the “heading” variable which is set “on” or “off” in the shell area. Next, we added the -E options to the grep so we could also exclude blank lines. The “^$” pattern matches any line containing only the beginning of the line immediately followed by the end of the line. The results of this code can be seen below: Instance --------------PROD.WORLD DEVEL.WORLD SYSTEST.WORLD

Max ----0 0 0

Warning ------0 0 0

Current ------0 0 0

Highwater --------1 1 1

This works well, but it is still hard coded. Every time an instance is added to the environment, the code must change. This can be fixed via the creation of a global function as describes in the Global Functions section.

Global Functions There are times when it is helpful to have a small piece of code that can be called from multiple scripts. In the previous example, we need to dynamically generate a list of instances on the machin e we are runn ing on. This code could be added to

www.rmoug.org



Ducat

the script, but it is something which could be useful in multiple areas. If the code was copied to each script, maintenance would be a problem if the logic ever changed. For instance, the first version may only work on the local machine, but if another machine was added, the code could be modified to pick up those instances as well. If the code is in one place, then one change would in effect, modify all scripts. Our first task is to create the logic required to generate a list of instances on the local machine. For our purposes, we will assume the tnsnames.ora file or the Oracle Names server is configured in a manner where the instance can be reached by including an @ after the password on a connect string. For instance, system/manager@devel will connect to the instance with a SID of devel. Most installations that I have been involved in use this standard, but there are reasons that this standard may not be used . If the SID and connect information are different, appropriate changes will need to be made to the sample scripts. Every instance running on a machine creates what is known as a PMON process. Using the UNIX “ps” command, we can take a look at these processes. Consider the following series of commands: %ps -aef |grep ora_pmon_ oracle 863 1 0 09:11 ? oracle 888 1 0 09:11 ? oracle 913 1 0 09:11 ? wducat 2584 2582 0 16:28 tty1 %ps -aef |grep ora_pmon_ |grep -v grep oracle 863 1 0 09:11 ? oracle 888 1 0 09:11 ? oracle 913 1 0 09:11 ? %ps -aef | \ grep ora_pmon_ | \ grep -vE 'sed|grep'| \ sed -e 's/^.*ora_pmon_//' devel prod systest %

00:00:00 00:00:00 00:00:00 00:00:00

ora_pmon_devel ora_pmon_prod ora_pmon_systest grep ora_pmon_

00:00:00 ora_pmon_devel 00:00:00 ora_pmon_prod 00:00:00 ora_pmon_systest

The first command shows us all of the Oracle pmon processes as well as our grep! The second command filters out the grep. In the third command, we modify the second grep to remove the sed command also added to the third command. The sed command tells the system to replace everything from the beginning of the line through the ora_pmon_ with nothing. The result is a list of all of the currently running insta nces. The following script creates a function using the above logic to create an environment variable containing a list of all available instances. Line numbers are included for reference purposes. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

#! /bin/ksh gen_sid_list() { sid_list="" touch /tmp/sids_$$.out ps -aef | \ grep ora_pmon | \ g rep - vE ' sed|g rep' | \ sed -e "s/^.*ora_pmon_//" \ >/tmp/sids_$$.out for iName in `sort /tmp/sids_$$.out` ;do tnsping ${iName} >/tmp/ping_$$.out

www.rmoug.org



16 17 18 19 20 21 22 23 24 25

Ducat

eCount=`grep TNS- /tmp/ping_$$.out|wc -l` i f [ $ eCou nt -e q 0 ] ;th en sid_list="${sid_list} ${iName}" else echo ERROR: Unable to connect to ${iName} fi rm /tmp/ping_$$.out done rm /tmp/sids_$$.out }

The first thing done is the creation of a function called gen_sid_list. The placement of this function will be discussed later. The following is an analysis of the various lines in the function:

Line Number

Purpose

6

Creates an empty environment variable which will ultimately contain the list of instances

8

Issues the same ps command discussed above, except the results are sent to a temporary file

14

Sets up a for loop which takes the sorted list of instance names in the temp file, and process es t hem one at a time, setting the variable

15

Performs a tnsping on each instance to very if it is available. The results of the ping are sent to a temp file for further review

16

Counts the number of TNS errors returned from the ping

17

Determines if the instance should be added to the variable

18

Adds the instance to the variable

20

Displays an error if problems were found connecting to the instance

22

Cleans up temporary file

24

Cleans up temporary file

If the above script is placed in another file of functions, the script can be called from multiple scripts as long as the file of functions is called from each script. In our example, the function was placed in a file called functions.ksh.

www.rmoug.org



Ducat

The following script as modified from the example in the Passing Parameters section uses this new fun ction: #! /bin/ksh . /common/bin/setup_passwords.ksh . /common/bin/functions.ksh __show_licenses() { $ORACLE_HOME/bin/sqlplus -s /nolog <
Notice the addition of a call to functions.ksh at the top. This makes the gen_sid_list function available. Nothing has changed in the __show_licenses funct ion. First, we call gen_sid_l ist to setup the s id_list environment variable. The heading variable is then set to “on” for the first run. Next a “for loop” is setup to loop through the list of instances. Each time through the loop, the iName variable will contain the name of the next Oracle instance to process. Next, the __show_licenses function is called, and the instance is specified via the iName variable that is passed in as a parameter. Finally, the heading variable is set to “off” for the remaining instances. This loop will be executed repeatedly for each instance. This code is now very generic. If a new instance is brought up on the machine, this script will automatically find it. If an existing instance is taken off-line, it will no longer show up.

www.rmoug.org



Ducat

Conclusion The process we just went through o nly scratches the surface of what can be accomplished with a well-written script. Under close inspection, a number of shortcomings become obvious in the above example. For instance, what if the Oracle instances are on multiple machines and we want to see them all togethe r? What if the insta nce names do not mat ch the connect strings ? What happens if gen_sid_li st generates an error? Can we get the scri pt to stop in this case? As any programmer k nows once a program is “finished”, the real work often begins as enhancements are implemented. Scripting in Korn shell is very much like programming in any 3GL such as “C”. If the task is approached from a programmer’s point of view, the possibilities are endless.

www.rmoug.org


Unix Oracle Shell Scripting

Recommend Documents