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.
No Comments Yet
Let us know what you think