Add OEM database targets using emcli

 Problem statement: Suppose you are running OMS instance and repository database in Solaris server and running older version of OMS. You want to upgrade OMS to 13.5 or later and at the same time you want to change OS platform from Solaris to Linux. During this process we need to build new OMS instance and repository database in new Linux platform. Once that is done we have to install new agent and run target discovery.Now you can promote targets from the autodiscovery results.

This will be very time-consuming effort if you have very large number of targets.This article will guide you how to reduce that effort using emcli utility.

1.       Connect to the repository database as the sysman user at sqlplus and run the following query

set heading off
set feedback off
set echo off
set linesize 1000
set pagesize 0

SELECT 'add_target -name="'
  || ManagementEntityEO.ENTITY_NAME
  || '" -type="oracle_database" -host="'
  || ManagementEntityEO.HOST_NAME
  || '" -credentials="UserName:dbsnmp;password:<dbsnmp_PASSWORD>;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
WHERE ManagementEntityEO.PROMOTE_STATUS=1 –This flag denotes target is eligible for promotion
AND ManagementEntityEO.MANAGE_STATUS   =1 –This flag denotes target is not managed yet
AND ManagementEntityEO.ENTITY_TYPE!    = 'host'
AND ManagementEntityEO.ENTITY_TYPE     ='oracle_database'
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'
  ))
AND ManagementEntityEO.HOST_NAME ='&hostname';
 
Now let’s explore this query little bit to understand how does it work.
 
When we try to add database targets manually we need to provide following inputs
·        Monitoring Username
·        Monitoring Password
·        Role
·        Oracle Home Path
·        Listener Machine Name
·        Port
·        Connection Protocol (TCP by default)s
·        Database SID
 
Lets explore the emcli command how to add targets
 
Login in oms server and login like below
emcli login -username=sysman -password=<password>
 
Now type emcli help add_target you can see below example
Examples:
    emcli add_target
          -name="database"
          -type="oracle_database"
          -host="myhost.example.com"
          -credentials="UserName:dbsnmp;password:dbsnmp;Role:Normal"
          -properties="SID:semcli;Port:15091;OracleHome:/oracle;MachineName:smpamp-sun1.example.com"
So you can see that emcli command expect same set of input parameters.So our target is to generate this command string from OMS repository.
Lets see table definition.You can see that we don’t have target name in MGMT_TARGET_PROPERTIES
Describe MGMT_TARGET_PROPERTIES
Name            Null     Type          
--------------- -------- --------------
TARGET_GUID     NOT NULL RAW(16 BYTE)  
PROPERTY_NAME   NOT NULL VARCHAR2(64)  
PROPERTY_TYPE   NOT NULL VARCHAR2(64)  
PROPERTY_VALUE           VARCHAR2(4000)
ECM_SNAPSHOT_ID          RAW(16 BYTE)  
 
 
Describe GC_MANAGEABLE_ENTITIES
Name                    Null     Type          
----------------------- -------- --------------
ENTITY_GUID             NOT NULL RAW(16 BYTE)  
ENTITY_TYPE             NOT NULL VARCHAR2(64)  
ENTITY_NAME             NOT NULL VARCHAR2(256) 
PARENT_ME_GUID          NOT NULL RAW(16 BYTE)  
PARENT_ME_TYPE                   VARCHAR2(64)  
PARENT_ME_NAME                   VARCHAR2(256) 
MANAGE_STATUS           NOT NULL NUMBER(2)     
PROMOTE_STATUS                   NUMBER        
DYNAMIC_PROPERTY_STATUS          NUMBER        
TYPE_META_VER           NOT NULL VARCHAR2(8)   
CATEGORY_PROP_1                  VARCHAR2(64)  
CATEGORY_PROP_2                  VARCHAR2(64)  
CATEGORY_PROP_3                  VARCHAR2(64)  
CATEGORY_PROP_4                  VARCHAR2(64)  
CATEGORY_PROP_5                  VARCHAR2(64)  
TIMEZONE_REGION                  VARCHAR2(64)  
DISPLAY_NAME                     VARCHAR2(256) 
OWNER                            VARCHAR2(256) 
HOST_NAME                        VARCHAR2(256) 
EMD_URL                          VARCHAR2(1024)
BROKEN_REASON                    NUMBER        
BROKEN_STR                       VARCHAR2(512) 
MONITORING_MODE                  NUMBER        
IS_PROPAGATING                   NUMBER(1)     
DISCOVERED_NAME                  VARCHAR2(256) 
ORG_ID                           NUMBER        
ORACLE_HOME                      VARCHAR2(256) 
ORACLE_CONFIG_HOME               VARCHAR2(256) 
LOAD_TIMESTAMP          NOT NULL DATE          
ON_REMOTE_HOST                   NUMBER        
 
That is why we need to use below type of subquery as column   where target_guid matches with entity_guid of  GC_MANAGEABLE_ENTITIES     
--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')
  )
 
--ORACLE_HOME
(SELECT PROPERTY_VALUE
  FROM MGMT_TARGET_PROPERTIES
  WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID
  AND (UPPER(PROPERTY_NAME) LIKE '%PORT'))
 
So above query creates output like below
 
add_target -name="xxxxxx" -type="oracle_database" -host="txxxxxxx" -credentials="UserName:dbsnmp;password:<dbsnmp_PASSWORD>;Role:Normal" -properties="SID:xxxx;Port:1521;Orac
leHome:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1;MachineName:txxxxxxxxx;"
 
add_target -name="xxxxxxx" -type="oracle_database" -host="txxxxxxxx"-credentials="UserName:dbsnmp;password:<dbsnmp_PASSWORD>;Role:Normal" -properties="SID:xxxxx;Port:1521;Oracle
Home:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1;MachineName:txxxxxxxx"
 
Copy this output in a text file and change dbsnmp_PASSWORD accordingly
 
Now call the script from emcli
 
emcli login -username=sysman -password=<sysman password>
sync
emcli argfile <textfilename>
 
 If the targets are added correctly a message will show on screen, for instance:
./emcli argfile testtargets.txt
Target "<INSTANCE NAME 1>_<HOST NAME>.<DOMAIN>:oracle_database" added successfully.

Below is a sample script which you can utilize and change according to your environment.

#!/bin/sh

# Usage: ./connect_repo.sh DBServerName SYSMAN password

# Set environment variable for OMS instance

conn=`emctl config oms -list_repos_details | grep "Connect Descriptor"`

#Below command extract string like HOST=hostname) from Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))(CONNECT_DATA=(SID=orcl))))

host=$(echo $conn | awk -F '(' '{ for (i = 1; i <= NF; i++) {

if ($i ~ /^HOST/) {

            print $i

        }

 } }')

port=$(echo $conn | awk -F '(' '{ for (i = 1; i <= NF; i++) {

if ($i ~ /^PORT/) {

            print $i

        }

 } }')

sid=$(echo $conn | awk -F '(' '{ for (i = 1; i <= NF; i++) {

if ($i ~ /^SID/) {

            print $i

        }

 } }')

 hostvar=`echo $host | awk -F ")" '{print $1}' | awk -F "=" '{print $2}'`

 portvar=`echo $port | awk -F ")" '{print $1}' | awk -F "=" '{print $2}'`

 sidvar=`echo $sid | awk -F ")" '{print $1}' | awk -F "=" '{print $2}'`

hostname=$1

password=$2

sqlplus -s sysman/$password@//$hostvar:$portvar/$sidvar.world <<EOF

set heading off

set feedback off

set echo off

set linesize 1000

set pagesize 0

spool /tmp/add_tagets.txt

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_database" -host="' ||

ManagementEntityEO.HOST_NAME || '" -credentials="UserName:dbsnmp;password:<dbsnmp_PASSWORD>;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'))

AND ManagementEntityEO.HOST_NAME ='$hostname';

spool off

EOF

sed -i 's/<dbsnmp_PASSWORD>/pwd/g' /tmp/add_tagets.txt

emcli login -username=sysman -password=$password

sync

emcli argfile /tmp/add_tagets.txt > /tmp/argfile.out

emcli logout

 
I hope you have learnt something useful…
 
 
 
 

How to manage OEM monitoring template using administration group

 

Problem statement:Suppose you are upgrading your OEM version from 13.4 to 13.5 or higher.During this upgrade you are also changing your OS platform from SOLARIS to Linux.Since this is a change of platform so we need to install new OMS instance along with repository database.After that we need to add targets either manually or through auto discovery option of OEM.Now, real problem is everytime we add targets we need to apply corresponding monitoring template.This will be really time consuming tasks if you have thousands of non-host targets.This problem can be solved using administration group

This document will guide you how to make this tasks simple and reduces effort.

 

This has been tested in following environments

 

Source	Target
OMS:-13.4	OMS:- 13.5
Agent: -13.4	Agent :-13.5
Repository database:-19.6	Repository database:-19.26
OS:-Solaris 10	OS:- OEL 8

 

Please follow below steps

1.       First create monitoring templates and define metric threshold according to your environment.

I have created 2 simple monitoring templates,one of database instance and other for host.

If you don’t know how to create template use below reference.

Reference:- Using Monitoring Templates

2.       Follow below navigation in OEM console

Setup>Add Targets>Adminstration Groups

You will end up landing in Getting started tab.Click on Hierarchy tab and follow next step

3.       There are different ways you can create hierarchy levels but I found that creating  hierarchy

based on Target Type is simple.Any other attributes needs to be added in target properties.

               Once you select Target Type you will get below warning saying only 10 targets can be selected

 

You can see 10 different targets type appeared by default.

 

Remove and add targets based on your environment.

Now create the group using Create button on top right.You will get a confirmation message

Now you can see targets are automatically added in corresponding hierarchy groups

4.       Now click on Template Collections tab and click on create button.Basically we will add various templates under this like below

 

  Now save the template collections.

 5.       Now Go to association tab where we will associate group with template collections.Click on each group and then associate like below

6.        

Now you can see templates are associated with administration group

7.       Final step is to synchronize this template collection changes to all groups

Go to Targets>Groups and select Adminstration group.

Group page will open and then click on start syncronization

You will see how many targets are synchronized.

 

You are done with all steps.Everytime you add a target it will be automatically assigned to administration group and corresponding templates will be applied based on synchronization schedule.

Hope you have learnt something useful…