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…