Capacity and Growth Forecast
How to estimate the growth and forecast of a database on a host.
Step-by-step guide
The procedure is at the bottom of this article.
Login to enterprise manager from sqlplus (from omsdbaup03)
oracle@omsdbaup03:/users/oracle [emrep12c]
$ sqlplus sysman/xxxxxx
2. Follow the following to calculate growth for each database on a host
SQL> set serveroutput on;
SQL> exec db_growth('ITRDBAUP02','text');
Host is ITRDBAUP02
Database: DOVITRHAUP1
Allocated DB free mb 319240.56 MB
Allocated OS free mb 2132379880 MB
Growth per month 247.91 MB
This system will reach 100% by : Too far in the future to calculate
------------------------------------------------------------------------
Host is ITRDBAUP02
Database: DOVITRKAUP1
Allocated DB free mb 281163.5 MB
Allocated OS free mb 2132379880 MB
Growth per month 21420.16 MB
This system will reach 100% by : Too far in the future to calculate
------------------------------------------------------------------------
Disclaimer: This is an estimate only, data is not distributed evenly across all
tablespaces and is not consumed evenly either!
The following parameters are accepted.
procedure: db_growth
Parameter 1: host_name
Parameter 2: output type, (text or excel). Default is excel
Parameter 3: header: on/off (if using excel, it will display a list of headings per column). Default is off
Parameter 4: debug: 0/1 (this will display a list of debugging features for development only). Default is 0 (off)
Here is the procedure:
CREATE OR REPLACE PROCEDURE SYSMAN.db_growth (in_host_name in varchar2, output in varchar2 default 'excel', header in varchar2 default 'off', debugme in number default 0)
IS
/* Who When Why
Mark Young 2-June-2016 Calculate growth and how much time we have left
Mark Young 2-August-2016 Changed the value of in_host to upper(in_host)
Mark Young 16-August-2016 If the value return null, output a 0
Mark Young 18-August-2016 Support for ASM and RAC systems
Mark Young 19-August-2016 Have 2 methods of output Excel or Text
Mark Young 24-August-2016 Change the algorithm
Mark Young 26-August-2016 alter session set nls_date_format='DD/MM/YYYY';
Mark Young 28-Nov-2016 Make a simple version Total space left divided by Growth Per Month
*/
--Declare the variables here
v_target_guid varchar2(255);
v_host_target_guid varchar2(90);
v_avg_month number;
v_datafile_count number;
v_datafile_1 varchar2(255);
v_os_free number;
v_output_result number;
in_host varchar2(80);
v_output_null varchar2(10);
v_cluster_name varchar2(50);
v_cluster_host varchar2(80);
v_is_asm number;
V_CLUSTER_TARGET varchar2(50);
v_alloc_free_mb number;
V_months_left varchar2(20);
v_enddate date;
v_enddate_mess varchar2(60);
v_mymonths_left varchar2(90);
v_diskgroup varchar2(2000);
v_total_mb number;
v_percent_used number;
v_usable_total_mb number;
--Get the TARGET_GUID FROM THE HOST
cursor get_db_targets
is
SELECT DISTINCT a.target_name, a.target_guid, t.target_type, a.host_name
FROM MGMT$DB_DBNINSTANCEINFO_ALL a, mgmt$target_type t
WHERE a.target_guid = t.target_guid
AND UPPER(A.HOST_NAME) like upper(substr(in_host_name,0,length(in_host_name)-1) || '%')
AND ( t.target_type = 'rac_database'
OR ( t.target_type = 'oracle_database'
AND t.TYPE_QUALIFIER3 != 'RACINST')) order by 1;
BEGIN
--Change the session to dd-mm-yyyy
dbms_session.set_nls('nls_date_format', '''dd-mm-yyyy''');
dbms_output.enable (1000000);
--Convert the host to uppercase
in_host := upper(in_host_name);
--First, lets get the target_guid from the input host name
SELECT TARGET_GUID into v_host_target_guid FROM MGMT$target WHERE upper(TARGET_NAME)=upper(in_host_name) AND TARGET_TYPE='host';
--generate the header if this is excel
if header = 'on' then
dbms_output.put_line(upper('Host ' || chr(9) || 'Target' || chr(9) || 'DB Growth (per month)' || chr(9) || 'OS Free Space' || chr(9) || 'Average Growth Per Month' || chr(9) || '100% Full' ));
end if;
FOR i in get_db_targets LOOP
--Lets see if it's a RAC instance
if debugme = 1 then
dbms_output.put_line('Target: ' || upper(i.target_type));
dbms_output.put_line('Host_target_guid=' || v_host_target_guid);
end if;
--Now lets open the cursor and get the result for each target on the host.
SELECT
(max(round(avg(a_size.size_gb*1024),2)) - min(round(avg(a_size.size_gb*1024),2)))/13 into v_avg_month
FROM
(SELECT
m.rollup_timestamp AS month_timestamp,
sum(m.average/1024) AS size_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW(i.target_guid) AND
(t.target_type='rac_database' OR
(t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
m.target_guid=t.target_guid AND
m.metric_guid=t.metric_guid AND
t.metric_name='tbspAllocation' AND
(t.metric_column='spaceAllocated') AND
m.rollup_timestamp >= sysdate-365 AND
m.rollup_timestamp <= sysdate GROUP BY m.metric_column, m.rollup_timestamp) a_size, (SELECT m.rollup_timestamp AS month_timestamp, sum(m.average/1024) AS used_gb FROM mgmt$metric_daily m, mgmt$target_type t WHERE t.target_guid=HEXTORAW(i.target_guid) AND (t.target_type='rac_database' OR (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND m.target_guid=t.target_guid AND m.metric_guid=t.metric_guid AND t.metric_name='tbspAllocation' AND (t.metric_column='spaceUsed') AND m.rollup_timestamp >= sysdate-365 AND
m.rollup_timestamp <= sysdate GROUP BY m.metric_column, m.rollup_timestamp) used WHERE a_size.month_timestamp =used.month_timestamp GROUP BY TO_CHAR(a_size.month_timestamp,'MON RR'); -- if output='text' then --dbms_output.put_line(upper('Host: ' || i.host_name)); -- dbms_output.put_line('Growth per month: ' || trunc(v_avg_month,2) || ' MB'); -- end if; --If the database is ASM then we need to do some other magic. -- select count(*) into v_is_asm from mgmt$target where upper(host_name)=in_host and (type_qualifier2='ASM' or type_qualifier2='ASMINST'); select count(*) into v_is_asm from sysman.CM$MGMT_ASM_INSTANCE_ECM where upper(host_name)=in_host; if debugme=1 then dbms_output.put_line('is this asm: ' || v_is_asm); end if; SELECT --to_char(trunc(SUM(t.tablespace_size/1024/1024), 2),'999,999,999,999') AS ALLOC_MB, -- to_char(trunc(SUM(t.tablespace_used_size/1024/1024), 2),'999,999,999,999') AS USED_MB, trunc(SUM((t.tablespace_size - tablespace_used_size)/1024/1024), 2) into v_alloc_free_mb FROM mgmt$db_tablespaces t, (SELECT target_guid FROM mgmt$target WHERE target_guid=HEXTORAW(i.target_guid) AND (target_type='rac_database' OR (target_type='oracle_database' AND TYPE_QUALIFIER3 != 'RACINST'))) tg WHERE t.target_guid=tg.target_guid; if v_is_asm > 0 then
--Get the cluster nanme
dbms_output.put_line ('in host is ' || in_host );
select lower(cluster_name) into V_CLUSTER_TARGET from sysman.MGMT_CLUSTER_CONFIG where upper(node_list) like '%' || in_host || '%';
--Get the diskgroup from the datafiles in the DB
select RTRIM(XMLAGG(XMLELEMENT(e,diskgroup || ',')).EXTRACT('//text()'),',') diskgroup into v_diskgroup from
(SELECT DISTINCT DISKGROUP FROM MGMT_ASM_CLIENT_ECM where upper(db_name) like SUBSTR(i.target_name,0,8) || '%' and UPPER(diskgroup) not like '%FRA%');
DBMS_OUTPUT.PUT_LINE('Diskgroup: ' || v_diskgroup);
dbms_output.put_line('Cluster name: +ASM_' || v_cluster_target);
dbms_output.put_line('Target name: ' || i.target_name);
--this is for calculating the ASM usage
SELECT
MAX (DECODE (seq, 1, CEIL (VALUE))),
MAX (DECODE (seq, 3, CEIL (VALUE))),
MAX (DECODE (seq, 2, CEIL (VALUE))), -- PERCENT_USED
MAX (DECODE (seq, 4, CEIL (VALUE))) -- USABLE_TOTAL_MB
into v_os_free,
v_total_mb,
v_percent_used,
v_usable_total_mb
FROM (SELECT target_name,
key_value diskgroup,
VALUE,
metric_column,
ROW_NUMBER ()
OVER (PARTITION BY target_name, key_value
ORDER BY metric_column)
seq
FROM mgmt$metric_current
WHERE lower(target_name) = '+asm_' || V_CLUSTER_TARGET
AND key_value in (SELECT DISTINCT DISKGROUP FROM sysman.MGMT_ASM_CLIENT_ECM where upper(db_name) like SUBSTR(i.target_name,0,8) || '%' and UPPER(diskgroup) not like '%FRA%')
AND metric_name = 'DiskGroup_Usage'
AND metric_column IN ('total_mb',
'free_mb',
'percent_used',
'usable_total_mb'));
dbms_output.put_line('os_free: ' || v_os_free);
else
--Now calculate free space in the OS, but only use the mountpoint allocated to the database.
SELECT --mountpoint,
--TRUNC (sizeb / 1024 / 1024, 2) "TOTAL",
--TRUNC (USEDB / 1024 / 1024, 2) "USED",
sum(TRUNC(FREEB/ 1024/1024, 2)) into v_os_free
--TRUNC ( (USEDB / 1024 / 1024) / (sizeb / 1024 / 1024) * 100, 2) "%"
FROM mgmt$storage_report_localfs
WHERE UPPER (target_name) = (in_host)
AND mountpoint IN
(SELECT DISTINCT os_storage_entity
FROM mgmt$DB_DATAFILES
WHERE upper (HOST_name) = in_host
AND upper(target_name) = upper(i.target_name));
end if;
if (v_avg_month > 0) then
select trunc(((v_alloc_free_mb + v_os_free)/v_avg_month),2) into v_months_left from dual;
dbms_output.put_line (' Months: ' || v_months_left);
if length(v_months_left) < 6 then select add_months(trunc(sysdate),v_months_left) into v_enddate from dual; v_enddate_mess := v_enddate; else if output='text' then v_enddate_mess := 'Too far in the future to calculate'; else v_enddate_mess := 'N/A'; end if; end if; if output='text' then v_mymonths_left := 'This system will reach 100% by : ' || v_enddate_mess; else v_mymonths_left := v_enddate_mess; end if; else v_mymonths_left := 'N/A'; end if; if output = 'text' then dbms_output.put_line('Host is ' || in_host); dbms_output.put_line('Database: ' || i.target_name); dbms_output.put_line('Allocated DB free mb ' || v_alloc_free_mb || ' MB'); if v_is_asm > 0 then
dbms_output.put_line('Allocated Diskgroup free ' || v_diskgroup || ' ' || v_os_free || ' MB');
dbms_output.put_line('Total MB free ' || v_diskgroup || ' ' || v_total_mb || ' MB');
dbms_output.put_line('Percentage free ' || v_diskgroup || ' ' || v_percent_used || '%');
dbms_output.put_line('Total Usable ' || v_diskgroup || ' ' || v_usable_total_mb || ' MB');
else
dbms_output.put_line('Allocated OS free mb ' || v_os_free || ' MB');
end if;
dbms_output.put_line('Growth per month ' || trunc(v_avg_month,2) || ' MB');
dbms_output.put_line (v_mymonths_left);
dbms_output.put_line ('------------------------------------------------------------------------');
else
dbms_output.put_line(i.host_name || chr(9) || i.target_name || chr(9) || v_alloc_free_mb || chr(9) || v_os_free || chr(9) || trunc(v_avg_month,2) || chr(9) || v_enddate_mess);
end if;
END LOOP;
dbms_output.put_line ('Disclaimer: This is an estimate only, data is not distributed evenly across all tablespaces and is not consumed evenly either!');
end;
/