Oracle Enterprise Manager collects and monitors configuration
information for every target it manages and layers support for sophisticated
lifecycle operations on top of this foundation. Within the configuration
management area itself Enterprise Manager has formalized support for Drift
Management, Inventory Management, Topology visualization and Compliance
Management. Although these features cover a good segment of configuration management
use cases there will always be additional uses for this important information.
Enterprise Manager makes it easy to leverage this valuable information both
inside and outside the product using the Configuration Search feature.
Within the Enterprise Manager UI, users can build
sophisticated queries against Enterprise Manager’s configuration management repository to generate reports
without writing even one line of SQL. If the Oracle provided Search library
does not already contain a matching search a user can build a search to their
exact specification completely graphically.
Outside of the Enterprise Manager UI, users can find and run
saved searches using the EMCLI in both interactive and the new script mode. When used in a script, configuration search
results can be used to drive other lifecycle operations like patch automation
In this article we will take a closer look at the
Configuration Search feature using some common real world examples.
Arguably one of the most important configuration items
collected by Enterprise Manager is applied patches. Finding the location of applied
patches can cause some confusion at first owing to the new target model
introduced in Enterprise Manager 12c. Oracle Home is now a separate and proper
target with its own configuration collection which includes patch information.
This makes great sense as patches are in fact applied to the Oracle Home and
not the software running out of it.
The question is how can you figure out which targets ( ie databases
) are using which Oracle Homes? The answer is using relationships. Enterprise
Manager 12c now discovers and collects relationships between targets. These
relationships include both physical (observed) and logical (inferred from
configuration). As an example, all databases running out of a given Oracle Home
will have an “Installed At” relationship to its specific Oracle Home target.
These relationships can be graphically viewed using the topology viewer
available under the configuration menu of all targets. They can also be used
when building a Configuration Search when starting with a well known target
like database instance.
Find all single instance databases with Advanced Compression option that do not have a patch applied. – Step by
Let’s build a configuration search to find all single
instance databases with Advanced Compression option that do NOT have a patch
applied to their Oracle Home. Since patches are typically specific to a version
let’s narrow it down to version 184.108.40.206 databases and patch 14275605. (
Database Patch Set Update : 220.127.116.11.4 )
1. Start by navigating to the Configuration Search Library. Enterprise->Configuration –>Search…
2. Click Create
to start building a new Configuration Search.
Database Instance from the Target Type list of value.
Next we need to narrow the list of databases to those of
version 18.104.22.168 and single instance. To do this we will use the target model
to choose properties which contain this data so we can filter it further.
Properties on the Database Instance row.
5. Open the Target
Properties and Instance Information folders and Select Property Name, Property Value,Version, Name and Selected as shown.
6. Click OK.
To filter down the results, we enter criteria into the text
boxes to the right of the properties.
7. Enter 22.214.171.124
next to Version.
8. Enter ‘Advanced
Compression’ for Name and ‘TRUE’ for
Selected under Database Options
9. Select ‘Metric
Scope’ for property Name and Enter
‘DB’ for value. ( Metric scope can have a value of DB for single instance and
RACINST for RAC instances. )
Your search should look something like this:
At any point while you are creating a Configuration Search,
you can see how your search is coming along by clicking Search. Doing so at
this point will show results similar to the results shown here. ( Note: If you are not interested in seeing
the results of a column you can uncheck the property to remove it from the
At this point we need to pull the Oracle Home target into
the picture to get at the applied patches configuration information.
10. Click Relationships
on the Database Instance row.
“Oracle Home” as the Destination Target Type then Click Search. This should result in one relationship type
“Installed At”. Select this row and click OK.
We now have something that looks like this:
To add collected patch information from the Oracle Home
target we need to use the target model again.
Properties on the Oracle Home row.
13. Open the
“Patches installed in Oracle Home” folder and select “Patch ID” property.
14. Click OK.
Enter 14275605 in the text box next to “Patch ID” to narrow the results to
Click Search. You should see something similar to the results below.
But wait, this shows the
databases that HAVE patch 14275605 installed. We are after databases that DON’T have this patch installed.
Fortunately we can achieve this
result by using the “Advanced Options” capabilities.
Click the “Advanced Options” button on the “Patches installed in Oracle
Home” row. ( Be sure to select the correct one! )
Change the Condition in the resulting dialog box to “NOT EXISTS”. ( The
explanatory text shown just happens to use patch search as an example. )
Notice the addition of “Condition
: NOT EXISTS” on the “Patches installed in Oracle Home” row. This will show
targets in which none of the targets matches the criteria. In our case, an
Oracle Home may have hundreds of patches applied. Only if none of the patch IDs
equal 14275605 will the target be in the results.
This time, the results finally
display what we are after. That is “126.96.36.199 Single Instance database with
Advanced Compression option that do NOT have patch 14275605 applied.”
Click ‘Save As’ to save the search with
the name “188.8.131.52 SI AC DBs without patch 14275605”.
The library now shows our new
search. You or any other user can run the search by selecting it and clicking
Run. You can modify it by using Edit or make a copy with Create Like to
continue to refine it without affecting the original.
Running Configuration Search using Interactive EMCLI
As mentioned at the opening, Enterprise
Manager Release 3 now supports the execution of saved Configuration Search from
the EMCLI. There are two verbs with which you can run configuration searches:
get_targets and run_configuration_search
The get_targets verb has been
available since Release 1 but now has an additional switch to specify a
configuration search. This results in a standardized result containing the
Target Name, Target Type and Status.
Here is an example using the
configuration search we just built.
The run_config_search verb
generates results exactly as you see them in the results of the configuration
search. The results are a little harder to read but the output could be
re-directed to an output file for import into something like a document editor
or spreadsheet for easier viewing or analysis.
Scripting Lifecycle processes using EMCLI Script mode
Enterprise Manager Release 3
introduced the EMCLI Script mode which is especially effective when performing
tasks in bulk or many tasks at once. This mode enables you to create Jython
scripts, store them as files and pass them as an argument to EMCLI. For more
information on EMCLI see the documentation here.
In this section, we will expand
on our previous work to automate the creation of patch plans to automate the
application of the missing patch. We will use a python script to retrieve the
list of databases without a patch, and then create a patch plan for each
As a prerequisite you must create
a sample patch plan for a single instance database which has the desired patch
( 14275605 ) added to the plan. We will use this plan to create the others.
The work flow is as follows:
- Retrieve specified patch plan metadata and extract
required patch information.
- Get list of databases without the patch applied
using a configuration search.
- Create a patch plan for each database.
the script in its entirety. Obviously you will need to make modifications for
you environment, specific patch plan and configuration search names.
from emcli import *
# Set Connection properties and logon
patch_id = 
release_id = 
platform_id = 
language_id = 
target_type = 
# Get Sample Patch Metadata
pp_xml = show_patch_plan(name='PSU4 Rollout').out()
# Parse plan metadata into XML
patchPlan = xml.dom.minidom.parseString(pp_xml)
# Retrieve metadata for each patch in the sample patch plan
for patchList in patchPlan.getElementsByTagName("patchList"):
for patch in patchList.getElementsByTagName("patch"):
# Run stored configuration search to get list of databases missing the patch
target_array = get_targets(config_search='184.108.40.206 SI AC DBs without patch 14275605').out()['data']
# For each target create a patch plan containing the patches in the sample patch plan
for targets in target_array:
tn = targets['Target Name']
nodeCount = 0
f = open('patchplan.txt', mode='w')
for node in patch_id:
f.write( "patch." + str(nodeCount) + ".patch_id=" + patch_id[nodeCount] + "\n")
f.write( "patch." + str(nodeCount) + ".release_id=" + release_id[nodeCount] + "\n")
f.write( "patch." + str(nodeCount) + ".platform_id=" + platform_id[nodeCount] + "\n")
f.write( "patch." + str(nodeCount) + ".language_id=" + language_id[nodeCount] + "\n")
f.write( "patch." + str(nodeCount) + ".target_name=" + tn + "\n")
f.write( "patch." + str(nodeCount) + ".target_type=" + target_type[nodeCount] + "\n")
nodeCount += 1
planName = 'PSU4 ' + tn
A zip of the script is available for download
Run the script by passing it as an
argument to EMCLI:
Here we can see all of the patch
plans created by the script plus the sample patch plan “PSU4 Rollout”
Enterprise Manager’s Configuration
Search feature is a powerful tool that can be leveraged both inside and outside
of the UI. It can quickly and easily provide answers to difficult configuration
questions without writing any SQL. When used via through the EMCLI it can be
used to dynamically generate a target list which can be used to drive complex
and otherwise time consuming tasks in the UI quickly and efficiently.
Configuration Search and patch
automation are both features of Enterprise Manager’s Database lifecycle
For more information on Enterprise
Manager’s database lifecycle management capabilities, visit http://www.oracle.com/technetwork/oem/lifecycle-mgmt/index.html
Twitter | Face book | You Tube | Linked in | Newsletter