Monitoring Tablespace usage using shell script

The following script will report if any tablespace (except temporary tablespace ) hits 85% of its usage.


From oracle user:
[oracle@server1 ~]$ vi /home/oracle/checktbs.sql

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 15
order by pct_free;
exit;

:wq(save & exit)


Now lets create the shell-script which will monitor the space usage of tablespaces:-
From oracle user
[oracle@server1 ~]$ vi /home/oracle/alert.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/checktbs.sql > /home/oracle/test.txt
file1=cat /home/oracle/test.txt
space=`cat /home/oracle/test.txt | wc -l`
if [ $space -gt 3 ]; then
$file1 | mail -s echo "Attention!! Low space Certain Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert.sh > /dev/null


P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

How to find out how many times a database has been restared since its creation

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC;

STARTUP_TIME
—————————————————————————
29-SEP-13 03.43.07.000 AM
29-SEP-13 03.42.31.000 AM
29-SEP-13 03.12.45.000 AM
29-SEP-13 03.11.28.000 AM
28-SEP-13 11.56.35.000 PM
07-SEP-13 10.04.07.000 PM



RMAN Full Backup script for Windows

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file and schedule it in task scheduler.


@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set RUNDIR=C:\TEMP
set LOGDIR=C:\oracle\log
set ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
rem set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS'
set BACKUPDIR=c:\oracle\oradata\backup\%mydate%_%mytime%
set LOGFILE=%LOGDIR%\%ORACLE_SID%_%mydate%_%mytime%.log
set ADDR='job@jobacle.nl'
set TMPLOG=%LOGDIR%\tmplog.$$
mkdir "c:\oracle\oradata\backup\%mydate%_%mytime%""

set CMDFILE=%RUNDIR%\%ORACLE_SID%.rman
echo run { > %CMDFILE%
echo CONFIGURE RETENTION POLICY TO REDUNDANCY 2; >> %CMDFILE%
echo CONFIGURE BACKUP OPTIMIZATION ON;  >> %CMDFILE%
echo CONFIGURE DEFAULT DEVICE TYPE TO DISK; >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP ON;  >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%BACKUPDIR%\ora_cf%%F'; >> %CMDFILE%
echo CONFIGURE DEVICE TYPE DISK PARALLELISM 2;  >> %CMDFILE%
echo CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;  >> %CMDFILE%
echo CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;  >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo CONFIGURE MAXSETSIZE TO UNLIMITED;  >> %CMDFILE%
echo CONFIGURE SNAPSHOT CONTROLFILE NAME TO '%BACKUPDIR%\snapcf_%ORACLE_SID%_%mydate%_%mytime%.f'; >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo SHOW ALL; >> %CMDFILE%
echo sql "alter system archive log current"; >> %CMDFILE%
echo DELETE FORCE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2'; >> %CMDFILE%
echo BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL=0 DATABASE INCLUDE CURRENT  CONTROLFILE PLUS ARCHIVELOG; >> %CMDFILE%
echo sql "alter database backup controlfile to trace as ''%BACKUPDIR%\ctrlfile_%ORACLE_SID%_%mydate%_%mytime%''"; >> %CMDFILE%
echo CROSSCHECK BACKUP; >> %CMDFILE%
echo REPORT OBSOLETE; >> %CMDFILE%
echo DELETE FORCE NOPROMPT OBSOLETE; >> %CMDFILE%
echo }  >> %CMDFILE%

echo LIST BACKUP OF DATABASE; >> %CMDFILE%
echo LIST BACKUP OF ARCHIVELOG ALL; >> %CMDFILE%
echo LIST BACKUP; >> %CMDFILE%

echo Starting the script > %LOGFILE%
%ORACLE_HOME%\bin\rman target / @%CMDFILE% msglog=%TMPLOG% >>%LOGFILE%

type %TMPLOG% >> %LOGFILE%

copy "%TMPLOG%" "%BACKUPDIR%\%mydate%_%mytime%.log"
@echo on
Related Posts Plugin for WordPress, Blogger...