Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Administration’ Category

Script to check disk space on Windows

Posted by Pavan DBA on March 9, 2012


######################################

DISK SPACE CHECK ON WINDOWS

######################################

usageLog = “C:\UsageLog.txt”

 

‘ 5%

 

 

Const cdoSendUsingMethod = “http://schemas.microsoft.com/cdo/configuration/sendusing”

Const cdoSendUsingPort = 2

Const cdoSMTPServer = “http://schemas.microsoft.com/cdo/configuration/smtpserver”

Const cdoSMTPServerPort = “http://schemas.microsoft.com/cdo/configuration/smtpserverport”

Const cdoSMTPConnectionTimeout = “http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout”

Const cdoSMTPAuthenticate = “http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”

Const cdoBasic = 1

Const cdoSendUserName = “http://schemas.microsoft.com/cdo/configuration/sendusername”

Const cdoSendPassword = “http://schemas.microsoft.com/cdo/configuration/sendpassword”

 

 

Dim objEmail

 

‘ WScript.Echo “Hi”

 

 

”””””””””””””””

‘ Mail for I Drive

”””””””””””””””’

minCapacity = 0.03

Set fso = WScript.CreateObject(“Scripting.FileSystemObject”)

Set oDrive = fso.GetDrive(“I”)

curSpace = oDrive.FreeSpace

totSize = oDrive.TotalSize

 

curUsage = curSpace/totSize

curFree = 1 – curUsage

 

‘WScript.Echo “Current free space: ” & FormatNumber(oDrive.FreeSpace,0)

‘WScript.Echo “Total size: ” & FormatNumber(oDrive.TotalSize,0)

‘WScript.Echo “Current usage(%): ” & FormatPercent(curUsage, 2)

‘WScript.Echo “Current free(%): ” & FormatPercent(curFree, 2)

 

‘————————-

‘ Check the capacity here

‘————————-

if curUsage < minCapacity then

‘ WScript.Echo “Too low!”

 

‘ write log file here

‘ Set fso = CreateObject(“Scripting.FileSystemObject”)

‘ Set f = fso.CreateTextFile(usageLog, True)

‘ f.WriteLine(“Space too low!”)

‘ f.Close

 

‘—————————-

‘Mailing Starts Here

‘—————————-

 

Set objEmail = CreateObject (“CDO.Message”)

Set Conf = CreateObject(“CDO.Configuration”)

 

Conf.Fields.Item(cdoSendUsingMethod) = cdoSendUsingPort

‘ Conf.Fields.Item(cdoSMTPServer) = “BP1XEUEX707-C.bp1.ad.bp.com”

Conf.Fields.Item(cdoSMTPServer) = “amsmtp.bp.com”

 

Conf.Fields.Item(cdoSMTPServerPort) = 25

Conf.Fields.update

set objEmail.Configuration = conf

 

objEmail.To = “dba_ora_offshore@bp.com; 919949520316@airtelap.com”

objEmail.From = “dba_ora_offshore@bp.com”

objEmail.Subject = “Disk Space Alert on I Drive for bp1tulap151(prd2)”

objEmail.HTMLBody = round(curSpace/1024/1024/1024,2) & “GB free space is available on I Drive – bp1tulap151(prd2)”

objEmail.Send

 

‘ wscript.echo “Mail Sent”

Set objEmail = nothing

Set Conf = nothing

 

‘——————————

‘Mailing ends here

‘—————————–

 

else

‘ WScript.Echo “OK!”

end if

 

 

Posted in Administration, Scripts | Tagged: , , , , | Leave a Comment »

Script for tablespace free space email alert

Posted by Pavan DBA on March 9, 2012


#####################################################################################

MONITOR SCRIPT – it will monitor objects close to max extents, tablespace free space

#####################################################################################

#! /bin/sh

TS=`date “+%y%m%d”`

ORACLE_HOME=/d001/oracle/9.2.0.8-64

LD_LIBRARY_PATH=$ORACLE_HOME/lib

ORACLE_TERM=vt100

ORACLE_SID=ORVIT8QA

LOG=/d002/oracle/$ORACLE_SID/utilitys

PATH=/$ORACLE_HOME/bin:$PATH

export ORACLE_HOME ORACLE_SID ORACLE_TERM PATH LOG LD_LIBRARY_PATH

for n in 1

do

if [ $n -eq 1 ]; then

TWO_TASK=brcvita8_ORVIT8QA

DB_NAME=ORVIT8QA

fi

export TWO_TASK

monitor_file=$LOG/$DB_NAME.log

#n=`expr $n + 1`

 

rm $monitor_file

echo “Objects close to max extents:” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set pagesize 100;

set linesize 80;

column segment_name format a35;

column type format a10;

column owner format a10;

column extents format 9999;

column max_extents format 9999;

select owner, segment_name, segment_type “TYPE”, extents, max_extents

from sys.dba_segments

where extents/greatest(max_extents,1) >= 0.90

and segment_type not in(‘CACHE’,’ROLLBACK’);

exit;

eof

 

echo “Objects that cannot get next extent:” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set pagesize 100;

set linesize 80;

column segment_name format a33;

column type format a8;

column owner format a10;

column tablespace format a13;

column next_extent format 999999999;

select owner, segment_name, segment_type “TYPE”,

tablespace_name “TABLESPACE”, next_extent

from sys.dba_segments b

where segment_type !=’ROLLBACK’ and

next_extent > (select max(bytes) from sys.dba_free_space a

where a.tablespace_name = b.tablespace_name);

exit;

eof

 

echo “Tablespaces with low freespace (< 20% & < 250 MB):” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set echo off

set pagesize 30

set feedback off;

drop table system.mon_smt_ts;

create table system.mon_smt_ts as

(select x.tablespace_name, round(sum(x.bytes)/1048576,1) freespace

from sys.dba_free_space x

group by x.tablespace_name);

 

drop table system.mon_smt_ts1;

create table system.mon_smt_ts1 as

(select tablespace_name, round(sum(bytes)/1048576,1) availspace

from sys.dba_data_files

group by tablespace_name);

set feedback on;

column “Table Space” format a20

column “Allocated” format a12

column “Free Space” format a12

column “Space Used” format a12

select x.tablespace_name “Table Space”,

to_char(availspace,’99999.9′)||’ Meg’ “Allocated”,

to_char(freespace,’99999.9′)||’ Meg’ “Free Space”,

round(freespace/availspace*100) “Pct Free”,

to_char((availspace-freespace),’99999.9′)||’ Meg’ “Space Used”

from system.mon_smt_ts x, system.mon_smt_ts1 y

where y.tablespace_name = x.tablespace_name

and round(freespace/availspace*100) < 20 and freespace<250

order by round(freespace/availspace*100);

exit;

eof

 

null_findings=`egrep “no rows selected” $monitor_file | wc -l`

null_findings=`echo $null_findings`

 

if [ “$null_findings” != 3 ]; then

/usr/bin/mailx -s “$DB_NAME Exception Report” dba_ora_offshore@bp.com < $monitor_file

fi

 

done

Posted in Administration, Scripts | Tagged: , , , | Leave a Comment »

Script to check mount point space and get email

Posted by Pavan DBA on March 9, 2012


 

######################################

SCRIPT FOR CHECKING DISK SPACE IN UNIX

######################################

 

#!/bin/ksh

#rm /tmp/dfk.txt

#echo “df -k output for `date` `uname -n`” > /tmp/dfk.txt

echo “File system usage exceeded the threshold on `uname -n` server- `date`” > /tmp/dfk.txt

echo “” >> /tmp/dfk.txt

i=1

while [ $i -le `df -k | grep -v proc | grep -v capacity | wc -l` ] ;do

if [ `df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 | awk ‘{print $5}’ | \

sed -e ‘s/%//’` -gt 97 ] ; then

#echo “File system usage exceeded the threshold on `uname -n` server- `date`” > /tmp/dfk.txt

#echo “” >> /tmp/dfk.txt

df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 >> /tmp/dfk.txt

fi

((i=i+1))

done

if [ `cat /tmp/dfk.txt | wc -l` -gt 2 ] ; then

cat /tmp/dfk.txt | mailx -s “File system full alert” bp_hostsupport@satyam.com,dba_ora_offshore@satyam.com

#cat /tmp/dfk.txt

else

exit

fi

Note: the above script will check for mount point space and will send an alert if the used space is > 97%

Posted in Administration, Scripts | Tagged: , , , , | Leave a Comment »

Script to check space for one mount point

Posted by Pavan DBA on March 9, 2012


##########################################

SPACE CHECK FOR A MOUNT POINT IN UNIX BOX

###########################################

#!/bin/ksh

lis=`df -k /d801/oracle/SDSS | grep % | awk ‘{print $5}’| sed ‘s/%//g’`

ci=80

SCRIPT_DIR=/d002/oracle/SDSS/utilitys/

CTIME=`date +%HH%MM%SS`

if test $lis -gt $ci

then

( echo “$lis% space is used in /d801/oracle/SDSS/” > /d002/oracle/SDSS/utilitys/spacecheck.txt

mailx -s “Archive Destination > 80% in /d801/oracle/SDSS/!!” dba_ora_offshore@bp.com 919949520316@airtelap.com < /d002/oracle/

SDSS/utilitys/spacecheck.txt

)

fi

Note : The above script is only for one mount point

Posted in Administration, Scripts | Tagged: , , , , , | Leave a Comment »

Script to get Tablespace total, free and used space

Posted by Pavan DBA on February 21, 2012


set lines 300

SELECT /* + RULE */  df.tablespace_name “Tablespace”,
       df.bytes / (1024 * 1024) “Size (MB)”,
       round(SUM(fs.bytes) / (1024 * 1024)) “Free (MB)”,
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
       Round((df.bytes – SUM(fs.bytes)) / 1024/1024) “Used space”,
       Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
  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
 order by  “% Used” desc;

Posted in Administration, Scripts | Tagged: , , , , , , | Leave a Comment »

 
%d bloggers like this: