Typically, what as been done is to schedule job for each database to collect database growth.
This may be problematic as it can be forgotten when new databases are created versus the likelihood of forgetting to add database to monitoring for OEM.
EM12c, EM13c : Querying the Repository Database for Building Reports using Metric Information (Doc ID 2347253.1)
Those raw data are inserted in various tables like EM_METRIC_VALUES for example.
EM aggregates those management data by hour and by day.
Those raw data are kept 7 days; the one hour aggregated data are kept 31 days, while one day aggregated data are kept one year.
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
The above blog post provided a good starting point.
This post is using query to collect database size (metric_name=’DATABASE_SIZE’) vs tablespace size (metric_name=’tbspAllocation’) to avoid having to sum all tablespaces to determine database size.
OMS: 13.2.0 and EMREP DB: 12.2.0
Comparison for METRIC_COLUMN between DATABASE_SIZE and tbspAllocation.
For tbspAllocation, the size was not clear and did not research further but it does appear to be GB.
SQL> select distinct metric_name, METRIC_COLUMN from sysman.mgmt$metric_daily where metric_name='tbspAllocation' order by 1; METRIC_NAME METRIC_COLUMN ---------------------------------------------------------------- ---------------------------------------------------------------- tbspAllocation spaceUsed tbspAllocation spaceAllocated SQL> select distinct METRIC_COLUMN from sysman.mgmt$metric_daily WHERE metric_name='DATABASE_SIZE'; METRIC_COLUMN ---------------------------------------------------------------- ALLOCATED_GB USED_GB
TARGET_TYPE used (not all results presented):
SQL> select distinct target_type from sysman.mgmt$metric_daily order by 1; TARGET_TYPE ---------------------------------------------------------------- oracle_database oracle_pdb rac_database
METRIC_NAME used (not all results presented):
SQL> select distinct metric_name from sysman.mgmt$metric_daily order by 1; METRIC_NAME ---------------------------------------------------------------- DATABASE_SIZE tbspAllocation
DEMO:
SQL> @dbsize.sql
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
Enter value for 1: perf
TARGET_NAME TARGET_TYPE MONTH_DT USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
xxxxperf rac_database 01-MAR-18 2698.6 3526.8
rac_database 01-APR-18 2709.9 3526.8 2698.6 11.31
rac_database 01-MAY-18 2728.8 3526.8 2709.9 18.86
rac_database 01-JUN-18 2735.4 3548.8 2728.8 6.61
rac_database 01-JUL-18 2746.4 3548.8 2735.4 11.01
rac_database 01-AUG-18 2758.7 3548.8 2746.4 12.27
rac_database 01-SEP-18 2772.5 3548.8 2758.7 13.82
rac_database 01-OCT-18 4888.8 6207.8 2772.5 2116.3
rac_database 01-NOV-18 4647.8 6207.8 4888.8 -241
rac_database 01-DEC-18 3383.2 6207.8 4647.8 -1265
yyyyperf oracle_database 01-MAR-18 63.07 395.58
oracle_database 01-APR-18 63.19 395.58 63.07 .12
oracle_database 01-MAY-18 64.33 395.58 63.19 1.14
oracle_database 01-JUN-18 64.81 395.58 64.33 .48
oracle_database 01-JUL-18 65.1 395.58 64.81 .29
oracle_database 01-AUG-18 65.22 395.58 65.1 .12
oracle_database 01-SEP-18 65.79 395.58 65.22 .57
oracle_database 01-OCT-18 68.18 395.58 65.79 2.39
oracle_database 01-NOV-18 75.79 395.72 68.18 7.61
oracle_database 01-DEC-18 80.4 395.72 75.79 4.61
29 rows selected.
SQL> @dbsize
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
Enter value for 1: *
TARGET_NAME TARGET_TYPE MONTH_DT USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
CDByyyy_xxxxxxxxxxxxxxxxxxxxxxxxxx_CDBROOT oracle_pdb 01-MAR-18 7.96 94.73
oracle_pdb 01-APR-18 3.44 94.73 7.96 -4.52
oracle_pdb 01-MAY-18 12.26 95.07 3.44 8.82
oracle_pdb 01-JUN-18 76.18 95.12 12.26 63.92
oracle_pdb 01-JUL-18 70.87 95.15 76.18 -5.31
oracle_pdb 01-AUG-18 77.63 95.15 70.87 6.76
oracle_pdb 01-SEP-18 4.9 95.15 77.63 -72.73
oracle_pdb 01-OCT-18 4 95.15 4.9 -.9
oracle_pdb 01-NOV-18 41.34 95.15 4 37.34
oracle_pdb 01-DEC-18 33.52 95.15 41.34 -7.82
CDByyyy_xxxxxxxxxxxxxxxxxxxxxxxxxx_xxxxxPDB oracle_pdb 01-MAR-18 1610.6 2571
oracle_pdb 01-APR-18 1644.9 2571 1610.6 34.27
oracle_pdb 01-MAY-18 1659.3 2571.3 1644.9 14.43
oracle_pdb 01-JUN-18 1694.7 2571.4 1659.3 35.32
oracle_pdb 01-JUL-18 1753.8 2571.4 1694.7 59.18
oracle_pdb 01-AUG-18 1827.9 2571.4 1753.8 74.06
oracle_pdb 01-SEP-18 1900.8 2571.4 1827.9 72.91
oracle_pdb 01-OCT-18 1977.2 2571.4 1900.8 76.43
oracle_pdb 01-NOV-18 2044.8 2571.4 1977.2 67.6
oracle_pdb 01-DEC-18 2144.5 2571.4 2044.8 99.64
Script:
set line 200 verify off trimspool off tab off pages 1000 numw 6 echo on
-- Michael Dinh : Dec 20, 2018
set echo off
/*
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
*/
col target_name for a50
col target_type for a15
undefine 1
break on target_name
WITH dbsz AS (
SELECT
target_name, target_type, month_dt,
SUM(DECODE(metric_column, 'USED_GB', maximum)) used_gb,
SUM(DECODE(metric_column, 'ALLOCATED_GB', maximum)) allocated_gb
FROM (
SELECT target_name, target_type, trunc(rollup_timestamp,'MONTH') month_dt, metric_column, MAX(maximum) maximum
FROM sysman.mgmt$metric_daily
WHERE target_type IN ('rac_database','oracle_database','oracle_pdb')
AND metric_name = 'DATABASE_SIZE'
AND metric_column IN ('ALLOCATED_GB','USED_GB')
AND REGEXP_LIKE(target_name,'&&1','i')
GROUP BY target_name, target_type, trunc(rollup_timestamp,'MONTH'), metric_column
)
GROUP BY target_name, target_type, month_dt
ORDER BY target_name, month_dt
)
SELECT target_name, target_type, month_dt, used_gb, allocated_gb,
LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) previous_month,
used_gb-LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) diff_used_gb
FROM dbsz
ORDER BY target_name, month_dt
;
UPDATED SQL SCRIPT:
SQL> @dbsize.sql xxxprod
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
TARGET_NAME TARGET_TYPE MONTH_DT USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
xxxprod rac_database 31-MAR-18 333.2 704.42
rac_database 30-APR-18 336.65 704.42 333.2 3.45
rac_database 31-MAY-18 350.48 704.42 336.65 13.83
rac_database 30-JUN-18 423.47 714.1 350.48 72.99
rac_database 31-JUL-18 397.42 714.1 423.47 -26.05
rac_database 31-AUG-18 415.61 714.1 397.42 18.19
rac_database 30-SEP-18 417.2 714.69 415.61 1.59
rac_database 31-OCT-18 421.04 714.69 417.2 3.84
rac_database 30-NOV-18 425.35 715.37 421.04 4.31
rac_database 20-DEC-18 428.44 723.11 425.35 3.09
10 rows selected.
SQL> !cat dbsize.sql
SQL> !cat dbsize.sql
set line 200 verify off trimspool off tab off pages 1000 numw 6 echo on
-- Michael Dinh : Dec 20, 2018
set echo off
/*
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
*/
col target_name for a50
col target_type for a15
break on target_name
WITH dbsz AS (
SELECT
target_name, target_type, month_dt,
SUM(DECODE(metric_column, 'USED_GB', maximum)) used_gb,
SUM(DECODE(metric_column, 'ALLOCATED_GB', maximum)) allocated_gb
FROM (
-- This shows LATEST date of month
SELECT target_name, target_type, MAX(rollup_timestamp) month_dt, metric_column, MAX(maximum) maximum
-- This shows FIRST date of month
-- SELECT target_name, target_type, TRUNC(rollup_timestamp,'MONTH') month_dt, metric_column, MAX(maximum) maximum
FROM sysman.mgmt$metric_daily
WHERE target_type IN ('rac_database','oracle_database','oracle_pdb')
AND metric_name = 'DATABASE_SIZE'
AND metric_column IN ('ALLOCATED_GB','USED_GB')
AND REGEXP_LIKE(target_name,'&1','i')
GROUP BY target_name, target_type, TRUNC(rollup_timestamp,'MONTH'), metric_column
)
GROUP BY target_name, target_type, month_dt
-- ORDER BY target_name, month_dt
)
SELECT target_name, target_type, month_dt, used_gb, allocated_gb,
LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) previous_month,
used_gb-LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) diff_used_gb
FROM dbsz
ORDER BY target_name, month_dt
;
undefine 1
Thinking Out Loud 
Leave a comment