Following on from the last post i foolishly thought that by setting the "line of business" property at the host level would mean that everything running on that host would somehow be able to inherit this property in some way or i could make reference to it in dynamic groups somehow. It seems this is not the case though so i needed to find some other way to set the property on all of my databases running on my already tagged hosts.
Now i can easily do this based on an earlier post i made about setting the property using emcli, but this wont be dynamic - it will have to be manual every time a new database is added - this is bound to get forgotten - there must be a way to dynamically update the properties of anything on a host to have the same setting as the host...?
Well there is - but it required bringing to gether a few things i'd learnt over the past few days
1) dynamic groups
2) querying the sysman repository
3) emcli without a password
4) saved credentials
So here is what i did
I created a job in the job library which would execute a simple shell script, this job would run on a regular interval, query the list of targets which had the "line of business" property set and build a list of the 'children' to be spooled to a file - this spooled file would then be executed by emcli (without a password).
Here is a screenshot from that job
And here is a cut and paste which I'm sure is more useful to anyone who wants to reuse this
#!/bin/bash
export ORACLE_SID=DB
export ORAENV_ASK=NO
. oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
env
sqlplus -s / << EOFSQL
set head off feed off pages 0 lines 1024 trimspool on echo off show off termout off sqlprompt ''
spool /tmp/run.sql
select target_name||':'||target_type||':'||'Line of Business'||':'||'EGC;' from sysman.gc\$target where target_type='oracle_database'
and host_name in (
select tgt.target_name
from sysman.GC\$TARGET_PROPERTIES tp,sysman.gc\$target tgt
where property_name='orcl_gtp_line_of_bus'
and tp.target_guid=tgt.target_guid
and tgt.target_type='host'
and tp.property_value='EGC'
);
spool off
exit;
EOFSQL
export ORACLE_SID=OMS12c
export ORAENV_ASK=NO
. oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
env
emcli set_target_property_value -property_records="REC_FILE" -input_file="REC_FILE:/tmp/run.sql"
Few things to note:
1) \ has to be used to escape the $ in the view names
2) a number of sqlplus options are used to make sure only the data ends up in the spool file and no headers etc
3) emcli i setup earlier with autlogin option
4) an oracle user (ops$oracle) was created so no username/password was required to be hardcoded
5) LD_LIBRARY_PATH had to be explicitly set
This job was then executed and ran perfectly - updating all the databases which are hosted on servers which have the line of business property set to 'EGC'
This combined with the dynamic group i mentioned in the last post means this group genuinely is completely dynamic and all we have to manually do is set the line of business property purely at the host level when we add it in to cloud control.
Comments
Post a Comment