The Lifecycle Status property of EM targets is often used to prioritize the notification of incident rule. For example, DBA will be paged while database which Lifecycle Status is 'Production' crashed, and only emailed for 'Test' database. This post is going to show how to use sql scripts to find out targets with different Lifecycle Status and how to change the Lifecycle Status.
Before you can run these scripts, you have to connect to EM repository database as sysman.
List all databases which Lifecycle Status are not set yet with following sql,
select t.target_name, t.target_type, t.host_name
from mgmt_targets t, mgmt_target_properties p
where t.target_guid=p.target_guid(+)
and t.target_type='oracle_database' -- comment out this line if going to widen the query
and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;
If you want to widen the query result, you can remove 'target_type' predicate from where clause, or you assign different value for the predicate to find out other type of targets (e.g. listener, asm instance, etc.), the populate values of 'target_type' include
oracle_database Single instance database or RAC instance
oracle_pdb Pluggable database
rac_database RAC database
osm_instance ASM instance
oracle_listener Oracle database listener
Sample query
sysman@OEMR> select t.target_name, t.target_type, t.host_name
2 from mgmt_targets t, mgmt_target_properties p
3 where t.target_guid=p.target_guid(+)
4 and t.target_type in ('oracle_database','oracle_pdb','rac_database','osm_instance','oracle_listener')
5 and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;
ENTITY_NAME ENTITY_TYPE HOST_NAME
------------------------------ -------------------------------------------------- ------------------------------
+ASM_host01.dbaplus.ca osm_instance host01.dbaplus.ca
ORCL oracle_database host01.dbaplus.ca
DB01 oracle_database host01.dbaplus.ca
DB01_CDBROOT oracle_pdb host01.dbaplus.ca
DB01_PDB1 oracle_pdb host01.dbaplus.ca
DB01_PDB2 oracle_pdb host01.dbaplus.ca
DB02 rac_database rac01.dbaplus.ca
DB02_CDBROOT oracle_pdb rac01.dbaplus.ca
DB02_DB02_1 oracle_database rac02.dbaplus.ca
DB02_DB02_2 oracle_database rac01.dbaplus.ca
DB02_PDB1 oracle_pdb rac01.dbaplus.ca
LISTENER_host01.dbaplus.ca oracle_listener host01.dbaplus.ca
The Lifecycle Status can be changed with following sql (pl/sql),
exec mgmt_target.set_target_property('<target_name>','<target_type>','orcl_gtp_lifecycle_status','INSTANCE','<Lifecycle Status>');
Valid 'Lifecycle Status' can be one of following values,
Development, Test, Stage, Production
Following command changes Lifcycle Status of database DB01 to 'Test',
# Before changing
sysman@OEMR> select t.target_name, t.target_type, t.host_name, p.property_value lifecycle_status
2 from mgmt_targets t, mgmt_target_properties p
3 where t.target_guid=p.target_guid(+)
4 and t.target_name='DB01'
5 and t.target_type='oracle_database'
6 and p.property_name(+)='orcl_gtp_lifecycle_status';
TARGET_NAME TARGET_TYPE HOST_NAME LIFECYCLE_STATUS
------------- ------------------ ------------------ ----------------
DB01 oracle_database host01.dbaplus.ca
# Change
sysman@OEMR> exec mgmt_target.set_target_property('DB01','oracle_database','orcl_gtp_lifecycle_status','INSTANCE','Test');
PL/SQL procedure successfully completed.
sysman@OEMR> commit;
Commit complete.
# After changing
sysman@OEMR> select t.target_name, t.target_type, t.host_name, p.property_value lifecycle_status
2 from mgmt_targets t, mgmt_target_properties p
3 where t.target_guid=p.target_guid(+)
4 and t.target_name='CHPTEST1'
5 and t.target_type='oracle_database'
6 and p.property_name(+)='orcl_gtp_lifecycle_status';
TARGET_NAME TARGET_TYPE HOST_NAME LIFECYCLE_STATUS
------------- ------------------ -------------------- ----------------
DB01 oracle_database host01.dbaplus.ca Test
Change multiple targets with sql statements like following,
declare
cursor c1 is
select t.target_name, t.target_type, t.host_name
from mgmt_targets t, mgmt_target_properties p
where t.target_guid=p.target_guid(+)
and t.target_type in ('oracle_database','oracle_pdb','rac_database','osm_instance','oracle_listener')
and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;
begin
for cc in c1 loop
mgmt_target.set_target_property(cc.target_name,cc.target_type,'orcl_gtp_lifecycle_status','INSTANCE','Test')
end loop;
end;
For example, change all new databases (Lifecycle Status not set) to 'Test',
sysman@OEMR> declare
2 cursor c1 is
3 select t.target_name, t.target_type, t.host_name
4 from mgmt_targets t, mgmt_target_properties p
5 where t.target_guid=p.target_guid(+)
6 and t.target_type='oracle_database'
7 and p.property_name(+)='orcl_gtp_lifecycle_status' and p.property_value is null;
8 begin
9 for cc in c1 loop
10 mgmt_target.SET_TARGET_PROPERTY(cc.target_name,cc.target_type,'orcl_gtp_lifecycle_status','INSTANCE','Test');
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
sysman@OEMR> commit;
Commit complete.
[/div]
No comments:
Post a Comment