Thinking Out Loud

December 21, 2018

Find Database Growth Using OEM Repository

Filed under: 12.2,oracle — mdinh @ 2:20 pm

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

Advertisements

December 9, 2018

Cloud Control 13c Release 3 Database Template

Filed under: Cloud Control — mdinh @ 12:20 am

Got excited to find:
Creating a Database Instance with Preconfigured Repository Using Database Templates

Templates can be download at:
Database Template (with EM 13.3.0.0 repository pre-configured) for Installing Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0)

Got disappointed to find template is only available for 12.1.0.2.0 DB and 18.1.0.0.0 DB.

Creating a Database Instance with Preconfigured Repository Using Database Templates Caution:

Make sure that the 18.1.0.0.0_Database_Template_for_EM13_3_0_0_0_.zip file is used only for Enterprise Manager Cloud Control 13c Release 3.

It looks like the template version must match exactly from an example below.

EM 13c: Creating an Enterprise Manager 13c Cloud Control Repository using a Database Template Fails: ORA-00603: ORACLE server session terminated by fatal error (Doc ID 2291220.1)	

While the 12.2.0.1 version of the database is supported for EM 13.2 repository use, a database template for the EM 13.2 repository database is only supplied for the 12.1.0.2 version of the database, and they are specific for the version:

Oracle skipped 12.2 DB version when creating DB template which is disappointing.

Does database really need to be on 12.2?

Blog at WordPress.com.