Thank you very much for your interest.I hope you will learn something valuable after reading this article
Problem statement:Lets assume you have migrated your OMS to new server with an upgraded version.Now you need to reinstall OEM agent to all database server and run auto discovery to add targets.If you have 1000 of database targets then discovery and adding targets itself is a very time consuming task.After that you have to apply monitoring template for the database targets once it is added.
This
adds up some more manual task and chance of missing the applying template.In
this article I will explain how this can be handled using some automation.
Test environment:-
Ø Database targets version: Oracle database 19c
Ø OEM version:13.5
Ø OS:Linux 8
High
level approach: We will schedule a
script which will query mgmt_targets view under sysman schema of repository
database.
If it
finds a new target which has been added recently but no monitoring templates
have been applied it will apply that corresponding template and send a
confirmation email after successful completion. This will make sure we don’t
need to invest time and effort for this which will be beneficial for any new
targets in future also.
Solution:Lets
get into solution part now
Step 1.
How will you find OEM repository details programmatically.Below is piece of sample
code written in bash shell script which will help you to find host,port number
and sid
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}'`
Step 2. Using
above information login in the repository and use below query to find targets
whether any monitoring templates have been applied or not
SELECT target_name,target_type from mgmt_targets trgt
where trgt.target_type in ('oracle_database','host','osm_cluster','oak')
and target_name not in
(
SELECT distinct mt.TARGET_NAME
FROM mgmt_update_operations_details muodt,
mgmt_update_operations_data muoda, mgmt_update_template_data_map mutdm,
mgmt_template_copies mtc, mgmt_targets mt, mgmt_templates
mts
WHERE muodt.operation_guid = muoda.operation_guid
AND muoda.data_set_guid = mutdm.data_set_guid
AND mt.target_type in
('oracle_database','host','osm_cluster','oak')
AND mutdm.template_copy_guid = mtc.template_copy_guid
AND mt.target_guid = muodt.destination_target_guid
AND mtc.template_guid = mts.template_guid
);
Ref:- How To Check Whether a Target Has the Default
Template, a Custom Template Or a User Defined Monitoring Template Applied?KB643222
Step 3. Refine above query to create emcli command to apply
monitoring template.Here I have target type specific template so I have joined mgmt_targets
and mgmt_templates with the joining column target_type and excluded public
template with flag=0
Command to apply monitoring template
emcli apply_template -name="my_db_template" -targets="db1:oracle_database "
SELECT 'apply_template
-name="'||tmpls.template_name||'" -targets="'|| trgt.target_name
||':'|| trgt.target_type ||'"' from mgmt_targets trgt,mgmt_templates tmpls
where trgt.target_type=tmpls.target_type
and tmpls.IS_PUBLIC=0
and trgt.target_type in
('oracle_database','host','osm_cluster','oak')
and trgt.target_name not in
(
SELECT distinct mt.TARGET_NAME
FROM mgmt_update_operations_details muodt,
mgmt_update_operations_data muoda, mgmt_update_template_data_map mutdm,
mgmt_template_copies mtc, mgmt_targets mt, mgmt_templates
mts
WHERE muodt.operation_guid = muoda.operation_guid
AND muoda.data_set_guid = mutdm.data_set_guid
AND mt.target_type in
('oracle_database','host','osm_cluster','oak')
AND mutdm.template_copy_guid = mtc.template_copy_guid
AND mt.target_guid = muodt.destination_target_guid
AND mtc.template_guid = mts.template_guid
);
Step 4. Spool
this output in sqlplus
set
heading off
set
feedback off
set echo
off
set
linesize 1000
set
pagesize 0
spool
/tmp/apply_monitorting_template.txt
above
query from step 3
spool of
Step 5. Execute
like below in OMS server or any server where you have installed emcli
emcli login -username=sysman -password=$password
emcli sync
emcli argfile /tmp/apply_monitorting_template.txt >
/tmp/argfile.out
emcli logout
Step 5.
Check the output and be ready to create your own script and schedule in crontab
or any other scheduler
Hope you
have enjoyed this learning
