{"id":158,"date":"2017-08-16T23:12:46","date_gmt":"2017-08-16T23:12:46","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=158"},"modified":"2019-09-26T11:56:18","modified_gmt":"2019-09-26T01:56:18","slug":"database-capacity-planning","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/16\/database-capacity-planning\/","title":{"rendered":"Database Capacity Planning"},"content":{"rendered":"

Capacity and Growth Forecast<\/strong><\/p>\n

How to estimate the growth and forecast of a database on a host.
\nStep-by-step guide<\/em><\/p>\n

The procedure is at the bottom of this article.<\/em><\/p>\n

Login to enterprise manager from sqlplus (from omsdbaup03)
\noracle@omsdbaup03:\/users\/oracle [emrep12c]<\/p>\n

$ sqlplus sysman\/xxxxxx<\/pre>\n
2. Follow the following to calculate growth for each database on a host<\/p>\n
SQL> set serveroutput on;\nSQL> exec db_growth('ITRDBAUP02','text');\nHost is ITRDBAUP02\nDatabase: DOVITRHAUP1\nAllocated DB free mb 319240.56 MB\nAllocated OS free mb 2132379880 MB\nGrowth per month 247.91 MB\nThis system will reach 100% by : Too far in the future to calculate\n------------------------------------------------------------------------\nHost is ITRDBAUP02\nDatabase: DOVITRKAUP1\nAllocated DB free mb 281163.5 MB\nAllocated OS free mb 2132379880 MB\nGrowth per month 21420.16 MB\nThis system will reach 100% by : Too far in the future to calculate\n------------------------------------------------------------------------\nDisclaimer: This is an estimate only, data is not distributed evenly across all\ntablespaces and is not consumed evenly either!<\/pre>\n
The following parameters are accepted.<\/p>\n
procedure: db_growth<\/p>\n
Parameter 1: host_name<\/p>\n
Parameter 2: output type, (text or excel). Default is excel<\/p>\n
Parameter 3: header: on\/off (if using excel, it will display a list of headings per column). Default is off<\/p>\n
Parameter 4: debug: 0\/1 (this will display a list of debugging features for development only). Default is 0 (off)<\/p>\n
Here is the procedure:<\/strong><\/p>\n
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)\nIS\n\/* Who         When          Why\n   Mark Young  2-June-2016   Calculate growth and how much time we have left\n   Mark Young  2-August-2016    Changed the value of in_host to upper(in_host)\n   Mark Young  16-August-2016   If the value return null, output a 0\n   Mark Young  18-August-2016   Support for ASM and RAC systems\n   Mark Young  19-August-2016   Have 2 methods of output Excel or Text\n   Mark Young  24-August-2016\tChange the algorithm \n   Mark Young  26-August-2016   alter session set nls_date_format='DD\/MM\/YYYY';\n   Mark Young  28-Nov-2016      Make a simple version Total space left divided by Growth Per Month\n*\/\n\n        --Declare the variables here\n        v_target_guid  \t\tvarchar2(255);\n\t\tv_host_target_guid \tvarchar2(90);\n\t\tv_avg_month \t\tnumber;\n\t\tv_datafile_count\tnumber;\t\n\t\tv_datafile_1\t\tvarchar2(255);\n\t\tv_os_free\t\t\tnumber;\n                v_output_result     number;\n                in_host             varchar2(80);\n                v_output_null       varchar2(10);\n                v_cluster_name      varchar2(50);\n                v_cluster_host      varchar2(80); \n                v_is_asm            number;\n                V_CLUSTER_TARGET    varchar2(50);\n                v_alloc_free_mb     number;\n                V_months_left       varchar2(20);\n                v_enddate           date;\n                v_enddate_mess      varchar2(60);\n                v_mymonths_left     varchar2(90);\n                v_diskgroup         varchar2(2000);\n                v_total_mb          number;\n                v_percent_used      number; \n                v_usable_total_mb   number;  \n        \n\t\t--Get the TARGET_GUID FROM THE HOST\n\t\tcursor get_db_targets\n\t\tis\n\t\t  SELECT DISTINCT a.target_name, a.target_guid, t.target_type, a.host_name\n          FROM MGMT$DB_DBNINSTANCEINFO_ALL a, mgmt$target_type t\n         WHERE     a.target_guid = t.target_guid\n                 AND UPPER(A.HOST_NAME) like upper(substr(in_host_name,0,length(in_host_name)-1) || '%')\n                    AND (   t.target_type = 'rac_database'\n                    OR (    t.target_type = 'oracle_database'\n                        AND t.TYPE_QUALIFIER3 != 'RACINST')) order by 1;\n\t\t\t\t\t\t\n\tBEGIN\n        --Change the session to dd-mm-yyyy\n        dbms_session.set_nls('nls_date_format', '''dd-mm-yyyy''');\n        dbms_output.enable (1000000); \n         \n        --Convert the host to uppercase\n        in_host := upper(in_host_name);\n\t\t\n\t--First, lets get the target_guid from the input host name\n        SELECT TARGET_GUID into v_host_target_guid FROM MGMT$target WHERE upper(TARGET_NAME)=upper(in_host_name) AND TARGET_TYPE='host';\n   \n        --generate the header if this is excel\n\tif header = 'on' then\n            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' ));\n        end if;\n\n\t\t\n\tFOR i in get_db_targets LOOP  \n       \n           --Lets see if it's a RAC instance\n           if debugme = 1 then\n                dbms_output.put_line('Target: ' || upper(i.target_type));\n                dbms_output.put_line('Host_target_guid=' || v_host_target_guid);\n           end if;\n           \n           --Now lets open the cursor and get the result for each target on the host.\n           SELECT\n\t\t\t   (max(round(avg(a_size.size_gb*1024),2)) - min(round(avg(a_size.size_gb*1024),2)))\/13 into v_avg_month\n\t\t\t FROM\n\t\t\t  (SELECT  \n\t\t\t\t   m.rollup_timestamp AS month_timestamp,\n\t\t\t\t   sum(m.average\/1024) AS size_gb\n\t\t\t\t FROM \n\t\t\t\t   mgmt$metric_daily m,\n\t\t\t\t   mgmt$target_type t\n\t\t\t\t WHERE \n\t\t\t\t   t.target_guid=HEXTORAW(i.target_guid) AND\n\t\t\t\t   (t.target_type='rac_database' OR \n\t\t\t\t   (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND      \n\t\t\t\t   m.target_guid=t.target_guid AND\n\t\t\t\t   m.metric_guid=t.metric_guid AND\n\t\t\t\t   t.metric_name='tbspAllocation' AND\n\t\t\t\t   (t.metric_column='spaceAllocated') AND\n\t\t\t\t   m.rollup_timestamp >= sysdate-365 AND\n\t\t\t\t   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\n\t\t\t\t   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\n            --Get the cluster nanme\n            dbms_output.put_line ('in host is ' || in_host );\n            \n            select lower(cluster_name) into V_CLUSTER_TARGET from sysman.MGMT_CLUSTER_CONFIG where upper(node_list) like '%' || in_host || '%';\n            \n            --Get the diskgroup from the datafiles in the DB\n            select RTRIM(XMLAGG(XMLELEMENT(e,diskgroup || ',')).EXTRACT('\/\/text()'),',') diskgroup into v_diskgroup from \n            (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%');\n            \n            DBMS_OUTPUT.PUT_LINE('Diskgroup: ' || v_diskgroup);\n            dbms_output.put_line('Cluster name: +ASM_' || v_cluster_target);\n            dbms_output.put_line('Target name: ' || i.target_name);\n            \n                      --this is for calculating the ASM usage\n                      SELECT \n                             MAX (DECODE (seq, 1, CEIL (VALUE))),    \n                             MAX (DECODE (seq, 3, CEIL (VALUE))),    \n                             MAX (DECODE (seq, 2, CEIL (VALUE))),         -- PERCENT_USED\n                             MAX (DECODE (seq, 4, CEIL (VALUE)))     -- USABLE_TOTAL_MB\n                             into v_os_free,\n                             v_total_mb,\n                             v_percent_used, \n                             v_usable_total_mb  \n                        FROM (SELECT target_name,\n                                     key_value diskgroup,\n                                     VALUE,\n                                     metric_column,\n                                     ROW_NUMBER ()\n                                     OVER (PARTITION BY target_name, key_value\n                                           ORDER BY metric_column)\n                                        seq\n                                FROM mgmt$metric_current\n                               WHERE     lower(target_name) = '+asm_' || V_CLUSTER_TARGET\n                                     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%')\n                                     AND metric_name = 'DiskGroup_Usage'\n                                     AND metric_column IN ('total_mb',\n                                                           'free_mb',\n                                                           'percent_used',\n                                                           'usable_total_mb'));\n                                                           \n                                                           dbms_output.put_line('os_free: ' || v_os_free);\n                                                           \n\n        else\n              \n            --Now calculate free space in the OS, but only use the mountpoint allocated to the database.\n            SELECT --mountpoint,\n                   --TRUNC (sizeb \/ 1024 \/ 1024, 2)                                  \"TOTAL\",\n                   --TRUNC (USEDB \/ 1024 \/ 1024, 2)                                  \"USED\",\n                   sum(TRUNC(FREEB\/ 1024\/1024, 2)) into v_os_free                                 \n                   --TRUNC ( (USEDB \/ 1024 \/ 1024) \/ (sizeb \/ 1024 \/ 1024) * 100, 2) \"%\"\n              FROM mgmt$storage_report_localfs\n             WHERE     UPPER (target_name) = (in_host)\n                   AND mountpoint IN\n                          (SELECT DISTINCT os_storage_entity\n                             FROM mgmt$DB_DATAFILES\n                            WHERE     upper (HOST_name) = in_host\n                                  AND upper(target_name) = upper(i.target_name)); \n            \n\n          end if;\t\n            \n            if (v_avg_month > 0)  then\n                select trunc(((v_alloc_free_mb + v_os_free)\/v_avg_month),2) into v_months_left from dual;\n                dbms_output.put_line (' Months: ' || v_months_left);\n                \n                \n                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\n                        dbms_output.put_line('Allocated Diskgroup free ' || v_diskgroup || ' ' || v_os_free || ' MB');\n                        dbms_output.put_line('Total MB free ' || v_diskgroup || ' ' || v_total_mb || ' MB');\n                        dbms_output.put_line('Percentage free ' || v_diskgroup || ' ' || v_percent_used || '%'); \n                        dbms_output.put_line('Total Usable ' || v_diskgroup || ' ' || v_usable_total_mb || ' MB');\n                    else\n                        dbms_output.put_line('Allocated OS free mb ' || v_os_free || ' MB');    \n                    end if;\n                 \n                    dbms_output.put_line('Growth per month ' || trunc(v_avg_month,2) || ' MB');\n                    dbms_output.put_line (v_mymonths_left);\n                    dbms_output.put_line ('------------------------------------------------------------------------');\n            else\n               \n                    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);\n            end if;  \n\n \n        END LOOP;\n        dbms_output.put_line ('Disclaimer: This is an estimate only, data is not distributed evenly across all tablespaces and is not consumed evenly either!');\nend;\n\/\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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> … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,4],"tags":[13,17],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-enterprise-manager","category-oracle-database","tag-capacity","tag-growth"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":3,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":347,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/158\/revisions\/347"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}