How can we get email whenever Tablespace needs Datafile in Oracle database
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In Oracle databases tables are stored in logical entities called Tablespaces. Each Tablespace consists of physical files named Datafiles. In Oracle, however, each datafile can have a maximum size of up to 32GB, because of this, if we have not created enough datafiles, while we have disk space, a tablespace and subsequently the tables it contains may not have space.
In this article we will see a way so that when a tablespace is estimated by its usage that it is approaching a few days before its limit it informs us with a warning email. This will contain the name of the server and instance, along with the name of the tablespace, the space it has left and the command we need to run to create the datafile in that tablespace.
However, we will see that with the following script we can completely automate the process and even create the datafile by itself.
The footsteps
In previous article we had seen how we can set one up Linux servers (Oracle Linux based on RHEL specifically) so that we receive an email whenever an error occurs in the Alert log of the database instance. Accordingly we will now look at the steps to receive email when a tablespace needs a datafile soon.
Create an SMTP relay with Google's gmail
If we have our own relay on the exchange server with configured smtp that will send the emails, we don't need this step.
But now we will see what we do in case we want to send the emails through Gmail.
First we need to connect to gmail account security.
There we should activate 2 factor authentication and then create an application password.
After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:
[oracle@dp-gr ~]$ sudo vi /etc/mail.rc
account gmail {
set smtp-use-starttls
set ssl-verify=ignore
set smtp-auth=login
set smtp=smtp://smtp.gmail.com:587
set from="info@dataplatform.gr"
set smtp-auth-user=dataplatform.gr@gmail.com
set smtp-auth-password="edw_vazoume_to_app_password"
set ssl-verify=ignore
set nss-config-dir=/etc/pki/nssdb
}
We are ready to test that we can send email:
[oracle@dp-gr ~]$ echo "Test" | /usr/bin/mail -A gmail -s "dokimastiko" ToEmailMas@outlook.com
How will we find the Tablespace that Datafile will soon need
We create the following sql query that generates the warning message for the tablespace, when with similar usage the estimate is that the datafile will be needed in less than 20 days or one reaches almost zero:
[oracle@dp-gr ~]$ vi check_tablespaces.sqlset linesize 7000;
set heading off;
set feedback off;
set echo off;
set pagesize 0;
SELECT 'Warning: Low Tablespace space on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance)||' to tablespace '|| TABLESPACE_NAME ||'. Space is enough for estimated ' || to_char(round(DAYS_EST_W_EXT,2),'999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') || ' days!!! There is left free with extends '||to_char(round(FREE_LEFT_MB_W_EXT,2),'999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')|| 'MB. You can execute this command as sysdba user to create the new datafile: ' || FILE_CREATION
FROM(SELECT T.TABLESPACE_NAME,
(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) FREE_LEFT_MB_W_EXT,
(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) / decode(max(t2.MB_PER_DAY),0,0.01,max(t2.MB_PER_DAY)) days_est_w_ext,
(select 'ALTER tablespace ' || d.tablespace_name || ' add datafile ''' || d.file_name ||'_adf_' || md.file_id ||'.dbf'' size 1g autoextend on next 64m maxsize 32767m;'
FROM (select tablespace_name,ROW_NUMBER() OVER (PARTITION BY tablespace_name order by file_id) rn, substr(file_name,0,(length(file_name)-4)) file_name from dba_data_files ) D,
(select (max(file_id)+1) file_id from dba_data_files) MD
where tablespace_name = T.TABLESPACE_NAME and RN=1
) FILE_CREATION
FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID) F,
(SELECT FILE_ID,
FILE_NAME,
BYTES / 1048576 TOTALSPACE,
TABLESPACE_NAME,
DECODE(MAXBYTES, 0, BYTES, MAXBYTES) / 1024 / 1024 AS F_MAXSIZE
FROM DBA_DATA_FILES) T,
(select name, case when count(*)=1 then null else case when sum(used_mb - p_used_mb) >=0 then sum(used_mb - p_used_mb) / (count(*) - 1) else 0 end end mb_per_day
from (select SN.begin_interval_time,
name,
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2) USED_MB,
nvl(LAG(round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) OVER(PARTITION by TS.name ORDER BY
Sn.begin_interval_time),
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) P_USED_MB
from V$TABLESPACE TS,
DBA_HIST_TBSPC_SPACE_USAGE TH,
DBA_HIST_SNAPSHOT SN,
DBA_TABLESPACES TSO
where TS.ts# = TH.tablespace_id
and TH.snap_id = SN.snap_id
and SN.instance_number = 1
and TS.NAME = TSO.tablespace_name
and to_char(SN.begin_interval_time, 'hh24:mi') = '07:00'
order by 2, 1)
group by name) T2
WHERE T.FILE_ID = F.FILE_ID(+)
AND T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME = T2.NAME(+)
GROUP BY T.TABLESPACE_NAME
)
WHERE
(
ROWNUM=1
AND TO_NUMBER(DAYS_EST_W_EXT) < 20 --change this if you need to change the days estimation threshold
AND FREE_LEFT_MB_W_EXT<10240 --minimum free mb left threshold
)
OR
(
ROWNUM=1
AND FREE_LEFT_MB_W_EXT<128 --ignore estimation threshold if space is very low
)
;
/*
select ' ' from dual;
select 'The below command was executed:' from dual;
spool create_dbf.txt
SELECT FILE_CREATION
FROM(SELECT T.TABLESPACE_NAME,
to_char(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0)), '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.') FREE_LEFT_MB_W_EXT,
(SUM(NVL(T.F_MAXSIZE - (T.TOTALSPACE - NVL(F.FREESPACE, 0)), 0))) / decode(max(t2.MB_PER_DAY),0,0.01,max(t2.MB_PER_DAY)) days_est_w_ext,
(select 'ALTER tablespace ' || d.tablespace_name || ' add datafile ''' || d.file_name ||'_adf_' || md.file_id ||'.dbf'' size 128mb autoextend on next 64m maxsize 32767m;'
FROM (select tablespace_name,ROW_NUMBER() OVER (PARTITION BY tablespace_name order by file_id) rn, substr(file_name,0,(length(file_name)-4)) file_name from dba_data_files ) D,
(select (max(file_id)+1) file_id from dba_data_files) MD
where tablespace_name = T.TABLESPACE_NAME and RN=1
) FILE_CREATION
FROM (SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID) F,
(SELECT FILE_ID,
FILE_NAME,
BYTES / 1048576 TOTALSPACE,
TABLESPACE_NAME,
DECODE(MAXBYTES, 0, BYTES, MAXBYTES) / 1024 / 1024 AS F_MAXSIZE
FROM DBA_DATA_FILES) T,
(select name, case when count(*)=1 then null else case when sum(used_mb - p_used_mb) >=0 then sum(used_mb - p_used_mb) / (count(*) - 1) else 0 end end mb_per_day
from (select SN.begin_interval_time,
name,
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2) USED_MB,
nvl(LAG(round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) OVER(PARTITION by TS.name ORDER BY
Sn.begin_interval_time),
round(tablespace_usedsize * TSO.block_size / 1024 / 1024, 2)) P_USED_MB
from V$TABLESPACE TS,
DBA_HIST_TBSPC_SPACE_USAGE TH,
DBA_HIST_SNAPSHOT SN,
DBA_TABLESPACES TSO
where TS.ts# = TH.tablespace_id
and TH.snap_id = SN.snap_id
and SN.instance_number = 1
and TS.NAME = TSO.tablespace_name
and to_char(SN.begin_interval_time, 'hh24:mi') = '07:00'
order by 2, 1)
group by name) T2
WHERE T.FILE_ID = F.FILE_ID(+)
AND T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME = T2.NAME(+)
GROUP BY T.TABLESPACE_NAME
)
WHERE
(
ROWNUM=1
AND TO_NUMBER(DAYS_EST_W_EXT) < 20 --change this if you need to change the days estimation threshold
AND FREE_LEFT_MB_W_EXT<10240 --minimum free mb left threshold
)
OR
(
ROWNUM=1
AND FREE_LEFT_MB_W_EXT<128 --ignore estimation threshold if space is very low
)
;
spool off;
@create_dbf.txt
*/
quit;
/
*The code that is commented /* to */ is to automate the process. We will refer in detail below.
How do we email when a Tablespace Datafile is needed soon?
Now we will create an executable that will run the above query, exporting the results to a text file. In case the file is more than one line, then it will send email with the title “Oracle DB Alert ORCL” in the emails we have defined, having as text a warning that contains the name of the server and the instance, together with the name of the tablespace, the remaining space and the command that we need to execute in order to create the datafile in this tablespace.
The reason the process only completes if the file contains more than one line is so that it doesn't send an email if there is no tablespace that needs a datafile and the text file is empty.
So we create the executable with the following code:
[oracle@dp-gr ~]$ vi check_tablespaces.sh#!/bin/bash
sqlplus -S "/as sysdba" @/home/oracle/check_tablespaces.sql > /home/oracle/check_tablespaces.log
cnt=$(wc -l </home/opc/check_tablespaces.log)
echo $cnt
if [ $cnt -ge "1" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Tablespace Warning on ORCL" \
-r info@dataplatform.gr \
dataplatform.gr@gmail.com \
< /home/opc/check_tablespaces.log
fi
We can alternatively run it remotely on another machine with the user SYS via ezconnect:
#!/bin/bash
sqlplus -S "sys/Kwdikos@hostname:1521/ORCL as sysdba" @/home/oracle/check_tablespaces.sql > /home/oracle/check_tablespaces.log
cnt=$(wc -l </home/opc/check_tablespaces.log)
echo $cnt
if [ $cnt -ge "1" ]
then
/usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
-r info@dataplatform.gr \
dataplatform.gr@gmail.com \
< /home/opc/check_tablespaces.log
fi
We don't forget to have given execute permission before executing it:
[oracle@dp-gr ~]$ chmod +x check_tablespaces.sh
[oracle@dp-gr ~]$ ./check_tablespaces.shHow do we schedule it to be checked every morning?
Since our executable is ready, we can add a line to the crontab in which we will set it to run every morning:
[oracle@dp-gr ~]$ crontab -e#crontab 00 08 * * * . ~/.bash_profile && (/home/oracle/check_tablespaces.sh >> /dev/null 2>&1)
The result
Now when the tablespace exceeds the threshold in the days we have set for it, we will receive an email.
This email will contain a message of the type:
Warning: Low Tablespace space on ORCL@dp-gr to tablespace EXAMPLE. Space is enough for estimated 19 days!!! There is left free with extends 1.085,98MB. You can execute this command as sysdba user to create the new datafile: ALTER tablespace EXAMPLE add datafile '/home/oracle/oracle/app/oracle/oradata/orcl/example01_adf_7.dbf' size 1g autoextend on next 64m maxsize 32767m;*for example I had put 34,000 days as shown in the image below.
How do we completely automate the process so that it creates the Datafile by itself
If in the same sql query we remove the tag as a comment from the code, i.e. the /* and */ . Then during its execution it will not only inform us, but it will also create the datafile in the tablespace itself which will be needed soon.
Then he will inform us with the following email:
*for example I had put 34,000 days as shown in the image below.
