Tuesday, November 12, 2013

Mass promotion of OEM12c targets

Once you have all the OEM12c agents installed on the servers, the next step is to get all the targets added on to OEM12c. OEM12c auto gathers the properties of the targets that you configure the agents to collect data on. Once done, you should see all the targets under auto configure results. The results shoudl include database instances, listeners, oracle homes, clusters, oracle HAS, rac databases.
There is no option to mass promote targets from OEM12c console so that these targets can be then monitored.

However emcli can run script files with multiple commands and a SQL can be used to generate the commands.

To generate the SQL commands, connect to the OMS database and run below SQLs to generate the commands.

alter session set CURRENT_SCHEMA=SYSMAN;


For Oracle Databases

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_database" -host="' ||
ManagementEntityEO.HOST_NAME || '" -credentials="UserName:dbsnmp;password:dbsnmp;Role:Normal" -properties="SID:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%SID')) || ';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%PORT')) || ';OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) ||
';MachineName:' || ManagementEntityEO.HOST_NAME || ';"'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='oracle_database'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'));



For Listeners

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_listener" -host="' || ManagementEntityEO.HOST_NAME ||'" -properties="LsnrName:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%LSNRNAME')) || ';ListenerOraDir:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%LISTENERORADIR')) ||
';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%PORT'))
|| ';OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) ||
';Machine:' || ManagementEntityEO.HOST_NAME || ';"'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='oracle_listener'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'));


For Clusters

select
'add_target -name="' || ManagementEntityEO.ENTITY_NAME
|| '" -type="cluster" -host="' ||ManagementEntityEO.HOST_NAME
|| '" -monitor_mode="1" -properties="OracleHome:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1)
|| ';eonsPort:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE 'EONSPORT%'))
|| ';scanName:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE 'SCANNAME%'))
|| ';scanPort:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE 'SCANPORT%'))
|| ';" -instances="' ||ManagementEntityEO.HOST_NAME||':host;'||
(select decode(b.host_name,null,'"',b.host_name||':host"')
FROM SYSMAN.GC_MANAGEABLE_ENTITIES a , SYSMAN.GC_MANAGEABLE_ENTITIES b WHERE a.PROMOTE_STATUS=1
AND a.MANAGE_STATUS=1
AND a.ENTITY_TYPE!= 'host'
AND a.ENTITY_TYPE='cluster'
and b.HOST_NAME like REGEXP_REPLACE(a.HOST_NAME, '([a-z]+)[0-9].*', '\1%')
and b.host_name <> a.host_name
and b.entity_type='host'
and a.host_name=ManagementEntityEO.host_name
)
FROM SYSMAN.GC_MANAGEABLE_ENTITIES ManagementEntityEO,SYSMAN.MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='cluster'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
SYSMAN.mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'))



For RAC databases

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="rac_database" -host="' ||
ManagementEntityEO.HOST_NAME ||
'" -monitor_mode="1" -properties="ServiceName:'||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%SERVICENAME%')) ||';ClusterName:'||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%CLUSTERNAME%')) ||'" -instances="'||
(select listagg(sub.entity_name,':oracle_database;') within group (order by sub.entity_name) FROM GC_MANAGEABLE_ENTITIES sub
where sub.ENTITY_TYPE='oracle_database' and sub.entity_name like ManagementEntityEO.ENTITY_NAME||'%')||':oracle_database”'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='rac_database'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'))



For ASM instances

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="osm_instance" -host="' ||
ManagementEntityEO.HOST_NAME || '" -credentials="UserName:sys;password:sTroNGPassWd;Role:sysdba" -properties="OracleHome:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1)
|| ';MachineName:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%MACHINENAME')) || ';SID:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%SID%'))||
';Port:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%PORT%')) ||'"'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='osm_instance'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'))


The output of this can be saved into an command argument file (in this example discover.dat)

EMCLI can then be invoked to run this command file to mass promore targets


emcli login -username=OEMDBA -password=sTrOnGPassWd
Login successful

emcli sync
Synchronized successfully

emcli argfile discover.dat

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi, thanks for providing such scripts, it is very valuable. I have a couple of questions though.

    1) I noticed that adding a target via command line takes about 5 mins to complete, this is because there is a default timeout set. Oracle say it is about 10mins, but when I tested it for about 9 database targets, it was about 5mins for each action. Can this be reduced to a lower value?

    2) Where are the values for PROMOTE_STATUS and MANAGE_STATUS in GC_MANAGEABLE_ENTITIES tables and what do they mean?

    Regards
    fogun

    ReplyDelete
  3. I have run the script to add cluster targets, and it is failing with this error message:-

    (select decode(b.host_name,null,'"',b.host_name||':host"')
    *
    ERROR at line 18:
    ORA-01427: single-row subquery returns more than one row

    As the cluster target has two instances associated with it, I expected it to include them. Does the add cluster script allow for clusters with multiple instances?

    Regards

    ReplyDelete