Oracle Database Capacity Report Automation

Planing the capacity needed to Oracle database is a most for a DBA.

This will help us what is the demand of the hardware for Oracle Database to support multi user connections.

It will also show the tablespace growth rate and also give you a knowledge about the expected growth of TS in near future.

PF the script for it.

Schedule the below script in each database server
***********************************************************

#!/usr/bin/ksh

#sid=$1     ### REQUIRED IFF INPUT PARAMATER IS USED.

#typeset -l $sid

sid=xyz ### CHANGE ACCORDING TO ENVIRONMENT

cd /${sid}/admin/scripts/capacity_report ## CREATE FOLDER capacity_report AND PLACE THE TWO SCRIPTS IN THAT.

. /${sid}/admin/profile/oracle.profile

sqlplus / as sysdba @capacity.sql

mv capacity.csv ${sid}_capacity.csv

# FOR 9i sqlplus "/as sysdba" @capacity.sql

scp ${sid}_capacity.csv oracle@rmsserver:/home1/oracle/capacity  ##

rm ${sid}_capacity.csv

*********************************************************** 

Where as

rmsserver- the jump server where we are collecting all the capacity.csv file

from every database server.

Oracle Database Usage threshold set- 85%, if the output show YES then we have to

notify to APPS team.

The capcity.sql is

*********************

SET PAGESIZE 200
SET FEEDBACK OFF
set colsep ,
set trimspool on
set echo  off
set feedback off
SET LINESIZE 20000
column host format a10
column "INSTNC" format a10
column "DB" format a10
COLUMN "85%?" FORMAT A2
column MMAXIOPS format a10
column MAXMBPS format a10
column AVGIOPS format a10
column AVGMBPS format a10
column "%USED" format a2
column "3MNTH" format a2
column "6MNTH" format a2
column "9MNTH" format a7
column "%USED" format 9999.99
column "3MNTH" format 9999.99
column "6MNTH" format 9999.99
column "9MNTH" format 9999.99
column "MMAXIOPS" format 9999.99
column "MAXMBPS" format 9999.99
column "AVGIOPS" format 9999.99
column "AVGMBPS" format 9999.99
column "DB_SIZE" format 99999.99
column "Used" format 99999.99
column "free" format 99999.99
spool capacity.csv
select i.instance_name "INSTNC",
d.NAME "DB",
s.DB_SIZE,
s.Used,
s.Free,
round((s.Used/s.DB_SIZE)*100) "%USED",
round((s.DB_SIZE/c.tot_mon)*3)  "3MNTH",
round((s.DB_SIZE/c.tot_mon)*6) "6MNTH",
round((s.DB_SIZE/c.tot_mon)*9) "9MNTH",
CASE when round((s.Used/s.DB_SIZE)*100)>85 THEN 'Y'
ELSE 'N' END "85%?",
io.MAXIOPS, 
io.MAXMBPS,
io.AVGIOPS, 
io.AVGMBPS
from
(select round(sum(used.bytes) / 1024 / 1024 / 1024 ) "DB_SIZE",
(round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024)) Used,
(round(free.p / 1024 / 1024 / 1024)) Free
from
(select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p)s,
(select ceil(months_between(sysdate, created)) tot_mon from v$database) C,
v$instance i,
v$database d,
(SELECT MAX(iops) "MAXIOPS", MAX(mbps) "MAXMBPS",AVG(AVG_IOPS) "AVGIOPS", AVG(AVG_MBPS) "AVGMBPS" FROM
   (
   SELECT MAX(rdi+wri) IOPS ,MAX(tr+tw) MBPS ,AVG(rdi+wri) AVG_IOPS,AVG(tr+tw) AVG_MBPS FROM ( SELECT end_time,
    ROUND(reads/inttime,3) rdi, ROUND(writes/inttime,3) wri,
    ROUND((reads+writes)/inttime,3) tio,ROUND((tbr/inttime)/1048576,3) tr,
    ROUND((tbw/inttime)/1048576,3) tw,          ROUND(((tbr+tbw)/inttime)/1048576,3) tm,
    BEG_ID, END_ID
     FROM (     SELECT beg.snap_id beg_id, end.snap_id end_id,  beg.instance_number beg_inst_num,end.instance_number end_inst_num,
    end.begin_interval_time begin_time, end.end_interval_time end_time,
    (extract(day from (end.end_interval_time - end.begin_interval_time))*86400)+
    (extract(hour   from (end.end_interval_time - end.begin_interval_time))*3600)+
    (extract(minute from (end.end_interval_time - end.begin_interval_time))*60)+
    (extract(second from (end.end_interval_time - end.begin_interval_time))*01) inttime,
    decode(end.startup_time,end.begin_interval_time,end.reads,(end.reads-beg.reads)) reads,
    decode(end.startup_time,end.begin_interval_time,end.writes,(end.writes-beg.writes)) writes,
    decode(end.startup_time,end.begin_interval_time,end.tbr,(end.tbr-beg.tbr)) tbr,
    decode(end.startup_time,end.begin_interval_time,end.tbw,(end.tbw-beg.tbw)) tbw
     FROM
     (SELECT dba_hist_snapshot.snap_id, dba_hist_snapshot.instance_number,startup_time, begin_interval_time, end_interval_time,
    sum(decode(stat_name,'physical read total IO requests',value,0)) reads,
    sum(decode(stat_name,'physical write total IO requests',value,0)) writes,
    sum(decode(stat_name,'physical read total bytes',value,0)) tbr,
    sum(decode(stat_name,'physical write total bytes',value,0)) tbw
     FROM SYS.wrh$_sysstat, SYS.wrh$_stat_name, dba_hist_snapshot
     WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
     AND wrh$_sysstat.instance_number = dba_hist_snapshot.instance_number
     AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
     AND wrh$_sysstat.dbid = wrh$_stat_name.dbid
     AND dba_hist_snapshot.dbid = wrh$_stat_name.dbid
      AND begin_interval_time >=(SELECT TO_CHAR(SYSDATE-180,'DD-MON-YYYY HH24:MI') FROM DUAL)
     AND end_interval_time <=(SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI') FROM DUAL)
     group by dba_hist_snapshot.snap_id, dba_hist_snapshot.instance_number,startup_time, begin_interval_time, end_interval_time
     order by 1,2) beg,
    (SELECT dba_hist_snapshot.snap_id, dba_hist_snapshot.instance_number,startup_time, begin_interval_time, end_interval_time,
sum(decode(stat_name,'physical read total IO requests',value,0)) reads,
    sum(decode(stat_name,'physical write total IO requests',value,0)) writes,
    sum(decode(stat_name,'physical read total bytes',value,0)) tbr,
    sum(decode(stat_name,'physical write total bytes',value,0)) tbw
     FROM SYS.wrh$_sysstat, SYS.wrh$_stat_name, dba_hist_snapshot
     WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
     AND wrh$_sysstat.instance_number = dba_hist_snapshot.instance_number
     AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
     AND wrh$_sysstat.dbid = wrh$_stat_name.dbid
     AND dba_hist_snapshot.dbid = wrh$_stat_name.dbid
    AND begin_interval_time >=(SELECT TO_CHAR(SYSDATE-180,'DD-MON-YYYY HH24:MI') FROM DUAL)
     AND end_interval_time <=(SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI') FROM DUAL)
     group by dba_hist_snapshot.snap_id, dba_hist_snapshot.instance_number,startup_time, begin_interval_time, end_interval_time
     order by 1,2) end
     WHERE beg.snap_id + 1 = end.snap_id
     and beg.instance_number = end.instance_number
)
)))io;
set feedback on
spool off
exit
 

Now in Jump server we have to merge all the *csv file and send the report 

in a single header.

In RMS SERVRER

*******************

********************

Sample Output: