Shell Scripting for Oracle DBA

Shell scripting is really important for Oracle DBA. most of the repetitive work can be done with the help of Shell Script. Recurring tasks, schedule jobs, monitoring, Automating can be automated navigator with the help of Shell Script.

Following video full explain you for creation of Shell Script, permission for Shell Script and scheduling of shell script with the help of crontab.

Shell variable

variable ="Hello"
echo $variable


read input from user.


#!/bin/sh

# Author : Ankush Thavali
# Copyright (c) blognow.com
# Script follows here:

echo "What is your name?"
read PERSON
echo "Hello, $PERSON

Check the input

#!/bin/sh
echo "Tell me your Training Details ?"
read input1

if [ $input1 == "Shell" -o $input1 == "Python" ]
then
echo "your input is $input1"
else
echo "Incorrect Input"
fi



Defining Array Values

#!/bin/sh

NAME[0]="testdb"
NAME[1]="prim"
NAME[2]="pdbprim"
NAME[3]="checkdb"
NAME[4]="ndb"
echo "First Index: ${NAME[0]}"
echo "Second Index: ${NAME[1]}"


For loop

#!/bin/sh
for i in 1 2 3 4 5
do
echo "Looping ... number $i"
done


While Loop


#!/bin/sh
INPUT_STRING=hello
while [ "$INPUT_STRING" != "bye" ]
do
echo "Please type something in (bye to quit)"
read INPUT_STRING
echo "You typed: $INPUT_STRING"
done



IF Statement

#!/bin/sh a=10 b=20 if [ $a == $b ] then echo "a is equal to b" elif [ $a -gt $b ] then echo "a is greater than b" elif [ $a -lt $b ] then echo "a is less than b" else echo "None of the condition met" fi



Command line argument

#!/bin/sh

echo "File Name: $0"
echo "First Parameter : $1"
echo "Second Parameter : $2"


Checking no of argument passed


sh hello.sh hello-world
# I am passing hello-world as argument in command line which will b considered as 1 argument
if [ $# -eq 1 ]
then
echo $1
else
echo "invalid argument please pass only one argument "
fi

Script to check no of processess running on databases.


. /dba/admin/common/kshell/setsid qmsmp
current_date=$(date +%Y-%m-%d)
sqlplus -S "/ as sysdba " <<EOF >/dba/admin/qmsmp/alter/process_count_qmsmp.log

set lines 12345 pages 12345;
col username for a30;
col status for a30;


select name,open_mode from v\$database;
select resource_name, current_utilization ,max_utilization ,INITIAL_ALLOCATION Allocated from v\$resource_limit where resource_name in ('processes', 'sessions');
EOF
mailx -s "Current:Processes & Session counts!! DB Name: QMSMP : $current_date EST " abc@abc.com,abc@abc.com < /dba/admin/qmsmp/alter/process_count_qmsmp.log
 This script can be used to check any ORA- errors are there in the alertlog file for the present day
 #
 #
 !/sbin/bash
 if ! [ $1 ] ; then
 echo Usage:
 echo "sh alertcheck.sh 'alertlogFilename'"
 else
  alrt=$1
  frm=$(grep -m 1 -n  "date '+%F'" $alrt |awk -F[:] '{print $1}')
  if ! [ $frm ]; then
   echo -e "\033[33mWe cannot find any entry for today."
   echo -e "Please check the file you have given is correct OR check with tail command manually\033[0m"
  else
   lst=$(wc -l $alrt | awk '{print $1}')
   lns=$(awk -v a="$lst" -v b="$frm" 'BEGIN{print a-b+1}')
   dt=$(date '+%a %b %d')
   echo -e "\033[34m……….Checking $lns lines writen today -$dt- out of $lst lines in the alert log………\033[0m"
   err=$(tail -$lns $alrt | grep ORA-|tail -1|awk '{print $1}')
   if [ $err ]; then
     echo -e "\033[31mErrors found:\033[0m"
     echo ------------
     tail -$lns $alrt |  grep  ORA- |grep -n ORA-
     echo ""
     echo ""
     echo -e "\033[31mCheck the details of the errors below. \033[0m (Details means the surroundig lines of the error message only)"
     echo  "-------------------------------------"
     tail -$lns $alrt |  grep -A 5 -B 2 --color ORA-
   else
     echo -e "\033[32mNo Errors Found. Enjoy \033[33m:)\033[0m"
   fi
  fi
 fi
 #######################End of the script

Share and Enjoy !

0Shares
0 0

Leave a Comment

Your email address will not be published. Required fields are marked *