Shell Scripting for Oracle DBA

• Kiran Dalvi
• 06 Jun, 2019
• 0 Comments
• 4 Mins Read

Shell Scripting for Oracle DBA

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 " </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 " [email protected],[email protected] < /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 "33[33mWe cannot find any entry for today." echo -e "Please check the file you have given is correct OR check with tail command manually33[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 "33[34m……….Checking $lns lines writen today -$dt- out of $lst lines in the alert log………33[0m" err=$(tail -$lns $alrt | grep ORA-|tail -1|awk '{print $1}') if [ $err ]; then echo -e "33[31mErrors found:33[0m" echo ------------ tail -$lns $alrt | grep ORA- |grep -n ORA- echo "" echo "" echo -e "33[31mCheck the details of the errors below. 33[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 "33[32mNo Errors Found. Enjoy 33[33m:)33[0m" fi fi fi #######################End of the script

Script to get the DB name from pmon process

dbname=$(ps -ef | grep pmon | grep -v grep | awk '{ print $8 }' | cut -d '_' -f3)
echo $dbname

Script to check database status.



#!/bin/bash
ORATAB=/etc/oratab
echo "`date` "
echo "Oracle Database(s) Status `hostname` :"

db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
pslist="`ps -ef | grep pmon`"
for i in $db ; do
echo "$pslist" | grep "ora_pmon_$i"
if (( $? )); then
echo "Oracle Instance - $i: Down"
else
echo "Oracle Instance - $i: Up"
fi
done

 

Shell Script to Take Backup using Function

#!/bin/bash
#setting up oracle environment
export ORACLE_HOME=/data/app/oracle/product/19c/dbhome
export ORACLE_SID=prim
export PATH=$PATH:$ORACLE_HOME/bin
date=$(date +"%y-%m-%d")
echo "RMAN BACKUP STARTED...$date"
INCREMENTAL_LEVEL_0(){
rman target / < /home/oracle/rman_level0_log_$date
RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p'
INCREMENTAL LEVEL 0 DATABASE
CURRENT CONTROLFILE
FORMAT '/data/rman/%d_C_%T_%u'
SPFILE
FORMAT '/data/rman/%d_S_%T_%u'
PLUS ARCHIVELOG
FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';
RELEASE CHANNEL ch11;
RELEASE CHANNEL ch12;
RELEASE CHANNEL ch13;
}
exit
EOF
}
INCREMENTAL_LEVEL_1(){
rman target / < /home/oracle/rman_level1_log_$date
RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p'
INCREMENTAL LEVEL 1 DATABASE
CURRENT CONTROLFILE
FORMAT '/data/rman/%d_C_%T_%u'
SPFILE
FORMAT '/data/rman/%d_S_%T_%u'
PLUS ARCHIVELOG
FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';
RELEASE CHANNEL ch11;
RELEASE CHANNEL ch12;
RELEASE CHANNEL ch13;
}
exit
EOF
}
filename=/home/oracle/rman_$1_log_$date
echo "filename is $filename"
if [ "$#" == 1 -a "$1" == "level0" ]
then
        echo "correct argument passed...$1"
        INCREMENTAL_LEVEL_0
elif [ "$#" == 1 -a "$1" == "level1" ]
then
        echo "correct argument passed...$1"
        INCREMENTAL_LEVEL_1
        echo "Backup is successfull" | mailx -s "Backup Script" -a $filename [email protected]
elif [ $# -eq 0 ]
then
        echo "Please pass one argument - level0 or level1"
else
        echo "Something went wrong"
fi