TO ADD A DATAFILE AUTOMATICALLY USING DFADDER V1 FOR TABLESPACE

Problem statement:

One of the major cause of database crash or hang or slowness is space crunch for a particular tablespace. It is frequently noted in all mission critical databases, mostly OLTP systems tend to

perform DML transactions so often in application tables. These tables grow very rapidly causing the tablespace to get full. It is DBA’s primary responsibility to add extra datafile to prevent any unexpected issues. So DBA has to pitch in at the right time to add space. But this task causes overhead to busy DBA’s who focus on revenue bleeding issues. Consider a situation where person A has a priority p1 task where the client scream due to business downtime and the person A at the same time get a mail saying “Tablespace XYZ 99% full” alert. There are no other DBA’s in the project, expect A! What can A do in this situation? It is obvious that A gives attention to p1 first but at the same time, if the space gets full, then there will be another escalation from client. This is where this script can be implemented.

Description:

This script can be used to add datafile to a tablespace automatically. Modify the script according to your needs and test it thoroughly. This is version 1 form of the script. Modifications and enhancements will be released in github in future.

Note: This script is in its basic form and may not work for all type of scenarios, configurations of database and also not in all platforms.

https://github.com/kishan0426/dfadder/blob/main/dfadder.sh

SHELL SCRIPT (dfadder.sh)

#!/bin/bash
#++++++++++++++++++++++++++++++++++++++++++#
# dfadder (v1) script for adding datafile automatically by kishan
#++++++++++++++++++++++++++++++++++++++++++#

#Set the environment variables and file variables
_env(){
touch /home/oracle/spacecrunch.out
touch /home/oracle/dfadd.sql
NOSPACE=/home/oracle/spacecrunch.out
ORACLE_SID=db9zx
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_HOME
PATH=/usr/sbin:/usr/local/bin:/usr/bin:$PATH:$ORACLE_HOME/bin:/u01/app/oracle/product/19.0.0/dbhome_1/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib

#Location of all the datafiles for a particular database
DFPATH=$(echo "/apps01/oradata/DB9ZX/datafile/")
DFADD=$(echo "/home/oracle/dfadd.sql")
}
#Function to check space in the filesystem
_fsspace(){
SPACE=$(df -hk $DFPATH|awk '{print $4}'|grep -v 'Available'|xargs -I {} expr {} / 1024)
if [ $((SPACE)) -ge 1000 ];
then
    _check_space_crunch
fi
}
#Function to check free space in tablespace
_check_space_crunch(){
sqlplus -S "/ as sysdba" <<EOF > log_for_reference_1
spool spacecrunch.out
set heading off
set lines 200  pages 1000
col TABLESPACE_NAME for a20
col TOTAL_FREE_SPACE_MB for 99999999999
with TABLESPACE_V
as
(
select x.TABLESPACE_NAME as TBLSPC,
       round(sum(x.bytes/1048576)) as TOTAL_SPACE_MB
from
    dba_data_files x
    where x.TABLESPACE_NAME <> 'TEMP'
    group by x.TABLESPACE_NAME
order by 1
),
SEGMENT_V as
(
select y.tablespace_name as TBLSEG,
       round(sum(y.bytes/1048576)) as TOTAL_SIZE_OCCUPIED
from
    dba_segments y
    group by y.tablespace_name
order by 1
)
select TBLSPC,
       TOTAL_SPACE_MB,
       sum(TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) as TOTAL_FREE_SPACE,
       round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB)) as PCT
from
    TABLESPACE_V a
    inner join SEGMENT_V b on (a.TBLSPC=b.TBLSEG)
group by TBLSPC,
         TOTAL_SPACE_MB,
         round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB))
having
    sum(TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) < 1000
and
    round(100*((TOTAL_SPACE_MB - TOTAL_SIZE_OCCUPIED) / TOTAL_SPACE_MB))  < 5
order by 4 desc;
spool off
exit;
EOF
}
#Check if space is needed by tablespace
_is_space_need(){
limit=$SPACE
dfminsize=50M
DFMAXSIZE=30000M

#Validate all the conditions before use. Condition is "if space become less than 10 MB and space percent become less than 5%" then datafile will be added. Warning: This may vary due to different sizes. Example - If a tablespace has 1TB of storage and space occupied is 990GB, then space percent become 99%. Still there is 100GB available. This situation needs more attention. If this script is configured for an individual database, then threshold can be modified as per database size and if multiple databases are configured, then it is better to keep this script for non critical and small databases.

if [[ $(cat spacecrunch.out|sed '/^$/d'|awk '{print $3}') -le 10 ]] && [[ $(cat spacecrunch.out|sed '/^$/d'|awk '{print $4}') -le 5 ]] && [[ -n $TSPACE ]] && [[ $(echo $dfminsize|grep -Eo '[0-9]{0,9}') -le `expr $((limit)) - 200` ]]
then
        _dfadd
        echo "Datafile of size $dfminsize has been added to $TSPACE tablespace with autoextend disabled"
elif [[ $(echo $dfminsize|grep -Eo '[0-9]{0,9}') -ge `expr $((limit)) - 200` ]]
then
        echo "Space crunch from OS mount! Add more storage for datafiles"
else
        echo "All tablespaces has sufficient space"
fi
}
TSPACE=$(cat spacecrunch.out|grep -v 'no rows'|awk '{print $1}'|sed '/^$/d'|head -1)

#If space is less than threshold, then add datafile if necessary
_dfadd(){
if [[ -n $TSPACE ]]
then
        RAND=$(shuf -i 0-10000 -n 1)
        RAND1=$(shuf -i 0-10000 -n 1)
        echo -e "def SIZE=$dfminsize\ndef TSPACE=$TSPACE\ndef DFPATH=$DFPATH\ndef RAND=$RAND\ndef RAND1=$RAND1\nalter tablespace &TSPACE add datafile '&DFPATH/&TSPACE&RAND&RAND1.dbf' size &SIZE autoextend off;" > $DFADD
sqlplus "/ as sysdba" @$DFADD <<EOF
exit;
EOF
else
        echo "exiting..."
fi
}
#Execution of all functions
_env
_fsspace
_is_space_need

Leave a Reply