Oracle upgrade failures due to METHOD_OPT and XDBCONFIG

Written by Simon Pane | Nov 11, 2015 5:00:00 AM

Background

I recently experienced a problem when upgrading an old Oracle 10.2.0.4 database to 11.2.0.4 that had no matches in a My Oracle Support (MOS) or Google search. The problem presented itself initially when upgrading as the following error was reported by the upgrade script:

ERROR at line 1:
 ORA-20001: invalid column name or duplicate columns/column groups/expressions
 in method_opt
 ORA-06512: at "SYS.UTL_RECOMP", line 865
 ORA-06512: at line 4

Initially, the problem was reported in the upgrade log file for the ORACLE_OCM schema which is not critical. However, it later caused the XDB component to become invalid and consequently other components that depend on XDB to also become invalid. The error reported when trying to validate XDB was:

Warning: XDB now invalid, could not find xdbconfig

Even if not upgrading, this error could be encountered when trying to install or re-install the XDB component in an 11g database. XDB is a mandatory component as of Oracle 12c but is optional with 11g and below. Hence, it's possible to experience this same problem if you're trying to add the XDB component to an 11g database that didn't already have it.

"Warning: XDB now invalid, could not find xdbconfig"

Several MOS documents already exist describing the error "Warning: XDB now invalid, could not find xdbconfig". Those include:

Utlrp.sql results to "Warning: XDB Now Invalid, Could Not Find Xdbconfig" (Doc ID 1631290.1)
XDB Invalid after Utlrp during Activation of Extended Datatypes (Doc ID 1667689.1)
XDB Invalid After utl32k.sql during activation of extended datatypes (Doc ID 1667684.1)

Unfortunately, none of those applied as either the cause or the solution to the problem I encountered. Either going through the XDB installation logs or simply manually running utlrp.sql shows that the xdbconfig is missing due to the "ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt" error. For example:

SQL> @?/rdbms/admin/utlrp
 
 TIMESTAMP
 --------------------------------------------------------------------------------
 COMP_TIMESTAMP UTLRP_BGN 2015-11-09 11:36:22
 
 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
 DOC> objects in the database. Recompilation time is proportional to the
 DOC> number of invalid objects in the database, so this command may take
 DOC> a long time to execute on a database with a large number of invalid
 DOC> objects.
 DOC>
 DOC> Use the following queries to track recompilation progress:
 DOC>
 DOC> 1. Query returning the number of invalid objects remaining. This
 DOC> number should decrease with time.
 DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
 DOC>
 DOC> 2. Query returning the number of objects compiled so far. This number
 DOC> should increase with time.
 DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
 DOC>
 DOC> This script automatically chooses serial or parallel recompilation
 DOC> based on the number of CPUs available (parameter cpu_count) multiplied
 DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
 DOC> On RAC, this number is added across all RAC nodes.
 DOC>
 DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
 DOC> recompilation. Jobs are created without instance affinity so that they
 DOC> can migrate across RAC nodes. Use the following queries to verify
 DOC> whether UTL_RECOMP jobs are being created and run correctly:
 DOC>
 DOC> 1. Query showing jobs created by UTL_RECOMP
 DOC> SELECT job_name FROM dba_scheduler_jobs
 DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
 DOC>
 DOC> 2. Query showing UTL_RECOMP jobs that are running
 DOC> SELECT job_name FROM dba_scheduler_running_jobs
 DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
 DOC>#
 DECLARE
 *
 ERROR at line 1:
 ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt
 ORA-06512: at "SYS.UTL_RECOMP", line 865
 ORA-06512: at line 4
 
 
 
 TIMESTAMP
 --------------------------------------------------------------------------------
 COMP_TIMESTAMP UTLRP_END 2015-11-09 11:36:23
 
 DOC> The following query reports the number of objects that have compiled
 DOC> with errors.
 DOC>
 DOC> If the number is higher than expected, please examine the error
 DOC> messages reported with each object (using SHOW ERRORS) to see if they
 DOC> point to system misconfiguration or resource constraints that must be
 DOC> fixed before attempting to recompile these objects.
 DOC>#
 
 OBJECTS WITH ERRORS
 -------------------
  0
 
 DOC> The following query reports the number of errors caught during
 DOC> recompilation. If this number is non-zero, please query the error
 DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
 DOC> are due to misconfiguration or resource constraints that must be
 DOC> fixed before objects can compile successfully.
 DOC>#
 
 ERRORS DURING RECOMPILATION
 ---------------------------
  0
 
 
 Function created.
 
 
 PL/SQL procedure successfully completed.
 
 
 Function dropped.
 
 Warning: XDB now invalid, could not find xdbconfig
 ORDIM INVALID OBJECTS: CARTRIDGE - INVALID - PACKAGE BODY
 ORDIM INVALID OBJECTS: SI_IMAGE_FORMAT_FEATURES - INVALID - VIEW
 ORDIM INVALID OBJECTS: SI_IMAGE_FORMAT_FEATURES - INVALID - SYNONYM
 ORDIM INVALID OBJECTS: SI_IMAGE_FRMT_FTRS - INVALID - SYNONYM
 ORDIM INVALID OBJECTS: ORDUTIL - INVALID - PACKAGE BODY
 ORDIM INVALID OBJECTS: ORDIMG_PKG - INVALID - PACKAGE BODY
 ORDIM INVALID OBJECTS: ORDIMGEXTCODEC_PKG - INVALID - PACKAGE BODY
 ORDIM INVALID OBJECTS: ORDX_FILE_SOURCE - INVALID - PACKAGE BODY
 ORDIM INVALID OBJECTS: DICOM_IMAGE105_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: exifMetadata243_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: PATIENT_STUDY129_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: GENERAL_SERIES134_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: GENERAL_IMAGE154_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: TiffIfd244_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: ExifIfd245_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: GpsIfd246_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: CODE_SQ103_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: iptcMetadataType94_T - INVALID - TYPE
 ORDIM INVALID OBJECTS: IMAGE_PIXEL163_T - INVALID - TYPE
 ORDIM registered 0 XML schemas.
 The following XML schemas are not registered:
 https://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0
 https://xmlns.oracle.com/ord/dicom/anonymity_1_0
 https://xmlns.oracle.com/ord/dicom/attributeTag_1_0
 https://xmlns.oracle.com/ord/dicom/constraint_1_0
 https://xmlns.oracle.com/ord/dicom/datatype_1_0
 https://xmlns.oracle.com/ord/dicom/manifest_1_0
 https://xmlns.oracle.com/ord/dicom/mapping_1_0
 https://xmlns.oracle.com/ord/dicom/mddatatype_1_0
 https://xmlns.oracle.com/ord/dicom/metadata_1_0
 https://xmlns.oracle.com/ord/dicom/orddicom_1_0
 https://xmlns.oracle.com/ord/dicom/preference_1_0
 https://xmlns.oracle.com/ord/dicom/privateDictionary_1_0
 https://xmlns.oracle.com/ord/dicom/rpdatatype_1_0
 https://xmlns.oracle.com/ord/dicom/standardDictionary_1_0
 https://xmlns.oracle.com/ord/meta/dicomImage
 https://xmlns.oracle.com/ord/meta/exif
 https://xmlns.oracle.com/ord/meta/iptc
 https://xmlns.oracle.com/ord/meta/ordimage
 https://xmlns.oracle.com/ord/meta/xmp
 Locator INVALID OBJECTS: ALL_SDO_GEOM_METADATA - INVALID - VIEW
 Locator INVALID OBJECTS: USER_SDO_INDEX_METADATA - INVALID - VIEW
 Locator INVALID OBJECTS: ALL_SDO_INDEX_METADATA - INVALID - VIEW
 Locator INVALID OBJECTS: USER_SDO_INDEX_INFO - INVALID - VIEW
 Locator INVALID OBJECTS: ALL_SDO_INDEX_INFO - INVALID - VIEW
 Locator INVALID OBJECTS: USER_SDO_LRS_METADATA - INVALID - VIEW
 Locator INVALID OBJECTS: SDO_LRS_TRIG_INS - INVALID - TRIGGER
 Locator INVALID OBJECTS: SDO_LRS_TRIG_DEL - INVALID - TRIGGER
 Locator INVALID OBJECTS: SDO_LRS_TRIG_UPD - INVALID - TRIGGER
 Locator INVALID OBJECTS: USER_SDO_TOPO_INFO - INVALID - VIEW
 Locator INVALID OBJECTS: ALL_SDO_TOPO_INFO - INVALID - VIEW
 Locator INVALID OBJECTS: USER_SDO_TOPO_METADATA - INVALID - VIEW
 Locator INVALID OBJECTS: ALL_SDO_TOPO_METADATA - INVALID - VIEW
 Locator INVALID OBJECTS: MDPRVT_IDX - INVALID - PACKAGE BODY
 Locator INVALID OBJECTS: PRVT_IDX - INVALID - PACKAGE BODY
 Locator INVALID OBJECTS: SDO_TPIDX - INVALID - PACKAGE BODY
 Locator INVALID OBJECTS: SDO_INDEX_METHOD_10I - INVALID - TYPE BODY
 Locator INVALID OBJECTS: SDO_GEOM - INVALID - PACKAGE BODY
 Locator INVALID OBJECTS: SDO_3GL - INVALID - PACKAGE BODY
 
 PL/SQL procedure successfully completed.
 
 SQL>

Hence the ORA-20001 error is the true cause of the XDB problem.

"ORA-20001: Invalid column name or duplicate columns/column groups/expressions in method_opt"

Searching My Oracle Support (MOS) for this error leads to the following notes:

Gather Table Statistics Fails With ORA-20001 ORA-06512 On "invalid Column Name" (Doc ID 1668579.1).
11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1).
Gather Schema Statistics Fails With Error For APPLSYS Schema (Doc ID 1393184.1).
Performance Issue Noted in Trading Partner Field of Invoice Workbench (Doc ID 1343489.1).

Unfortunately, those are all related to specific tables from Oracle Applications Technology Stack, Oracle EBS, or Oracle Payables - none of those were applicable in my case. In my case the application was home grown. Hence, MOS and Google searches returned no relevant results.

The Root Cause & Solution

The root cause of this problem was the METHOD_OPT parameter of DBMS_STATS. The METHOD_OPT parameter is related to how optimizer statistic histograms are collected for columns. METHOD_OPT is set using DBMS_STATS.SET_PARAM and can be queried through DBMS_STATS.GET_PARAM or directly from the underlying base table SYS.OPTSTAT_HIST_CONTROL$. For example:

SQL> exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');
 
 PL/SQL procedure successfully completed.
 
 SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
 
 DBMS_STATS.GET_PARAM('METHOD_OPT')
 --------------------------------------------------------------------------------
 FOR ALL COLUMNS SIZE AUTO
 
 SQL> select sname, spare4 from SYS.OPTSTAT_HIST_CONTROL$ where sname = 'METHOD_OPT';
 
 SNAME SPARE4
 ------------------------------ ----------------------------------------
 METHOD_OPT FOR ALL COLUMNS SIZE AUTO
 
 SQL>

The actual root cause of the ORA-20001 error and all of the subsequent failures and invalid components is that in the problematic database, the METHOD_OPT was set to the rarely used and outdated setting of "FOR COLUMNS ID SIZE 1". From the database that experienced this issue:

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
 
 DBMS_STATS.GET_PARAM('METHOD_OPT')
 --------------------------------------------------------------------------------
 FOR COLUMNS ID SIZE 1
 
 SQL>

The "FOR COLUMNS ID SIZE 1" setting was sometimes used in older versions of Oracle to prevent histogram buckets for being collected for primary keys and for plan stability through statistic changes. However, it should not be used for modern 11g or 12c databases. In fact it's not even settable through the DBMS_STATS package after Oracle 10g. Executing against an 11.2.0.4 database will give:

SQL> exec dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1');
 BEGIN dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1'); END;
 
 *
 ERROR at line 1:
 ORA-20001: method_opt should follow the syntax "[FOR ALL [INDEXED|HIDDEN]
 COLUMNS [size_caluse]]" when gathering statistics on a group of tables
 ORA-06512: at "SYS.DBMS_STATS", line 13179
 ORA-06512: at "SYS.DBMS_STATS", line 13268
 ORA-06512: at "SYS.DBMS_STATS", line 13643
 ORA-06512: at "SYS.DBMS_STATS", line 31462
 ORA-06512: at line 1

Though it can still be set in 11.2.0.4 by directly updating SYS.OPTSTAT_HIST_CONTROL$, which is definitely NOT recommended. And of course this setting can be present in an 11g database that was upgraded from an older version such as a 10g release. Reverting this parameter to "FOR ALL COLUMNS SIZE AUTO" resolved the ORA-20001 error with UTL_RECOMP allowing the XDB component to validate and become VALID in the registry and subsequently all other components that depend on XDB.

Conclusion

If upgrading an older databases to 11.2.0.4 (to remain on a supported version) it is prudent to check the setting of the METHOD_OPT parameter of the DBMS_STATS package. This isn't mentioned in any of the pre-upgrade documents or checklists and isn't caught by even the most recent version of Oracle's Database Pre-Upgrade Utility (MOS Doc ID 884522.1) or the DB Upgrade/Migrate Diagnostic Information (MOS Doc ID 556610.1). The check and solution are simple and should be incorporated into your own pre-upgrade procedure:

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
 
 DBMS_STATS.GET_PARAM('METHOD_OPT')
 --------------------------------------------------------------------------------
 FOR COLUMNS ID SIZE 1
 
 SQL> exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');
 
 PL/SQL procedure successfully completed.
 
 SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
 
 DBMS_STATS.GET_PARAM('METHOD_OPT')
 --------------------------------------------------------------------------------
 FOR ALL COLUMNS SIZE AUTO
 
 SQL>

Discover more about our expertise in the world of Oracle.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

View full post