Have you ever added your RAC cluster to OEM, thinking that you entered all the target properties at the right places? But after it’s done, you realize that there are still targets that have their properties left unset? The problem is more prevalent with Exadata, since it has many more targets. Or have you ever changed your monitoring hierarchy and you need to change the properties for a lot of targets? If it’s more than 10 targets, you’d be better off scripting it. This article illustrates mass updating OEM target properties through generating the emcli utility commands from dynamic SQL.
Definitely, there are other ways to do this, but this method seems easier than many of these articles online. Nevertheless, I will link to them at the bottom.
Command Syntax
This is what the command looks like:
cd $OMS_HOME/bin
emcli set_target_property_value -subseparator=property_records="@@" -property_records="{Target Name}@@{Target Type}@@{Property Name}@@{Property Value}"
Execute this command as the oracle user on the Oracle Management Services(OMS) boxes.
In order do this repetitively, we are going to use dynamic SQL to compose these statements for many targets all at once.
First of all, we’ll have to go over each of these 4 elements and understand what the appropriate values are. Each of these four fields are delimited using a colon by default. But you can change the delimitor using the -subseparator= parameter.
For the Agent targets(oracle_emd) and the Agent Oracle Home targets(oracle_home), there are colons already in the target names. Using colon as the separator would mess up the delimitation. Therefore, you see the use of the @@ separator below.
Target Name
The Target Name is just the name of the target you’re trying to update.
Target Type
The Target Type parameter value can be queried in the SYSMAN.EM_TARGET_TYPES table on the OMR Database as follows. You need to know these target types if you need to update certain types of targets.
set lines 200 pages 10000
col target_type format a50
col type_display_name format a60
select type_display_name, target_type
from sysman.em_target_types
order by 1;
| Target Types | Target Types (Display Value) |
|---|---|
| apm_ba_service | Business Application |
| asm_diskgroup_component | ASM Disk Group Component |
| bea_alsb | Oracle Service Bus |
| casm_diskgroup_component | Cluster ASM Disk Group Component |
| cloud_resource_provider | Cloud Resource Provider |
| cloud_service | Cloud Service |
| cluster | Cluster |
| composite | Group |
| composite_application_system | Oracle Composite Application |
| coreid_access_sys | Access Manager Access System |
| coreid_identity_sys | Access Manager Identity System |
| exa_rack | Exalogic Rack |
| exalogic_control | Exalogic Control System |
| exalogic_system | Exalogic Elastic Cloud |
| exalytics_machine | Exalytics Machine |
| exalytics_system | Exalytics System |
| formsapp | formsapp |
| gds_cloud | Global Data Services |
| gds_gsm | Global Service Manager |
| gds_pool | Global Data Services Pool |
| generic_apache | Apache HTTP Server |
| generic_idm_system | Identity and Access System |
| generic_redundancy_group | Redundancy Group |
| generic_service | Generic Service |
| generic_system | Generic System |
| glassfish_cluster | Oracle GlassFish Cluster |
| glassfish_domain | Oracle GlassFish Domain |
| glassfish_j2eeserver | Oracle GlassFish Server |
| has | Oracle High Availability Service |
| host | Host |
| host_group | Host Group |
| j2ee_application | Application Deployment |
| j2ee_application_cluster | Clustered Application Deployment |
| j2ee_application_domain | Domain Application Deployment |
| java_virtual_machine | Java Virtual Machine |
| java_virtual_machine_alltargets | JVM Pools |
| java_virtual_machine_pool | Java Virtual Machine Pool |
| jboss_domain | Fusion Middleware JBoss J2ee Domain |
| jboss_j2eeserver | JBoss Application Server |
| jboss_partition | JBoss Partition |
| jee_webservice | JEE Web Service |
| jrf_webservice | Oracle Infrastructure Web Service |
| jvm | JVM |
| metadata_repository | Metadata Repository |
| netapp_filer | Network Appliance Filer |
| oc4j | OC4J |
| oc4j_cluster | OC4J Cluster |
| oc4jjvm | OC4J JVM |
| oracle_activitygraph | Activity Graph |
| oracle_analytics | Analytics |
| oracle_apache | Oracle HTTP Server |
| oracle_apache_cluster | HTTP Server Cluster |
| oracle_apm | Oracle Authorization Policy Manager |
| oracle_application | Oracle Application |
| oracle_apps_system | Oracle Apps |
| oracle_bc4j | ADF Business Components for Java |
| oracle_beacon | Beacon |
| oracle_bi_ans | Oracle BI Analytics Server (10g) |
| oracle_bi_cc | Oracle BI Cluster Controller (10g) |
| oracle_bi_cluster_controller | Oracle BI Cluster Controller |
| oracle_bi_dac | Oracle BI DAC Server |
| oracle_bi_instance | Oracle BI Instance |
| oracle_bi_java_host | Oracle BI JavaHost |
| oracle_bi_presentation_services | Oracle BI Presentation Server |
| oracle_bi_ps | Oracle BI Presentation Server (10g) |
| oracle_bi_sch | Oracle BI Scheduler (10g) |
| oracle_bi_scheduler | Oracle BI Scheduler |
| oracle_bi_server | Oracle BI Server |
| oracle_bi_suite | Oracle BI Suite EE |
| oracle_bi_web | OracleBI Web Plug-in |
| oracle_bpelprocess | Oracle BPEL PRocess |
| oracle_btm_sphere | BTM Sphere Engine |
| oracle_btm_sys | BTM System |
| oracle_camm_manager | OCAMM Manager |
| oracle_cef | Connector Extension Framework |
| oracle_cef_cluster | Connector Extension Framework Cluster |
| oracle_cloud | Oracle Cloud |
| oracle_coherence | Oracle Coherence Cluster |
| oracle_coherence_cache | Oracle Coherence Cache |
| oracle_coherence_node | Oracle Coherence Node |
| oracle_coreid_access_server | Access Manager Access Server |
| oracle_coreid_identity_server | Access Manager Identity Server |
| oracle_csa_collector | CSA Collector |
| oracle_das | Delegated Administration Services |
| oracle_das_server | Delegated Administration Server |
| oracle_database | Database Instance |
| oracle_database_group | Database Group |
| oracle_dbmachine | Oracle Database Machine |
| oracle_dbsvc | Schema Service |
| oracle_dbsys | Database System |
| oracle_discussion | Discussion |
| oracle_dsee | Oracle Directory Server Enterprise Edition Server |
| oracle_dsee_ent | Directory Server Enterprise |
| oracle_dsee_grp | Directory Server Enterprise Group |
| oracle_em_service | EM Service |
| oracle_emd | Agent |
| oracle_emd_proxy | Agent proxy |
| oracle_emrep | OMS and Repository |
| oracle_emsvrs_sys | EM Servers System |
| oracle_ep_service | Service Entry Point |
| oracle_eps | Directory Integration Platform |
| oracle_eps_server | Directory Integration Platform Server |
| oracle_essbase | Essbase Server |
| oracle_essbaseapp | Essbase Application |
| oracle_exa_cisco_switch | Oracle Engineered System Cisco Switch |
| oracle_exa_ilom | Oracle Engineered System ILOM Server |
| oracle_exa_kvm | Oracle Exadata KVM |
| oracle_exa_pdu | Oracle Engineered System PDU |
| oracle_exadata | Oracle Exadata Storage Server |
| oracle_exadata_dbsys | Oracle Database Exadata Storage Server System |
| oracle_exadata_grid | Oracle Exadata Storage Server Grid |
| oracle_federation | Identity Federation Server |
| oracle_federation_sys | Identity Federation System |
| oracle_forms | Forms |
| oracle_home | Oracle Home |
| oracle_ias | Oracle Application Server |
| oracle_ias_cluster | Oracle Application Server Cluster |
| oracle_ias_farm | Oracle Fusion Middleware Farm |
| oracle_ias_jmx_cluster | Oracle Application Server 10.1.3 Cluster |
| oracle_ibnetwork | Oracle Infiniband Network |
| oracle_ibswitch | Oracle Infiniband Switch |
| oracle_idm | Identity Management |
| oracle_idm_repos | Identity Manager Repository |
| oracle_idm_server | Identity Manager Server |
| oracle_idm_system | Identity Manager System |
| oracle_integrationbpm | Oracle BPEL Process Manager |
| oracle_irm | Oracle Information Rights Management |
| oracle_jserv | JServ |
| oracle_ldap | Oracle Internet Directory |
| oracle_listener | Listener |
| oracle_oaam | Oracle Adaptive Access Manager |
| oracle_oaam_cluster | Oracle Adaptive Access Manager Cluster |
| oracle_oam | Oracle Access Management Server |
| oracle_oam_cluster | Oracle Access Management Cluster |
| oracle_odi_j2eeagent | ODI Java EE Agent |
| oracle_odi_standaloneagent | ODI Standalone Agent |
| oracle_oid | Internet Directory |
| oracle_oim | Oracle Identity Manager |
| oracle_oim_cluster | Oracle Identity Manager Cluster |
| oracle_oms | Oracle Management Service |
| oracle_oms_console | OMS Console |
| oracle_oms_pbs | OMS Platform |
| oracle_otd_cluster | Traffic Director Configuration |
| oracle_otd_instance | Traffic Director Instance |
| oracle_ovd | Oracle Virtual Directory |
| oracle_pdb | Pluggable Database |
| oracle_personalization | Personalization |
| oracle_portal | Oracle Portal |
| oracle_portaltools | Portlet Producers |
| oracle_repapp | Oracle Reports Application |
| oracle_repbrd | Oracle Reports Bridge |
| oracle_repserv | Oracle Reports Server |
| oracle_reptools | Oracle Reports Tools |
| oracle_ruei_reporter | RUEI Reporter Engine |
| oracle_ruei_sys | RUEI System |
| oracle_sdpmessagingdriver | User Messaging Driver |
| oracle_sdpmessagingdriver_email | Email Driver |
| oracle_sdpmessagingdriver_proxy | Proxy Driver |
| oracle_sdpmessagingdriver_smpp | SMPP Driver |
| oracle_sdpmessagingdriver_voicexml | VoiceXML Driver |
| oracle_sdpmessagingdriver_worklist | Worklist Driver |
| oracle_sdpmessagingdriver_xmpp | XMPP Driver |
| oracle_sdpmessagingserver | User Messaging Service |
| oracle_soa_composite | SOA Composite |
| oracle_soa_composite_sys | Oracle SOA Composite Sys |
| oracle_soa_folder | SOA Partition |
| oracle_soainfra | SOA Infrastructure |
| oracle_soainfra_cluster | Oracle SOA Infra Cluster |
| oracle_soainfra_sys | Oracle SOA Infra Sys |
| oracle_social | Oracle Social Network |
| oracle_social_cluster | Oracle Social Network Application Cluster |
| oracle_social_instance | Oracle Social Network Instance |
| oracle_sso | Single Sign-On |
| oracle_sso_server | Single Sign-On Server |
| oracle_ucm | Oracle Universal Content Management |
| oracle_webcache | Web Cache |
| oracle_webcenter | Oracle WebCenter Portal |
| osb_business_service | OSB Business Service |
| osb_pipeline | OSB Pipeline |
| osb_proxy_service | OSB Proxy Service |
| osb_server | Oracle Secure Backup |
| osb_splitjoin | OSB Split Join |
| osm_cluster | Cluster ASM |
| osm_instance | Automatic Storage Management |
| osm_ioserver | Asm IO Server |
| osm_proxy | Asm Proxy |
| rac_database | Cluster Database |
| rest_webservice | RESTful Web Service |
| scheduler_service | Scheduler Service |
| scheduler_service_group | Scheduler Service Group |
| soa_composite_component | SOA Composite Component |
| soa_composite_reference | SOA Composite Reference |
| soa_composite_service | SOA Composite Service |
| soainfra_engine | SOA Infrastructure Engine |
| wbem_cimom | Wbem CIM Object Manager |
| webcache_cluster | WebCache Cluster |
| webcenter_portal_app | webcenter_portal_app |
| weblogic_cluster | Oracle WebLogic Cluster |
| weblogic_domain | Oracle WebLogic Domain |
| weblogic_domain_group | Oracle WebLogic Domain Group |
| weblogic_j2eeserver | Oracle WebLogic Server |
| website | Web Application |
| websphere_cell | IBM WebSphere Application Server Cell |
| websphere_cluster | IBM WebSphere Application Server Cluster |
| websphere_j2eeserver | IBM WebSphere Application Server |
| wmq | IBM WebSphere MQ Queue Manager |
| wmq_cluster | IBM WebSphere MQ Cluster |
Some of these target type names aren’t very straightforward. For example, the Agent target type is “oracle_emd”.
Property Name
The Property Name is the property that you want to change. A list of these property names can be found in mgmt$all_target_prop_defs:
select property_display_name, property_name
from mgmt$all_target_prop_defs;
| Property Name(display) | Property Name |
|---|---|
| Comment | orcl_gtp_comment |
| Contact | orcl_gtp_contact |
| Cost Center | orcl_gtp_cost_center |
| Customer Service Identifier | orcl_gtp_csi |
| Department | orcl_gtp_department |
| Deployment Type | orcl_gtp_deployment_type |
| Lifecycle Status | orcl_gtp_lifecycle_status |
| Line of Business | orcl_gtp_line_of_bus |
| Location | orcl_gtp_location |
| Operating System | orcl_gtp_os |
| Platform | orcl_gtp_platform |
| Target Version | orcl_gtp_target_version |
| Owner | udtp_1 |
Property Value
The Property Value is where you specify the value. Most properties accept any value, except for Lifecycle Status, which accepts only:
-
- None
- Development
- Test
- Staging
- Production
- Mission Critical
Monitoring Hierarchy
In order to figure out what to update, you do need to have a solid understanding on how your targets hierarchy is structured.
If you adopt the very common monitoring hierarchy, you’d have something like this:
where the root group is divided into the Lifecycle Status: Development, Test, Staging, Production, and Mission Critical. And then each of those is divided into Cost Centers, which can be your organization’s teams or departments.
In your organization, you can have many other ways of organizing the targets, but you’d just have to know how the structure is laid out.
Based on this, let’s say we’re changing the properties of Lifecycle Status to “Production” and Cost Center to “BLAH” of some targets.
Procedures
From the OMR database, you’d run a query to dynamically generate the commands from data. Several points:
- Specify a where clause to narrow down to only the targets you want to update
- Exclude certain target types that you simply don’t use, like the cloud types and composite
Cost Center
select './emcli set_target_property_value -subseparator=property_records="@@" -property_records="' || target_name || '@@' || target_type || '@@Cost Center@@BLAH"'
from mgmt$target
where target_name || ',' || target_type not in (
select target_name || ',' || target_type from mgmt$target_properties
where property_name = 'orcl_gtp_cost_center' <-- specifying the cost center property
)
and target_name like '%......%' ← Use your own criteria to match the targets you want to update
and target_type not in ('composite', 'cloud', 'oracle_cloud', 'oracle_vm_cloud') <-- excluding some targets
order by target_type, target_name;
Lifecycle Status
select './emcli set_target_property_value -subseparator=property_records="@@" -property_records="' || target_name || '@@' || target_type || '@@Lifecycle Status@@Production"'
from mgmt$target
where target_name || ',' || target_type not in (
select target_name || ',' || target_type from mgmt$target_properties
where property_name = 'orcl_gtp_lifecycle_status' <-- specifying the Lifecycle Status property
)
and target_name like '%......%' ← Use your own criteria to match the targets you want to update
and target_type not in ('composite', 'cloud', 'oracle_cloud', 'oracle_vm_cloud') <-- excluding some targets
order by target_type, target_name;
Examples
The following are some examples of the generated commands, including some for Exadata targets.
Lifecycle Status
# Cluster ASM ./emcli set_target_property_value -subseparator="@@" -property_records="+ASM_lax1-XXXX@@osm_cluster@@Lifecycle Status@@Production" # Automatic Storage Management ./emcli set_target_property_value -subseparator="@@" -property_records="+ASM1_lax1-XXXX-dbadm01.domain.com@@osm_instance@@Lifecycle Status@@Production" # Oracle Database Exadata Storage Server System ./emcli set_target_property_value -subseparator="@@" -property_records="usXXXX_cellsys@@oracle_exadata_dbsys@@Lifecycle Status@@Production" # Cluster Database ./emcli set_target_property_value -subseparator="@@" -property_records="usXXXX@@rac_database@@Lifecycle Status@@Production" # Oracle Home ./emcli set_target_property_value -subseparator="@@" -property_records="agent12c1_3_lax1-XXXX-dbadm01.domain.com@@oracle_home@@Lifecycle Status@@Production" #Oracle High Availability Service ./emcli set_target_property_value -subseparator="@@" -property_records="has_lax1-XXXX-dbadm01.domain.com@@has@@Lifecycle Status@@Production" # Cluster ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX@@cluster@@Lifecycle Status@@Production" # Oracle Exadata Storage Server ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-celadm01.domain.com@@oracle_exadata@@Lifecycle Status@@Production" # Host ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01.domain.com@@host@@Lifecycle Status@@Production" # Agent ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01.domain.com:3872@@oracle_emd@@Lifecycle Status@@Production" # Oracle Engineered System ILOM Server ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01-ilom.domain.com@@oracle_exa_ilom@@Lifecycle Status@@Production" # Oracle Engineered System Cisco Switch ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-sw-adm01.domain.com@@oracle_exa_cisco_switch@@Lifecycle Status@@Production" # Oracle Infiniband Switch ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-sw-iba1.domain.com@@oracle_ibswitch@@Lifecycle Status@@Production" # Oracle Engineered System PDU ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-sw-pdua01.domain.com@@oracle_exa_pdu@@Lifecycle Status@@Production" # Listener ./emcli set_target_property_value -subseparator="@@" -property_records="LISTENER_lax1-XXXX-dbadm01.domain.com@@oracle_listener@@Lifecycle Status@@Production" # Oracle Home ./emcli set_target_property_value -subseparator="@@" -property_records="OraDb11g_home1_2_lax1-XXXX-dbadm01.domain.com@@oracle_home@@Lifecycle Status@@Production" # Database System ./emcli set_target_property_value -subseparator="@@" -property_records="usXXXX_lax1-XXXX_sys@@oracle_dbsys@@Lifecycle Status@@Production" # Oracle Engineered System Healthchecks ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01.domain.com@@oracle_exadata_hc@@Lifecycle Status@@Production"
Cost Center
# Cluster ASM ./emcli set_target_property_value -subseparator="@@" -property_records="+ASM_lax1-XXXX:osm_cluster:Cost Center:BLAH" # Automatic Storage Management ./emcli set_target_property_value -subseparator="@@" -property_records="+ASM1_lax1-XXXX-dbadm01.domain.com:osm_instance:Cost Center:BLAH" # Oracle Database Exadata Storage Server System ./emcli set_target_property_value -subseparator="@@" -property_records="usXXXX_cellsys:oracle_exadata_dbsys:Cost Center:BLAH" # Cluster Database ./emcli set_target_property_value -subseparator="@@" -property_records="usXXXX:rac_database:Cost Center:BLAH" # Oracle Home ./emcli set_target_property_value -subseparator="@@" -property_records="agent12c1_3_lax1-XXXX-dbadm01.domain.com:oracle_home:Cost Center:BLAH" #Oracle High Availability Service ./emcli set_target_property_value -subseparator="@@" -property_records="has_lax1-XXXX-dbadm01.domain.com:has:Cost Center:BLAH" # Cluster ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX:cluster:Cost Center:BLAH" # Oracle Exadata Storage Server ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-celadm01.domain.com:oracle_exadata:Cost Center:BLAH" # Host ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01.domain.com:host:Cost Center:BLAH" # Agent ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01.domain.com:3872@@oracle_emd@@Cost Center@@BLAH" # Oracle Engineered System ILOM Server ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01-ilom.domain.com:oracle_exa_ilom:Cost Center:BLAH" # Oracle Engineered System Cisco Switch ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-sw-adm01.domain.com:oracle_exa_cisco_switch:Cost Center:BLAH" # Oracle Infiniband Switch ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-sw-iba1.domain.com:oracle_ibswitch:Cost Center:BLAH" # Oracle Engineered System PDU ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-sw-pdua01.domain.com:oracle_exa_pdu:Cost Center:BLAH" # Listener ./emcli set_target_property_value -subseparator="@@" -property_records="LISTENER_lax1-XXXX-dbadm01.domain.com:oracle_listener:Cost Center:BLAH" # Oracle Home ./emcli set_target_property_value -subseparator="@@" -property_records="OraDb11g_home1_2_lax1-XXXX-dbadm01.domain.com:oracle_home:Cost Center:BLAH" # Database System ./emcli set_target_property_value -subseparator="@@" -property_records="usXXXX_lax1-XXXX_sys:oracle_dbsys:Cost Center:BLAH" # Oracle Engineered System Healthchecks ./emcli set_target_property_value -subseparator="@@" -property_records="lax1-XXXX-dbadm01.domain.com:oracle_exadata_hc:Cost Center:BLAH"
References
https://www.krenger.ch/blog/target-types-in-oracle-em-cloud-control-12c
https://blogs.oracle.com/oem/using-em-cli-for-mass-update-of-lifecycle-status-property-value-v2
https://mjgangler.wordpress.com/2013/06/26/em12c-changing-target-properties-in-mass/
https://blog.toadworld.com/2017/04/19/using-emcli-for-mass-update-a-property-value
Thanks