12c Enterprise Manager Cloud Control Reports

###########################
## EM12c Storage Report  ##
###########################

select
 target_name,
 name,
 ceil(sizeb/1024/1024/1024) as allocated_gb,
 ceil(usedb/1024/1024/1024) as used_gb,
 ceil(freeb/1024/1024/1024) as free_gb
 from sysman.mgmt$storage_report_data
 where entity_type='Mountpoint'
 order by 1 asc
--EM12c ASM Storage Report
 SELECT target_name,
          diskgroup,
          MAX (DECODE (seq, 7, VALUE)) REDUNDANCY,
          MAX (DECODE (seq, 4, VALUE)) PERCENT_USED,
          MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB,
          MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB,
          MAX (DECODE (seq, 3, ceil(VALUE/1024))) FREE_GB,
          MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB,
          MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK,
          ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, VALUE)))) LUN_SIZE,
          MAX (DECODE (seq, 5, decode(VALUE,'No','',value))) REBAL_PENDING,
          MAX (DECODE (seq, 1, VALUE)) IMBALANCE
     FROM (SELECT target_name,
                  key_value diskgroup,
                  VALUE,
                  metric_column,
                  ROW_NUMBER ()
                  OVER (PARTITION BY target_name, key_value
                        ORDER BY metric_column)
                     AS seq
             FROM MGMT$METRIC_CURRENT
           WHERE        target_type in ('osm_instance','osm_cluster')
                    AND metric_column IN
                           ('rebalInProgress',
                            'free_mb',
                            'usable_file_mb',
                            'type',
                            'computedImbalance',
                            'usable_total_mb',
                            'percent_used','diskCnt')
                 OR (    metric_column = 'total_mb'
                     AND metric_name = 'DiskGroup_Usage'))
 GROUP BY target_name, diskgroup
 order by 1,2


###########################
## EM12c Server Report   ##
###########################

SELECT DISTINCT
             a.target_guid,
             CASE
                WHEN host_name LIKE '%.%'
                THEN
                   LOWER (SUBSTR (host_name,
                                  1,
                                    INSTR (host_name,
                                           '.',
                                           2,
                                           1)
                                  - 1))
                ELSE
                   host_name
             END
                host_name,
             system_config hardware,
             OS_summary OS,
             c.freq_in_mhz,
             c.impl,
             cpu_count cpu_cores,
             logical_cpu_count cpu_threads,
             CEIL (mem / 1024) AS total_memory_size_in_gb,
             CEIL (max_swap_space_in_mb / 1024) AS total_swap_space_in_gb,
             d.TYPE AS root_filesystem_type
        FROM MGMT$OS_HW_SUMMARY a,
             MGMT$OS_SUMMARY b,
             (SELECT target_guid, freq_in_mhz, impl
                FROM (SELECT t1.target_guid,
                             T1.FREQ_IN_MHZ,
                             t1.impl,
                             ROW_NUMBER ()
                             OVER (PARTITION BY t1.target_guid ORDER BY t1.impl)
                                rn
                        FROM MGMT$HW_CPU_DETAILS T1)
               WHERE rn = 1) c,
             (SELECT cm_target_guid, TYPE
                FROM CM$MGMT_ECM_OS_FILESYSTEM
               WHERE mount_location IN ('c:\', '/', 'C:\')) d
       WHERE     a.target_guid = b.target_guid
             AND a.target_guid = c.target_guid
             AND a.target_guid = d.cm_target_guid
    ORDER BY 2 ASC;


#####################################
## EM12c Database Inverntory Report
#####################################
 
SELECT DISTINCT
             tbl_tar.target_guid,
             tbl_sid.sid AS instance_name,
             CASE
                WHEN tbl_tar.host_name LIKE '%.%'
                THEN
                   LOWER (SUBSTR (tbl_tar.host_name,
                                  1,
                                    INSTR (tbl_tar.host_name,
                                           '.',
                                           2,
                                           1)
                                  - 1))
                ELSE
                   tbl_tar.host_name
             END
                host_name,
             tbl_ver.version,
             CASE
                WHEN tbl_mem.mem_max > 0
                THEN
                   CEIL (tbl_mem.mem_max / 1024 / 1024)
                ELSE
                   CEIL (tbl_sga.sga / 1024 / 1024 + tbl_pga.pga / 1024 / 1024)
             END
                total_memory,
             tbl_dg.data_guard_status,
             tbl_port.port,
             tbl_home.PATH
        FROM (SELECT p.target_guid, p.property_value AS port
                FROM mgmt_target_properties p
               WHERE p.property_name = 'Port') tbl_port,
             (SELECT s.target_guid, UPPER (s.property_value) AS sid
                FROM mgmt_target_properties s
               WHERE s.property_name = 'SID') tbl_sid,
             (SELECT s.target_guid, s.property_value AS version
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('Version')) tbl_ver,
             (SELECT s.target_guid, s.property_value AS PATH
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('OracleHome')) tbl_home,
             (SELECT s.target_guid, s.property_value AS data_guard_status
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('DataGuardStatus')) tbl_dg,
             (SELECT s.target_guid, s.VALUE AS PGA
                FROM mgmt$db_init_params s
               WHERE s.name = 'pga_aggregate_target') tbl_pga,
             (SELECT s.target_guid, s.VALUE AS SGA
                FROM mgmt$db_init_params s
               WHERE s.name = 'sga_max_size') tbl_sga,
             (SELECT s.target_guid, s.VALUE AS mem_max
                FROM mgmt$db_init_params s
               WHERE s.name = 'memory_target') tbl_mem,
             mgmt_target_properties tbl_main,
             mgmt_targets tbl_tar
       WHERE     tbl_main.target_guid = tbl_port.target_guid(+)
             AND tbl_main.target_guid = tbl_sid.target_guid(+)
             AND tbl_main.target_guid = tbl_tar.target_guid(+)
             AND tbl_main.target_guid = tbl_ver.target_guid(+)
             AND tbl_main.target_guid = tbl_home.target_guid(+)
             AND tbl_main.target_guid = tbl_dg.target_guid(+)
             AND tbl_main.target_guid = tbl_pga.target_guid(+)
             AND tbl_main.target_guid = tbl_sga.target_guid(+)
             AND tbl_main.target_guid = tbl_mem.target_guid(+)
             AND tbl_tar.target_type = 'oracle_database'
    GROUP BY tbl_tar.target_guid,
             tbl_port.port,
             tbl_sid.sid,
             tbl_tar.host_name,
             tbl_ver.version,
             tbl_home.PATH,
             tbl_dg.data_guard_status,
             tbl_pga.pga,
             tbl_sga.sga,
             tbl_mem.mem_max
    ORDER BY 2;
   
###########################################
## EM12c Dynamically Generate TNS Entry  ##
###########################################

SELECT DISTINCT
             lower(tbl_sid.sid) as db_name,LOWER (tbl_sid.sid)
          || ' = '
          || '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='
          || tbl_tar.host_name
          || ')(PORT='
          || tbl_port.port
          || '))(CONNECT_DATA=(sid='
          || tbl_sid.sid
          || ')))'
             AS TNS_ENTRY
        FROM (SELECT p.target_guid, p.property_value AS port
                FROM mgmt_target_properties p
               WHERE p.property_name = 'Port') tbl_port,
             (SELECT s.target_guid, UPPER (s.property_value) AS sid
                FROM mgmt_target_properties s
               WHERE s.property_name = 'SID') tbl_sid,
             (SELECT s.target_guid, s.property_value AS version
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('Version')) tbl_ver,
             (SELECT s.target_guid, s.property_value AS PATH
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('OracleHome')) tbl_home,
             (SELECT s.target_guid, s.property_value AS data_guard_status
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('DataGuardStatus')) tbl_dg,
             (SELECT s.target_guid, s.VALUE AS PGA
                FROM mgmt$db_init_params s
               WHERE s.name = 'pga_aggregate_target') tbl_pga,
             (SELECT s.target_guid, s.VALUE AS SGA
                FROM mgmt$db_init_params s
               WHERE s.name = 'sga_max_size') tbl_sga,
             (SELECT s.target_guid, s.VALUE AS mem_max
                FROM mgmt$db_init_params s
               WHERE s.name = 'memory_target') tbl_mem,
             mgmt_target_properties tbl_main,
             mgmt_targets tbl_tar
       WHERE     tbl_main.target_guid = tbl_port.target_guid(+)
             AND tbl_main.target_guid = tbl_sid.target_guid(+)
             AND tbl_main.target_guid = tbl_tar.target_guid(+)
             AND tbl_main.target_guid = tbl_ver.target_guid(+)
             AND tbl_main.target_guid = tbl_home.target_guid(+)
             AND tbl_main.target_guid = tbl_dg.target_guid(+)
             AND tbl_main.target_guid = tbl_pga.target_guid(+)
             AND tbl_main.target_guid = tbl_sga.target_guid(+)
             AND tbl_main.target_guid = tbl_mem.target_guid(+)
             AND tbl_tar.target_type = 'oracle_database'
    GROUP BY tbl_port.port,
             tbl_sid.sid,
             tbl_tar.host_name
             ORDER BY 1

####################################            
## EM12c Database Data Inventory  ##       
####################################

SELECT DISTINCT
             tbl_tar.target_guid,
             tbl_sid.sid AS instance_name,
             CASE
                WHEN tbl_tar.host_name LIKE '%.%'
                THEN
                   LOWER (SUBSTR (tbl_tar.host_name,
                                  1,
                                    INSTR (tbl_tar.host_name,
                                           '.',
                                           2,
                                           1)
                                  - 1))
                ELSE
                   LOWER (tbl_tar.host_name)
             END
                host_name,
             DECODE (tbl_ava.current_status,
                     0, 'Down',
                     1, 'Up',
                     2, 'Metric Error',
                     3, 'Agent Down',
                     4, 'Unreachable',
                     5, 'Blackout',
                     6, 'Unknown')
                status,
             tbl_groups.composite_target_name AS "GROUP",
             tbl_ver.version,
             CASE
                WHEN tbl_mem.mem_max > 0
                THEN
                   CEIL (tbl_mem.mem_max / 1024 / 1024)
                ELSE
                   CEIL (tbl_sga.sga / 1024 / 1024 + tbl_pga.pga / 1024 / 1024)
             END
                total_memory,
             tbl_dg.data_guard_status,
             tbl_port.port,
             tbl_home.PATH,
             tbl_company.company,
             tbl_location.location,
             tbl_appcontact.app_contact,
             tbl_costcenter.cost_center,
             tbl_tier.tier,
             tbl_department.department,
             tbl_dbplatform.db_platform,
             tbl_dbhostos.db_host_os,
             tbl_comment.notes
        FROM (SELECT p.target_guid, p.property_value AS port
                FROM mgmt_target_properties p
               WHERE p.property_name = 'Port') tbl_port,
             (SELECT s.target_guid, UPPER (s.property_value) AS sid
                FROM mgmt_target_properties s
               WHERE s.property_name = 'SID') tbl_sid,
             (SELECT s.target_guid, s.property_value AS version
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('Version')) tbl_ver,
             (SELECT s.target_guid, s.property_value AS PATH
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('OracleHome')) tbl_home,
             (SELECT s.target_guid, s.property_value AS data_guard_status
                FROM mgmt_target_properties s
               WHERE s.property_name IN ('DataGuardStatus')) tbl_dg,
             (SELECT s.target_guid, s.VALUE AS PGA
                FROM mgmt$db_init_params s
               WHERE s.name = 'pga_aggregate_target') tbl_pga,
             (SELECT s.target_guid, s.VALUE AS SGA
                FROM mgmt$db_init_params s
               WHERE s.name = 'sga_max_size') tbl_sga,
             (SELECT s.target_guid, s.VALUE AS mem_max
                FROM mgmt$db_init_params s
               WHERE s.name = 'memory_target') tbl_mem,
             (SELECT p.target_guid, p.property_value AS notes
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_comment') tbl_comment,
             (SELECT p.target_guid, p.property_value AS company
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_line_of_bus') tbl_company,
             (SELECT p.target_guid, p.property_value AS location
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_location') tbl_location,
             (SELECT p.target_guid, p.property_value AS app_contact
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_contact') tbl_appcontact,
             (SELECT p.target_guid, p.property_value AS cost_center
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_cost_center') tbl_costcenter,
             (SELECT p.target_guid, p.property_value AS tier
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_lifecycle_status') tbl_tier,
             (SELECT p.target_guid, p.property_value AS department
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_department') tbl_department,
             (SELECT p.target_guid, p.property_value AS db_platform
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_platform') tbl_dbplatform,
             (SELECT p.target_guid, p.property_value AS db_host_os
                FROM mgmt_target_properties p
               WHERE p.property_name = 'orcl_gtp_os') tbl_dbhostos,
             mgmt_target_properties tbl_main,
             mgmt_targets tbl_tar,
             mgmt_current_availability tbl_ava,
             (SELECT composite_target_name, member_target_guid
                FROM MGMT_TARGET_MEMBERSHIPS
               WHERE     composite_target_type = 'composite'
                     AND composite_target_name IN
                            ('Production', 'Non-Production', 'SuperCluster')
                     AND member_target_type = 'oracle_database') tbl_groups
       WHERE     tbl_main.target_guid = tbl_port.target_guid(+)
             AND tbl_main.target_guid = tbl_sid.target_guid(+)
             AND tbl_main.target_guid = tbl_tar.target_guid(+)
             AND tbl_main.target_guid = tbl_ver.target_guid(+)
             AND tbl_main.target_guid = tbl_home.target_guid(+)
             AND tbl_main.target_guid = tbl_dg.target_guid(+)
             AND tbl_main.target_guid = tbl_pga.target_guid(+)
             AND tbl_main.target_guid = tbl_sga.target_guid(+)
             AND tbl_main.target_guid = tbl_mem.target_guid(+)
             AND tbl_main.target_guid = tbl_ava.target_guid(+)
             AND tbl_main.target_guid = tbl_comment.target_guid(+)
             AND tbl_main.target_guid = tbl_company.target_guid(+)
             AND tbl_main.target_guid = tbl_location.target_guid(+)
             AND tbl_main.target_guid = tbl_appcontact.target_guid(+)
             AND tbl_main.target_guid = tbl_costcenter.target_guid(+)
             AND tbl_main.target_guid = tbl_tier.target_guid(+)
             AND tbl_main.target_guid = tbl_department.target_guid(+)
             AND tbl_main.target_guid = tbl_dbplatform.target_guid(+)
             AND tbl_main.target_guid = tbl_dbhostos.target_guid(+)
             AND tbl_main.target_guid = tbl_groups.member_target_guid(+)
             AND tbl_tar.target_type = 'oracle_database'
    GROUP BY tbl_tar.target_guid,
             tbl_port.port,
             tbl_sid.sid,
             tbl_tar.host_name,
             tbl_ver.version,
             tbl_home.PATH,
             tbl_dg.data_guard_status,
             tbl_pga.pga,
             tbl_sga.sga,
             tbl_mem.mem_max,
             tbl_ava.current_status,
             tbl_groups.composite_target_name,
             tbl_comment.notes,
             tbl_company.company,
             tbl_location.location,
             tbl_appcontact.app_contact,
             tbl_costcenter.cost_center,
             tbl_tier.tier,
             tbl_department.department,
             tbl_dbplatform.db_platform,
             tbl_dbhostos.db_host_os
    ORDER BY 2;