Patch Fails on Fem_bal_nacc_hier_l2_mv.xdf After Upgrade to Oracle 11.2.0.2 Database
(Doc ID 1284055.1)
Last updated on MARCH 10, 2023
Applies to:
Oracle Enterprise Performance Foundation - Version 12.1.1 and later Information in this document applies to any platform.
Symptoms
While applying an Oracle Applications patch after upgrading your Oracle Database to 11.2.0.2 or higher, the adpatch process fails with the following error in the adpatch log:
ATTENTION: All workers either have failed or are waiting:
FAILED: file fem_bal_nacc_hier_l2_mv.xdf on worker 1.
ATTENTION: Please fix the above failed worker(s) so the manager can continue.
The adworkxxx.log contains the following error on a materialized view create statement:
Materialized View Name is FEM_BAL_NACC_HIER_L2_MV Materialized View does not exist in the target database
Executing create Statement Create Statement is
CREATE MATERIALIZED VIEW "APPS"."FEM_BAL_NACC_HIER_L2_MV" ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 LOGGING STORAGE(INITIAL 4096 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) TABLESPACE "APPS_TS_SUMMARY" BUILD DEFERRED USING INDEX REFRESH FORCE ON DEMAND WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT ENABLE QUERY REWRITE AS select FEM_BAL_BASE_SUMMARY_MV.LEDGER_ID, FEM_BAL_BASE_SUMMARY_MV.DATASET_CODE, FEM_BAL_BASE_SUMMARY_MV.CAL_PERIOD_ID, FEM_BAL_BASE_SUMMARY_MV.CURRENCY_CODE, FEM_BAL_BASE_SUMMARY_MV.COMPANY_COST_CENTER_ORG_ID, FEM_BAL_BASE_SUMMARY_MV.COMPANY_COST_CENTER_ORG_VS_ID, FEM_DIS_NAT_ACCTS_HIER_B.VALUE_SET_ID, FEM_DIS_NAT_ACCTS_HIER_B.OBJECT_ID, FEM_DIS_NAT_ACCTS_HIER_B.OBJECT_DEFINITION_ID, FEM_DIS_NAT_ACCTS_HIER_B.LEVEL1_ID, FEM_DIS_NAT_ACCTS_HIER_B.LEVEL2_ID , FEM_DIS_NAT_ACCTS_HIER_B.VALUE_SET_ID NATURAL_ACCOUNT_VS_ID, SUM( FEM_BAL_BASE_SUMMARY_MV.XTD_BALANCE_E) XTD_BALANCE_E, SUM( FEM_BAL_BASE_SUMMARY_MV.XTD_BALANCE_F) XTD_BALANCE_F, SUM( FEM_BAL_BASE_SUMMARY_MV.QTD_BALANCE_E) QTD_BALANCE_E, SUM( FEM_BAL_BASE_SUMMARY_MV.QTD_BALANCE_F) QTD_BALANCE_F, SUM( FEM_BAL_BASE_SUMMARY_MV.YTD_BALANCE_E) YTD_BALANCE_E, SUM( FEM_BAL_BASE_SUMMARY_MV.YTD_BALANCE_F) YTD_BALANCE_F, SUM(PTD_CREDIT_BALANCE_E) PTD_CREDIT_BALANCE_E, SUM(PTD_DEBIT_BALANCE_E) PTD_DEBIT_BALANCE_E, SUM(YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E, SUM(YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E from FEM_DIS_NAT_ACCTS_HIER_B, FEM_BAL_BASE_SUMMARY_MV where FEM_DIS_NAT_ACCTS_HIER_B.VALUE_SET_ID = FEM_BAL_BASE_SUMMARY_MV.NATURAL_ACCOUNT_VS_ID AND FEM_DIS_NAT_ACCTS_HIER_B.LEVEL20_ID = FEM_BAL_BASE_SUMMARY_MV.NATURAL_ACCOUNT_ID GROUP BY FEM_BAL_BASE_SUMMARY_MV.LEDGER_ID, FEM_BAL_BASE_SUMMARY_MV.DATASET_CODE, FEM_BAL_BASE_SUMMARY_MV.CAL_PERIOD_ID, FEM_BAL_BASE_SUMMARY_MV.CURRENCY_CODE, FEM_BAL_BASE_SUMMARY_MV.COMPANY_COST_CENTER_ORG_ID, FEM_BAL_BASE_SUMMARY_MV.COMPANY_COST_CENTER_ORG_VS_ID, FEM_DIS_NAT_ACCTS_HIER_B.VALUE_SET_ID, FEM_DIS_NAT_ACCTS_HIER_B.OBJECT_ID, FEM_DIS_NAT_ACCTS_HIER_B.OBJECT_DEFINITION_ID, FEM_DIS_NAT_ACCTS_HIER_B.LEVEL1_ID, FEM_DIS_NAT_ACCTS_HIER_B.LEVEL2_ID, FEM_DIS_NAT_ACCTS_HIER_B.VALUE_SET_ID
Start time for statement above is Tue Jan 18 11:44:06 CST 2011 Exception occured ORA-54015: Duplicate column expression was specified ORA-06512: at "APPS.AD_MV", line 131 ORA-06512: at "APPS.AD_MV", line 289 ORA-06512: at line 1
java.sql.SQLException: ORA-54015: Duplicate column expression was specified ORA-06512: at "APPS.AD_MV", line 131 ORA-06512: at "APPS.AD_MV", line 289 ORA-06512: at line 1
Updating task with status 1
AD Worker error: The utility FndXdfCmp returned error for the above task.
AD Worker error: The above program failed. See the error messages listed above, if any, or see the log and output files for the program.
This issue stops the production upgrade.
Cause
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!