########################################################################################################
## Database Health Check Report for RAC. You must create two scripts tbs_usage.ksh and disk_space.sh ##
########################################################################################################
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@ Create tbs_usage.ksh @@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
$ vi /u01/scripts/tbs_usage.ksh
#!/bin/ksh
#This script determines the percentage of disk usage.
# If that percentage is greater than 90% then a mail will be sent to user
# a report notifying them of the usage.
######### ENVIORNMENT SETUP##############################
typeset -i warn="80"
typeset -i crit="90"
typeset -i cnt="0"
logdir=/u01/datapump/daily_report
export logdir
currsid=$1
currdb=$2
logfile=$logdir/daily_usage_report_${currsid}_`date +%y%m%d`.txt
tbsfile=$logdir/tbs_usage_report_$currsid_`date +%y%m%d`.txt
asmfile=$logdir/asm_usage_report_$currsid_`date +%y%m%d`.txt
email_list=`cat /u01/scripts/mail_list.lis`
export logfile tbsfile asmfile email_list
progname=`basename $0`
export ORAENV_ASK=NO
export ORACLE_SID=$currsid
. oraenv
pct_thresh=90
HOSTNAME=`hostname`
export HOSTNAME
######### LOG FILE CLEANUP#####################
if [ -e "$logfile" ]; then
rm -f $logfile
fi
if [ -e "$tbsfile" ]; then
rm -f $tbsfile
fi
if [ -e "$asmfile" ]; then
rm -f $asmfile
fi
######### LOGIC FOR CHECKING DISK USAGE ON NODE 1 START ######################
echo "\n" >> $logfile
echo "################## TODAYS DATE IS:" `date` " ###################################" > $logfile
echo "\n" >> $logfile
echo "############################# DISK USAGE REPORT FOR " `hostname` " ########################" >> $logfile
echo "\n" >> $logfile
for disc in `df| egrep '^/dev|li' | egrep -iv 'cdrom|proc|sys|tmp|pts' |awk '{print $7}'`
do
discUsage=`df -g $disc| awk '{print $4}' | cut -d'%' -f1|grep -v Free`
if [ "$discUsage" -ge "$crit" ]; then
Free=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Critical: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free GB" >> $logfile
typeset -i cnt="1"
elif [ "$discUsage" -ge "$warn" ]; then
Free1=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Warning: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free1 GB" >> $logfile
typeset -i cnt="1"
fi
done
if [ "$cnt" -eq "0" ]; then
echo "All Disc Usage of node '$HOSTNAME' is 'Normal'" >> $logfile
fi
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DISK USAGE ON NODE 1 END ##############################
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 START ##############################
ssh node2 /u01/scripts/disk_space.sh
ssh node2 cat /u01/datapump/daily_report/daily_usage_report.txt >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 END ##############################
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 START ##############################
ssh node3 /u01/scripts/disk_space.sh
ssh node3 cat /u01/datapump/daily_report/daily_usage_report.txt >> $logfile
echo "\n" >> $logfile
############### LOGIC FOR CHECKING DISK USAGE ON NODE 2 END #############################
echo "###################### SWAP SPACE SIZE REPORT node1 #######################" >> $logfile
echo "\n" >> $logfile
./check_swap.ksh
cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "###################### SWAP SPACE SIZE REPORT node2 #######################" >> $logfile
echo "\n" >> $logfile
ssh node2 /u01/scripts/check_swap.ksh
ssh node2 cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "###################### SWAP SPACE SIZE REPORT node3 #######################" >> $logfile
echo "\n" >> $logfile
ssh node3 /u01/scripts/check_swap.ksh
ssh node3 cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "############################# END OF SWAP SPACE SIZE REPORT ########################"
########### LOGIC FOR CHECKING TABLESPACE USAGE START ##############################
sqlplus -s '/ as sysdba' << EOF
set echo off
set feedback off
set lines 150 pages 66 feedback off
spool ${tbsfile}
column tablespace_name format a35 heading 'Tablespace'
column used_pct_of_max format 999 heading 'Used % of Max'
column actual_free_gb format 999 heading 'Actual Free GB'
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) total_tbs_bytes
from dba_data_files
group by tablespace_name),
fragments as
(select tablespace_name, count (*) tbs_fragments,
sum (bytes) total_tbs_free_bytes,
max (bytes) 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) size_to_grow
from dba_data_files
where autoextensible = 'YES'
group by tablespace_name
union
select tablespace_name, sum (bytes) size_to_grow
from dba_data_files
where autoextensible = 'NO'
group by tablespace_name)
group by tablespace_name)
select a.tablespace_name,
round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) used_pct_of_max,
round(autoextend.total_growth_tbs/1024/1024/1024 - round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024/1024)) actual_free_gb
from dba_tablespaces a, 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 round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) >=$pct_thresh
order by used_pct_of_max desc;
spool off
EOF
echo "############################# TABLESPACE USAGE REPORT for $currdb ########################" >> $logfile
echo "\n" >> $logfile
cat $tbsfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING TABLESPACE USAGE END ##############################
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
select name,state,total_mb,free_mb,round(((TOTAL_MB - FREE_MB) / TOTAL_MB)*100) as PCT_USED from v\$asm_diskgroup;
spool off
EXIT;
ENDSQL
#
#
echo "############################# ASM DISKGROUP USAGE REPORT ########################" >> $logfile
cat $asmfile >> $logfile
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
col disk for a20
spool $asmfile
select path DISK,OS_MB "Size in MB",header_status "Disk Type" from v\$asm_disk where header_status IN('CANDIDATE','FORMER') order by os_mb desc;
set heading off
set feedback off
select '*** ASM FREE DISK REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
echo "############################# ASM AVAILABLE DISKS REPORT ########################" >> $logfile
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE END ##############################
#
#
echo "############################# GET DATABASE SIZE REPORT ########################" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
--col disk for a20
spool $asmfile
SELECT ' Data: ' || lpad( round( SUM (bytes) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_data_files
union
SELECT ' Temp: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_temp_files
union
SELECT ' Redo Log: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM v_\$log
order by 1;
set heading off
set feedback off
select '*** DATABASE SIZE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
#
#
echo "\n" >> $logfile
echo "############################# CHECKING INSTANCE STATUS ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
column HOST_NAME format a15
column STATUS format a6
column INSTANCE_NAME format a13
column STIME format a36
column uptime format a55
select Host_Name, Status, Instance_Name
,'Started At: ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime: ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.gv_\$instance;
set heading off
set feedback off
select '*** INSTANCE STATUS END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS END #################################
#
#
echo "############################# GET ALERT LOG ERRORS ########################" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 200
set pagesize 100
spool $asmfile
column MESSAGE_TEXT FORMAT A100;
column ORIGINATING_TIMESTAMP format a40;
select INSTANCE_NUMBER || '-' || INSTANCE_NAME || '-' || HOST_NAME ALERT_LOG_MESSAGE_TEXT from V\$INSTANCE;
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%ORA-%'
union all
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%Global Enqueue Services Deadlock detected%'
order by ORIGINATING_TIMESTAMP desc
;
set heading off
set feedback off
select '*** ALERT LOG SQL END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG END #################################
#
#
echo "############################# GET RMAN REPORT ########################" >> $logfile
########### LOGIC FOR RMAN REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set pagesize 200
set linesize 200
COL STATUS FORMAT a25
COL INPUT_BYTES_DISPLAY FORMAT a20
COL OUTPUT_BYTES_DISPLAY FORMAT a20
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES_DISPLAY,
OUTPUT_BYTES_DISPLAY
FROM V\$RMAN_BACKUP_JOB_DETAILS
where START_TIME between sysdate -7 and sysdate
ORDER BY SESSION_KEY;
set heading off
set feedback off
select '*** RMAN REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT END #################################
#
#
echo "############################# FLASHBACK USAGE REPORT ########################" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set lines 100
col name format a20
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
,round((floor(space_limit / 1024 / 1024) - ceil(space_used / 1024 / 1024) )/ floor(space_limit / 1024 / 1024)*100,2) as PCT_FREE
,round((ceil(space_used / 1024 / 1024) / floor(space_limit / 1024 / 1024))*100,2) as PCT_USED
from v\$recovery_file_dest
order by name;
set heading off
set feedback off
select '*** FLASHBACK USAGE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT END #################################
#
#
echo "############################# GET LOG SWITCH REPORT ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR SWITCH REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 500
set pagesize 500
spool $asmfile
set echo off
SET VERIFY OFF
--Define start date and end date. Enter 0 for today's date.
variable START_DATE number;
variable END_DATE number;
begin
select 30, 0 into :START_DATE, :END_DATE from dual;
end;
/
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
COLUMN DAY FORMAT a6
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v\$log_history a
WHERE
(TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
>=
TO_CHAR(sysdate-:START_DATE, 'DD-MON-YYYY')
)
AND
(TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
<=
TO_CHAR(sysdate-:END_DATE, 'DD-MON-YYYY')
)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
order by 1;
set heading off
set feedback off
select '*** LOG SWITCH REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR LOG SWITCH REPORT END #################################
#
#
rm -f $tbsfile
rm -f $asmfile
mailx -s "Disk Usage report of $currdb for `date +%m-%d-%y`" $email_list < $logfile
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@ Create disk_space.sh @@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
$ vi /u01/scripts/disk_space.sh
#!/bin/ksh
#This script determines the percentage of disk usage.
# If that percentage is greater than 90% then a mail will be sent to user
# a report notifying them of the usage.
######### ENVIORNMENT SETUP##############################
typeset -i warn="80"
typeset -i crit="90"
typeset -i cnt="0"
logdir=/u01/datapump/daily_report
export logdir
logfile=$logdir/daily_usage_report.txt
export logfile
progname=`basename $0`
pct_thresh=90
HOSTNAME=`hostname`
export HOSTNAME
######### LOG FILE CLEANUP#####################
if [ -e "$logfile" ]; then
rm -f $logfile
fi
if [ -e "$tbsfile" ]; then
rm -f $tbsfile
fi
if [ -e "$asmfile" ]; then
rm -f $asmfile
fi
######### LOGIC FOR CHECKING DISK USAGE ON NODE 2 START ######################
echo "#############################DISK USAGE REPORT FOR " `hostname` " ########################" >> $logfile
echo "\n" >> $logfile
for disc in `df| egrep '^/dev|li' | egrep -iv 'cdrom|proc|sys|tmp|pts|shared' |awk '{print $7}'`
do
discUsage=`df -g $disc| awk '{print $4}' | cut -d'%' -f1|grep -v Free`
if [ "$discUsage" -ge "$crit" ]; then
Free=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Critical: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free GB" >> $logfile
typeset -i cnt="1"
elif [ "$discUsage" -ge "$warn" ]; then
Free1=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Warning: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free1 GB" >> $logfile
typeset -i cnt="1"
fi
done
if [ "$cnt" -eq "0" ]; then
echo "All Disc Usage of node '$HOSTNAME' is 'Normal'" >> $logfile
fi
########### LOGIC FOR CHECKING DISK USAGE ON NODE 2 END ##############################
###############################################
## Database Health Check Report for NON-RAC ##
###############################################
$ vi /u01/scripts/tbs_usage.ksh
#!/bin/ksh
#This script determines the percentage of disk usage.
# If that percentage is greater than 90% then a mail will be sent to user
# a report notifying them of the usage.
######### ENVIORNMENT SETUP##############################
typeset -i warn="80"
typeset -i crit="90"
typeset -i cnt="0"
logdir=/u01/datapump/daily_report
export logdir
currsid=$1
currdb=$2
logfile=$logdir/daily_usage_report_${currsid}_`date +%y%m%d`.txt
tbsfile=$logdir/tbs_usage_report_$currsid_`date +%y%m%d`.txt
asmfile=$logdir/asm_usage_report_$currsid_`date +%y%m%d`.txt
email_list=`cat /u01/scripts/mail_list.lis`
export logfile tbsfile asmfile email_list
progname=`basename $0`
export ORAENV_ASK=NO
export ORACLE_SID=$currsid
. oraenv
pct_thresh=90
HOSTNAME=`hostname`
export HOSTNAME
######### LOG FILE CLEANUP#####################
if [ -e "$logfile" ]; then
rm -f $logfile
fi
if [ -e "$tbsfile" ]; then
rm -f $tbsfile
fi
if [ -e "$asmfile" ]; then
rm -f $asmfile
fi
######### LOGIC FOR CHECKING DISK USAGE ON NODE 1 START ######################
echo "\n" >> $logfile
echo "################## TODAYS DATE IS:" `date` " ###################################" > $logfile
echo "\n" >> $logfile
echo "############################# DISK USAGE REPORT FOR " `hostname` " ########################" >> $logfile
echo "\n" >> $logfile
for disc in `df| egrep '^/dev|li' | egrep -iv 'cdrom|proc|sys|tmp|pts' |awk '{print $7}'`
do
discUsage=`df -g $disc| awk '{print $4}' | cut -d'%' -f1|grep -v Free`
if [ "$discUsage" -ge "$crit" ]; then
Free=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Critical: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free GB" >> $logfile
typeset -i cnt="1"
elif [ "$discUsage" -ge "$warn" ]; then
Free1=`df -g $disc|awk '{print $3}'|grep -v block`
echo "Warning: Disc usage of node '$HOSTNAME' for "$disc" is $discUsage% and Available space for $disc is $Free1 GB" >> $logfile
typeset -i cnt="1"
fi
done
if [ "$cnt" -eq "0" ]; then
echo "All Disc Usage of node '$HOSTNAME' is 'Normal'" >> $logfile
fi
echo "\n" >> $logfile
################ LOGIC FOR CHECKING DISK USAGE ON NODE 1 END ################################
echo "###################### SWAP SPACE SIZE REPORT #######################" >> $logfile
echo "\n" >> $logfile
/check_swap.ksh
cat /u01/scripts/check_swap.log >> $logfile
echo "\n" >> $logfile
echo "############################# END OF SWAP SPACE SIZE REPORT for ########################"
# LOGIC FOR CHECKING TABLESPACE USAGE START ##############################
sqlplus -s '/ as sysdba' << EOF
set echo off
set feedback off
set lines 150 pages 66 feedback off
spool ${tbsfile}
column tablespace_name format a20 heading 'Tablespace'
column used_pct_of_max format 999 heading 'Used % of Max'
column actual_free_gb format 999 heading 'Actual Free GB'
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) total_tbs_bytes
from dba_data_files
group by tablespace_name),
fragments as
(select tablespace_name, count (*) tbs_fragments,
sum (bytes) total_tbs_free_bytes,
max (bytes) 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) size_to_grow
from dba_data_files
where autoextensible = 'YES'
group by tablespace_name
union
select tablespace_name, sum (bytes) size_to_grow
from dba_data_files
where autoextensible = 'NO'
group by tablespace_name)
group by tablespace_name)
select a.tablespace_name,
round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) used_pct_of_max,
round(autoextend.total_growth_tbs/1024/1024/1024 - round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024/1024)) actual_free_gb
from dba_tablespaces a, 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 round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) >=$pct_thresh
order by used_pct_of_max desc;
spool off
EOF
echo "############################# TABLESPACE USAGE REPORT for $currdb ########################" >> $logfile
echo "\n" >> $logfile
cat $tbsfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING TABLESPACE USAGE END ##############################
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
select name,state,total_mb,free_mb,round(((TOTAL_MB - FREE_MB) / TOTAL_MB)*100) as PCT_USED from v\$asm_diskgroup;
spool off
EXIT;
ENDSQL
echo "############################# ASM DISKGROUP USAGE REPORT ########################" >> $logfile
cat $asmfile >> $logfile
########### LOGIC FOR CHECKING ASM DISKGROUP USAGE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE START ##############################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
col disk for a20
spool $asmfile
select path DISK,OS_MB "Size in MB",header_status "Disk Type" from v\$asm_disk where header_status IN('CANDIDATE','FORMER') order by os_mb desc;
set heading off
set feedback off
select '*** ASM FREE DISK REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
echo "############################# ASM AVAILABLE DISKS REPORT ########################" >> $logfile
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ASM FREE DISKS AVAILABLE END ##############################
#
#
echo "############################# GET DATABASE SIZE REPORT ########################" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE END ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING DATABASE SIZE #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
--col disk for a20
spool $asmfile
SELECT ' Data: ' || lpad( round( SUM (bytes) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_data_files
union
SELECT ' Temp: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM dba_temp_files
union
SELECT ' Redo Log: ' || lpad( round (NVL (SUM (bytes), 0) / 1024 / 1024/1024, 0), 7) || ' GB' type_size FROM v_\$log
order by 1;
set heading off
set feedback off
select '*** DATABASE SIZE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
#
#
echo "\n" >> $logfile
echo "############################# CHECKING INSTANCE STATUS ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
column HOST_NAME format a15
column STATUS format a6
column INSTANCE_NAME format a13
column STIME format a36
column uptime format a55
select Host_Name, Status, Instance_Name
,'Started At: ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime: ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_\$instance;
set heading off
set feedback off
select '*** INSTANCE STATUS END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING INSTANCE STATUS END #################################
#
#
echo "############################# GET ALERT LOG ERRORS ########################" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG ##############################
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 200
set pagesize 100
spool $asmfile
column MESSAGE_TEXT FORMAT A100;
column ORIGINATING_TIMESTAMP format a40;
select INSTANCE_NUMBER || '-' || INSTANCE_NAME || '-' || HOST_NAME ALERT_LOG_MESSAGE_TEXT from V\$INSTANCE;
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%ORA-%'
union all
select to_char(ORIGINATING_TIMESTAMP) ORIGINATING_TIMESTAMP, message_text MESSAGE_TEXT from X\$DBGALERTEXT
where ORIGINATING_TIMESTAMP > ( sysdate -1 )
and message_text like '%Global Enqueue Services Deadlock detected%'
order by ORIGINATING_TIMESTAMP desc
;
set heading off
set feedback off
select '*** ALERT LOG SQL END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR CHECKING ALERT LOG END #################################
#
#
echo "############################# GET RMAN REPORT ########################" >> $logfile
########### LOGIC FOR RMAN REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set pagesize 200
set linesize 200
COL STATUS FORMAT a25
COL INPUT_BYTES_DISPLAY FORMAT a20
COL OUTPUT_BYTES_DISPLAY FORMAT a20
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES_DISPLAY,
OUTPUT_BYTES_DISPLAY
FROM V\$RMAN_BACKUP_JOB_DETAILS
where START_TIME between sysdate -7 and sysdate
ORDER BY SESSION_KEY;
set heading off
set feedback off
select '*** RMAN REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR RMAN REPORT END #################################
#
#
echo "############################# FLASHBACK USAGE REPORT ########################" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT ##############################
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 132
set pagesize 100
spool $asmfile
set echo off
set lines 100
col name format a20
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
,round((floor(space_limit / 1024 / 1024) - ceil(space_used / 1024 / 1024) )/ floor(space_limit / 1024 / 1024)*100,2) as PCT_FREE
,round((ceil(space_used / 1024 / 1024) / floor(space_limit / 1024 / 1024))*100,2) as PCT_USED
from v\$recovery_file_dest
order by name;
set heading off
set feedback off
select '*** FLASHBACK USAGE REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR FLASHBACK USAGE REPORT END #################################
#
#
echo "############################# GET LOG SWITCH REPORT ########################" >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR SWITCH REPORT #################################
sqlplus -s '/as sysdba' <<ENDSQL
WHENEVER SQLERROR EXIT 5
WHENEVER OSERROR EXIT 10
set feedback off
set linesize 500
set pagesize 500
spool $asmfile
set echo off
SET VERIFY OFF
--Define start date and end date. Enter 0 for today's date.
variable START_DATE number;
variable END_DATE number;
begin
select 30, 0 into :START_DATE, :END_DATE from dual;
end;
/
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
COLUMN DAY FORMAT a6
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v\$log_history a
WHERE
(TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
>=
TO_CHAR(sysdate-:START_DATE, 'DD-MON-YYYY')
)
AND
(TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
<=
TO_CHAR(sysdate-:END_DATE, 'DD-MON-YYYY')
)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
order by 1;
set heading off
set feedback off
select '*** LOG SWITCH REPORT END ***' from dual;
spool off
EXIT;
ENDSQL
cat $asmfile >> $logfile
echo "\n" >> $logfile
########### LOGIC FOR LOG SWITCH REPORT END #################################
#
#
rm -f $tbsfile
rm -f $asmfile
mailx -s "Disk Usage report of $currdb for `date +%m-%d-%y`" $email_list < $logfile
