EM12c Information Reporting

Information Publisher, (IP) Reporting has been a feature of Enterprise Manager for quite some time now, but I’ve just recently started to work with a few clients who’ve wanted to put it to use, but not implement BI Publisher, (yet… :))

If you haven’t investigated the “canned” reports that are already available to a user of the Enterprise Manager, (EM12c) then you are missing out, as there are a number that are highly valuable.  Accessing the feature is as simple as logging into the EM12c console and clicking on Enterprise, Reports, then Information Publisher Reports.  You will see the list of reports already incorporated as part of the EM12c installation and any additional reports that may have been part of a plug-in or patch installation:

I recommend taking a few of them out for a test drive and see if they provide data that you may previously been gathering by hand, but if they don’t, this post is going to show you how to create a report to address that small problem.  The reports, “as is” are locked, but if you do see a report that provides much of the data you need, but could use a bit of “tweaking” to complete your task, you can perform a “create like” with the report and enhance it to meet your needs.

Manual Reporting in Excel

One of my clients perform a quarterly review, creating an excel spreadsheet of their environment.

They had a few queries that provided some of the data, but they were database specific, which means they ran them against each database and then they had to enter this data into the excel spreadsheet report to provide to management.  I was asked if I could create some type of re-usable report in EM12c that would provide a similar report.

Starting with the spreadsheet, it’s important to decide what will make an easily viewable report and taken into consideration format of the report within the queries as well.

The spreadsheet was a detailed report on host, database, licensing and resource information.  The spreadsheet, as you can see, was very “wide” and would be difficult to view in a report, but broken down, would be more easily readable and easier on performance.  Creating an IP Report on this data offered the opportunity to use the EM12c repository, which would save time and resources from performing this task quarterly outside of running the report and printing it out or copying and saving the data into another spreadsheet, (if this was the goal of the report user…)

Creating a Report

I then went into the Information Publisher Reports Menu and clicked on Create.

General

1.  Name the report

2.  Choose the Category, (I choose Compliance, as that is what this report will be used as part of…) and a subcategory.

3.  Choose the target_type=Database_Instance, (I want to run this against a database)

4.  Specific target=EM12c, (I want to run this against the repository to gather ALL targets in the environment)

5.  Choose to run this with the SYSMAN privileges.

6.  There is no time period for this report, so I have unchecked this option.

Next we need to configure the actual Elements of the report, which is where the format, the report type and the specifics of the report, (in this case SQL statements) will be stored.

Elements

You need to add what elements for the report you want, then add the specifics and change the layout as you want afterwards.  For this report, I’m going to want simple tables for my output from SQL statement, so I click on Add, then choose the element I want and click continue.  After reviewing the requirements from the spreadsheet, I came up with three queries that provided the data for the client and created an aesthetically pleasing and easily readable report.  I added three elements “Add Table for SQL”:

Here are the three elements already titled, so you clearly see the finished element design:

For each of the three elements, then you can click on the edit option at the right, (there is an edit or a remove icon to the right of each) where you can then enter the specifics for each element.  You then need to add the Header, the actual SQL statement and the amount of rows and columns you wish for your report:

I then edited each one of the elements and added the proper header you see in the list, SQL statements for each, but kept the rows and total number of rows the same.

The statements to populate each part of the report are as follows:

Target Host Information

SELECT DISTINCT tbl_sid.sid "DB Name", tbl_tar.host_name "Host Name", tbl_ver.version "Version", tbl_sga.SGA "SGA", tbl_pga.PGA "PGA"
 FROM (SELECT s.target_guid, UPPER (s.property_value) AS sid
 FROM sysman.mgmt$target_properties s
 WHERE s.property_name = 'SID') tbl_sid,
 (SELECT s.target_guid, s.property_value AS version
 FROM sysman.mgmt$target_properties s
 WHERE s.property_name IN ('Version')) tbl_ver,
 (SELECT s.target_guid, sum(s.VALUE/1024/1024) AS PGA
 FROM sysman.mgmt$db_init_params s
 WHERE s.name = 'pga_aggregate_target' group by s.target_guid) tbl_pga,
 (SELECT s.target_guid, sum(s.VALUE/1024/1024) AS SGA
 FROM sysman.mgmt$db_init_params s
 WHERE s.name = 'sga_max_size' group by s.target_guid) tbl_sga,
 (SELECT s.target_guid, s.VALUE AS mem_max
 FROM sysman.mgmt$db_init_params s
 WHERE s.name = 'memory_target') tbl_mem,
 sysman.mgmt$target_properties tbl_main,
 sysman.mgmt$target tbl_tar
 WHERE 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_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'
 ORDER BY 1;

Licensing Usage

select distinct main_mdf.database_name, adv_sec.advsec "Advanced Security",
rac_info.racid "RAC Used", hcc_info.hcc "Advanced Compression Used", 
tune_pk.tune_usr "Tuning Pack Used",
DIAG_PK.diag "Diagnostic Pack Used", part_used.part_usr "Partitioning in Use"
FROM
(select database_name, NAME AS advsec, currently_used 
FROM MGMT$DB_FEATUREUSAGE
where NAME = 'Advanced Security'
and currently_used='TRUE') ADV_SEC,
(select database_name, NAME AS racid, currently_used
FROM MGMT$DB_FEATUREUSAGE
where NAME = 'Real Application Clusters (RAC)'
and currently_used='TRUE') RAC_INFO,
(select database_name, NAME AS hcc, currently_used
FROM MGMT$DB_FEATUREUSAGE
where NAME = 'Hybrid Columnar Compression'
and currently_used='TRUE') HCC_INFO,
(select database_name, NAME AS diag, currently_used
FROM MGMT$DB_FEATUREUSAGE
where NAME = 'Diagnostic Pack'
and currently_used='TRUE') DIAG_PK,
(select database_name, NAME AS part_usr, currently_used
FROM MGMT$DB_FEATUREUSAGE
where NAME = 'Partitioning (user)'
and currently_used='TRUE') PART_USED,
(select database_name, NAME AS tune_usr, currently_used
FROM MGMT$DB_FEATUREUSAGE
where NAME = 'Tuning Pack'
and currently_used='TRUE') TUNE_PK,
MGMT$DB_FEATUREUSAGE MAIN_MDF
where MAIN_MDF.database_name=ADV_SEC.database_name(+)
and MAIN_MDF.database_name=RAC_INFO.database_name(+)
and MAIN_MDF.database_name=HCC_INFO.database_name(+)
and MAIN_MDF.database_name=DIAG_PK.database_name(+)
and MAIN_MDF.database_name=PART_USED.database_name(+)
and MAIN_MDF.database_name=TUNE_PK.database_name(+)
order by 1;

Advanced Host Info

select distinct ohs.host_name "Target Host", ohs.distributor_version "OS Version", ohs.system_config "OS Hardware", DDI.database_name "DB Name", ddi.edition "DB Edition", hcd.vendor_name "CPU Vendor", hcd.IMPL "CPU Type", hcd.instance_count "DB Count", hcd.num_cores "Number of Cores"
from sysman.MGMT$HW_CPU_DETAILS HCD, sysman.mgmt$os_hw_summary OHS, SYSMAN.MGMT$DB_DBNINSTANCEINFO DDI
where HCD.target_guid=OHS.target_guid
and ohs.host_name=DDI.host_name
and ohs.host_name=DDI.host_name
group by ohs.host_name, ohs.distributor_version, ohs.system_config, DDI.database_name, DDI.edition,hcd.vendor_name, hcd.IMPL, hcd.instance_count, hcd.num_cores
order by ddi.database_name

Layout

Clicking on Layout will show that I’ve changed how the format is, placing two of the tables next to each other, allowing for a more aesthetically pleasing report.

This is done by clicking in the radio button of the element you wish to move and clicking on the Move Up and Move Down buttons.

For this report, I’m actually finished, but if you wanted to, you could click on the Schedule tab and add a schedule to set it up to run quarterly and even email it out to users.  You can also set up a purge policy for the report in this section.  You can email reports to anyone and there is no requirement to be set up in the EM12c environment to have a report delivered via email, (so you can send all the reports to pesky managers… :))

The last tab, (also not used for this report…) is the Access tab.

If you want to limit who can manage the report, here is the page you do that.  The user must be set up in the EM12c administrators list to be added, (unlike with the email option on the report….) Simply add them here and EM12c will manage the rest for you!

You can then click on OK/Save for the report to return you to the original IP Report menu.  To run a report is as simple as clicking on the report link, which will either run the report or ask for any prerequisite information before running the report.

The Actual Report

I’ve “washed” the client data out, but here’s the report, in two parts.  A report that used to take them a couple days to collect is now something they can just run, print or email… 🙂

Things to Remember

There are multiple objects in the SYSMAN schema that may query the same data, (MGMT$TARGETS/MGMT_TARGETS/MGMT$TARGET) but I was surprised to find out that only the last view in that list actually works within the console.  This can be a bit disconcerting when a query from SQL*Plus, which logged in with the same user, succeeds and yet returns a 942 error in the console.  For successful IP Reports with SQL queries:

•  Attempt to stick with SYSMAN objects
• Use the MGMT$ views  vs. MGMT_ views
• Fully qualify whenever possible.

If you do run into problems with a complicated query, (which won’t return exact info on what view is experiencing difficulty and yet succeeding from SQL*Plus) creating a “test report” where you can run small queries against single objects to pinpoint the issue can be very helpful.

This is a good starting point for creating a report and I have to remind everyone-  the migration to BI Publisher is coming, so this is a short-lived solution for your reports, which will soon need to be migrated over to the future integrated BI Publisher in EM12c in an upcoming release!