Oracle Database Commands and Practical Examples

Crontab -Daily Sar output Example in mail alert system

#### Daily Sar Output ######################

Crontab Details:

26 09 * * * /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh

Script Output:

oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh

DD=`date +%d%m%y`

cd /u04/MasterDB/scripts/daily_health_check/sar_output

touch /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log

sar 3600 24 > /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log

cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log >> /u04/MasterDB/scripts/daily_health_check/sar_output/sar.tmp

mailx -s " Hourly OS CPU Utilization SAR Details " abc@abc.com< sar.tmp

rm -rf sar.tmp

exit

oracle@test02:~>

SAR OUTPUT :

Linux 4.12.14-122.20-default (test022) 05/25/20 _x86_64_ (32 CPU)

09:26:01 CPU %user %nice %system %iowait %steal %idle
10:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
11:26:01 all 0.53 0.00 0.19 0.01 0.00 99.28
12:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
13:26:01 all 0.52 0.00 0.19 0.01 0.00 99.28
14:26:01 all 0.57 0.00 0.21 0.01 0.00 99.22
15:26:01 all 0.54 0.00 0.19 0.01 0.00 99.26
16:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
17:26:01 all 0.53 0.00 0.19 0.01 0.00 99.28
18:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
19:26:01 all 0.53 0.00 0.19 0.01 0.00 99.28
20:26:01 all 0.56 0.00 0.20 0.01 0.00 99.22
21:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
22:26:01 all 0.60 0.00 0.20 0.01 0.00 99.20
23:26:01 all 0.59 0.00 0.24 0.01 0.00 99.16
00:26:01 all 0.54 0.00 0.19 0.02 0.00 99.26
01:26:01 all 0.52 0.00 0.18 0.02 0.00 99.28
02:26:01 all 0.56 0.00 0.20 0.02 0.00 99.22
03:26:01 all 0.54 0.00 0.18 0.02 0.00 99.26
04:26:01 all 0.52 0.00 0.18 0.01 0.00 99.28
05:26:01 all 1.64 0.00 0.21 0.02 0.00 98.13
06:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
07:26:01 all 0.53 0.00 0.18 0.02 0.00 99.27
08:26:01 all 0.56 0.00 0.20 0.01 0.00 99.23
09:26:01 all 0.54 0.00 0.19 0.01 0.00 99.26
Average: all 0.59 0.00 0.19 0.01 0.00 99.21

Crontab - Expdp Scripts with mail example

########################### EXPDP ###########################

Crontab Details :

25 06 * * * /u04/MasterDB/scripts/expdp_smrprodb_full.sh 2>&1 >>/u04/MasterDB/expdp/RODB_FULL.log | mail -s "EXPDP Production database backup logs" varun.yadav@mailid.com

Script Output :

oracle@test02:~> cat /u04/MasterDB/scripts/expdp_smrprodb_full.sh

export ORACLE_BASE=/u02/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1

export ORACLE_SID=PRODB

export PATH=$PATH:$ORACLE_HOME/bin

expdp abc/abc@PRODB dumpfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).dmp directory=expdp logfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_PRODB parallel=2

#Moving log files

mv /u04/MasterDB/expdp/expdp*.log /u04/MasterDB/expdp/expdp_fulldb_backup_logs

#Granting permissions for Dump files

chmod -R 775 /u04/MasterDB/expdp/

#Compressing files

gzip /u04/MasterDB/expdp/*.*

#Removing 2 days old dump files

#find /u04/MasterDB/expdp/ -mtime +2 -exec rm {} \;

Crontab- Disk Threshold Alert

#### Disk Alert #################

Crontab Details :

#0,15,30,45 * * * * /u04/MasterDB/scripts/DR_check/diskalert_threshold.sh

Script Output:

oracle@test02:~> cat /u04/MasterDB/scripts/disk_threshod.sh

# Shell script to monitor or watch the disk space

# set admin email so that you can get email

ADMIN="varun.yadav@abc.com"

# set alert level 90% is default

ALERT=90

df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;

#echo $output

usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )

partition=$(echo $output | awk '{ print $2 }' )

if [ $usep -ge $ALERT ]; then

i echo "Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)" |

mail -s " Alert: Almost out of disk space" $usep $ADMIN

done

oracle@test02:~>

HOME

Tuesday 26 May 2020

Crontab -Daily Sar output Example in mail alert system

Crontab - Expdp Scripts with mail example

Crontab- Disk Threshold Alert