Linux Shell Scripting 101

Plumbing for DBAs

Ray Smith

Portland General Electric

Copyright 2010 Raymond S. Smith

Agenda

• Why Plumbing

• Tools

• Fittings and Parts

• Procedures

• Leaks and Repairs

The Plumbing Metaphor

• Part of the Unix tradition

– 40 year track record

• Part of the lexicon

– Pipes |

– Redirects >

– Input and Output

– Buckets and spools

Part 1: Tools

• Books and man pages

• Spare parts

• Stuff in the truck

• Other plumbers

Most Important Tool: Nutshells

Handbooks

Ken O. Burtch Anatole Olczak

Steal, Adapt, Repeat

• If it works

– Find out why it works

– Test it for your problem

– Apply it

Ask for Help

• Hit the blog-o-sphere

• Ask your SA

– Have an example in hand

• Ask other DBAs

• Hit the man page

David Korn (ksh)

Part 2 : Fittings and Parts

• Input and Output

• Pipes

• Valves

• Filters

• Buckets

Input and Output

• Unix Fundamental Rules

– Simple, predictable interfaces

– Text/string input expected

– Text output anticipated

– Something happens in between

Text In Text Out

grep “string”

grep –i

grep –v

cut –d: -f2

awk –F:„{print $2}‟

Redirect out example

echo “Hello, world” > anyfile.txt

cat anyfile.txt

Hello, world

echo “Collaborate OK?” >> anyfile.txt

cat anyfile.txt

Hello, world

Collaborate OK?

Redirect ‘in’ example

mailx –s “Collaborate” ray@mail.com < anyfile.txt

Subshell redirection

• Subshell redirection

– Opens a subshell to your shell script

– Executes the commands

– Exits when the matching ‘EOF’ is seen

• EOF must be on left column of text file

• Use this syntax for sqlplus, RMAN, etc

sqlplus / nolog << EOF

connect / as sysdba

shutdown abort

exit

EOF

Shell scripting is pipe work

echo “Hello, world” > anyfile.txt

echo “Collaborate OK? >> anyfile.txt

echo “Collaborate OK? >> anyfile.txt

cat anyfile.txt | sort –u

Collaborate OK?

Hello, world

cat anyfile.txt | grep OK

Collaborate OK?

cat anyfile.txt | grep OK | cut –f2

OK?

Part 3: Procedures

• How to write a shell script

– Define the output

– Define the input

– Fill in the gaps

hostname

oratab

Report

Only 10g

Skip

comments

Wanted:

List of 10g databases

Sample oratab script

#!/bin/bash

WORKFILE=/tmp/workfile.lst

REPORT=/tmp/oratab_report.rpt

export HOSTNAME=`hostname`

touch $REPORT

cat /etc/oratab | grep “10.” | grep –v ^# >$WORKFILE

for thisENTRY in `cat $WORKFILE`; do

thisSID=`cat $thisENTRY | cut –d: –f1`

echo “$thisSID is on $HOSTNAME” >>$REPORT

done

cat $REPORT

Sample oratab script

#!/bin/bash

WORKFILE=/tmp/workfile.lst

REPORT=/tmp/oratab_report.rpt

export HOSTNAME=`hostname`

touch $REPORT

cat /etc/oratab | grep “10.” | grep –v ^# >$WORKFILE

for thisENTRY in `cat $WORKFILE`; do

thisSID=`cat $thisENTRY | cut –d: –f1`

echo “$thisSID is on $HOSTNAME” >>$REPORT

done

cat $REPORT

• Interpreter callout

Sample oratab script

#!/bin/bash

WORKFILE=/tmp/workfile.lst

REPORT=/tmp/oratab_report.rpt

export HOSTNAME=`hostname`

touch $REPORT

cat /etc/oratab | grep “10.” | grep –v ^# >$WORKFILE

for thisENTRY in `cat $WORKFILE`; do

thisSID=`cat $thisENTRY | cut –d: –f1`

echo “$thisSID is on $HOSTNAME” >>$REPORT

done

cat $REPORT

• Variables declaration

• No ‘export’ : Only for this script

• With ‘export’: Session variable

export WORKFILE=xyz.lst

Sample oratab script

#!/bin/bash

WORKFILE=/tmp/workfile.lst

REPORT=/tmp/oratab_report.rpt

export HOSTNAME=`hostname`

touch $REPORT

cat /etc/oratab | grep “10.” | grep –v ^# >$WORKFILE

for thisENTRY in `cat $WORKFILE`; do

thisSID=`cat $thisENTRY | cut –d: –f1`

echo “$thisSID is on $HOSTNAME” >>$REPORT

done

cat $REPORT

• Grep -v ^#

• Dash v = ignore

• Caret = starts with

#/etc/oratab

##Production:Sales:18-6:10.2.0.4:Morgan

salesp:/oracle/product/10.2.0:Y

##Production:Finance:24-7:11.1.0.7:Ames

finp:/oracle/product/11.2.0:Y

Do loops

for thisENTRY in `cat $WORKFILE`; do

thisSID=`cat $thisENTRY | cut –d: –f1`

echo “$thisSID is on $HOSTNAME” >>$REPORT

done

• For something in something else

• Do something

• Quit when I say you’re done

for thisENTRY in file1.txt file2.txt file3.txt; do

thisSID=`cat $thisENTRY | cut –d: –f1`

echo “$thisSID is on $HOSTNAME” >>$REPORT

done

Manipulating strings w/ cut

cat $thisENTRY | cut –d: –f1

• Cut the string using

• Delimiter (-d) of colon

• Return field (-f)

salesp:/oracle/product/10.2.0:Y

-f1

Manipulating strings w/ awk

cat $thisENTRY | awk –F: „{print $1}

• Cut the string using

• Delimiter (-F) of colon

• Print first string ( print $1 )

salesp:/oracle/product/10.2.0:Y

${1}

Database

Mail

No filter

Email

address

Wanted:

Oracle accounts with DBA role

SQL query script

#!/bin/bash

SPOOL01=/tmp/spoolfile_01.lst

DISTRIBUTION=“myemail@someplace.com”

[ $SPOOL01 ] && rm –f ${SPOOL01}

sqlplus / as sysdba <<EOF

spool ${SPOOL01}

SELECT grantee

FROM dba_role_privs

WHERE granted_role = „DBA‟;

spool off

exit

EOF

mailx –s “DBA grantees” ${DISTRIBUTION} <${SPOOL01}

[ $SPOOL01 ] && rm –f ${SPOOL01}

RMAN script

#!/bin/bash

RMN_SCRIPT=/tmp/rman_script.rmn

WORKFILE=/tmp/rman_workfile.lst

DISTRIBUTION=“myemail@someplace.com”

echo "connect target /" > ${RMN_SCRIPT}

echo "connect catalog ${USER}/${PASS}@${RMANCAT}“ >>${RMN_SCRIPT}

echo "resync catalog;" >>${RMN_SCRIPT}

echo "exit;" >>${RMN_SCRIPT}

rman @RMN_SCRIPT <<EOF | tee –a $WORKFILE

exit

EOF

mailx –s “RMAN execution” ${DISTRIBUTION} <${SPOOL01}

Pipe to a Tee

OEM emcli script

#!/bin/bash

BO_NAME=scripted_blackout_${thisSID}

echo "Creating blackout named '${BO_NAME}' ..."

${EMCLI} create_blackout -name="${BO_NAME}" \

-add_targets=${thisTARGET}:oracle_database \

-schedule="duration::360;tzinfo:specified;tzregion:America/Los_Angeles" \

-reason="Scripted blackout for maintenance or refresh"

sleep 5

$ECHO "Getting blackout information for '${BO_NAME}' ..."

${EMCLI} get_blackout_details -name="${BO_NAME}"

Part 4: Leaks and Repairs

Rule #1: SHELL SCRIPTS MUST WORK

Rule #2: SCRIPTS MUST KEEP WORKING

Design for maintenance

– Clarity

– Simplicity

– Scalability

Predictable Layout

• Consistent layout for all your scripts

1. Header block

2. Change log

3. Independent variables

4. Dependent variables

5. Functions

6. Run-time procedure

• Take out the guesswork

Layout example

#!/bin/bash

#set -x

#########################################################

# File : sample_script.sh

# Parameter : Database name (optional)

# Purpose : Amaze others

########################################################

#=======================================================

# Independent variables

#=======================================================

export BASEDIR=/usr/lbin/orascripts

#=======================================================

# Dependent variables

# Nothing to change below this line

#=======================================================

LOGDIR=$BASEDIR/logs

WORKFILE=$LOGDIR/workfile.lst

Visual clarity

Just like a SQL statement …

select distinct table_name,column_name from all_tab_columns

where column_name like '%AGREE%' or column_name like '%ANN%„ or

table_name like '%ANN%„ or table_name like „%STOR%„ order by

table_name,column_name;

SELECT DISTINCT table_name,column_name

FROM all_tab_columns

WHERE column_name LIKE '%AGREE%'

OR column_name LIKE '%ANN%„

OR table_name LIKE '%ANN%'

OR table_name LIKE '%STOR%'

ORDER BY table_name,column_name;

Emphasize Visual Flow

for thisHOST in `cat ${HOSTLIST}`; do

if [ ${#thisHOST} -gt 5 ]; then

echo "BIG: ${thisHOST} is ${#thisHOST} characters"

else

if [ ${#thisHOST} -lt 3 ]; then

echo "LITTLE: ${thisHOST} is ${#thisHOST} characters"

fi

fi

done

for thisHOST in `cat ${HOSTLIST}`; do

if [ ${#thisHOST} -gt 5 ]; then

echo "BIG: ${thisHOST} name is long"

else

if [ ${#thisHOST} -lt 3 ]; then

echo "LITTLE: ${thisHOST} name is short"

fi

fi

done

The Penny Wise Quiz

a. Shorter variable names =

Less typing =

Less work

b. Obscure variable names =

Reduced transparency =

Poor quality

• Save your cycles for decyphering the logic

– Not the variable names

Shell script functions

function SetPerms770 {

echo “Setting permission to 770 for ${thisFILE}”

chmod 770 ${thisFILE}/*

}

> for thisFILE in `cat ${FILELIST}`; do

> SetPerms770

> done

function CopyFiles {

cd $SOURCE_DIR

ls -1 | grep -i $ORACLE_SID >$WORKFILE

for thisFILE in `cat $WORKFILE`; do

SOURCE_FILE=$SOURCE_DIR/$thisFILE

TARGET_FILE=$TARGET_DIR/$thisFILE

cp –f $SOURCE_FILE $TARGET_FILE

done

rm -f ${WORKFILE}

}

# -----------------------------------------------------------

# Run-time procedure

# ------------------------------------------------------------

SOURCE_DIR=${GOLD_DIR}/rman

TARGET_DIR=${LIVE_DIR}/rman

CopyFiles

Scalability

• Scalability goal #1: Never customize a script

– Overuse variables

– Never hardcode

• Passwords

• Host or database names

• Paths

– Use command-line input, ‘read’, or parameter files

• Balance risk vs. maintenance cost

Command Line Values

#!/bin/bash

thisSCRIPT=$0

ORACLE_SID=$1

BU_TYPE=$2

echo “Executing ${thisSCRIPT} for ${thisSID}”

if [ ${BU_TYPE} == hot ]; then

echo “\tRunning a hot backup”

TestForArchiveMode

else

RunColdBackup

fi

${0} ${1} ${2}

> backup_db.sh silver hot

Command Line Illustration

#!/bin/bash

# file: input_variables.sh

if [ $1 ] ; then

echo "Amaze your $1"

else

echo “provide input, pathetic human!"

fi

>

> ./input_variables.sh friends

> Amaze your friends

No tools required

• Nothing inside the tested script changes

– No surprises

– No customizations

– One script across the enterprise

• Securable

– Does not require write permissions for others

Make the Machine do the Work

• Create everything you need, every time

– Fix permissions too

– Before and after pictures = Acts of Kindness

if [ ! -d $thisDIR ]; then

mkdir $thisDIR

fi

echo “Directory before:”

ls –l

chmod 775 $thisDIR

echo “Directory after:”

ls -l

Thanks

Complete evaluation

• Session 999

ray.smith@pgn.com