Upgraded database broken in Enterprise Manager: No such metadata

This past week I have been having fun upgrading our AWR Warehouse instance from 12.1 to 12.2.  I thought this would be a good chance to get some exposure to 12.2 on a real-life system without affecting anything critical.  I have run into quite a few issues.

First was that after I upgraded the instance, it had an invalid state withing Enterprise Manager.  Note in particular that Version is detected as “0.170117”.

This was particularly worrying, as if Enterprise Manager “loses” AWRW target, AWR Warehouse has to be reconfigured resulting in loss of all configured targets and history unless hacks are performed (maybe the subject of a future blog post):

Running emctl command to refresh dynamic properties showed similar results (note I have also enabled debug):

[oracle@vmhkoemawrbpd1 log]$ emctl setproperty agent -name "Logger.log.level" -value DEBUG
[oracle@vmhkoemawrbpd1 ~]$ emctl reload agent dynamicproperties AWRW.SIB:oracle_database                                                                                                                                  
Oracle Enterprise Manager Cloud Control 13c Release 2                                                                                                                                                                     
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.                                                                                                                                                        
---------------------------------------------------------------                                                                                                                                                           
EMD reload error:Target oracle_database.AWRW.SIB is broken: Target {oracle_database.AWRW.SIB} is broken: No such metadata - No valid queryDescriptor or executionDescriptor found for target [oracle_database.AWRW.SIB$17]
[oracle@vmhkoemawrbpd1 ~]$

Analysis of the trace files is informative:

 ELSIF IS_OPEN AND DB_VERSION_CHECK_CONVERT_NUM >= 121020 THEN
BEGIN
 EXECUTE IMMEDIATE 'select v.version "DBVersion", p.value "DBDomain" from (select nvl((select version from (select version || ''.''|| bundle_id version
 from dba_registry_sqlpatch where BUNDLE_SERIES= ''PSU'' order by ACTION_TIME desc) where rownum = 1),
 (select version from v$instance)) version from dual) v, v$parameter p
 where p.name=''db_domain'''INTO DB_VERSION,DB_DOMAIN;


2017-03-17 03:03:39,635 [87831:F9D70D8D] DEBUG - Adding a new dynamic property for oracle_database.AWRW.SIB$23: Dynamic property [DBVersion] {0.170117}(D)

Note that we can see the query used to calculate the database version, and the results of that query 0.170117.  I ran the same query from SQLPlus, and version calculated was the same: 0.170117.

The contents of that table externalized as dba_registry_sqlpatch  contained records from previous version 12.1.0, however column “version” had been updated to to “0”, I presume as part of the upgrade. This was causing the problem.  As I believe this information is no longer needed I made a backup of the underlying table registry$sqlpatch and trucated it.

Reloading dynamic properties is now successful:

[oracle@vmhkoemawrbpd1 log]$ emctl reload agent dynamicproperties AWRW.SIB:oracle_database
Oracle Enterprise Manager Cloud Control 13c Release 2  
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD recompute dynprops completed successfully
[oracle@vmhkoemawrbpd1 log]$

And target is now in a good state in Enterprise Manager:

Job done.

Update: working through with support I applied patch 25501452 (em db plugin bundle patch 13.2.1.0.170228) to monitoring agent, version is now correctly determined to be 12.1.0.2.0.

Trace now reveals the updated query doesn’t even attempt to read PSU or DBBP version information for 12.2 database.

 EXECUTE IMMEDIATE 'select i.version "DBVersion", p.value "DBDomain" from v$instance i, v$parameter p  where p.name=''db_domain''' INTO DB_VERSION,DB_DOMAIN;

Apparently this is waiting on unpublished bug “25174937 – NO DATA FOR EM TO QUERY DB VERSION FROM dba_registry_sqlpatch”. Hopefully that will be resolved as part of first patchset for 12.2.0.1.