Shell Script to Ping Hostnames and Send SMS if Host is not Available

We use the below procedure to ping hosts and based on availability send SMS to our team. The SMS is send by calling a web service from the BASH Shell script using curl 

The hosts are defined in a file called hosts.txt

— Shell Script —

#!/bin/bash
current_time=$(date “+%Y%m%d%H%M%S%3N”)
#### Above Date Format is in MilliSeconds Accuracy ###
cd /home/online
rm -rf /home/online/SMSHOST.TXT
date
#### Below Code Will Loop the Hosts.TXT file for the Ping Probes ####
cat /home/online/hosts.txt | while read output
do
ping -w 30 -c 1 $output > /dev/null
if [ $? -ne 0 ]
then
SMSTEXT=’The+Host+’$output’+is+not+reachable.+Please+Contact+the+Database+Team.’
echo “Host $output is Down”
curl “http://192.168.10.100:8080/SMSConnect/SendServlet?application=http_gw2&password=http_gw2&content=$SMSTEXT&destination=9998887190&source=97249&mask=COMPANY”
else
echo “Host $output is Up”
fi
done

 

— hosts.txt file containing list of hostnames —

proddb01

proddb02

proddb03

drdb01

drdb02

drdb03

Check ASM Diskgroup Space and Directory Size

The script below can be used to check the Disk Group Space Free and also Check Directory sizes for each Disk Group

The script is written by somebody at Pythian but i cannot re-collect the original link to the blog.

eg:
./asmcmd_du.sh

DiskGroup Total_MB Free_MB % Free
——— ——– ——- ——
DATAC1 15962112 11215880 70
DBFS_DG 415296 403740 97
RECOC1 3996000 3460272 86

./asmcmd_du.sh DATAC1

DiskGroup Total_MB Free_MB % Free
——— ——– ——- ——
DATAC1 15962112 10155732 63

DATAC1 subdirectories size

Subdir Used MB Mirror MB
—— ——- ———
CARATST/ 55646 111356
ECCTST/ 174912 349856
—— ——- ———
Total 2799978 5600788

Script Below :

[code language=”bash”]
#!/bin/bash
# Shadab Mohammad — 2016
#
# – If no parameter specified, show a du of each DiskGroup
# – If a parameter, print a du of each subdirectory
#

D=$1

#
# Colored thresholds (Red, Yellow, Green)
#
CRITICAL=90
WARNING=75

#
# Set the ASM env
#
ORACLE_SID=`ps -ef | grep pmon | grep asm | awk ‘{print $NF}’ | sed s’/asm_pmon_//’ | egrep "^[+]"`
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1

#
# A quick list of what is running on the server
#
ps -ef | grep pmon | grep -v grep | awk ‘{print $NF}’ | sed s’/.*_pmon_//’ | egrep "^([+]|[Aa-Zz])" | sort | awk -v H="`hostname -s`" ” | sed s’/, $//’

#
# Manage parameters
#
if [[ -z $D ]]
then # No directory provided, will check all the DG
DG=`asmcmd lsdg | grep -v State | awk ‘{print $NF}’ | sed s’/\///’`
SUBDIR="No" # Do not show the subdirectories details if no directory is specified
else
DG=`echo $D | sed s’/\/.*$//g’`
fi

#
# A header
#
printf "\n%25s%16s%16s%14s" "DiskGroup" "Total_MB" "Free_MB" "% Free"
printf "\n%25s%16s%16s%14s\n" "———" "——–" "——-" "——"

#
# Show DG info
#
for X in ${DG}
do
asmcmd lsdg ${X} | tail -1 |\
awk -v DG="$X" -v W="$WARNING" -v C="$CRITICAL" ‘\
BEGIN \
{COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = COLOR_BEGIN"31m" ;
GREEN = COLOR_BEGIN"32m" ;
YELLOW = COLOR_BEGIN"33m" ;
COLOR = GREEN ;
}
{ FREE = sprintf("%12d", $8/$7*100) ;
if ((100-FREE) > W) {COLOR=YELLOW ;}
if ((100-FREE) > C) {COLOR=RED ;}
printf("%25s%16s%16s%s\n", DG, $7, $8, COLOR FREE COLOR_END) ; }’
done
printf "\n"

#
# Subdirs info
#
if [ -z ${SUBDIR} ]
then
(for DIR in `asmcmd ls ${D}`
do
echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
done) | awk -v D="$D" ‘ BEGIN { printf("\n\t\t%40s\n\n", D " subdirectories size") ;
printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB") ;
printf("%25s%16s%16s\n", "——", "——-", "———") ;}
{
printf("%25s%16s%16s\n", $1, $2, $3) ;
use += $2 ;
mir += $3 ;
}
END { printf("\n\n%25s%16s%16s\n", "——", "——-", "———") ;
printf("%25s%16s%16s\n\n", "Total", use, mir) ;} ‘
fi

#************************************************************************#
#* E N D O F S O U R C E *#
#************************************************************************#
[/code]

Query to check sessions writing to flashcache on Exadata

Query to check sessions writing to flashcache on Exadata
select se.sid, sn.name, s.value, se.program from v$sesstat s natural join v$statname sn left join v$session se on (s.sid = se.sid) where sn.name in (‘physical write requests optimized’, ‘cell writes to flash cache’, ‘cell overwrites in flash cache’) and s.value <> 0 order by s.sid,name;

Shell Script to Monitor Oracle Tablespace and Send Email Alert Only If Threshold Exceeds

[code language=”bash”]

export MMSG=/tmp/$$.mail
export ADDR="email address here"
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=appdev1

file=/tmp/${$}_`date +%Y%m%d`

sqlplus -s system/password << .eof > $file
set pages 0
select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) "%_free",
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;
exit;
.eof
#list all datafile below
egrep "SOE|APEX|SYSAUX|SYSTEM|TEMP|UNDOTBS1|USERS|AUDIT_TBS|APP_ENCRYPTED" /tmp/${$}_`date +%Y%m%d` >/tmp/table${ORACLE_SID}.txt
check_stat=`cat /tmp/table${ORACLE_SID}.txt|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
then
echo "tablespace less than 20% for $ORACLE_SID : $oracle_num" > $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE" $ADDR < $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE IN DATABASE $ORACLE_SID" $ADDR < /tmp/table$ORACLE_SID.txt
fi

rm -f $MMSG > /dev/null 2>&1
rm $file

[/code]

Simple Shell Script to Check Diskspace and Send Email on AIX

The below simple shell script will check all filesystems and create output file for only filesystems which are more than 80% full and send the log by email.

The below has been checked on AIX7.1 , not too Sure if it works on Solaris. I suppose the -P flag does not work on Solaris.

[code language=”bash”]

#!/bin/ksh
df -P | grep -v Capacity | awk ‘{if ($5 >= 80) {print $5 $6;}}’ > /home/root/diskspacelog
mailx -s "DiskSpace for Server Esb1" </home/root/diskspacelog sysadmin@company.com

[/code]

Shell Script to Monitor AIX Filesystem and Send Email

The Below Shell Script checks the Filesystem mount points and using AWK outputs all filesystem exceeding 90% space to a file called diskspacepoll. Once that is done the sed command removes any special character like ‘%’ from the output file and cleans it to a file called output.log

The next important logic is in the AWK block. Here a variable called pattern is defined using the threshold of 90%. Another variable called var is defined. This is your baseline metric. So it value of pattern exceeds var then the mail is dispatched else the script does nothing. You can put this in crontab as a every 5 minute job to continuously poll the filesystems and incase the threshold is exceed it will dispatch an email immediately to the admin

[code language=”bash”]

#!/bin/ksh
df -P | grep -v Capacity | awk ‘{if ($5 >= 90) {print $5;}}’ > /home/root/diskspacepoll
sed ‘s/[!@#\$%^&*()]//g’ /home/root/diskspacepoll > /home/root/output.log
####### AWK LOGICAL BLOCK #########
pattern=$(awk ‘$1 > 90 {print $1}’ /home/root/output.log)
var=90
if [[ $pattern > $var ]]
then
echo "Please Check with System Administrator" | mailx -s "90% Threshold of DiskSpace exceeded on Server 1 (ESB1)" sysadmin@company.com
fi

[/code]

How many cores is a process using in Linux

Script to check no. of cores being utilized by a process in linux. Save the script below as a shell script and execute:

[root@~]#  sh check_cpu_cores.sh  1111

#!/bin/bash
pids=()
while [ $# != 0 ]; do
        pids=("${pids[@]}" "$1")
        shift
done
if [ -z "${pids[0]}" ]; then
        echo "Usage: $0 <pid1> [pid2] ..."
        exit 1
fi
for pid in "${pids[@]}"; do
        if [ ! -e /proc/$pid ]; then
                echo "Error: pid $pid doesn't exist"
                exit 1
        fi
done
while [ true ]; do
        echo -e "\033[H\033[J"
        for pid in "${pids[@]}"; do
                ps -p $pid -L -o pid,tid,psr,pcpu,comm=
        done
        sleep 1
done

Script to Check Total No. of Blocks in Buffer Cache for each User Object

COLUMN object_name FORMAT A40

COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(*) number_of_blocks

FROM DBA_OBJECTS o, V$BH bh

WHERE o.data_object_id = bh.OBJD

AND o.owner != ‘SYS’

GROUP BY o.object_Name

ORDER BY COUNT(*);

ASH and AWR Scripts for Checking Wait Events and Top Consuming SQL’s

http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-scripts/

Check Objects in Flashcache in Oracle Exadata

The Exadata Smart Flash Cache is a great Performance optimizer. The contents of the flash cache can be read fro m the cell servers on Exadata. But one of the restrictions on this is that it only lists the object id’s and not the name of the object. So that requires us to map those object ID’s to Object name in DBA_OBJECTS view.

We can use Oracle’s very powerful dcli utility to colelct the object id’s from all cells and scp to the DB nodes and load it into an External table to join it with the DBA_OBJECTS view.
1. Firstly we have to add all cells to dcli utility
list flashcache
listcell

chmod 700 mycommands.scl
dcli -g cells.txt -l root -x mycommands.scl

## this will give output of commands put in file mycommands.scl from all cell nodes ##
Incase if there is error with one key then drop and recreate that cells key

[root@exatestceladm01 ~]# dcli -c exatestceladm01 --unkey -l root

root@exatestceladm01’s password:
exatestceladm01: ssh key dropped
## Rekey that cell

root@exatestceladm01 ~]# dcli -g ./cells.txt -l root -k -s ‘-o StrictHostKeyChecking=no’
root@exatestceladm01’s password:
exatestceladm01: ssh key added
exatestceladm02: ssh key already exists
exatestceladm03: ssh key already exists
————————————————————————–

Configure DB nodes SSH using dcli command utility

On Any Cell node create a text file in /root called db.txt

exatestdbadm01
exatestdbadm02
chmod 700 db.txt

dcli -g db.txt -l root -k -s '-o StrictHostKeyChecking=no'

root@exatestdbadm01’s password:
root@exatestdbadm02’s password:
exatestdbadm01: ssh key added
exatestdbadm02: ssh key added
[root@exatestceladm01 ~]# dcli -g db.txt -l root hostname
exatestdbadm01: exatestdbadm01.qiibonline.com
exatestdbadm02: exatestdbadm02.qiibonline.com

2. Create the dcli command to generate the contents of the flash cache

dcli -g /root/cells.txt -l root "cellcli -e list flashcachecontent where dbUniqueName='SWX' and tableSpaceNumber=6" > /tmp/swxoutput.log
This will generate all the flascache objects for Database ‘SWX’ and Tablespace Number 6. You can further refine this criteria to your liking

3. Now Let’s create a script to generate the flash contents, clean the output using AWK to only include the Object ID’s and copy it to
the DB node

#!/bin/bash
PATH=$PATH:$HOME/bin
export PATH
/usr/local/bin/dcli -g /root/cells.txt -l root "cellcli -e list flashcachecontent where dbUniqueName='SWX' and tableSpaceNumber=6" >
/tmp/swxoutput.log
awk -F'\t' '{ print $3 }' /tmp/swxoutput.log > /tmp/swxclean.log
scp -r /tmp/swxclean.log root@exaproddbadm01:/u03/expdp/swx
ssh root@exaproddbadm01 << EOF
chmod 777 /u03/expdp/swx/swxclean.log
chown oracle:dba /u03/expdp/swx/swxclean.log
EOF
exit

## You can schedule this in the crontab of cell server to send it daily

30 07 * * * /root/flashlogs.sh

4. Create an External Table to Read the Flash Contents inside the Database for which you generated the flash contents in above script

SQL> create or replace directory load_flash as '/u03/expdp/swx';

Directory created.

SQL> grant read,write on directory load_flash to public;

Grant succeede

create table flash_contents (
cell_object_id varchar(20)
)
organization external (
type oracle_loader
default directory load_flash
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('swxclean.log')
)
reject limit unlimited;
-- Check if External Tabling is Loading Properly --
select * from flash_contents;

5. Now create a Query to check the Flash Contents and Map it to DBA_OBJECTS to identify it
set lines 999

select DISTINCT a.OBJECT_NAME,a.OBJECT_TYPE from DBA_OBJECTS a,flash_contents b where a.owner=’SWX’ and a.DATA_OBJECT_ID=b.cell_object_id
order by 1;

OBJECT_NAME
OBJECT_TYPE
——————————————————————————————————————————–
——————-
SYS_IL0000107895C00039$$ INDEX
SYS_IL0000107898C00039$$ INDEX
SYS_IL0000107901C00006$$ INDEX
SYS_IL0000108307C00005$$ INDEX
PARTITION
SYS_IL0000108342C00006$$ INDEX
PARTITION
This will display the objects running in flash for this particular database in the particular tablespace

 

Update 2: If you get error like below

 

ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04001: error opening file

 

Most likely your database is an Orace RAC DB so create the same directory structure for the external table files on both nodes and make sure the clean file is SCP’ed  to both compute nodes from the cell node.