Selecting Data from the Repository

Introduction

As you may know, one of the many areas of responsibility I have as a product manager in the Enterprise Manager product management team is BI Publisher. BI Publisher is the tool of choice for reporting in Enterprise Manager. If it’s not a tool you’re familiar with, then you are missing out on a lot of the power of the Enterprise Manager repository to provide you the information you need to extend Enterprise Manager beyond the capabilities it currently has. You can read more about it on my BI Publisher page.

Of course, to reach the full capacity of the reporting you can extend Enterprise Manager with, you need to understand the Enterprise Manager repository, and that’s what this post will introduce you to. First, however, you need to understand a little more about the BI Publisher security model.

The BI Publisher Security Model

The Oracle Management Repository (more commonly referred to as either the OMR or simply the repository) contains a large number of objects owned by the SYSMAN user. The restricted-use BI Publisher license that is provided with Enterprise Manager connects to the EMREPOS data source from the BI Publisher server that is configured for use with Enterprise Manager reports. The EMREPOS data source connects to the MGMT_VIEW account in the repository and establishes the proper security context for the Enterprise Manager user logged on to BI Publisher.

As a security measure, BI Publisher reports that use the EMREPOS data source have read-only access to the public MGMT$ and GC$ views, and not to the underlying Enterprise Manager tables. This model also supports sharing report queries with Enterprise Manager users who might want to use the Enterprise Manager-provided reports as a basis for their own reports.

The details of how the BI Publisher secuirty models is implemented in Enterprise Manager have already been well explained in a couple of demos from the good people at Oracle University. You can see those demos at this link from Enterprise Manager 12c, and this link for Enterprise Manager 13c.

Understanding the MGMT$ Views

Now that you understand the security model used with BI Publisher in Enterprise Manager, let’s spend some time looking at the MGMT$ views you can use to select information from. There are a few different places you can look at to understand these views.

The Management Repository Views Reference

The obvious place to state is the documentation, and in fact there is a whole manual just dedicated to the management repository views. That manual is located here for EM13c, and here for EM12c.

There are two important things to note about this manual:

  1. It has separate sections for the different areas of Enterprise Manager, so it’s broken down into separate chapters for Compliance views, Configuration Management views, and so forth.
  2. It also has a final chapter that is very important to be aware of. That chapter shows examples of queries against the different management views, broken down into the same sections as the rest of the manual, so you can see examples of queries against Compliance views, Configuration Management views, and so on.

This last chapter is something you can find very useful for creating your own queries, as you can see the SQL used for the specific examples and modify those according to your requirements.

The Enterprise Manager Cloud Control Extensibility Programmer’s Reference

A less well-known place to be looking for information on writing your own reports is the Enterprise Manager Cloud Control Extensibility Programmer’s Reference, located here for EM13c and here for EM12c. Those links take you straight to chapter 6 of the manuals, Developing BI Publisher Reports. While this doesn’t cover specifics of the MGMT$ views, it does give you a general understanding of both the security model and the overall process you go through to develop your own BI Publisher reports.

The Enterprise Manager Software Development Kit

Again, this is a less well-known tool for understanding the MGMT$ views. Full details on how to download and access the SDK are given in support note 1386614.1, Documentation for Enterprise Manager Cloud Control 12c Repository Views. While the note is specifically written for EM12c, the steps for downloading and installing it are the same for EM13c.

The EM SDK (sometimes also referred to as the Extensibility Development Kit or EDK) is designed to be a one-stop shop for developing management extensions on top of Enterprise Manager. It includes information on developing, debugging, packaging and archiving management extensions on top of EM. For the purposes of understanding the MGMT$ views, the important part of the SDK can be found by opening the file “doc/partnersdk/db_objects/emcore/others/index.html”, which takes you to a page listing all the views you have access to. By clicking on the links in the left hand pane, you can find more information on a lot of these views (though some of the links don’t show much information). One view that does is the MGMT$TARGET view. If you click on that link in the left hand pane, you’ll see this:

SDK1http://petewhodidnottweet.com/wp-content/uploads/2016/07/SDK1-300x186.jpg 300w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/SDK1-768x475.jpg 768w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/SDK1-1024x634.jpg 1024w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/SDK1-242x150.jpg 242w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/SDK1-150x93.jpg 150w" sizes="(max-width: 1340px) 100vw, 1340px" />

It is also worth exploring two links at the top of the page:

  1. Deprecated – This link shows all the views that are now deprecated, and probably more importantly it generally also shows which views have replaced them.
  2. Index – This link provides a brief description of what each view is used for, including a list of the columns and a link to more information on each view.

Configuration Searches

The final tool I wanted to introduce you to was configuration search. The first step to using this is to access the Configuration Search library page. To do this, follow the menu path “Enterprise” -> “Configuration” -> “Search…” (note the screenshots shown below are from an EM13c environment):

cs1http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs1-288x300.jpg 288w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs1-144x150.jpg 144w" sizes="(max-width: 445px) 100vw, 445px" />

On the “Configuration Search Library” page, select one of the configuration searches that is similar to what you want to do and click “Create Like”. In the example shown below, I’ve selected the “Pluggable Database Initialization Parameter Setting” configuration search:

cs2http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs2-300x111.jpg 300w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs2-768x284.jpg 768w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs2-250x92.jpg 250w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs2-150x55.jpg 150w" sizes="(max-width: 801px) 100vw, 801px" />

You will be prompted for a name of the new configuration search. Enter a meaningful name and click OK:

cs3http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs3-300x133.jpg 300w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs3-250x111.jpg 250w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs3-150x66.jpg 150w" sizes="(max-width: 418px) 100vw, 418px" />

You will be returned to the “Configuration Search Library” page, and will see a confirmation message that your configuration search was created successfully. Select the new configuration search and then click the “Edit” button:

cs4http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs4-300x147.jpg 300w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs4-250x122.jpg 250w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs4-150x73.jpg 150w" sizes="(max-width: 662px) 100vw, 662px" />

Click the “Search Using SQL” button:

cs5http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs5-300x96.jpg 300w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs5-768x245.jpg 768w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs5-1024x326.jpg 1024w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs5-250x80.jpg 250w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs5-150x48.jpg 150w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs5-1150x367.jpg 1150w" sizes="(max-width: 1152px) 100vw, 1152px" />

This will show you the actual SQL used for this specific configuration search:

cs6http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs6-300x196.jpg 300w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs6-230x150.jpg 230w, http://petewhodidnottweet.com/wp-content/uploads/2016/07/cs6-150x98.jpg 150w" sizes="(max-width: 471px) 100vw, 471px" />

You can then use this SQL as the basis for a BI Publisher report that you can build yourself.

Summary

So there you are – hopefully some of this information will be of use to you in creating your own reports! I’m hoping to start adding some examples of SQL that might be of use to you for writing your own reports in the FAQ page so stay tuned for some of those posts!

The post Selecting Data from the Repository appeared first on PeteWhoDidNotTweet.com.