Pages

Wednesday, June 23, 2021

EM 12c/13c Change Lifecycle Status with SQL

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: