- 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 " 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 "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 environmentexport ORACLE_HOME=/data/app/oracle/product/19c/dbhomeexport ORACLE_SID=primexport PATH=$PATH:$ORACLE_HOME/bindate=$(date+"%y-%m-%d")echo"RMAN BACKUP STARTED...$date"INCREMENTAL_LEVEL_0(){rman target / &amp;lt; /home/oracle/rman_level0_log_$dateRUN{ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;BACKUPFORMAT'/data/rman/%d_D_%T_%u_s%s_p%p'INCREMENTAL LEVEL 0 DATABASECURRENT CONTROLFILEFORMAT'/data/rman/%d_C_%T_%u'SPFILEFORMAT'/data/rman/%d_S_%T_%u'PLUS ARCHIVELOGFORMAT'/data/rman/%d_A_%T_%u_s%s_p%p';RELEASE CHANNEL ch11;RELEASE CHANNEL ch12;RELEASE CHANNEL ch13;}exitEOF}INCREMENTAL_LEVEL_1(){rman target / &amp;lt; /home/oracle/rman_level1_log_$dateRUN{ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 10G;ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 10G;BACKUPFORMAT'/data/rman/%d_D_%T_%u_s%s_p%p'INCREMENTAL LEVEL 1 DATABASECURRENT CONTROLFILEFORMAT'/data/rman/%d_C_%T_%u'SPFILEFORMAT'/data/rman/%d_S_%T_%u'PLUS ARCHIVELOGFORMAT'/data/rman/%d_A_%T_%u_s%s_p%p';RELEASE CHANNEL ch11;RELEASE CHANNEL ch12;RELEASE CHANNEL ch13;}exitEOF}filename=/home/oracle/rman_$1_log_$dateecho"filename is $filename"if["$#"== 1 -a"$1"=="level0"]thenecho"correct argument passed...$1"INCREMENTAL_LEVEL_0elif ["$#"== 1 -a"$1"=="level1"]thenecho"correct argument passed...$1"INCREMENTAL_LEVEL_1echo"Backup is successfull"| mailx -s"Backup Script"-a$filenameinfoorcldata@gmail.comelif [ $# -eq 0 ]thenecho"Please pass one argument - level0 or level1"elseecho"Something went wrong"fi