Running OEM SQL Queries using EMCLI

emcli list -noheader -script -sql=”select t.target_name || ‘:oracle_database:Department:Vintage’ from MGMT\$TARGET t, MGMT\$TARGET os where t.HOST_NAME = os.TARGET_NAME and
t.target_type=’oracle_database’ AND t.host_name like ‘db%’” >/export/home/oracle/sandeep/dp_vandb01.log
emcli list -noheader -script -sql=”select t.target_name || ‘:oracle_database:\”Line of Business\”:Pre-Production’ from MGMT\$TARGET t, MGMT\$TARGET os where t.HOST_NAME = os.TARGET_NAME and
t.target_type=’oracle_database’ AND t.host_name like ‘db%’”>/export/home/oracle/sandeep/lob_PREOSCAT.log

emcli list -noheader -script -sql=”select t.target_name || ‘:oracle_listener:Department:Vintage’ from MGMT\$TARGET t, MGMT\$TARGET os where t.HOST_NAME = os.TARGET_NAME and
t.target_type=’oracle_listener’ AND t.host_name like ‘db%’” >/export/home/oracle/sandeep/dp_listvandb01.log
emcli list -noheader -script -sql=”select t.target_name || ‘:oracle_listener:\”Line of Business\”:Pre-Production’ from MGMT\$TARGET t, MGMT\$TARGET os where t.HOST_NAME = os.TARGET_NAME and
t.target_type=’oracle_listener’ AND t.host_name like ‘db%’”>/export/home/oracle/sandeep/lob_PREOSCAT.log

Building the Property file
emcli list -noheader -script -sql=”select t.target_name || ‘:oracle_database:Department:VOICE’ from MGMT\$TARGET t,
MGMT\$TARGET os where t.HOST_NAME = os.TARGET_NAME and t.target_type=’oracle_database’
AND t.host_name like ‘\%’” >/export/home/oracle/db_promote/department_PREVOICE.log
emcli list -noheader -script -sql=”select ’emcli set_target_property_value -property_records=’|| t.target_name || ‘:oracle_database:\”LifeCycle Status\”:Stage’
from MGMT\$TARGET t, MGMT\$TARGET os where t.HOST_NAME = os.TARGET_NAME and t.target_type=’oracle_database’
AND t.host_name like ‘%’”>/export/home/oracle/db_promote/LOC_PREVOICE.log

Recording the file
emcli set_target_property_value -property_records=”REC_FILE” -input_file=”REC_FILE:/export/home/oracle/db_promote/department_PREVOICE.log” -separator=property_records=”\n”
Run the LOC_PREVOICE.log

emcli list -noheader -script -sql=”select ’emcli set_target_property_value -property_records=’|| target_name||’:host:Department:change’ from sysman.MGMT\$TARGET where target_type=’host’”>/export/home/oracle/db_promote/department_Hosts.log
emcli list -noheader -script -sql=”select ’emcli set_target_property_value -property_records=’|| target_name||’:host:”LifeCycle\ Status”:change’ from sysman.MGMT\$TARGET where target_type=’host’”>/export/home/oracle/db_promote/LOC_Hosts.log

emcli list -noheader -script -sql=”select ’emcli set_target_property_value -property_records=’|| target_name||’:oracle_emd:Department:change’ from sysman.MGMT\$TARGET where target_type=’oracle_emd’”>/export/home/oracle/db_promote/department_AGT.log
emcli list -noheader -script -sql=”select ’emcli set_target_property_value -property_records=’|| target_name||’:oracle_emd:”LifeCycle\ Status”:change’ from sysman.MGMT\$TARGET where target_type=’oracle_emd’”>/export/home/oracle/db_promote/LOC_AGT.log

Deleting OEM Targets
emcli delete_target -name=CCCC -type=”oracle_database” -delete_monitored_targets
emcli delete_target -name=CCCC -type=”oracle_database” -delete_monitored_targets

Set Below target Properties

plpsp21:host:LifeCycle Status: Production Department:Tools -propagate_to_members

emcli set_target_property_value -property_records=plpsp21:host:Department:Tools -propagate_to_members
emcli set_target_property_value -property_records=plpsp21:host:”Line of Business”:Production -propagate_to_members

Modifing the groups

emcli modify_group -name=”Pre–Ash-Grp” -type=group -add_targets=”DBADB:oracle_database”

List Target from OEM

emcli get_targets -noheader -script -target=”oracle_database” | awk ‘{print $4″:”$3″:Line of Business:”}’

emcli get_targets -noheader -script -targets=host | awk ‘{print $4″:”$3″:Department:”}’