By DaveWolf on Jul 16, 2013
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 and provisioning.
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 Step
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.
3. Select 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.
4. Click 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 results. )
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.
11. Choose “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.
12. Click Properties on the Oracle Home row.
13. Open the “Patches installed in Oracle Home” folder and select “Patch ID” property.
14. Click OK.
15. Enter 14275605 in the text box next to “Patch ID” to narrow the results to this patch.
16. 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.
17. Click the “Advanced Options” button on the “Patches installed in Oracle Home” row. ( Be sure to select the correct one! )
18. Change the Condition in the resulting dialog box to “NOT EXISTS”. ( The explanatory text shown just happens to use patch search as an example. )
19. Click OK.
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.
20. Click Search.
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.”
21. Click ‘Save As’ to save the search with the name “188.8.131.52 SI AC DBs without patch 14275605”.
22. Click OK.
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 database.
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.
A zip of the script is available for download here.
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 management pack.
For more information on Enterprise Manager’s database lifecycle management capabilities, visit http://www.oracle.com/technetwork/oem/lifecycle-mgmt/index.html