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: 

1. Before you update Oracle Home, it is strongly recommended to add the new Oracle Home to EM.

2. It is recommended to be used for Oracle database targets which target type is 'rac_database', 'oracle_database' and 'oracle_pdb'.

3. It is not recommended for the cluster (as well as associated has members) targets because it cannot enforce and guarantee the consistency between cluster and its has members. Of course, you can use it and enforce the consistency manually and make sure all related Oracle Home properties are updated.

4. It is not applicable for the listener targets because it is not enough to change Oracle Home property in EM repository, it also needs to push change to host agent which cannot be implemented by the SQL described in this article.

Connect to EM repository as user sysman and use following SQL to update Oracle Home properties for database targets including RAC database, database instance and pdb,

declare
  cursor cc is 
    select t.target_name,t.target_type, p1.host_name, p2.oracle_home
      from mgmt_targets t,
          (select target_guid,property_value as host_name
             from mgmt_target_properties where property_name=('MachineName')) p1,
          (select target_guid,property_value as oracle_home
             from mgmt_target_properties where property_name=('OracleHome')) p2
     where t.target_type in ('rac_database','oracle_database','oracle_pdb')
           and t.target_guid=p1.target_guid
           and t.target_guid=p2.target_guid
           and p1.host_name='<HOST_NAME>'  -- Host on which the Oracle is patched
           and p2.oracle_home='<OLD_ORACLE_HOME>'; -- Old (non-patched) Oracle Home
begin
  for c1 in cc loop
      mgmt_target.set_target_property(c1.target_name,
                                      c1.target_type,
                                      'OracleHome',
                                      'INSTANCE',
                                      '<NEW_ORACLE_HOME>'); -- Patched Oracle Home
  end loop;
end;
/
commit;

Based on your environment, assign proper value for <HOST_HAME>, <OLD_ORACLE_HOME> and <NEW_ORACLE_HOME>. 

For RAC installation, you may have to list all cluster nodes with vip host name 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, p1.host_name, p2.oracle_home
      from mgmt_targets t,
          (select target_guid,property_value as host_name
             from mgmt_target_properties where property_name=('MachineName')) p1,
          (select target_guid,property_value as oracle_home
             from mgmt_target_properties where property_name=('OracleHome')) p2
     where t.target_type in ('rac_database','oracle_database','oracle_pdb')
           and t.target_guid=p1.target_guid
           and t.target_guid=p2.target_guid
           and p1.host_name in ('rac01-vip','rac02-vip','rac01-vip.dbaplus.ca','rac02-vip.dbaplus.ca')
           and p2.oracle_home='/u01/app/oracle/product/19.9.0/dbhome_1';
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;