Friday, June 25, 2021

EM 12c/13c Update Oracle Home Path of Targets with SQL

 As out-of-place patching can dramatically help minimize database downtime, especially when multiple databases run out of same home, it does not require all databases shut down at same time, more and more DBA adopt this method. However, if you are patching a home which host multiple databases, you may feel frustrated to update the databases' Oracle Home property in Oracle Enterprise Manage by clicking through different windows and changing it one by one. Now, the first question you will ask is how I can update all databases at one time. The answer is to run SQL (PL/SQL) in EM repository database.

Note: before you update Oracle Home, it is strongly recommended to add the new Oracle Home to EM.

I has been using following PL/SQL code for many years to update Oracle Home path , and it works for EM 12c & 13c.
declare
  /* Cursor lists all targets which need to change home*/
  cursor cc is 
    select t.target_name,t.target_type,o.property_value oracle_home,h.property_value host_name
    from (select target_guid,property_value from mgmt_target_properties where property_name='OracleHome') o,
         (select target_guid,property_value from mgmt_target_properties where property_name='MachineName') h,
         mgmt_targets t
    where h.property_value='<HOST_NAME>'        -- Name of the host on which the Oracle is patched
      and o.property_value='<OLD_ORACLE_HOME>'  -- Old Oracle Home path
    --and t.target_type='<TARGET_TYPE>'         -- Un-Comment this line if updating specific type of targets
      and o.target_guid=t.target_guid and h.target_guid=t.target_guid;
begin
  for c1 in cc loop
      mgmt_target.set_target_property(c1.target_name,c1.target_type,'OracleHome','INSTANCE','<NEW_ORACLE_HOME>');
  end loop;
end;
/
commit;

Based on your environment, assign proper value for <HOST_HAME>, <OLD_ORACLE_HOME>, <TARGET_TYPE> and <NEW_ORACLE_HOME>. It may not be a good idea to use 'target_type' predicate in the cursor if you switched all targets from OLD_ORACLE_HOME to NEW_ORACLE_HOME, it will take you much more manual effort. For example, there are file CDB patched to new patch level in same home, and each database has three application pdbs, there will be 

   5 database instances (oracle_database) + 5 root containers (oracle_pdb) + 5 * 3 pdbs (oracle_pdb) = totally 25 targets

needed to update Oracle Home. You have to write two PL/SQL blocks for two different target types (roacle_database & oracle_pdb) to update all targets if 'target_type' predicate is used.

For RAC installation, you may have to list all cluster nodes with vip host namd in both FQDN and short format. For example, I have patched my Oracle RAC database home from 19.9 to 19.11 on both nodes of the cluster, the cluster information as following

   Node 1 virtual name:  rac01-vip.dbaplus.ca
   Node 2 virtual name:  rac02-vip.dbaplus.ca
   Old database home:    /u01/app/oracle/product/19.9.0/dbhome_1
   New database home:    /u01/app/oracle/product/19.11.0/dbhome_1

The PL/SQL will be
declare
  cursor cc is 
    select t.target_name,t.target_type,o.property_value oracle_home,h.property_value host_name
    from (select target_guid,property_value from mgmt_target_properties where property_name='OracleHome') o,
         (select target_guid,property_value from mgmt_target_properties where property_name='MachineName') h,
         mgmt_targets t
    where h.property_value in ('rac01-vip','rac02-vip','rac01-vip.dbaplus.ca','rac02-vip.dbaplus.ca')
      and o.property_value='/u01/app/oracle/product/19.9.0/dbhome_1'
      and o.target_guid=t.target_guid and h.target_guid=t.target_guid;
begin
  for c1 in cc loop
      mgmt_target.set_target_property(c1.target_name,c1.target_type,'OracleHome','INSTANCE','/u01/app/oracle/product/19.11.0/dbhome_1');
  end loop;
end;
/
commit;

No comments: