DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

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:

ip_report

 

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.

excel_rpt

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

1st_create_rpt

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”:

add_element

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

ip_rpt_ele

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:

em_element_target_query

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.

ip_rpt_options

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.

ip_rpt_layout

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… :))

ip_job_sched

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

ip_rpt_access

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… :)

Part1_rpt

part2_rpt

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!

Print Friendly

facebook comments:

10 thoughts on “EM12c Information Reporting
  • Christine Cochran says:

    Is there a way to create a ‘dashboard’ that shows a collection of information? I would like to create a dashboard for our on-call DBA’s so that they can view all the information they need on a daily basis on a single page. Things like RMAN backup failures, storage usage, FRA usage, etc.

  • dbakevlar says:

    Yes, you can create a dashboard, with a combination of charts and results that will fulfill this request with an Information Publisher report. There are a couple dashboards prebuilt. I would check them out, do a create like to become familiar with the concepts and then build out from there.

  • Very nice blog post Kellyn. Information Publisher remains a great way to fairly easily create reports out of all that great data in the Oracle Enterprise Manager OMR. You’ve provided excellent examples of how to put that data to use and how to create the Information Publisher reports!

  • Christine Cochran says:

    Thanks Kellyn! This is very helpful. Could you talk a little bit about permissions needed to create reports on views that are not owned by SYSMAN or the MGMT_USER? I am trying to create the dashboard I mentioned earlier and am querying the rman catalog. The query works fine when I run it against the rman repository but fails when I try to run it via the Information Publisher report with the error “Error rendering element. Exception: ORA-00942: table or view does not exist”. I have seen some notes that say you should grant select on the view to the DBSNMP or MGMT_USER. But how do you grant permissions to user in one database (grid repository) to another database (rman repository)?

  • dbakevlar says:

    I’ll get a post out on this soon. It’s important to create a report owner that can perform this task with the proper credentials to the rman repository tables. If you wish to do so to the DBSNMP user, remember, this is the DBSNMP@.
    Hope that until then, this helps,
    Kellyn

  • Ray Smith says:

    Christine:
    Grant select on the link to MGMT_VIEW repository user (I know, it should be SYSMAN, but it isn’t) and create a public synonym for the link. Information Publisher can’t resolve fully-qualified object names.

    grant select on christine.catalog_view to mgmt_view;
    create or replace public synonym catalog_view for christine.catalog_view;

    More information on http://oramanageability.wordpress.com/

    Sorry to highjack your blog Kellyn…

  • Christine Cochran says:

    Thank you Ray! I will give this a try.

  • We use Information Publisher extensively. Love it. Something else I’ve done with it – I have another schema which just happens to be in the same database as OEM12c. It pulls all kinds of application-specific info on a daily basis and OEM has grants to select on its tables… so I’m able to easily include this info in OEM InfoPub reports. The team loves the quick access to this info (with pie charts and stuff too), and I got the whole thing setup in a matter of hours.

  • Jean-François Léguillier says:

    Hello,
    great site, very helpful.
    I have a little problem with oem 12.1.0.4 : MGMT$DB_FEATUREUSAGE view is empty for my oracle targets (11.2.0.3).
    When i used grid control 12.1.0.3 MGMT$DB_FEATUREUSAGE was populated.
    Any idea ?

  • dbakevlar says:

    Have you applied the patches from the master list for 12.1.0.4, Master Bundle Patch List (Doc ID 1900943.1) ?

    I believe there is a patch that will correct the issue.
    Thanks,
    Kellyn

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

  • Facebook
  • Google+
  • LinkedIn
  • Twitter