Advanced Shell Scripting Automation for Oracle DBAs

To execute Oracle SQL from a Linux shell script, configure your environment variables (like ORACLE_SID), invoke sqlplus, and use a Here-Document (<<EOF) to pass queries directly

We have learned the basics of shell scripts.Now its time to turn to more useful advanced shell scripting side. Here are the Useful Advanced  shell scripting examples for Oracle DBAs

How to Invoke vi within shell script and make changes while executing the script

We often get requirement where we are supposed to edit the file and make changes during the execution of the script.  We can do that in many ways using various command. Here I would be giving some information on editing the file using vi editor

How to use the vi in script

Example:

!/bin/ksh
If [ $# -ne 1 ]
then
echo “\n Usage: $0 \n”
exit 1
fi
FND_SECUREOLD=/u01/app/oracle/tech.dbc
FND_SECURENEW=/u02/app/oracle/tech.dbc
 
ORACLE_SID_OLD=TOM
ORACLE_SID_NEW=TECH
 
file_name=$1
If [ -f ${file_name} ]
then
echo “\n The name of the file is - $1. \n”
echo “\n About to make changes in $1. \n”
vi $file_name << EOF
:%s/${FND_SECUREOLD}/${FND_SECUREOLD}/g
:%s/ ${ORACLE_SID_OLD}/${ORACLE_SID_NEW}/g
:%s/^d/newlines/g
:wq
EOF
 
echo “\n file - $1 has been changed.\n”
 
exit 0

Important observation

1. we can use all the feature of the vi
2. We  have to use space character EOF  for this to be done as given in above example

Related article in vi editor

vi editor Tutorial -1

How to Invoke another shell script from one shell script.

We  can call another script from unix shell script easily.

$cat x.sh
!/bin/ksh
If [ $# -ne 1 ]
then
echo “\n Usage: $0 \n”
exit 1
fi
Filename=$1
Wordcount=wc $Filename
 
Echo “ Word count of $filename   is $ Wordcount”
 
$cat y.sh
!/bin/ksh
If [ $# -ne 1 ]
then
echo “\n Usage: $0 \n”
exit 1
fi
 
file_name=$1
If [ -f ${file_name} ]
then
echo “\n The name of the file is - $1. \n”
echo “\n About to count the word. \n”
x.sh $file_name
 
exit 0

Important observation

1) we can  call the script as it is from the script with proper argument

 

How to Invoke and run Oracle database sql from within the shell script.

If you are a dba ,you are often required to execute oracle sql statement from various shell script to monitor the database

!/bin/ksh
If [ $# -ne 2 ]
then
echo “\n Usage: $0 \n”
exit 1
fi
dbuser=$1
dbpwd=$2
ORAENV_ASK=NO; export ORAENV_ASK
export ORACLE_SID=TECH
. /usr/local/bin/oraenv
 
echo “\n The total space in database \n”
sqlplus $dbuser/$pwd <<EOF
select sysdate from dual;
select sum(bytes) from dba_data_files;
exit
EOF
exit 0

Important observation

• You need to source the oracle environment in shell script

ORAENV_ASK=NO; export ORAENV_ASK
export ORACLE_SID=TECH
. /usr/local/bin/oraenv

•  We can execute sqlplus using EOF clause as shown  in script
• We should put exit in the sqlplus

 

How to use arithmetic in the shell scripts

we can use the Utility used: expr

Arithmetic is done with expr or $(())

expr 1 + 2
expr 5 \* 7

or
$(( 1 + 2))
$(($a + $b))

Important Note Backslash required in front of ‘*’ since it is a filename wildcard and would be translated by the shell into a list of file names.

You can save arithmetic result in a variable.

x=`expr 1 + 2`
or
x=$((1 + 2))

Example

!/bin/sh
x=1
y=2
z=3
Result=`expr $x + $y + $z`
echo “The total is $Result "

or

!/bin/sh
x=1
y=2
z=3
Result=$(($x + $y + $z))
echo “The total is $Result "

Related articles in Shell scripting

• Unix Script Tutorial 1: What is shell and Shell Scripts
• The Guide to Shell Scripting: The Basics
• The guide to Shell Script: If statement
• Unix shell script while ,for loop with examples