Tablespace Monitoring Shell Script

 Below script can be configured in crontab to send a notification to the support DBAs in case tablespace usage crosses a threshold.


1. First, make the below .sql file, which will be used inside the shell script.


In this script we have defined the threshold as 90%. You can change it as per your requirement.


cat /export/home/oracle/Housekeeping/scripts/tablespace_alert.sql

 

 

 

set feedback off

set pagesize 70;

set linesize 2000

set head on

COLUMN Tablespace        format a25 heading 'Tablespace Name'

COLUMN autoextensible         format a11              heading 'AutoExtend'

COLUMN files_in_tablespace    format 999             heading 'Files'

COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'

COLUMN total_used_space       format 99999999 heading 'UsedSpace'

COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'

COLUMN total_used_pct              format 9999      heading '%Used'

COLUMN total_free_pct              format 9999     heading '%Free'

COLUMN max_size_of_tablespace      format 99999999 heading 'ExtendUpto'

COLUM total_auto_used_pct         format 999.99      heading 'Max%Used'

COLUMN total_auto_free_pct         format 999.99      heading 'Max%Free'

WITH tbs_auto AS

     (SELECT DISTINCT tablespace_name, autoextensible

                 FROM dba_data_files

                WHERE autoextensible = 'YES'),

     files AS

     (SELECT   tablespace_name, COUNT (*) tbs_files,

               SUM (BYTES/1024/1024) total_tbs_bytes

          FROM dba_data_files

      GROUP BY tablespace_name),

     fragments AS

     (SELECT   tablespace_name, COUNT (*) tbs_fragments,

               SUM (BYTES)/1024/1024 total_tbs_free_bytes,

               MAX (BYTES)/1024/1024 max_free_chunk_bytes

          FROM dba_free_space

      GROUP BY tablespace_name),

     AUTOEXTEND AS

     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs

          FROM (SELECT   tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow

                    FROM dba_data_files

                   WHERE autoextensible = 'YES'

                GROUP BY tablespace_name

                UNION

                SELECT   tablespace_name, SUM (BYTES)/1024/1024 size_to_grow

                    FROM dba_data_files

                   WHERE autoextensible = 'NO'

                GROUP BY tablespace_name)

      GROUP BY tablespace_name)

SELECT c.instance_name,a.tablespace_name Tablespace,

       CASE tbs_auto.autoextensible

          WHEN 'YES'

             THEN 'YES'

          ELSE 'NO'

       END AS autoextensible,

       files.tbs_files files_in_tablespace,

       files.total_tbs_bytes total_tablespace_space,

       (files.total_tbs_bytes - fragments.total_tbs_free_bytes

       ) total_used_space,

       fragments.total_tbs_free_bytes total_tablespace_free_space,

       round((  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)

           / files.total_tbs_bytes

          )

        * 100

       )) total_used_pct,

       round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100

       )) total_free_pct

  FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto

WHERE a.tablespace_name = files.tablespace_name

   AND a.tablespace_name = fragments.tablespace_name

   AND a.tablespace_name = AUTOEXTEND.tablespace_name

   AND a.tablespace_name = tbs_auto.tablespace_name(+)

and (((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/ files.total_tbs_bytes))* 100 > 90

order by total_free_pct;

 

2. Now prepare the shell script:

At the beginning of the script, we need to define the env variables like ORACLE_HOME, PATCH, LD_LIBRARY_PATH, ORACLE_SID.



Below is the final script(tablespace_threshold.ksh)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

 

 

cat /export/home/oracle/Housekeeping/scripts/tablespace_threshold.ksh

 

 

 

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export ORACLE_SID=PRODDB

cd /export/home/oracle/Housekeeping/scripts

logfile=/export/home/oracle/Housekeeping/scripts/Tablespace_alert.log

cnt1=`ps -ef|grep pmon|grep $ORACLE_SID|wc -l`

if [ $cnt1 -eq 1 ];

then

sqlplus -s "/as sysdba" > /dev/null << EOF

spool $logfile

@/export/home/oracle/Housekeeping/scripts/tablespace_alert.sql

spool off

exit

EOF

# If there are more then these two lines in the output file, mail it.

count=`cat $logfile|wc -l`

#echo $count

if [ $count  -ge 4 ];

then

  mailx -s "TABLESPACE ALERT FOR PROD DB  " support@dbaclass.com <$logfile

fi

fi

 

 Now configure in crontab:

 0,15,30,45 * * * * /export/home/oracle/Housekeeping/scripts/tablespace_threshold.ksh > /export/home/oracle/Housekeeping/logs/ts_alert.log  2>&1

 

Comments

Post a Comment

Popular posts from this blog

Shell script to monitor asm diskgroup usage

 Shell script to monitor asm diskgroup usage =========================================== REQUIREMENT: Write a shell script, which will trigger a mail alert, if the utilization of the asm diskgroup reached 90 percent. SOLUTION: 1. Below is the shell script. Make sure to update ORACLE_HOME, ORACLE_SID inside the shell script. cat /export/home/oracle/asm_dg.sh export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1 export ORACLE_SID=PRODDB1 export PATH=$ORACLE_HOME/bin:$PATH logfile=/export/home/oracle/asm_dg.log sqlplus -s "/as sysdba" > /dev/null << EOF spool $logfile SET LINESIZE 150 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a25 HEAD 'DISKGROUP_NAME' COLUMN state FORMAT a11 HEAD 'STATE' COLUMN type FORMAT a6 HEAD 'TYPE' COLUMN total_mb FORMAT 999,999,999 HEAD 'TOTAL SIZE(GB)' COLUMN free_mb FORMAT 999,999,999 HEAD 'FREE SIZE (GB)' COLUMN used_mb FORMAT 999,999,999 HEAD 'USED SIZE (GB)' COLUMN pct...
Read more

Alert Log Rotation Script In Oracle

 Alert Log Rotation Script In Oracle ==================================== Day by day, alert log size will grow in Oracle database. So for housekeeping, we need to move the existing alert log to a backup location and compress there. Upon moving the alert log, the database will create a fresh alert log automatically. Below is the shell script. WE need to define the ORACLE_HOME in the script. and ORACLE_SID will be passed as an argument while running the script. # $Header: rotatealertlog.sh # *====================================================================================+ # | AUTHOR : DBACLASS SUPPORT TEAM # | | # +====================================================================================+ # | #!/bin/bash echo ======================== echo Set Oracle Database Env echo ======================== ORACLE_SID=$1; export ORACLE_SID ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1 ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/...
Read more

Shell Script To Monitor Lag In Standby Datbase Using Dgmgrl

 Shell Script To Monitor Lag In Standby Datbase Using Dgmgrl =========================================================== Below script is helpful in monitoring lag in standby database and send mail to DBAs in case the lag is increasing. For the script to work, make sure dataguard broker is enabled between primary and standby database.   SEE DGBROKER ARTICLE:  How to setup dgbroker in oracle 12c: Useful dgmgrl commands:   SCRIPT PREPARATION: PRIMARY DB UNIQUE_NAME – > PRIMDB STANDBY DB UNIQUE_NAME -> STYDB cat /home/oracle/dgmgrl_standby_lag.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=primdb export PATH=$ORACLE_HOME/bin:$PATH echo -e “show database stydb”|${ORACLE_HOME}/bin/dgmgrl sys/orcl1234 > DB_DG_DATABASE.log cat /home/oracle/DB_DG_DATABASE.log  | grep “Apply Lag”  > FILTERED_DB_DG_DATABASE.log time_value=`cut -d ” ” -f 14 FILTERED_DB_DG_DATABASE.log` time_param=`cut -d ” ” -f 15 FILTERED_DB_...
Read more